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

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