<div dir="ltr">Can we fix it using the same mechanics as ST_Intersects injecting the operators into the plan? Like <a href="https://github.com/postgis/postgis/pull/875">https://github.com/postgis/postgis/pull/875</a></div><br><div class="gmail_quote gmail_quote_container"><div dir="ltr" class="gmail_attr">On Thu, Jun 4, 2026 at 7:19 PM Paul Ramsey via postgis-users <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="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 implementation 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 = d2.geom" which<br>
directly calls into gserialized_cmp. The issue is not the function,<br>
but the plan. Using the = 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 might<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 AM MONTICOLO Julien<br>
<<a href="mailto:Julien.MONTICOLO@strasbourg.eu" target="_blank">Julien.MONTICOLO@strasbourg.eu</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 the RGF93 / Lambert-93, french main CRS.<br>
><br>
><br>
><br>
> 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<br>
><br>
> SELECT postgis_version(); -- 3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1<br>
><br>
><br>
><br>
> SELECT<br>
><br>
> ROW_NUMBER() OVER()::BIGINT AS id,<br>
><br>
> ST_POINT(<br>
><br>
> CEIL(100000 + RANDOM() * 1100000),<br>
><br>
> CEIL(6000000 + RANDOM() * 1100000),<br>
><br>
> 2154<br>
><br>
> )::GEOMETRY(POINT, 2154) AS geom<br>
><br>
> INTO TEMPORARY TABLE my_point_table<br>
><br>
> FROM<br>
><br>
> GENERATE_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>
> <a href="http://d1.id" rel="noreferrer" target="_blank">d1.id</a><br>
><br>
> FROM<br>
><br>
> pt_tab_with_dup d1,<br>
><br>
> pt_tab_with_dup d2<br>
><br>
> WHERE<br>
><br>
> <a href="http://d1.id" rel="noreferrer" target="_blank">d1.id</a> > <a href="http://d2.id" rel="noreferrer" target="_blank">d2.id</a><br>
><br>
> AND ST_OrderingEquals(d1.geom, d2.geom)<br>
><br>
> ; -- 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>
> <a href="http://d1.id" rel="noreferrer" target="_blank">d1.id</a><br>
><br>
> FROM<br>
><br>
> pt_tab_with_dup d1,<br>
><br>
> pt_tab_with_dup d2<br>
><br>
> WHERE<br>
><br>
> <a href="http://d1.id" rel="noreferrer" target="_blank">d1.id</a> > <a href="http://d2.id" rel="noreferrer" target="_blank">d2.id</a><br>
><br>
> AND ST_AsBinary(d1.geom) = ST_AsBinary(d2.geom)<br>
><br>
> ; -- 0.153 sec<br>
><br>
><br>
><br>
><br>
><br>
> I think it’s correct.<br>
><br>
> Are there any cases where it doesn’t work ? If so, why not improve 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 établi à usage exclusif de son destinataire.<br>
> Toute utilisation ou diffusion, partielle ou totale, doit être préalablement autorisée.<br>
><br>
> Tout message électronique est susceptible d'altération et son intégrité ne peut être assurée.<br>
> L'expéditeur décline toute responsabilité au titre de ce message s'il a été modifié ou falsifié.<br>
><br>
> Si vous n'êtes pas destinataire de ce message, merci de le détruire et d'avertir l'expéditeur.<br>
><br>
> Ville et Eurométropole de Strasbourg<br>
</blockquote></div>