Re: Performance of ST_OrderingEquals

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 =3D
B would work just as well? PostgreSQL generally inlines SQL functions.

P.

On Wed, Jun 10, 2026 at 7:41=E2=80=AFPM Darafei "Kom=D1=8Fpa" Praliaskouski
<[email protected]> wrote:
>
> Can we fix it using the same mechanics as ST_Intersects injecting the ope=
rators into the plan? Like https://github.com/postgis/postgis/pull/875
>
> On Thu, Jun 4, 2026 at 7:19=E2=80=AFPM Paul Ramsey via postgis-users <pos=
[email protected]> 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 =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=
 faster.
>> >
>> >
>> >
>> > Here the code to reproduce. I generate a table with 20000 points in th=
e RGF93 / Lambert-93, french main CRS.
>> >
>> >
>> >
>> > SELECT version() ;  -- PostgreSQL 16.9 on x86_64-pc-linux-gnu, compile=
d by 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 imp=
rove 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 so=
n int=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 c=
e message 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=
=A9truire et d'avertir l'exp=C3=A9diteur.
>> >
>> > Ville et Eurom=C3=A9tropole de Strasbourg