From b6ebe309a92328110bc66786476b29879ee1272f Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Tue, 4 Dec 2018 10:33:55 +0900 Subject: [PATCH] Don't reset plpgsql nest level at every command end Clean up code for global exit is forgetting about loops that run hinted commands multiple times. This leads to pg_hint_plan's failure to affect after the first execution of the inner commands. The hint below worked only for the first time. FOR outer IN EXECUTE 'outer-command' LOOP FOR inner IN EXECUTE '/*+ hint */ inner-command' LOOP END LOOP; END LOOP; --- expected/pg_hint_plan.out | 179 ++++++++++++++++++++++++++++++++++++++++++++++ pg_hint_plan.c | 2 +- sql/pg_hint_plan.sql | 61 ++++++++++++++++ 3 files changed, 241 insertions(+), 1 deletion(-) diff --git a/expected/pg_hint_plan.out b/expected/pg_hint_plan.out index 32f1bb1..fe2cf9c 100644 --- a/expected/pg_hint_plan.out +++ b/expected/pg_hint_plan.out @@ -8178,6 +8178,185 @@ error hint: 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 reset_stats_and_wait(); + reset_stats_and_wait +---------------------- + +(1 row) + +SELECT dynsql1(9000); + dynsql1 +--------- + 8999 +(1 row) + +SELECT pg_sleep(1); + pg_sleep +---------- + +(1 row) + +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'; + relname | seq_scan | idx_scan +---------+----------+---------- + t1 | t | f +(1 row) + +SET pg_hint_plan.enable_hint = true; +SELECT reset_stats_and_wait(); + reset_stats_and_wait +---------------------- + +(1 row) + +SELECT dynsql1(9000); +LOG: pg_hint_plan: +used hint: +IndexScan(t1) +not used hint: +duplication hint: +error hint: + +CONTEXT: SQL statement "/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1" +PL/pgSQL function dynsql1(integer) line 4 at EXECUTE + dynsql1 +--------- + 8999 +(1 row) + +SELECT pg_sleep(1); + pg_sleep +---------- + +(1 row) + +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'; + relname | seq_scan | idx_scan +---------+----------+---------- + t1 | f | t +(1 row) + +-- 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(); + reset_stats_and_wait +---------------------- + +(1 row) + +SELECT dynsql2(9000); + dynsql2 +--------- + 9900 +(1 row) + +SELECT pg_sleep(1); + pg_sleep +---------- + +(1 row) + +-- 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'); + relname | seq_scan | idx_scan +---------+----------+---------- + p1_c1 | 1 | 0 + p1_c2 | 1 | 1 +(2 rows) + +SET pg_hint_plan.enable_hint = true; +SELECT reset_stats_and_wait(); + reset_stats_and_wait +---------------------- + +(1 row) + +SELECT dynsql2(9000); +LOG: available indexes for IndexScan(p1_c1): p1_c1_pkey +CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000" +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement +LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_pkey +CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000" +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement +LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_pkey +CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000" +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement +LOG: pg_hint_plan: +used hint: +IndexScan(p1_c1 p1_c1_pkey) +not used hint: +duplication hint: +error hint: + +CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000" +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement +LOG: available indexes for IndexScan(p1_c2): p1_c2_pkey +CONTEXT: SQL statement "/*+ IndexScan(p1_c2 p1_c2_pkey) */ SELECT sum(val) FROM p1_c2 WHERE id < 9000" +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement +LOG: pg_hint_plan: +used hint: +IndexScan(p1_c2 p1_c2_pkey) +not used hint: +duplication hint: +error hint: + +CONTEXT: SQL statement "/*+ IndexScan(p1_c2 p1_c2_pkey) */ SELECT sum(val) FROM p1_c2 WHERE id < 9000" +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement + dynsql2 +--------- + 9900 +(1 row) + +SELECT pg_sleep(1); + pg_sleep +---------- + +(1 row) + +-- 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'); + relname | seq_scan | idx_scan +---------+----------+---------- + p1_c1 | 0 | 1 + p1_c2 | 0 | 2 +(2 rows) + -- -- Rows hint tests -- diff --git a/pg_hint_plan.c b/pg_hint_plan.c index 78d2637..3afa34c 100644 --- a/pg_hint_plan.c +++ b/pg_hint_plan.c @@ -4730,7 +4730,7 @@ void plpgsql_query_erase_callback(ResourceReleasePhase phase, bool isTopLevel, void *arg) { - if (phase != RESOURCE_RELEASE_AFTER_LOCKS) + if (!isTopLevel || phase != RESOURCE_RELEASE_AFTER_LOCKS) return; /* Cancel plpgsql nest level*/ plpgsql_recurse_level = 0; diff --git a/sql/pg_hint_plan.sql b/sql/pg_hint_plan.sql index f8172b2..5e480de 100644 --- a/sql/pg_hint_plan.sql +++ b/sql/pg_hint_plan.sql @@ -947,6 +947,67 @@ 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 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'); + -- -- Rows hint tests -- -- 2.11.0