mirror of
https://github.com/postgres/postgres.git
synced 2026-03-23 02:43:22 -04:00
b335fe56f3
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 <tgl@sss.pgh.pa.us> (per BF member avocet)
36 lines
1.1 KiB
SQL
36 lines
1.1 KiB
SQL
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;
|
|
|
|
-- Prepared, so advice should not be generated.
|
|
PREPARE pt1 AS SELECT * FROM ptab;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) EXECUTE pt1;
|
|
|
|
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;
|
|
|
|
-- 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;
|
|
|
|
-- Prepared, so feedback should not be generated.
|
|
PREPARE pt3 AS SELECT * FROM ptab;
|
|
EXPLAIN (COSTS OFF) EXECUTE pt3;
|
|
|
|
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;
|