[postgis-users] Partitioning spatial table

[Paul Ramsey]

On 18-Feb-06, at 3:09 PM, Markus Schaber wrote:
>
>> If I create a multicolumn index (with the geometry column first),  
>> would
>> it be a better idea to cluster the table on this index instead ?
>
> Only if most of those queries actually use that multicolumn index.
>
> 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.