Re: Optimizing a read-only database

Le 18/05/2015 17:20, William Dunn a écrit :

Hello William,

> Hello François - the CLUSTER command doesn't have to do with where your
> indexes are. What the CLUSTER command does is physically sort the table
> data based on the index (Doc:
> http://www.postgresql.org/docs/devel/static/sql-cluster.html) So for
> example if you have a column called 'region_code' with an index and
> CLUSTER the table by that index all the rows for 'region_code'=15 will
> be located next to each other on disk and can be read in the same IO
> operation. The usual disadvantage of CLUSTER is that it does not
> maintain itself, but since your data is read-only that doesn't matter.
> And yes you can still have the index on an SSD and the source table on
> slower storage.

My bad, got it. May be interesting but as I have a lot of indexes it
will be hard to test and to choose the best candidate. No idea of how it
can affect EWKB data indexed by a GiST (PostGIS) index, but it's
something to try just to know.

Thanks a lot, may be I will be able to do something with that!

Best regards