Re: explain and PARAM_EXEC

On Fri, Feb 19, 2010 at 11:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Feb 19, 2010 at 10:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Maybe, but the only reasonable place to put it would be within the
>>> (SubPlan N) reference,
>
>> I thought maybe it could do something like this:
>
>> SubPlan 1
>>   Parameters: $0 := b.oid
>>   -> Index Scan etc.
>
> No, that's the wrong end of the stick --- that's like trying to annotate
> a function definition with the actual parameter values being passed to
> it from somewhere else.  You haven't got the info there, and even if you
> did, it's assuming that there is exactly one call site for any subplan.

OK. Will have to think this one over.

>> I am under the (perhaps faulty) impression that when evaluating an
>> expression there can only ever be three tuples in score: inner, outer,
>> and scan.  So when we go to evaluate the expression whose result will
>> be assigned to $0, where do we get those inner and/or outer and/or
>> scan tuples from?  IOW, I understand where the subplan is putting its
>> OUTPUT, what I don't understand is what context is being used to set
>> its input parameters.
>
> Consider this small mod on your example:
>
> regression=# explain (verbose) select oid::int + 1,(select oid from pg_class a where a.oid = b.relfilenode) from pg_class b;
>                                               QUERY PLAN
> --------------------------------------------------------------------------------------------------------
>  Seq Scan on pg_catalog.pg_class b  (cost=0.00..5573.04 rows=671 width=8)
>   Output: ((b.oid)::integer + 1), (SubPlan 1)
>   SubPlan 1
>     ->  Index Scan using pg_class_oid_index on pg_catalog.pg_class a  (cost=0.00..8.27 rows=1 width=4)
>           Output: a.oid
>           Index Cond: (a.oid = $0)
> (6 rows)
>
> When we are evaluating the output targetlist of the seqscan node, we
> have a scan tuple of pg_class b in scope.  We can fetch that tuple's
> oid and use it in the first expression.

OK.

> We can also fetch that tuple's
> relfilenode and pass it to the subplan, which we do by setting the $0
> Param value before invoking the subplan.

Are the same tuples in scope when evaluating the expression that sets
$0 as were in scope when evaluating ((b.oid)::integer + 1)?

> The subplan runs an indexscan
> and returns a single scalar value (to wit, a.oid from some row of
> pg_class a), which becomes the value of the (SubPlan 1) reference
> back at the evaluation of the seqscan's targetlist.

This part I get, 100%.

...Robert