[postgis-devel] ST_OrderingEquals vs ~=

[Paragon Corporation]

If I change ST_OrderingEquals to 

CREATE OR REPLACE FUNCTION st_orderingequals(geometry, geometry)
  RETURNS boolean AS
$BODY$ 
    SELECT  $1 ~= $2
	$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT
  ;

It starts using the index, but I guess for some reason the $1 && $2 is not
only redundant but preventing it from using the index path (confusing the
planner?).  Does anyone have an explanation for this odd behavior or
Does it not always happen?

I've tested this on 8.2.6 with PostGIS 1.3.6, 8.3 PostGIS 1.3.6 as well as
8.4RC1 PostGIS 1.4 and seems to exhibit the same peculiar behavior in all.
The 8.2.6 one I tested is using a completely different data set.

Thanks,
Regina

 

-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net
[mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Paragon
Corporation
Sent: Sunday, June 21, 2009 3:05 AM
To: 'PostGIS Development Discussion'
Subject: Re: [postgis-devel] ST_OrderingEquals vs ~=


> 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#L15
> 2

> 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.




_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-devel