Thread: something to suggest indexes

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.

If there is nothing in Postgres does anyone have any suggestions?

John

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