On Fri, Feb 6, 2009 at 11:14 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Mario Splivalo <mario.splivalo@xxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
