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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Make EXPLAIN generate a generic plan for a parameterized query
Date: 2022-10-11 13:49:14
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

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


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;
regression=# explain execute foo(42);
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

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Richard Guo 2022-10-11 13:49:18 Re: Remove an unnecessary LSN calculation while validating WAL page header
Previous Message Ajin Cherian 2022-10-11 13:30:37 Re: Support logical replication of DDLs