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

[Mark Cave-Ayland]

Nicholas Bower wrote:

> Perhaps this makes it more obvious - 9s to query a table of just 1.3M 
> rows with ST_Intersects and 20ms using &&.
> 
> 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))'));
>  
>  Aggregate  (cost=364056.80..364056.81 rows=1 width=0) (actual 
> time=9175.760..9175.761 rows=1 loops=1)
>    ->  Seq Scan on t_tile_geometry  (cost=0.00..362993.14 rows=425463 
> width=0) (actual time=2164.049..9175.706 rows=95 loops=1)
>          Filter: st_intersects(border, 
> '0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280A
> F46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
>  Total runtime: 9175.859 ms
> (4 rows)
> 
> 
> 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))'));
> 
>  Aggregate  (cost=364056.80..364056.81 rows=1 width=0) (actual 
> time=8854.932..8854.933 rows=1 loops=1)
>    ->  Seq Scan on t_tile_geometry  (cost=0.00..362993.14 rows=425463 
> width=0) (actual time=2017.629..8854.879 rows=95 loops=1)
>          Filter: st_intersects(border, 
> '0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280A
> F46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
>  Total runtime: 8854.976 ms
> (4 rows)
> 
> 
> wastac=> 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))');
> 
>  Aggregate  (cost=28.99..29.00 rows=1 width=0) (actual 
> time=24.443..24.443 rows=1 loops=1)
>    ->  Index Scan using t_tile_geometry_border_key on t_tile_geometry 
>  (cost=0.00..28.97 rows=6 width=0) (actual time=9.952..24.407 rows=112 
> loops=1)
>          Index Cond: (border && 
> '0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C
> 01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
>  Total runtime: 24.681 ms

Right, because && is an index-only bounding box search (and so doesn't 
require access to the heap unless you are using an older version of 
PostGIS) whereas ST_Intersects() calculates the intersection predicate 
based upon all points within the geometry (time consuming if they have 
lots of points).

Incidentally, you've missed two of the most important bits of 
information - the version of PostgreSQL and the version of PostGIS you 
are using.

What does the output of:

SELECT version(), postgis_full_version();

return?

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