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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
