> 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)
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
