Re: Postgres not willing to use an index?

Mario Splivalo wrote:
> Robert Haas wrote:
> jura=# set enable_seqscan to false;
> SET
> jura=# explain analyze select * from transactions where
> transaction_time_commit between '2008-01-01' and '2008-01-31 23:59:59';
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Bitmap Heap Scan on transactions (cost=428882.89..651630.52
> rows=759775 width=91) (actual time=1358.040..1633.867 rows=722176 loops=1)
> Recheck Cond: ((transaction_time_commit >= '2008-01-01
> 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
> '2008-01-31 23:59:59+01'::timestamp with time zone))
> -> Bitmap Index Scan on transactions_idx__client_data
> (cost=0.00..428692.95 rows=759775 width=0) (actual
> time=1354.485..1354.485 rows=722176 loops=1)
> Index Cond: ((transaction_time_commit >= '2008-01-01
> 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
> '2008-01-31 23:59:59+01'::timestamp with time zone))
> Total runtime: 1778.938 ms
> (5 rows)
>
>

I neglected to paste this 'explain analyze', when I changed the index so
that 'transaction_time_commit' is first column in the index:

jura=# explain analyze select * from transactions where
transaction_time_commit between '2009-01-01' and '2009-01-31 23:59:59';

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on transactions (cost=7550.51..233419.58 rows=250880
width=91) (actual time=95.139..280.008 rows=525051 loops=1)
Recheck Cond: ((transaction_time_commit >= '2009-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2009-01-31 23:59:59+01'::timestamp with time zone))
-> Bitmap Index Scan on transactions_idx__client_data
(cost=0.00..7487.79 rows=250880 width=0) (actual time=93.382..93.382
rows=525051 loops=1)
Index Cond: ((transaction_time_commit >= '2009-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2009-01-31 23:59:59+01'::timestamp with time zone))
Total runtime: 386.665 ms
(5 rows)

Thank you, Tom!

Mike