Re: Index not being used properly

Waldo Nell <pwnell(at)telkomsa(dot)net> writes:
> I have a *huge* problem. I have a table with indexes on but the moment
> I have an OR in my SELECT query it seems to not use the appropriate
> index.

7.5 will be smarter about this, but in 7.4 and before you need to fool
with the column order of your indexes. The query structure is basically

WHERE col1 = const1 AND (col2 = const2 OR col2 = const3)

7.4 can turn this into a 2-column indexscan given an index on (col2,col1)
but not one on (col1,col2).

Your concern about the 3-column index can likewise be explained by
thinking about column order and how much of the index has to be scanned
for the given constraints. Generally you want equalities for the
leftmost index columns and ranges for only the rightmost columns in
order to keep the index scan range reasonable.

regards, tom lane