Performance of ST_OrderingEquals
MONTICOLO Julien <[email protected]> Mon, 1 Jun 2026 14:47:51 +0000
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <[email protected]> |
--===============4356866498483918364==
Content-Language: fr-FR
Content-Type: multipart/alternative;
boundary="_000_9ea6f81cb2c148dab4de47041f98e61bstrasbourgeu_"
--_000_9ea6f81cb2c148dab4de47041f98e61bstrasbourgeu_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
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 fast=
er.
Here the code to reproduce. I generate a table with 20000 points in the RGF=
93 / 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's correct.
Are there any cases where it doesn't work ? If so, why not improve the ST_O=
rderingEquals by comparing the WKB ?
Kind regards,
Julien Monticolo
--_000_9ea6f81cb2c148dab4de47041f98e61bstrasbourgeu_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"FR" link=3D"#0563C1" vlink=3D"#954F72">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">Hello everyone,<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">I recently worked on a query to check duplicates.<o:=
p></o:p></p>
<p class=3D"MsoNormal">I initially used ST_OrderingEquals to find exact mat=
ches.<o:p></o:p></p>
<p class=3D"MsoNormal">But with a great number of geometries, the query tak=
es a long time.<o:p></o:p></p>
<p class=3D"MsoNormal">I changed the ST_OrderingEquals by comparison of WKB=
and this is a lot faster.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">Here the code to reproduce. I generate a table with =
20000 points in the RGF93 / Lambert-93, french main CRS.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">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<o:p></o:p></p>
<p class=3D"MsoNormal">SELECT postgis_version(); -- 3.4 USE_GEOS=3D1 =
USE_PROJ=3D1 USE_STATS=3D1<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">SELECT<o:p></o:p></p>
<p class=3D"MsoNormal"> ROW_NUMBER() OVER()::BIGINT AS id=
,<o:p></o:p></p>
<p class=3D"MsoNormal"> ST_POINT(<o:p></o:p></p>
<p class=3D"MsoNormal"> CEIL(1000=
00 + RANDOM() * 1100000),<o:p></o:p></p>
<p class=3D"MsoNormal"> CEIL(6000=
000 + RANDOM() * 1100000),<o:p></o:p></p>
<p class=3D"MsoNormal"> 2154<o:p>=
</o:p></p>
<p class=3D"MsoNormal"> )::GEOMETRY(POINT, 2154) AS geom<=
o:p></o:p></p>
<p class=3D"MsoNormal">INTO TEMPORARY TABLE my_point_table<o:p></o:p></p>
<p class=3D"MsoNormal">FROM<o:p></o:p></p>
<p class=3D"MsoNormal"> GENERATE_SERIES(1, 20000)<o:p></o=
:p></p>
<p class=3D"MsoNormal">;<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">WITH pt_tab_with_dup AS (<o:p></o:p></p>
<p class=3D"MsoNormal">SELECT id, geom FROM my_point_table UNION ALL<o:p></=
o:p></p>
<p class=3D"MsoNormal">SELECT id * -1, geom FROM my_point_table TABLESAMPLE=
BERNOULLI (10)<o:p></o:p></p>
<p class=3D"MsoNormal">)<o:p></o:p></p>
<p class=3D"MsoNormal">SELECT<o:p></o:p></p>
<p class=3D"MsoNormal"> d1.id<o:p></o:p></p>
<p class=3D"MsoNormal">FROM<o:p></o:p></p>
<p class=3D"MsoNormal"> pt_tab_with_dup d1,<o:p></o:p></p=
>
<p class=3D"MsoNormal"> pt_tab_with_dup d2<o:p></o:p></p>
<p class=3D"MsoNormal">WHERE<o:p></o:p></p>
<p class=3D"MsoNormal"> d1.id > d2.id<o:p></o:p></p>
<p class=3D"MsoNormal"> AND ST_OrderingEquals(d1.geom, d2=
.geom)<o:p></o:p></p>
<p class=3D"MsoNormal">; -- 2 min 36 sec<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">WITH pt_tab_with_dup AS (<o:p></o:p></p>
<p class=3D"MsoNormal">SELECT id, geom FROM my_point_table UNION ALL<o:p></=
o:p></p>
<p class=3D"MsoNormal">SELECT id * -1, geom FROM my_point_table TABLESAMPLE=
BERNOULLI (10)<o:p></o:p></p>
<p class=3D"MsoNormal">)<o:p></o:p></p>
<p class=3D"MsoNormal">SELECT<o:p></o:p></p>
<p class=3D"MsoNormal"> d1.id<o:p></o:p></p>
<p class=3D"MsoNormal">FROM<o:p></o:p></p>
<p class=3D"MsoNormal"> pt_tab_with_dup d1,<o:p></o:p></p=
>
<p class=3D"MsoNormal"> pt_tab_with_dup d2<o:p></o:p></p>
<p class=3D"MsoNormal">WHERE<o:p></o:p></p>
<p class=3D"MsoNormal"> d1.id > d2.id<o:p></o:p></p>
<p class=3D"MsoNormal"> AND ST_AsBinary(d1.geom) =3D ST_A=
sBinary(d2.geom)<o:p></o:p></p>
<p class=3D"MsoNormal">; -- 0.153 sec<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">I think it’s correct.<o:p></o:p></p>
<p class=3D"MsoNormal">Are there any cases where it doesn’t work =
;? If so, why not improve the ST_OrderingEquals by comparing the WKB ?=
<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">Kind regards,<o:p></o:p></p>
<p class=3D"MsoNormal">Julien Monticolo<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>
--_000_9ea6f81cb2c148dab4de47041f98e61bstrasbourgeu_--
--===============4356866498483918364==
Content-Type: multipart/alternative; boundary="===============5079697678729027460=="
MIME-Version: 1.0
Content-Disposition: inline
--===============5079697678729027460==
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
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 mes=
sage 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=A9trui=
re et d'avertir l'exp=C3=A9diteur.
Ville et Eurom=C3=A9tropole de Strasbourg
--===============5079697678729027460==
Content-Type: text/html; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
<br><br>
<div style=3D"font-size:8pt; line-height:10pt; font-family: 'Calibri','Aria=
l','sans-serif'; background-color:#D5EAFF; border:1px dotted #003333; paddi=
ng:.6em; ">
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 et 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 me=
ssage 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=A9trui=
re et d'avertir l'exp=C3=A9diteur.<br>
<br>
<span style=3D"padding-top:10px; font-weight:bold; font-size:10pt; font-fam=
ily: 'Calibri','Arial','sans-serif'; ">Ville et Eurom=C3=A9tropole de Stras=
bourg</span>
--===============5079697678729027460==--
--===============4356866498483918364==--