Fwd: Performance of ST_OrderingEquals

The problem seems to be higher up. The actual implementation of
ST_OrderingEquals calls into gserialized_cmp quite quickly and that
function is deliberately very very vast. I can actually make your
query even faster by just using "where d1.geom =3D d2.geom" which
directly calls into gserialized_cmp. The issue is not the function,
but the plan. Using the =3D operator we get a HashJoin, using the WKB we
get a MergeJoin, while using the ST_OrderingEquals function we get a
NestedLoopJoin, even if we push the cost of the joining function down
to nothing.

ALTER FUNCTION ST_OrderingEquals (geometry, geometry) COST 0.00001;

I'm not sure if there's any way around this, the NestedLoopJoin might
be a consequence of the join condition being a function rather than an
operator, it would take some digging to figure why PostgreSQL is
choosing it.

On Mon, Jun 1, 2026 at 7:55=E2=80=AFAM MONTICOLO Julien
<[email protected]> wrote:
>
> Hello everyone,
>
>
>
> I recently worked on a query to check duplicates.
>
> I initially used ST_OrderingEquals to find exact matches.
>
> But with a great number of geometries, the query takes a long time.
>
> I changed the ST_OrderingEquals by comparison of WKB and this is a lot fa=
ster.
>
>
>
> Here the code to reproduce. I generate a table with 20000 points in the R=
GF93 / Lambert-93, french main CRS.
>
>
>
> SELECT version() ;  -- PostgreSQL 16.9 on x86_64-pc-linux-gnu, compiled b=
y gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26), 64-bit
>
> SELECT postgis_version();  -- 3.4 USE_GEOS=3D1 USE_PROJ=3D1 USE_STATS=3D1
>
>
>
> SELECT
>
>     ROW_NUMBER() OVER()::BIGINT AS id,
>
>     ST_POINT(
>
>         CEIL(100000 + RANDOM() * 1100000),
>
>         CEIL(6000000 + RANDOM() * 1100000),
>
>         2154
>
>     )::GEOMETRY(POINT, 2154) AS geom
>
> INTO TEMPORARY TABLE my_point_table
>
> FROM
>
>     GENERATE_SERIES(1, 20000)
>
> ;
>
>
>
> WITH pt_tab_with_dup AS (
>
> SELECT id, geom FROM my_point_table UNION ALL
>
> SELECT id * -1, geom FROM my_point_table TABLESAMPLE BERNOULLI (10)
>
> )
>
> SELECT
>
>     d1.id
>
> FROM
>
>     pt_tab_with_dup d1,
>
>     pt_tab_with_dup d2
>
> WHERE
>
>     d1.id > d2.id
>
>     AND ST_OrderingEquals(d1.geom, d2.geom)
>
> ;  -- 2 min 36 sec
>
>
>
>
>
> WITH pt_tab_with_dup AS (
>
> SELECT id, geom FROM my_point_table UNION ALL
>
> SELECT id * -1, geom FROM my_point_table TABLESAMPLE BERNOULLI (10)
>
> )
>
> SELECT
>
>     d1.id
>
> FROM
>
>     pt_tab_with_dup d1,
>
>     pt_tab_with_dup d2
>
> WHERE
>
>     d1.id > d2.id
>
>     AND ST_AsBinary(d1.geom) =3D ST_AsBinary(d2.geom)
>
> ;  -- 0.153 sec
>
>
>
>
>
> I think it=E2=80=99s correct.
>
> Are there any cases where it doesn=E2=80=99t work ? If so, why not improv=
e the ST_OrderingEquals by comparing the WKB ?
>
>
>
> Kind regards,
>
> Julien Monticolo
>
>
>
>
>
> Ce message est =C3=A9tabli =C3=A0 usage exclusif de son destinataire.
> Toute utilisation ou diffusion, partielle ou totale, doit =C3=AAtre pr=C3=
=A9alablement autoris=C3=A9e.
>
> Tout message =C3=A9lectronique est susceptible d'alt=C3=A9ration et son i=
nt=C3=A9grit=C3=A9 ne peut =C3=AAtre assur=C3=A9e.
> L'exp=C3=A9diteur d=C3=A9cline toute responsabilit=C3=A9 au titre de ce m=
essage s'il a =C3=A9t=C3=A9 modifi=C3=A9 ou falsifi=C3=A9.
>
> Si vous n'=C3=AAtes pas destinataire de ce message, merci de le d=C3=A9tr=
uire et d'avertir l'exp=C3=A9diteur.
>
> Ville et Eurom=C3=A9tropole de Strasbourg