Re: Partition prune with stable Expr

On Mon, Sep 28, 2020 at 2:44 PM Jesse Zhang <sbjesse(at)gmail(dot)com> 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('2018-12-13', 'yyyy-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 difference is so huge, that doesn't make sense in human view.
> Can
> >> > we do something for that? to_date(text, text) should be a "immutable"
> >> > function IMO. Does that have a semantic issue or other issues?
> >>
> >> Yeah. It depends on the lc_time setting, and possibly also the timezone
> >> GUC. (Admittedly, common values of the format string would not have
> >> any lc_time dependency, but the immutability property is not
> fine-grained
> >> enough to recognize that.)
> >>
> >> regards, tom lane
> >
> >
> > Thanks for your reply. Even it has something on GUC or lc_time setting,
> suppose
> > it should be decided at planning time. Do we have concerns about changes
> > between planning and execution?
>
> Planner can be called at prepared statement creation time, like
>
> PREPARE yolo() AS SELECT * FROM foo WHERE pk = to_date(...);
>
> Here, there's an arbitrary gap between planning time, and execution.
>
> >
> > The attached patch marked some common formatting function as immutable,
> > only one partition prune test case needed fixing because of this. I only
> changed
> > to_char/to_date/to_timestamp, however the whole list is below. I can
> change
> > all of them if needed.
> >
> > proname | count
> > -----------------+-------
> > to_ascii | 3
> > to_char | 8
> > to_date | 1
> > to_hex | 2
> > to_json | 1
> > to_jsonb | 1
> > to_number | 1
> > to_regclass | 1
> > to_regcollation | 1
> > to_regnamespace | 1
> > to_regoper | 1
> > to_regoperator | 1
> > to_regproc | 1
> > to_regprocedure | 1
> > to_regrole | 1
> > to_regtype | 1
> > to_timestamp | 2
> > to_tsquery | 2
> > to_tsvector | 6
> > (19 rows)
> >
> This patch is ridiculous.
>
> Immutable functions need to produce the same output for the same
> argument values. None of the functions changed in the patch is
> immutable: they are all stable because they all depend on GUC settings
> (e.g. to_tsvector depends on default_text_search_config).
>

Thanks, how can I misunderstand Tom's comment above!!

--
Best Regards
Andy Fan