Re: Allow use of immutable functions operating on constants with constraint exclusion

ITAGAKI Takahiro wrote:

>Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>
>
>>"Marshall, Steve" <smarshall(at)wsi(dot)com> writes:
>>
>>
>>>I have developed a small patch to optimizer/util/plancat.c that
>>>eliminates one of hte caveats associated with constraint exclusions,
>>>namely the inability to avoid searching tables based on the results of
>>>stable functions.
>>>
>>>
>>Do you not understand why this is completely unsafe?
>>
>>
>
>I think the proposal itself is very useful, because time-based
>partitioning is commonly used and functions like now() or
>CURRENT_TIMESTAMP are marked as stable.
>
>I'm not clear why the optimization is unsafe. I'm confused to read the
>definition of stable functions in our documentation. Which is required
>for stable functions 'stable in a single table scan' or 'stable in a
>SQL statements' ? If the latter definition is true, can we use them
>in constraint exclusions?
>
>| STABLE indicates that the function cannot modify the database, and
>| that within a single table scan it will consistently return the same
>| result for the same argument values, but that its result could change
>| across SQL statements
>
>
The lack of safety comes from prepared statements. If the above
optimization was used, the value of the stable function would be used
when the statement was prepared, and the query plan would then be set
using the stable function value as though it were a constant. For
partitioned tables, this could result in a failure to scan tables needed
to meet the query constraints.

I think the optimization could work if you could exclude prepared
statements. However, I looked at the planning code and found no clear
way to distinguish between a statement being prepared for later
execution, and a statement being planned only for immediate execution.
As a result, I don't think stable functions can (or should) be expanded
to help optimize queries using constraint exclusion.

However, I think it would be possible to expand immutable functions
operating on constants to optimize constraint exclusion. Immutable
functions will always return the same result given the same inputs, so
this would be safe. Currently, immutable functions are not expanded
during planning such that the first query would be optimized using
constraint exclusion, while the second query would not:

SELECT * FROM test_bulletins WHERE created_at > '2006-09-09
05:00:00+00'::timestamptz;
SELECT * FROM test_bulletins WHERE created_at > '2006-09-09
05:00:00+00'::timestamptz + '0 days'::interval;

See the attached SQL file for table creation and other SQL examples.

The real question here is if the optimization is worth the effort.
Personally, I commonly use queries of this sort, and so would be in
favor of this expansion of immutable functions operating on constrats as
an optimization. I find it convenient to use the database to do the
time manipulation (e.g. adding intervals to timestamps). However, the
logic to manipulate times can be pushed into application code if need
be. I've found I have to do a lot of explaining to developers as to
why two queries that look so similar perform very differently.