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
