Support PostgreSQL 14

[pghintplan/pg_hint_plan.git] / sql / pg_hint_plan.sql

diff --git a/sql/pg_hint_plan.sql b/sql/pg_hint_plan.sql
index ae8aac3..a5309cb 100644 (file)
--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -1,5 +1,6 @@
 SET search_path TO public;
 SET client_min_messages TO log;
+\set SHOW_CONTEXT always
 
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
@@ -51,6 +52,7 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 /*+Set(work_mem TO "1MB")*/
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 
+/*+SeqScan() */ SELECT 1;
 /*+SeqScan(t1 t2)*/
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 /*+SeqScan(t1)*/
@@ -71,6 +73,8 @@ EXPLAIN (COSTS false) SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1
 /*+NoTidScan(t1)*/
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)';
 
+/*+ NestLoop() */ SELECT 1;
+/*+ NestLoop(x) */ SELECT 1;
 /*+HashJoin(t1 t2)*/
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 /*+NestLoop(t1 t2)*/
@@ -335,10 +339,11 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1
 EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN  t2 ON (t1.id = t2.id);
 /*+MergeJoin(t1 t2)*/
 EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN  t2 ON (t1.id = t2.id);
+-- Cannot work
 /*+NestLoop(t1 t2)*/
 EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN  t2 ON (t1.id = t2.id);
 
--- inherite table test
+-- inheritance tables test
 SET constraint_exclusion TO off;
 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
 SET constraint_exclusion TO on;
@@ -587,9 +592,9 @@ SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.i
 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id 
 );
 
--- ambigous error
+-- ambiguous error
 EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
-/*+NestLoop(t1 t2)*/
+/*+MergeJoin(t1 t2)*/
 EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
 /*+Leading(t1 t2 t1)*/
 EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
@@ -728,7 +733,10 @@ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid =
 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
 
 EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
-/*+IndexScan(p2 p2_val)*/
+
+-- Inhibit parallel exection to avoid interfaring the hint
+set max_parallel_workers_per_gather to 0;
+/*+ IndexScan(p2 p2_val)*/
 EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
 /*+IndexScan(p2 p2_pkey)*/
 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
@@ -942,6 +950,98 @@ SELECT * FROM testfunc() LIMIT 1;
 DROP FUNCTION testfunc();
 DROP EXTENSION pg_hint_plan;
 
+CREATE FUNCTION reset_stats_and_wait() RETURNS void AS $$
+DECLARE
+  rows int;
+BEGIN
+  rows = 1;
+  while rows > 0 LOOP
+   PERFORM pg_stat_reset();
+   PERFORM pg_sleep(0.5);
+   SELECT sum(seq_scan + idx_scan) from pg_stat_user_tables into rows;
+  END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Dynamic query in pl/pgsql
+CREATE OR REPLACE FUNCTION dynsql1(x int) RETURNS int AS $$
+DECLARE c int;
+BEGIN
+  EXECUTE '/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1'
+       INTO c USING x;
+  RETURN c;
+END;
+$$ VOLATILE LANGUAGE plpgsql;
+vacuum analyze t1;
+SET pg_hint_plan.enable_hint = false;
+SELECT pg_sleep(1);
+SELECT reset_stats_and_wait();
+SELECT dynsql1(9000);
+SELECT pg_sleep(1);
+SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
+SET pg_hint_plan.enable_hint = true;
+SELECT reset_stats_and_wait();
+SELECT dynsql1(9000);
+SELECT pg_sleep(1);
+SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
+
+-- Looped dynamic query in pl/pgsql
+CREATE OR REPLACE FUNCTION dynsql2(x int, OUT r int) AS $$
+DECLARE
+  c text;
+  s int;
+BEGIN
+  r := 0;
+  FOR c IN SELECT f.f FROM (VALUES ('p1_c1'), ('p1_c2')) f(f) LOOP
+    FOR s IN EXECUTE '/*+ IndexScan(' || c || ' ' || c || '_pkey) */ SELECT sum(val) FROM ' || c || ' WHERE id < ' || x LOOP
+      r := r + s;
+    END LOOP;
+  END LOOP;
+END;
+$$ VOLATILE LANGUAGE plpgsql;
+SET pg_hint_plan.enable_hint = false;
+SELECT reset_stats_and_wait();
+SELECT dynsql2(9000);
+SELECT pg_sleep(1);
+-- one of the index scans happened while planning.
+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
+SET pg_hint_plan.enable_hint = true;
+SELECT reset_stats_and_wait();
+SELECT dynsql2(9000);
+SELECT pg_sleep(1);
+-- the index scan happened while planning.
+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
+
+-- Subqueries on inheritance tables under UNION
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p1 p1_val2) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+
+-- union all case
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+
 --
 -- Rows hint tests
 --
@@ -949,6 +1049,10 @@ DROP EXTENSION pg_hint_plan;
 -- this test needs it enabled for get rows count. So do tests via psql
 -- and grep -v the mutable line.
 
+-- Parse error check
+/*+ Rows() */ SELECT 1;
+/*+ Rows(x) */ SELECT 1;
+
 -- value types
 \o results/pg_hint_plan.tmpout
 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
@@ -1020,5 +1124,20 @@ EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
 /*+ Rows(t1 t3 *10) */
 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
 \o
+set max_parallel_workers_per_gather to DEFAULT;
 \! sql/maskout.sh results/pg_hint_plan.tmpout
 \! rm results/pg_hint_plan.tmpout
+
+-- hint error level
+set client_min_messages to 'DEBUG1';
+set pg_hint_plan.debug_level to 'verbose';
+/*+ SeqScan( */ SELECT 1;
+/*+ SeqScan(t1) */ SELECT * FROM t1 LIMIT 0;
+set pg_hint_plan.message_level to 'DEBUG1';
+set pg_hint_plan.parse_messages to 'NOTICE';
+/*+ SeqScan( */ SELECT 1;
+/*+ SeqScan(t1) */ SELECT * FROM t1 LIMIT 0;
+
+-- all hint types together
+/*+ SeqScan(t1) MergeJoin(t1 t2) Leading(t1 t2) Rows(t1 t2 +10) Parallel(t1 8 hard) Set(random_page_cost 2.0)*/
+EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);