<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<br>
<br>
<div class="moz-cite-prefix">On 23/06/17 01:39, Kyotaro HORIGUCHI
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:20170623(dot)103957(dot)31224862(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp">
<pre wrap="">Hello,
At Wed, 21 Jun 2017 14:00:56 +0000, <a class="moz-txt-link-abbreviated" href="mailto:cliveevans(at)ntlworld(dot)com">cliveevans(at)ntlworld(dot)com</a> wrote in <a class="moz-txt-link-rfc2396E" href="mailto:20170621140056(dot)27883(dot)82221(at)wrigleys(dot)postgresql(dot)org"><20170621140056(dot)27883(dot)82221(at)wrigleys(dot)postgresql(dot)org></a>
</pre>
<blockquote type="cite">
<pre wrap="">PostgreSQL version: 9.6.3
</pre>
</blockquote>
<pre wrap="">...
</pre>
<blockquote type="cite">
<pre wrap="">The same query written using PL/PGSQL will only scan the expected partition
tables.
</pre>
</blockquote>
<pre wrap="">
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.</pre>
</blockquote>
<br>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
thanks,<br>
-- <br>
Clive Evans<br>
</body>
</html>