Re: explain and PARAM_EXEC

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.

> 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. 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. 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.

It's really not much different from a function call with subplans as
functions. The PARAM_EXEC stuff looks just like 1950's era
non-reentrant function parameter passing mechanisms, back before anybody
had thought of recursive functions and they passed a function's
parameters in fixed storage locations. It's okay for this because
subplan trees are never recursive ...

regards, tom lane