Curious planning decision

From: Steve Rogerson <steve(dot)pg(at)yewtc(dot)demon(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Curious planning decision
Date: 2017-08-10 14:28:15
Message-ID: bf4c7637-3d86-4422-d91b-7e2066492636@yewtc.demon.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-08-10 14:54:54 Re: foreign key with char and varchar
Previous Message Colin 't Hart 2017-08-10 13:49:35 Re: How to ALTER EXTENSION name OWNER TO new_owner ?