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

[Mark Cave-Ayland]

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