Re: BUG #14715: Constraint exclusion isn't used in function using language sql

On 23/06/17 08:59, Clive Evans wrote:
>
>
> On 23/06/17 01:39, Kyotaro HORIGUCHI wrote:
>> Hello,
>>
>> At Wed, 21 Jun 2017 14:00:56 +0000,cliveevans(at)ntlworld(dot)com wrote in<20170621140056(dot)27883(dot)82221(at)wrigleys(dot)postgresql(dot)org>
>>> PostgreSQL version: 9.6.3
>> ...
>>> The same query written using PL/PGSQL will only scan the expected partition
>>> tables.
>> The two are different in that the parameters of the PL/PgSQL
>> function are regarded as constants at the time of planning of the
>> inner SQL statement, while those of the SQL function are
>> not. Constraint exclusion is considered while planning so the SQL
>> function doesn't get benefit of it.
>
> I understand this, and I understand why. You can't plan based on what
> you don't yet know. I'm not entirely clear why the inner query can
> treat the parameter as a constant, whereas the outer one is forced to
> treat it as dynamic.
>
> I assume it's something to do with when the plan is created. Possibly
> it's an attempt to save the planning time and re-use the plan in the
> case of a 'normal' SQL function, although this seems like a trade off
> that may well have significant downsides - in this case obviously, but
> also others.
>
> Perhaps I'm looking for a new feature, rather than reporting a bug.
> Something that allows lazy planning, where there's a good chance that
> treating the parameters as constants is likely to improve the produced
> plan sufficiently to offset the additional overhead of repeatedly
> planning.

Sorry, mail client went a bit rogue. Here it is again, (hopefully)
legibly ...

thanks,
Clive