Re: [BUGS] BUG #14715: Constraint exclusion isn't used in functionusing 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@ntlworld.com  wrote
in<20170621140056.27883.82221@wrigleys.postgresql.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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs