Re: Partition prune with stable Expr - Mailing list pgsql-hackers

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).