[GENERAL] Curious planning decision - Mailing list pgsql-general

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