[postgis-users] Postgresql not using Spatial Index

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of dblasby at openplans.org
> Sent: 10 August 2005 16:43
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Postgresql not using Spatial Index
> 
> 
> I'm trying to get postgresql to use the spatial index, but 
> I'm having an awful time.
> 
> For example, in the included query postgresql wants to use a 
> sequential scan instead of an index scan.  The table has just 
> under 100,000 rows, and the estimate (and actual) number of 
> returned rows is 4,400 (5%). 
> It seems a bit excessive to do a sequential scan for 5% of the table!
> 
> I changed my random_page_cost from 4 to 2 to try to get it to 
> prefer the index, but its still no go!


Hi Dave,

Have you tried setting the effective_cache_size parameter for your laptop as
well? This is used to calculate how many tuples are cached outside of shared
buffers (i.e. incur very little disk I/O). I generally find setting
effective_cache_size correctly and reducing random_page_cost to 2 solves
nearly all index problems.

I also seem to remember reading somewhere on -hackers recently that 5% was
the fixed threshold between index and sequential scans (determined by
PostgreSQL), but I can't find a reference to it in the archives at the
moment.


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Tamar Science Park
Plymouth
PL6 8BT 

T: +44 (0)1752 797131
F: +44 (0)1752 791023
W: http://www.webbased.co.uk