[postgis-users] Partitioning spatial table

[David Bitner]

Will the new multi-index capabilities in 8.1 make a difference?

On 2/18/06, Bill Binko <bill at binko.net> wrote:
> 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>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>