Re: Postgres not willing to use an index?

On Fri, Feb 6, 2009 at 11:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

What's weird about this example is that when he sets enable_seqscan to
off, the bitmap index scan plan is actually substantially faster, even
though it in fact does scan nearly the entire heap.  I don't
understand how it can be faster to scan the index and the heap than to
just scan the heap.

...Robert