Re: optimisation for a table with frequently used query

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Lew
> Sent: Tuesday, May 29, 2007 6:38 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] optimisation for a table with frequently used
query
>
> danmcb wrote:
> >> SELECT * from my_table where id_1 = x and id_2 = y;
> >> Neither id_1 or id_2 or the combination of them is unique. I expect
> >> this table to become large over time.
>
> PFC wrote:
> > Create an index on (id_1, id_2), or (id_2,id_1).
>
> What are the strengths and weaknesses compared to creating two
indexes,
> one on
> each column?

Creating one index on each column will not do nearly so much filtering
as creating one index that contains both columns (unless by chance the
data always comes in pairs).  The only time that there might be an
advantage is if your query contains only one of the two columns for
filtering.  If the missing column is the most significant from a two
column index, then the two column index won't be used.

> I am guessing that changes to the table are slower with two indexes.
How
> could it affect queries?

It will slow down update queries.  The more indexes you add, the slower
update queries become.

> What if the typical query pattern was more balanced among constraints
on
> one
> column, on the other, and on both?

Collect some statistics to reduce guesswork.