Re: Questions about indexes - Mailing list pgsql-general

From Andreas Kretschmer
Subject Re: Questions about indexes
Date
Msg-id 20060611073853.GA4480@KanotixBox
Whole thread Raw
In response to Re: Questions about indexes  (Alan Hodgson <ahodgson@simkin.ca>)
List pgsql-general
Alan Hodgson <ahodgson@simkin.ca> schrieb:

> On Saturday 10 June 2006 13:30, "Pat Maddox" <pergesu@gmail.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°

pgsql-general by date:

Previous
From: "Alex Turner"
Date:
Subject: Re: Problems with database bloat
Next
From: "Harald Armin Massa"
Date:
Subject: Invitation to EuroPython conference