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

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