Re: Partition prune with stable Expr

inion about the impact of "cached the plan > for later use will be wrong" now. Generic plan will never be partition > pruned plan since we don't know which partition to prune at plan time. > So for any cached plan, it is not a plan time partition pruned plan. > Partition prun

Re: Partition prune with stable Expr

On Mon, Sep 28, 2020 at 9:17 PM Thomas Kellerer wrote: > Andy Fan schrieb am 28.09.2020 um 02:54: > > Well, that's very interesting. Specific to my user case, > > SELECT * FROM p WHERE pkey = to_date('2018-12-13', '-mm-dd)'; > > Why use to_date() at all for a constant value? > Can't you use

Re: Partition prune with stable Expr

Andy Fan schrieb am 28.09.2020 um 02:54: > Well, that's very interesting.  Specific to my user case,  > SELECT * FROM p WHERE pkey = to_date('2018-12-13', '-mm-dd)'; Why use to_date() at all for a constant value? Can't you use a standard ANSI date literal (date '2018-12-13')? Alternatively, y

Re: Partition prune with stable Expr

ce we don't know which partition to prune at plan time. So for any cached plan, it is not a plan time partition pruned plan. Partition prune with stable expr is still unacceptable even this is not an issue but hope the snapshot issue will be the only one issue to fix in future for this direction. I'd like to know if I am wrong again. -- Best Regards Andy Fan

Re: Partition prune with stable Expr

On Mon, Sep 28, 2020 at 9:23 AM Andy Fan wrote: > On Mon, Sep 28, 2020 at 2:44 PM Jesse Zhang wrote: > >> On Sun, Sep 27, 2020 at 7:52 PM Andy Fan wrote: >> > On Mon, Sep 28, 2020 at 9:15 AM Tom Lane wrote: >> >> >> >> Yeah. It depends on the lc_time setting, and possibly also the >> timezone >

Re: Partition prune with stable Expr

On Mon, Sep 28, 2020 at 2:44 PM Jesse Zhang wrote: > On Sun, Sep 27, 2020 at 7:52 PM Andy Fan wrote: > > > > > > On Mon, Sep 28, 2020 at 9:15 AM Tom Lane wrote: > >> > >> Andy Fan writes: > >> > Well, that's very interesting. Specific to my user case, > >> > SELECT * FROM p WHERE pkey = to_date(

Re: Partition prune with stable Expr

On Sun, Sep 27, 2020 at 7:52 PM Andy Fan wrote: > > > On Mon, Sep 28, 2020 at 9:15 AM Tom Lane wrote: >> >> Andy Fan writes: >> > Well, that's very interesting. Specific to my user case, >> > SELECT * FROM p WHERE pkey = to_date('2018-12-13', '-mm-dd)'; >> > p has 1500+ partitions and planning

Re: Partition prune with stable Expr

On Mon, Sep 28, 2020 at 9:15 AM Tom Lane wrote: > Andy Fan writes: > > Well, that's very interesting. Specific to my user case, > > SELECT * FROM p WHERE pkey = to_date('2018-12-13', '-mm-dd)'; > > p has 1500+ partitions and planning takes lots of time, which is so same > > with SELECT * FR

Re: Partition prune with stable Expr

Andy Fan writes: > Well, that's very interesting. Specific to my user case, > SELECT * FROM p WHERE pkey = to_date('2018-12-13', '-mm-dd)'; > p has 1500+ partitions and planning takes lots of time, which is so same > with SELECT * FROM p WHERE pkey = '2018-12-13', however the planning > time

Re: Partition prune with stable Expr

On Mon, Sep 28, 2020 at 7:15 AM Tom Lane wrote: > Andy Fan writes: > > On Mon, Sep 28, 2020 at 4:46 AM David Rowley > wrote: > >> Thanks for showing an interest in partition pruning. Unfortunately, > >> it's not possible to use stable functions to prune partitions during > >> planning. > > > Si

Re: Partition prune with stable Expr

Andy Fan writes: > On Mon, Sep 28, 2020 at 4:46 AM David Rowley wrote: >> Thanks for showing an interest in partition pruning. Unfortunately, >> it's not possible to use stable functions to prune partitions during >> planning. > Sigh.. I understand you now, I ignored the plan can be cached for l

Re: Partition prune with stable Expr

Thank you David for coming:) On Mon, Sep 28, 2020 at 4:46 AM David Rowley wrote: > On Mon, 28 Sep 2020 at 08:59, Andy Fan wrote: > > I find we can't prune partitions in the planner if the qual is a stable > function. > > > IMO, we should do it. Why not? > > Thanks for showing an interest in par

Re: Partition prune with stable Expr

On Mon, 28 Sep 2020 at 08:59, Andy Fan wrote: > I find we can't prune partitions in the planner if the qual is a stable > function. > IMO, we should do it. Why not? Thanks for showing an interest in partition pruning. Unfortunately, it's not possible to use stable functions to prune partitions

Partition prune with stable Expr

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, peaktempint, unitsales int ) PARTITION BY RANGE (logdate); CREATE TABLE measurement_y2006m02