[postgis-devel] ST_OrderingEquals vs ~=

[Paragon Corporation]

> Anywho, back to your original post.  It does appear that ST_OrderingEquals
is invoking the index twice.
> http://trac.osgeo.org/postgis/browser/trunk/postgis/sqlmm.sql.in.c#L152

> But then, I can't seem to make it work at all.

> kneufeld=# explain analyze select * from pts where the_geom ~= 
'010100000080B75585372032413885390ABF8A2441';


                                                    QUERY 
> PLAN                                                    
----------------------------------------------------------------------------
---------------------------------------
 Index Scan using pts_geom_idx on pts (cost=0.00..8.49 rows=1 width=88)
(actual time=9.469..9.657 rows=1 loops=1)
   Index Cond: (the_geom ~=
'010100000080B75585372032413885390ABF8A2441'::geometry)
 Total runtime: 9.968 ms
(3 rows)

kneufeld=# explain analyze select * from pts where
st_orderingequals(the_geom, 
'010100000080B75585372032413885390ABF8A2441');    
                                                QUERY 
> PLAN                                                
>
----------------------------------------------------------------------------
-------------------------------
 Seq Scan on pts (cost=0.00..266343.74 rows=333300 width=88) (actual
> time=0.076..9472.457 rows=1 loops=1)
>   Filter: st_orderingequals(the_geom,
>'010100000080B75585372032413885390ABF8A2441'::geometry)
> Total runtime: 9472.499 ms
> (3 rows)

> -- Kevin

Kevin,

Oh My, ST_orderingEquals for some reason is forcing a table scan.  It looks
like your st_orderingequals is not using an index -- doing a table scan, but
your 
~= is using the index.  On mine if I run ~= its super fast -- returns one
record out of about 1 million records and in 61ms definitely using index. I
tried on bigger geoms as well.
If I do ST_OrderingEquals -- it appears to be doing a table scan like yours
and takes whopping 21 seconds to return the same answer.