Re: Partition prune with stable Expr

On Mon, Sep 28, 2020 at 9:15 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> 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?

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)

With this change, the exact issue on the beginning of this thread can be
fixed as
well with this patch.

--
Best Regards
Andy Fan