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

[Paragon Corporation]

Mark,

He did include it in an earlier email

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,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE77280A
F46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::g
eography)
 Total runtime: 8854.976 ms
(4 rows)

That does look like a pretty huge bounding polygon, but the geography we
agree should still be using the spatial index, so probably making the index
cost higher than it should

Nick,

Can you just for contrast, try to force it to use the index by doing

set enable_seqscan = off;

 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))'));

That should force it to use the index unless it can't for some other reason.

Leo and Regina,
http://www.postgis.us



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mark
Cave-Ayland
Sent: Wednesday, May 26, 2010 8:44 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?

Nick Bower wrote:

>> 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.
> 
> That's precisely what I showed in the original post - geography 
> intersecting geography column. See the table def. I was outlining in 
> this latter geometry example that a 9s intersection is not right and 
> there is a problem with geograhies.

Okay - so can we see the explain analyze output for your last query for the
geography intersecting geography case for comparison?


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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users