Runtime pruning problem

From: "Yuzuko Hosoya" <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp>
To: <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Runtime pruning problem
Date: 2019-04-16 11:54:36
Message-ID: 001001d4f44b$2a2cca50$7e865ef0$@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all,

I found a runtime pruning test case which may be a problem as follows:

----
create table t1 (id int, dt date) partition by range(dt);
create table t1_1 partition of t1 for values from ('2019-01-01') to ('2019-04-01');
create table t1_2 partition of t1 for values from ('2019-04-01') to ('2019-07-01');
create table t1_3 partition of t1 for values from ('2019-07-01') to ('2019-10-01');
create table t1_4 partition of t1 for values from ('2019-10-01') to ('2020-01-01');

In this example, current_date is 2019-04-16.

postgres=# explain select * from t1 where dt = current_date + 400;
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..198.42 rows=44 width=8)
Subplans Removed: 3
-> Seq Scan on t1_1 (cost=0.00..49.55 rows=11 width=8)
Filter: (dt = (CURRENT_DATE + 400))
(4 rows)

postgres=# explain analyze select * from t1 where dt = current_date + 400;
QUERY PLAN
---------------------------------------------------------------------------------------
Append (cost=0.00..198.42 rows=44 width=8) (actual time=0.000..0.001 rows=0 loops=1)
Subplans Removed: 3
-> Seq Scan on t1_1 (cost=0.00..49.55 rows=11 width=8) (never executed)
Filter: (dt = (CURRENT_DATE + 400))
Planning Time: 0.400 ms
Execution Time: 0.070 ms
(6 rows)
----

I realized t1_1 was not scanned actually since "never executed"
was displayed in the plan using EXPLAIN ANALYZE. But I think
"One-Time Filter: false" and "Subplans Removed: ALL" or something
like that should be displayed instead.

What do you think?

Best regards,
Yuzuko Hosoya
NTT Open Source Software Center

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2019-04-16 12:09:52 Re: Runtime pruning problem
Previous Message David Rowley 2019-04-16 11:30:51 Re: Caveats from reloption toast_tuple_target