[postgis-users] Query performanace against huge point table

[Kevin Neufeld]

Yes, clustering your table based on your GIST index does help because it 
physically orders the rows in your table based on the spatial index.  
The disk head won't have far to move to retrieve the next page. 

I've worked with a table of about 170 million points (not as large as 
your 300m :) ) before and I was running into the same problem.  We were 
able to significantly speed up index queries by grouping points in sets 
of about 100 - 200 based on some arbitrary grid.  Instead of having an 
index entry for every point, now you have one entry for every 100 
points, reducing the size of your index by 100!  Of course, you've added 
the complexity of having to deal with MultiPoints, but depending on your 
use cases, this may or may not be an issue.
ie.
CREATE TABLE new_pt_tbl AS
SELECT collect(geom) AS my_multi_pt
FROM pt_tbl
GROUP BY ST_SnapToGrid(geom, <grid_size>);

Cheers,
Kevin

Santosh Gaikwad wrote:
> Hi Kevin, 
>
> Thank you very much for your help. I created now the functional index of
> "transform(the_geom,2163)" on my geometry column. It is faster now. I
> would like to know whether clustering of indices helps in speeding up
> the query.
>
> Thanks & Regards,
>
> Santosh Gaikwad
> Senior Software Developer
> Saama Technologies (India) Pvt. Ltd.,
> Unit No.101-102, First floor,
> Weikfield IT CITI INFO PARK, 
> Weikfield estates, 
> Pune Nagar Road,
> Pune - 411 014. India
> Phone : +91 20 66071397
> Mobile: +91-9422005927
> E-mail :Santosh.Gaikwad at saama.com
> http://www.saama.com
>   
>