[postgis-users] Point as Index

[Sandro Santilli]

On Wed, Mar 30, 2011 at 10:58:57AM +0100, Ioannis Anagnostopoulos wrote:

> I am involved in a heavy database design initiative where the only kind 
> of geometries I am dealing with are points. I have recently hit a 
> 50million rows long table with those points and my default gist index on 
> the points does not seem to be working very fast (if not at all to be 
> honest). I have started now thinking that probably for "points" an index 
> may not be the best option since in a 50million rows long table most of 
> the points are unique so the index may just duplicate the actual table, 
> of course I may be wrong and I may just missing a very important part of 
> the concept. 

How many points from the 50M set does your tipical query hits ?
Does PostgreSQL selectivity estimator make a good guess about that ?
Use EXPLAIN ANALYZE <your query here> to see.

--strk; 

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html