Andy Fan <zhihui.fan1213@gmail.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
