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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
