Re: something to suggest indexes

John wrote:
> Hi,
>
> Is there something built-in to Postgres that would suggest what indexes I
> might add to improve performance? I created my required tables (they only
> contain small amounts of test data) and the performance is great. But as the
> data starts growing I'm betting that creating a few indexes will be needed.
>
> In the past I just started playing with explain using a hit and miss way of
> doing it.

You'll want EXPLAIN once you know which queries you really care about
but before that you'll need to identify them. Two things might prove useful:

http://www.postgresql.org/docs/8.4/static/monitoring-stats.html

The statistics views will let you see which tables and indexes are being
used the most. You don't want unnecessary indexes either. Take a copy of
the table, leave it 24 hours (or whatever testing time is suitable) and
take another copy. Compare the two.

You can also turn on query-time logging and use a log analyser to see
precisely how much time you spend with each query. Then, you know which
to target with EXPLAIN. A couple of log-analyser packages are:
http://pgfouine.projects.postgresql.org/
http://pqa.projects.postgresql.org/

--
Richard Huxton
Archonet Ltd