[postgis-users] No index usage on geography query plan?

[Paragon Corporation]

Nick,
Okay we are seeing the same issue with our fastfoods data even with smaller
windows.  I think the clue is  the plan here.
 
The ST_Intersects geography function seems to be treated as a blackbox
rather than a transparent function composed of && and _ST_Distance
 
The magic of the geometry ST_Intersects is that the query plan can see into
the function and decomposes it into
&& + _ST_Intersects
 
For some reason, that's just not happening with geography.  Maybe Paul or
Mark have a clue why that is.
 
So I bet this uses an index and is much faster
select count(*) from wastac.t_tile_geometry where
border && ST_GeographyFromText('SRID=4326;POLYGON((116.751709

-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))') AND ST_Intersects(border,
ST_GeographyFromText('SRID=4326;POLYGON((116.751709 -31.381779,116.883545
-32.676373,114.741211 -32.796510,114.796143-31.316101,116.751709
-31.381779))'));
 
Thanks
Regina  and Leo
h <http://www.postgis.us> ttp://www.postgis.us

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Nicholas
Bower
Sent: Wednesday, May 26, 2010 6:42 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?


 

That does look like a pretty huge bounding polygon, but the geography we
agree should still be using the spatial index, so probably making the index
cost higher than it should



Fyi the border values are are simply composed of a regular 20km grid of
ajoining polygon squares covering Australia (about 1.3M tiles).
Incidentally, with the border of tiles being square, the bounding box *is*
the shape I guess.  It's not relevant, but the query returns a count 95 of
these 20km tiles.

We're dealing with satellite data - the datasets themselves have far bigger
coverages (different table entirely), but this is why you've noticed the
"region of interest" in the query is large.



Nick,

Can you just for contrast, try to force it to use the index by doing

set enable_seqscan = off;

 explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709

-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'));



Strange - no difference.

wastac=> set enable_seqscan = off;
SET
wastac=> explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211
-32.796510,114.796143-31.316101,116.751709 -31.381779))'));
 
QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------
 Aggregate  (cost=10000364056.80..10000364056.81 rows=1 width=0) (actual
time=8909.585..8909.586 rows=1 loops=1)
   ->  Seq Scan on t_tile_geometry  (cost=10000000000.00..10000362993.14
rows=425463 width=0) (actual time=2028.827..8909.533 rows=95 loops=1)
         Filter: st_intersects(border,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC
1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
 Total runtime: 8909.626 ms
(4 rows)

 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100527/08c32738/attachment.html>