|From:||Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>|
|To:||Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>|
|Subject:||Re: Make EXPLAIN generate a generic plan for a parameterized query|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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,
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;
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)
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
|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|