[postgis-devel] ST_OrderingEquals vs ~=

[Kevin Neufeld]

Paragon Corporation wrote:
> You sure it doesn't.  If it doesn't then our documentation is wrong
>
> http://postgis.refractions.net/documentation/manual-svn/ST_Geometry_Same.htm
> l
>
> Also I thought it did because when I do this
>
> SELECT * FROM
> tbl_feature_data WHERE the_geom ~=
> '0101000020E610000009D31947329B5DC059448510E3A84240'
>   
Hmm.  Yeah, your right.  I was obviously missreading the code.

The ~= operator uses the procedure ST_geometry_same
http://trac.osgeo.org/postgis/browser/trunk/postgis/postgis.sql.in.c#L972

The ST_geometry_same function simply exposes 'LWGEOM_same'  (note that 
it does not expose 'LWGEOM_gist_same')
http://trac.osgeo.org/postgis/browser/trunk/postgis/postgis.sql.in.c#L911

The LWGEOM_same C function is defined as a basic function, not a gist 
function.
http://trac.osgeo.org/postgis/browser/trunk/postgis/lwgeom_functions_basic.c#L2669

But I see that ~= is listed as a gist operator op
http://trac.osgeo.org/postgis/browser/trunk/postgis/postgis.sql.in.c#L1042

It's curious that ~= is using gist indexing but it's exposing 
LWGEOM_same - which has nothing to do with gist. ... I think.
Is it because behind the scenes the operands are automagically converted 
to box2d where LWGEOM_gist_same is invoked?

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