Fwd: Performance of ST_OrderingEquals

[Paul Ramsey]

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 = d2.geom" which
directly calls into gserialized_cmp. The issue is not the function,
but the plan. Using the = 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 AM MONTICOLO Julien
<Julien.MONTICOLO at strasbourg.eu> 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 faster.
>
>
>
> Here the code to reproduce. I generate a table with 20000 points in the RGF93 / Lambert-93, french main CRS.
>
>
>
> SELECT version() ;  -- PostgreSQL 16.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26), 64-bit
>
> SELECT postgis_version();  -- 3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>
>
>
> 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) = ST_AsBinary(d2.geom)
>
> ;  -- 0.153 sec
>
>
>
>
>
> I think it’s correct.
>
> Are there any cases where it doesn’t work ? If so, why not improve the ST_OrderingEquals by comparing the WKB ?
>
>
>
> Kind regards,
>
> Julien Monticolo
>
>
>
>
>
> Ce message est établi à usage exclusif de son destinataire.
> Toute utilisation ou diffusion, partielle ou totale, doit être préalablement autorisée.
>
> Tout message électronique est susceptible d'altération et son intégrité ne peut être assurée.
> L'expéditeur décline toute responsabilité au titre de ce message s'il a été modifié ou falsifié.
>
> Si vous n'êtes pas destinataire de ce message, merci de le détruire et d'avertir l'expéditeur.
>
> Ville et Eurométropole de Strasbourg