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