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.
thanks,
--
Clive Evans
