Re: something to suggest indexes

On Friday 17 July 2009 12:29:59 am Richard Huxton wrote:
> 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

Yes that's what I was looking for. Thanks for taking the time.
Johnf