Nicholas Bower wrote:
But simply swapping the query region above from geometry to geography
we're back to no index usage,
explain analyze select count(*) from wastac.t_tile_geometry_old where
ST_Intersects(border,
ST_GeographyFromText('SRID=4316;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'));
Aggregate (cost=362164.77..362164.78 rows=1 width=0) (actual
time=80302.237..80302.237 rows=1 loops=1) -> Seq Scan on
t_tile_geometry_old (cost=0.00..361101.11 rows=425463 width=0) (actual
time=19680.252..80302.172 rows=95 loo
ps=1)
Filter: st_intersects((border)::geography,
'0103000020DC1000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geogra
phy)
Total runtime: 80302.285 ms
Right. So the give away in the above query is the cast in the
st_intersects() filter; it looks as if your border column in
wastac.t_tile_geometry_old is a *geometry* while your intersection
geometry is a *geography*. Since PostgreSQL sees that you are trying to
perform an operation on two different types, it cannot make use of the
indexes on those two columns. Hence it performs a sequential scan across
the whole table converting on the fly, which is going to take a lot more
time.
Try making a copy of your wastac.t_tile_geometry_old table but with a
geography instead of geometry column for border, and you should see an
improvement.
HTH,
Mark.
--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063
Sirius Labs: http://www.siriusit.co.uk/labs
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
