Partition prune with stable Expr

From: Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Partition prune with stable Expr
Date: 2020-09-27 19:59:10
Message-ID: CAKU4AWrksHsgWtjAD7fFsMfbZWdFtnTUGD6kEM6TeVGOJJGbwQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi:

I find we can't prune partitions in the planner if the qual is a stable
function.

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

postgres=# explain (costs off) select * from measurement
postgres-# where logdate = to_date('2006-03-02', 'yyyy-mm-dd');
QUERY PLAN
-----------------------------------------------------------------------------
Append
Subplans Removed: 1 <-- Here
-> Seq Scan on measurement_y2006m03 measurement_1
Filter: (logdate = to_date('2006-03-02'::text, 'yyyy-mm-dd'::text))
(4 rows)

IMO, we should do it. Why not? The attached is used to show the things
in my mind.

Btw, why the to_date function is declared as stable rather than immutable
since it always delivers the same result for the same inputs.

--
Best Regards
Andy Fan

Attachment Content-Type Size
v1-0001-Allow-planner-prune-partitionn-with-stable-Expr.patch application/octet-stream 8.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2020-09-27 20:46:37 Re: Partition prune with stable Expr
Previous Message Justin Pryzby 2020-09-27 18:37:52 Re: AppendStringInfoChar instead of appendStringInfoString