mirror of
https://github.com/hanzoai/postgres.git
synced 2026-10-30 18:33:07 +00:00
Robert Haas
20899fb7e5
The second half of this file is meant to test feedback, not generated advice, and is meant to use the statements that it prepares, not leftover prepared statements from earlier in the file. These mistakes resulted in failures under debug_discard_caches = 1, because re-executing pt2 instead of executing pt4 for the first time resulted in different output depending on whether the query was replanned. Reported-by: Tom Lane <[email protected]> (per BF member avocet)
70 lines
1.9 KiB
Plaintext
70 lines
1.9 KiB
Plaintext
LOAD 'pg_plan_advice';
|
|
SET max_parallel_workers_per_gather = 0;
|
|
CREATE TABLE ptab (id integer, val text) WITH (autovacuum_enabled = false);
|
|
SET pg_plan_advice.always_store_advice_details = false;
|
|
-- Not prepared, so advice should be generated.
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM ptab;
|
|
QUERY PLAN
|
|
------------------------
|
|
Seq Scan on ptab
|
|
Generated Plan Advice:
|
|
SEQ_SCAN(ptab)
|
|
NO_GATHER(ptab)
|
|
(4 rows)
|
|
|
|
-- Prepared, so advice should not be generated.
|
|
PREPARE pt1 AS SELECT * FROM ptab;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) EXECUTE pt1;
|
|
QUERY PLAN
|
|
------------------
|
|
Seq Scan on ptab
|
|
(1 row)
|
|
|
|
SET pg_plan_advice.always_store_advice_details = true;
|
|
-- Prepared, but always_store_advice_details = true, so should show advice.
|
|
PREPARE pt2 AS SELECT * FROM ptab;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) EXECUTE pt2;
|
|
QUERY PLAN
|
|
------------------------
|
|
Seq Scan on ptab
|
|
Generated Plan Advice:
|
|
SEQ_SCAN(ptab)
|
|
NO_GATHER(ptab)
|
|
(4 rows)
|
|
|
|
-- Not prepared, so feedback should be generated.
|
|
SET pg_plan_advice.always_store_advice_details = false;
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(ptab)';
|
|
EXPLAIN (COSTS OFF)
|
|
SELECT * FROM ptab;
|
|
QUERY PLAN
|
|
--------------------------------
|
|
Seq Scan on ptab
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(ptab) /* matched */
|
|
(3 rows)
|
|
|
|
-- Prepared, so feedback should not be generated.
|
|
PREPARE pt3 AS SELECT * FROM ptab;
|
|
EXPLAIN (COSTS OFF) EXECUTE pt3;
|
|
QUERY PLAN
|
|
------------------
|
|
Seq Scan on ptab
|
|
(1 row)
|
|
|
|
SET pg_plan_advice.always_store_advice_details = true;
|
|
-- Prepared, but always_store_advice_details = true, so should show feedback.
|
|
PREPARE pt4 AS SELECT * FROM ptab;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) EXECUTE pt4;
|
|
QUERY PLAN
|
|
--------------------------------
|
|
Seq Scan on ptab
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(ptab) /* matched */
|
|
Generated Plan Advice:
|
|
SEQ_SCAN(ptab)
|
|
NO_GATHER(ptab)
|
|
(6 rows)
|
|
|