Performance of ST_OrderingEquals

[Paul Ramsey]

That seems like it would work, but then I wonder if changing
ST_OrderingEquals(A, B) to a SQL language function that just does A =
B would work just as well? PostgreSQL generally inlines SQL functions.

P.

On Wed, Jun 10, 2026 at 7:41 PM Darafei "Komяpa" Praliaskouski
<me at komzpa.net> wrote:
>
> 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