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

[Mark Cave-Ayland]

Paragon Corporation wrote:

> 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

Right. I'd quite like to see the explain analyze plan generated when 
performing the ST_Intersects() substitution manually, e.g.

explain analyze 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_Distance(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))'), 0.0, false) < 0.00001;

This should give us a better idea as to why this particular plan is 
being chosen.


ATB,

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