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