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

[Nicholas Bower]

> 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,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::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/2ed59dee/attachment.html>