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

[Nicholas Bower]

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




On 25 May 2010 22:45, Nicholas Bower <nick at petangent.net> wrote:

>
>
> On 25 May 2010 22:35, Mark Cave-Ayland <mark.cave-ayland at siriusit.co.uk>wrote:
>
>> Nicholas Bower wrote:
>>
>>  Neither of the ST_Intersects clauses below invoke index usage according
>>> to explain output, despite docs saying they should automatically be doing
>>> bbox on the index;
>>>
>>
>> (cut)
>>
>>
>>  What's going on - the difference in total cost above proves to me the
>>> indexes are not being used.
>>>
>>
>> I think you're missing the point here; the aim of the planner is to work
>> out which join order will produce the query that executes in the shortest
>> time. Therefore just because an index is present does not necessarily mean
>> it is correct to use it.
>>
>> From your first query (where the spatial index is not being used):
>>
>> Total runtime: 44744.241 ms
>>
>> From your second query (where the spatial index is being used):
>>
>> Total runtime: 65952.140 ms
>>
>> So I'd say that this is working exactly as it should be, since the join
>> order chosen by the planner has resulted in the shortest query time.
>>
>
> A repeated explain analyze on the first query showed about 45s second time
> around (same).  However for the && version, second time around was just 2s
> (from 65s).
>
> Saving 15s and losing caching seems like a bit of a false win no?
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100525/e543cf35/attachment.html>