[postgis-users] Partitioning spatial table

[Bill Binko]

On Sat, 2006-02-18 at 15:13 -0800, Paul Ramsey wrote:
> On 18-Feb-06, at 3:09 PM, Markus Schaber wrote:
> > But I don't know wether GIST indices can be multicolumn yet, last  
> > time I
> > tried it did not work as expected. (But thas was for PostGIS 0.9 and
> > PostgreSQL 7.4, so it may be much better now.)
> 
> They do, but both components have to be GiST.  So if you want an  
> index ( geometry, integer ) you need a GiST index for integers.   
> There are btree-gist implementations in ./contrib of pgsql, but not  
> necessarily for all data types.  The other note is that I have yet to  
> hear of anyone really getting a great performance boost from this  
> approach, but I will wait and hope to be surprised.

Multi-columned indexes involving GiST indexes have performed badly for
me.  That's why I tried the partial indexing approach I posted
yesterday.

I think that approach will improve your situation significantly.  Also,
remember that you can only have an table clustered on one index at a
time: that's why I added the cluster on the non-spatial column that the
partial indexes are split on.  That really should limit the physical
reads to about as well as you can do in this environment.

Good luck,
Bill

-- 
Bill Binko <bill at binko.net>