I have a query that behaves in an odd way, specifically it does different
things depending on if I use "=" or ">=" :
mydb=# explain analyze select count(*) from ctable c where c.date1 >=
'2017-08-10' and c.col2 = '637' and c.col3 = 'TEXT3' and c.col4 not in ('TEXT4');
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=104183.81..104183.82 rows=1 width=0) (actual
time=520.221..520.221 rows=1 loops=1)
-> Bitmap Heap Scan on ctable c (cost=83843.79..104161.45 rows=8947
width=0) (actual time=515.833..520.192 rows=540 loops=1)
Recheck Cond: ((date1 >= '2017-08-10'::date) AND (col2 = 637) AND
((col3)::text = 'TEXT3'::text))
Rows Removed by Index Recheck: 4894
Filter: ((col4)::text <> 'TEXT4'::text)
Rows Removed by Filter: 360
Heap Blocks: exact=1159
-> BitmapAnd (cost=83843.79..83843.79 rows=10294 width=0) (actual
time=515.629..515.629 rows=0 loops=1)
-> Bitmap Index Scan on ctable_date1 (cost=0.00..22534.71
rows=598419 width=0) (actual time=5.204..5.204 rows=37536 loops=1)
Index Cond: (date1 >= '2017-08-10'::date)
-> Bitmap Index Scan on ctable_col2_col3 (cost=0.00..61304.36
rows=1158379 width=0) (actual time=505.479..505.479 rows=1213850 loops=1)
Index Cond: ((col2 = 637) AND ((col3)::text = 'TEXT3'::text))
Planning time: 0.306 ms
Execution time: 520.283 ms
(14 rows)
mydb=# explain analyze select count(*) from ctable c where c.date1 =
'2017-08-10' and c.col2 = '637' and c.col3 = 'TEXT3' and c.col4 not in ('TEXT4');
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=9929.67..9929.68 rows=1 width=0) (actual time=10.500..10.501
rows=1 loops=1)
-> Index Scan using ctable_date1_col3_col4_in on ctable c
(cost=0.57..9929.05 rows=245 width=0) (actual time=0.062..10.456 rows=540 loops=1)
Index Cond: ((date1 = '2017-08-10'::date) AND ((col3)::text =
'TEXT3'::text))
Filter: (((col4)::text <> 'TEXT4'::text) AND (col2 = 637))
Rows Removed by Filter: 9059
Planning time: 0.499 ms
Execution time: 10.557 ms
(7 rows)
The used indexes are:
Indexes:
"ctable_date1" btree (date1)
"ctable_col2_col3" btree (col2, col3)
"ctable_date1_col2_col3" btree (date1, col2, col3)
ctable has about 60,000,000 rows.
[ Caveat: I sanitized the query and I might not have this character perfect,
though I believe it's ok ]
The date is "today" at the time of writing. The table is analyzed, to pg
should "know" that date1 <= "today" hence there is no data beyond today.
On similar - and more relevant lines the same query with a date range so as
above but "c.date1 >= '2017-08-01' and c.date1 <= '2017-08-10'" uses the
date1_col2_col3 index and takes about 143ms, whereas "c.date1 >= '2017-07-31'
and c.date1 <= '2017-08-10'" uses the date1/col2_col3 indexes takes about 902ms.
Is there a planner option I can tweak that might help?
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
