Re: Performance of ST_OrderingEquals
Darafei "Komяpa" Praliaskouski <[email protected]> Thu, 11 Jun 2026 06:41:14 +0400
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <CAC8Q8tJGWV2YZC-qXKK-qZ92L0y_rvSk5QeBnNygqsjkedkzEQ@mail.gmail.com> |
--000000000000e6bd0c0653f14b67 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Paul Ramsey via postgis-users < [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 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=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 impr= ove 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= 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 ce= 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=A9= truire et > d'avertir l'exp=C3=A9diteur. > > > > Ville et Eurom=C3=A9tropole de Strasbourg > --000000000000e6bd0c0653f14b67 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Can we fix it using the same mechanics as ST_Intersects in= jecting the operators into the plan? Like <a href=3D"https://github.com/pos= tgis/postgis/pull/875">https://github.com/postgis/postgis/pull/875</a></div= ><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" clas= s=3D"gmail_attr">On Thu, Jun 4, 2026 at 7:19=E2=80=AFPM Paul Ramsey via pos= tgis-users <<a href=3D"mailto:[email protected]">postgis-use= [email protected]</a>> wrote:<br></div><blockquote class=3D"gmail_quote= " style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);= padding-left:1ex">The problem seems to be higher up. The actual implementat= ion of<br> ST_OrderingEquals calls into gserialized_cmp quite quickly and that<br> function is deliberately very very vast. I can actually make your<br> query even faster by just using "where d1.geom =3D d2.geom" which= <br> directly calls into gserialized_cmp. The issue is not the function,<br> but the plan. Using the =3D operator we get a HashJoin, using the WKB we<br= > get a MergeJoin, while using the ST_OrderingEquals function we get a<br> NestedLoopJoin, even if we push the cost of the joining function down<br> to nothing.<br> <br> ALTER FUNCTION ST_OrderingEquals (geometry, geometry) COST 0.00001;<br> <br> I'm not sure if there's any way around this, the NestedLoopJoin mig= ht<br> be a consequence of the join condition being a function rather than an<br> operator, it would take some digging to figure why PostgreSQL is<br> choosing it.<br> <br> On Mon, Jun 1, 2026 at 7:55=E2=80=AFAM MONTICOLO Julien<br> <<a href=3D"mailto:[email protected]" target=3D"_blank">Jul= [email protected]</a>> wrote:<br> ><br> > Hello everyone,<br> ><br> ><br> ><br> > I recently worked on a query to check duplicates.<br> ><br> > I initially used ST_OrderingEquals to find exact matches.<br> ><br> > But with a great number of geometries, the query takes a long time.<br= > ><br> > I changed the ST_OrderingEquals by comparison of WKB and this is a lot= faster.<br> ><br> ><br> ><br> > Here the code to reproduce. I generate a table with 20000 points in th= e RGF93 / Lambert-93, french main CRS.<br> ><br> ><br> ><br> > SELECT version() ;=C2=A0 -- PostgreSQL 16.9 on x86_64-pc-linux-gnu, co= mpiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26), 64-bit<br> ><br> > SELECT postgis_version();=C2=A0 -- 3.4 USE_GEOS=3D1 USE_PROJ=3D1 USE_S= TATS=3D1<br> ><br> ><br> ><br> > SELECT<br> ><br> >=C2=A0 =C2=A0 =C2=A0ROW_NUMBER() OVER()::BIGINT AS id,<br> ><br> >=C2=A0 =C2=A0 =C2=A0ST_POINT(<br> ><br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CEIL(100000 + RANDOM() * 1100000),<br= > ><br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CEIL(6000000 + RANDOM() * 1100000),<b= r> ><br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A02154<br> ><br> >=C2=A0 =C2=A0 =C2=A0)::GEOMETRY(POINT, 2154) AS geom<br> ><br> > INTO TEMPORARY TABLE my_point_table<br> ><br> > FROM<br> ><br> >=C2=A0 =C2=A0 =C2=A0GENERATE_SERIES(1, 20000)<br> ><br> > ;<br> ><br> ><br> ><br> > WITH pt_tab_with_dup AS (<br> ><br> > SELECT id, geom FROM my_point_table UNION ALL<br> ><br> > SELECT id * -1, geom FROM my_point_table TABLESAMPLE BERNOULLI (10)<br= > ><br> > )<br> ><br> > SELECT<br> ><br> >=C2=A0 =C2=A0 =C2=A0<a href=3D"http://d1.id" rel=3D"noreferrer" target= =3D"_blank">d1.id</a><br> ><br> > FROM<br> ><br> >=C2=A0 =C2=A0 =C2=A0pt_tab_with_dup d1,<br> ><br> >=C2=A0 =C2=A0 =C2=A0pt_tab_with_dup d2<br> ><br> > WHERE<br> ><br> >=C2=A0 =C2=A0 =C2=A0<a href=3D"http://d1.id" rel=3D"noreferrer" target= =3D"_blank">d1.id</a> > <a href=3D"http://d2.id" rel=3D"noreferrer" targ= et=3D"_blank">d2.id</a><br> ><br> >=C2=A0 =C2=A0 =C2=A0AND ST_OrderingEquals(d1.geom, d2.geom)<br> ><br> > ;=C2=A0 -- 2 min 36 sec<br> ><br> ><br> ><br> ><br> ><br> > WITH pt_tab_with_dup AS (<br> ><br> > SELECT id, geom FROM my_point_table UNION ALL<br> ><br> > SELECT id * -1, geom FROM my_point_table TABLESAMPLE BERNOULLI (10)<br= > ><br> > )<br> ><br> > SELECT<br> ><br> >=C2=A0 =C2=A0 =C2=A0<a href=3D"http://d1.id" rel=3D"noreferrer" target= =3D"_blank">d1.id</a><br> ><br> > FROM<br> ><br> >=C2=A0 =C2=A0 =C2=A0pt_tab_with_dup d1,<br> ><br> >=C2=A0 =C2=A0 =C2=A0pt_tab_with_dup d2<br> ><br> > WHERE<br> ><br> >=C2=A0 =C2=A0 =C2=A0<a href=3D"http://d1.id" rel=3D"noreferrer" target= =3D"_blank">d1.id</a> > <a href=3D"http://d2.id" rel=3D"noreferrer" targ= et=3D"_blank">d2.id</a><br> ><br> >=C2=A0 =C2=A0 =C2=A0AND ST_AsBinary(d1.geom) =3D ST_AsBinary(d2.geom)<b= r> ><br> > ;=C2=A0 -- 0.153 sec<br> ><br> ><br> ><br> ><br> ><br> > I think it=E2=80=99s correct.<br> ><br> > Are there any cases where it doesn=E2=80=99t work ? If so, why not imp= rove the ST_OrderingEquals by comparing the WKB ?<br> ><br> ><br> ><br> > Kind regards,<br> ><br> > Julien Monticolo<br> ><br> ><br> ><br> ><br> ><br> > Ce message est =C3=A9tabli =C3=A0 usage exclusif de son destinataire.<= br> > Toute utilisation ou diffusion, partielle ou totale, doit =C3=AAtre pr= =C3=A9alablement autoris=C3=A9e.<br> ><br> > Tout message =C3=A9lectronique est susceptible d'alt=C3=A9ration e= t son int=C3=A9grit=C3=A9 ne peut =C3=AAtre assur=C3=A9e.<br> > L'exp=C3=A9diteur d=C3=A9cline toute responsabilit=C3=A9 au titre = de ce message s'il a =C3=A9t=C3=A9 modifi=C3=A9 ou falsifi=C3=A9.<br> ><br> > Si vous n'=C3=AAtes pas destinataire de ce message, merci de le d= =C3=A9truire et d'avertir l'exp=C3=A9diteur.<br> ><br> > Ville et Eurom=C3=A9tropole de Strasbourg<br> </blockquote></div> --000000000000e6bd0c0653f14b67--