Re: Postgres not willing to use an index?

Mario Splivalo <mario.splivalo@megafon.hr> writes:
> Besides PK and uq-constraint indices I have this index:

> CREATE INDEX transactions_idx__client_data ON transactions
> USING btree (transaction_client_id, transaction_destination_id,
> transaction_operator_id, transaction_application_id,
> transaction_time_commit)

> SELECT <some-columns> FROM transactions WHERE transaction_time_commit
> BETWEEN '2009-01-01' AND '2009-01-31 23:59:59';
> The problem is that postgres is never using an index:

Hardly surprising --- a search on the index's lowest-order column would
require scanning practically all of the index.  (If you think about the
ordering of the index entries you'll see why.)  If this is a typical
query then you need a separate index on transaction_time_commit.

The fine manual goes into some detail about how to design indexes;
http://www.postgresql.org/docs/8.3/static/indexes.html
particularly 11.3, 11.5.

            regards, tom lane