Thread: Questions about indexes

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?

Also, how can I find what other parts of my app would benefit from more indexes?

Thanks,
Pat

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.

>
> 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.

If you can think of a way to force programmers to do this while building
their apps, that would also be useful ...

--
In a truly free society, "Alcohol, Tobacco and Firearms" would be a
convenience store chain.

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°