explain and PARAM_EXEC - Mailing list pgsql-hackers

Consider the following (rather lame) query:

rhaas=# explain (verbose) select (select oid from pg_class a where
a.oid = b.relfilenode) from pg_class b;                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------Seq Scan on
pg_catalog.pg_classb  (cost=0.00..2250.22 rows=271 width=4)  Output: (SubPlan 1)  SubPlan 1    ->  Index Scan using
pg_class_oid_indexon pg_catalog.pg_class a
 
(cost=0.00..8.27 rows=1 width=4)          Output: a.oid          Index Cond: (a.oid = $0)

It seems odd to me that we don't display any information about where
$0 comes from or how it's initialized.  Should we?  I believe what's
happening is that the sequential scan of b kicks out b.oid, and that
then gets yanked into $0 when we invoke the subplan.  But you can't
really see what's happening.  Interestingly, if you contrive to make
the sequential scan not the toplevel plan node, then you actually do
get to see what it's kicking out:

rhaas=# explain (verbose) select (select oid from pg_class a where
a.oid = b.oid::integer) from pg_class b, generate_series(1,5);                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------Nested Loop
(cost=0.00..2245943.89rows=271000 width=4)  Output: (SubPlan 1)  ->  Seq Scan on pg_catalog.pg_class b
(cost=0.00..9.71rows=271 width=4)        Output: b.oid  ->  Function Scan on pg_catalog.generate_series
(cost=0.00..10.00
rows=1000 width=0)        Output: generate_series.generate_series  SubPlan 1    ->  Index Scan using pg_class_oid_index
onpg_catalog.pg_class a
 
(cost=0.00..8.27 rows=1 width=4)          Output: a.oid          Index Cond: (a.oid = ($0)::oid)
(10 rows)

We can even make it kick out two things:

rhaas=# explain (verbose) select (select oid from pg_class a where
a.oid = b.oid::integer + b.relfilenode::integer) from pg_class b,
generate_series(1,5);                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------Nested Loop
(cost=0.00..2246621.39rows=271000 width=8)  Output: (SubPlan 1)  ->  Seq Scan on pg_catalog.pg_class b
(cost=0.00..9.71rows=271 width=8)        Output: b.oid, b.relfilenode  ->  Function Scan on pg_catalog.generate_series
(cost=0.00..10.00
rows=1000 width=0)        Output: generate_series.generate_series  SubPlan 1    ->  Index Scan using pg_class_oid_index
onpg_catalog.pg_class a
 
(cost=0.00..8.27 rows=1 width=4)          Output: a.oid          Index Cond: (a.oid = ((($0)::integer +
($1)::integer))::oid)
(10 rows)

But if we drop the generate_series call we're back in the dark -
where's the node that's emitting oid and relfilenode?

rhaas=# explain (verbose) select (select oid from pg_class a where
a.oid = b.oid::integer + b.relfilenode::integer) from pg_class b;                                              QUERY
PLAN
--------------------------------------------------------------------------------------------------------Seq Scan on
pg_catalog.pg_classb  (cost=0.00..2250.90 rows=271 width=8)  Output: (SubPlan 1)  SubPlan 1    ->  Index Scan using
pg_class_oid_indexon pg_catalog.pg_class a
 
(cost=0.00..8.27 rows=1 width=4)          Output: a.oid          Index Cond: (a.oid = ((($0)::integer +
($1)::integer))::oid)
(6 rows)

So I guess there are two issues here: (1) somehow I feel like we
should be telling the user what expression is being used to initialize
$0, $1, etc. when they are PARAM_EXEC parameters; and (2) where does
the output list for the sequential scan "go" when there's only one
table involved?

This is when you all start explaining to me why I'm woefully confused...

...Robert