Performance of ST_OrderingEquals

[Darafei "Komяpa" Praliaskouski]

Can we fix it using the same mechanics as ST_Intersects injecting the
operators into the plan? Like https://github.com/postgis/postgis/pull/875

On Thu, Jun 4, 2026 at 7:19 PM Paul Ramsey via postgis-users <
postgis-users at lists.osgeo.org> wrote:

> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20260611/50a6d5e0/attachment.htm>