Performance of ST_OrderingEquals

[MONTICOLO Julien]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20260601/d6517b13/attachment.htm>
-------------- next part --------------
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20260601/d6517b13/attachment-0001.htm>