Re: Make EXPLAIN generate a generic plan for a parameterized query

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> Today you get
> test=> EXPLAIN SELECT * FROM tab WHERE col = $1;
> ERROR: there is no parameter $1
> which makes sense. Nonetheless, it would be great to get a generic plan
> for such a query.

I can see the point, but it also seems like it risks masking stupid
mistakes.

> I tied that behavior to the setting of "plan_cache_mode" where you
> are guaranteed to get a generic plan; I couldn't think of a better way.

I think it might be better to drive it off an explicit EXPLAIN option,
perhaps

EXPLAIN (GENERIC_PLAN) SELECT * FROM tab WHERE col = $1;

This option (bikeshedding on the name welcome) would have the effect
both of allowing unanchored Param symbols and of temporarily forcing
generic-plan mode, so that you don't need additional commands to
set and reset plan_cache_mode. We could also trivially add logic
to disallow the combination of ANALYZE and GENERIC_PLAN, which
would otherwise be a bit messy to prevent.

For context, it does already work to do this when you want to
investigate parameterized plans:

regression=# prepare foo as select * from tenk1 where unique1 = $1;
PREPARE
regression=# explain execute foo(42);
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
Index Cond: (unique1 = 42)
(2 rows)

If you're trying to investigate custom-plan behavior, then you
need to supply concrete parameter values somewhere, so I think
this approach is fine for that case. (Shoehorning parameter
values into EXPLAIN options seems like it'd be a bit much.)
However, investigating generic-plan behavior this way is tedious,
since you have to invent irrelevant parameter values, plus mess
with plan_cache_mode or else run the explain half a dozen times.
So I can get behind having a more convenient way for that.

regards, tom lane