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

Jim Jones <jim(dot)jones(at)uni-muenster(dot)de> writes:
> However, when GENERIC_PLAN is used combined with BUFFERS, the 'Buffers'
> node is shown the first time the query executed in a session:

> psql (16devel)
> Type "help" for help.

> postgres=# \c db
> You are now connected to database "db" as user "postgres".
> db=# EXPLAIN (BUFFERS, GENERIC_PLAN) SELECT * FROM t WHERE col = $1;
>                                QUERY PLAN
> -------------------------------------------------------------------------
>  Index Only Scan using t_col_idx on t  (cost=0.42..4.44 rows=1 width=11)
>    Index Cond: (col = $1)
>  Planning:
>    Buffers: shared hit=62
> (4 rows)

> db=# EXPLAIN (BUFFERS, GENERIC_PLAN) SELECT * FROM t WHERE col = $1;
>                                QUERY PLAN
> -------------------------------------------------------------------------
>  Index Only Scan using t_col_idx on t  (cost=0.42..4.44 rows=1 width=11)
>    Index Cond: (col = $1)
> (2 rows)

That's not new to this patch, the same thing happens without it.
It's reflecting catalog accesses involved in loading per-session
caches (which, therefore, needn't be repeated the second time).

> Also, this new parameter seems only to work between parenthesis
> `(GENERIC_PLAN)`:

> db=# EXPLAIN GENERIC_PLAN SELECT * FROM t WHERE col = $1;
> ERROR:  syntax error at or near "GENERIC_PLAN"
> LINE 1: EXPLAIN GENERIC_PLAN SELECT * FROM t WHERE col = $1;

That's true of all but the oldest EXPLAIN options. We don't do that
anymore because new options would have to become grammar keywords
to support that.

> On a very personal note: wouldn't just GENERIC (without _PLAN) suffice?
> Don't bother with it if you disagree :-)

FWIW, "GENERIC" would be too generic for my taste ;-). But I agree
it's a judgement call.

regards, tom lane