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

Hi Laurenz,

I'm testing your patch and the GENERIC_PLAN parameter seems to work just
OK ..

db=# CREATE TABLE t (col numeric);
CREATE TABLE
db=# CREATE INDEX t_col_idx ON t (col);
CREATE INDEX
db=# INSERT INTO t SELECT random() FROM generate_series(1,100000) ;
INSERT 0 100000
db=# EXPLAIN (GENERIC_PLAN) SELECT * FROM t WHERE col = $1;
                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=15.27..531.67 rows=368 width=32)
   Recheck Cond: (col = $1)
   ->  Bitmap Index Scan on t_col_idx  (cost=0.00..15.18 rows=368 width=0)
         Index Cond: (col = $1)
(4 rows)

.. the error message when combining GENERIC_PLAN with ANALYSE also works
as expected

db=# EXPLAIN (ANALYSE, GENERIC_PLAN) SELECT * FROM t WHERE col = $1;
ERROR:  EXPLAIN ANALYZE cannot be used with GENERIC_PLAN

.. and the system also does not throw an error when it's used along
other parameters, e.g. VERBOSE, WAL, SUMMARY, etc.

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)

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)

Is it the expected behaviour?

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;

If it's intended to be consistent with the other "single parameters",
perhaps it should work also without parenthesis? e.g.

db=# EXPLAIN ANALYSE SELECT * FROM t WHERE col < 0.42;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using t_col_idx on t  (cost=0.42..1637.25 rows=41876
width=11) (actual time=0.103..6.293 rows=41932 loops=1)
   Index Cond: (col < 0.42)
   Heap Fetches: 0
 Planning Time: 0.071 ms
 Execution Time: 7.316 ms
(5 rows)

db=# EXPLAIN VERBOSE SELECT * FROM t WHERE col < 0.42;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Index Only Scan using t_col_idx on public.t (cost=0.42..1637.25
rows=41876 width=11)
   Output: col
   Index Cond: (t.col < 0.42)
(3 rows)

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

Cheers
Jim

On 09.01.23 17:40, Laurenz Albe wrote:
> On Tue, 2022-12-27 at 14:37 -0800, Michel Pelletier wrote:
>> I built and tested this patch for review and it works well, although I got the following warning when building:
>>
>> analyze.c: In function 'transformStmt':
>> analyze.c:2919:35: warning: 'generic_plan' may be used uninitialized in this function [-Wmaybe-uninitialized]
>>  2919 |         pstate->p_generic_explain = generic_plan;
>>       |         ~~~~~~~~~~~~~~~~~~~~~~~~~~^~~~~~~~~~~~~~
>> analyze.c:2909:25: note: 'generic_plan' was declared here
>>  2909 |         bool            generic_plan;
>>       |                         ^~~~~~~~~~~~
> Thanks for checking. The variable should indeed be initialized, although
> my compiler didn't complain.
>
> Attached is a fixed version.
>
> Yours,
> Laurenz Albe