Re: Make EXPLAIN generate a generic plan for a parameterized query - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Make EXPLAIN generate a generic plan for a parameterized query
Date
Msg-id 1889657.1673888532@sss.pgh.pa.us
Whole thread Raw
In response to Re: Make EXPLAIN generate a generic plan for a parameterized query  (Jim Jones <jim.jones@uni-muenster.de>)
List pgsql-hackers
Jim Jones <jim.jones@uni-muenster.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



pgsql-hackers by date:

Previous
From: Alexander Pyhalov
Date:
Subject: Re: Inconsistency in vacuum behavior
Next
From: Nikita Malakhov
Date:
Subject: Re: Inconsistency in vacuum behavior