Re: Questions about indexes

Alan Hodgson <ahodgson(at)simkin(dot)ca> schrieb:

> On Saturday 10 June 2006 13:30, "Pat Maddox" <pergesu(at)gmail(dot)com> wrote:
> > I've got a table with that has 5 fields. Nearly every query I make to
> > this table is of the form
> > SELECT * FROM table1 WHERE field1='foo' AND field2=7;
> >
> > It's always those two exact fields. How should I index this to get
> > the best performance?
>
> A single index on field1,field2 (or field2, field1) would obviously be
> ideal.

With 8.1 we have Bitmap Index Scan, and i think, 2 indexes (one on
field1, one on field2) are also useful, in this case we have also an
index when we ask: '... where field1='foo';', without field2.

>
> >
> > Also, how can I find what other parts of my app would benefit from more
> > indexes?
>
> Log the queries that are being run, extract the ones that are run the most,
> and examine them and their underlying data in detail and with explain
> analyze.

A very nice tool for this is set 'log_min_duration_statement' in the
postgresql.conf:

#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, in milliseconds.

HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°