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