• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: コミット

firtst release


コミットメタ情報

リビジョンc9864a4b043d9a127167ab2393758e45a744ba88 (tree)
日時2018-12-05 17:31:48
作者Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
コミッターKyotaro Horiguchi

ログメッセージ

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
<something>
END LOOP;

END LOOP;

変更サマリ

差分

--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -8178,6 +8178,185 @@ error hint:
81788178
81798179 DROP FUNCTION testfunc();
81808180 DROP EXTENSION pg_hint_plan;
8181+CREATE FUNCTION reset_stats_and_wait() RETURNS void AS $$
8182+DECLARE
8183+ rows int;
8184+BEGIN
8185+ rows = 1;
8186+ while rows > 0 LOOP
8187+ PERFORM pg_stat_reset();
8188+ PERFORM pg_sleep(0.5);
8189+ SELECT sum(seq_scan + idx_scan) from pg_stat_user_tables into rows;
8190+ END LOOP;
8191+END;
8192+$$ LANGUAGE plpgsql;
8193+-- Dynamic query in pl/pgsql
8194+CREATE OR REPLACE FUNCTION dynsql1(x int) RETURNS int AS $$
8195+DECLARE c int;
8196+BEGIN
8197+ EXECUTE '/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1'
8198+ INTO c USING x;
8199+ RETURN c;
8200+END;
8201+$$ VOLATILE LANGUAGE plpgsql;
8202+vacuum analyze t1;
8203+SET pg_hint_plan.enable_hint = false;
8204+SELECT reset_stats_and_wait();
8205+ reset_stats_and_wait
8206+----------------------
8207+
8208+(1 row)
8209+
8210+SELECT dynsql1(9000);
8211+ dynsql1
8212+---------
8213+ 8999
8214+(1 row)
8215+
8216+SELECT pg_sleep(1);
8217+ pg_sleep
8218+----------
8219+
8220+(1 row)
8221+
8222+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';
8223+ relname | seq_scan | idx_scan
8224+---------+----------+----------
8225+ t1 | t | f
8226+(1 row)
8227+
8228+SET pg_hint_plan.enable_hint = true;
8229+SELECT reset_stats_and_wait();
8230+ reset_stats_and_wait
8231+----------------------
8232+
8233+(1 row)
8234+
8235+SELECT dynsql1(9000);
8236+LOG: pg_hint_plan:
8237+used hint:
8238+IndexScan(t1)
8239+not used hint:
8240+duplication hint:
8241+error hint:
8242+
8243+CONTEXT: SQL statement "/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1"
8244+PL/pgSQL function dynsql1(integer) line 4 at EXECUTE
8245+ dynsql1
8246+---------
8247+ 8999
8248+(1 row)
8249+
8250+SELECT pg_sleep(1);
8251+ pg_sleep
8252+----------
8253+
8254+(1 row)
8255+
8256+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';
8257+ relname | seq_scan | idx_scan
8258+---------+----------+----------
8259+ t1 | f | t
8260+(1 row)
8261+
8262+-- Looped dynamic query in pl/pgsql
8263+CREATE OR REPLACE FUNCTION dynsql2(x int, OUT r int) AS $$
8264+DECLARE
8265+ c text;
8266+ s int;
8267+BEGIN
8268+ r := 0;
8269+ FOR c IN SELECT f.f FROM (VALUES ('p1_c1'), ('p1_c2')) f(f) LOOP
8270+ FOR s IN EXECUTE '/*+ IndexScan(' || c || ' ' || c || '_pkey) */ SELECT sum(val) FROM ' || c || ' WHERE id < ' || x LOOP
8271+ r := r + s;
8272+ END LOOP;
8273+ END LOOP;
8274+END;
8275+$$ VOLATILE LANGUAGE plpgsql;
8276+SET pg_hint_plan.enable_hint = false;
8277+SELECT reset_stats_and_wait();
8278+ reset_stats_and_wait
8279+----------------------
8280+
8281+(1 row)
8282+
8283+SELECT dynsql2(9000);
8284+ dynsql2
8285+---------
8286+ 9900
8287+(1 row)
8288+
8289+SELECT pg_sleep(1);
8290+ pg_sleep
8291+----------
8292+
8293+(1 row)
8294+
8295+-- one of the index scans happened while planning.
8296+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
8297+ relname | seq_scan | idx_scan
8298+---------+----------+----------
8299+ p1_c1 | 1 | 0
8300+ p1_c2 | 1 | 1
8301+(2 rows)
8302+
8303+SET pg_hint_plan.enable_hint = true;
8304+SELECT reset_stats_and_wait();
8305+ reset_stats_and_wait
8306+----------------------
8307+
8308+(1 row)
8309+
8310+SELECT dynsql2(9000);
8311+LOG: available indexes for IndexScan(p1_c1): p1_c1_pkey
8312+CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
8313+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
8314+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_pkey
8315+CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
8316+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
8317+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_pkey
8318+CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
8319+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
8320+LOG: pg_hint_plan:
8321+used hint:
8322+IndexScan(p1_c1 p1_c1_pkey)
8323+not used hint:
8324+duplication hint:
8325+error hint:
8326+
8327+CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
8328+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
8329+LOG: available indexes for IndexScan(p1_c2): p1_c2_pkey
8330+CONTEXT: SQL statement "/*+ IndexScan(p1_c2 p1_c2_pkey) */ SELECT sum(val) FROM p1_c2 WHERE id < 9000"
8331+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
8332+LOG: pg_hint_plan:
8333+used hint:
8334+IndexScan(p1_c2 p1_c2_pkey)
8335+not used hint:
8336+duplication hint:
8337+error hint:
8338+
8339+CONTEXT: SQL statement "/*+ IndexScan(p1_c2 p1_c2_pkey) */ SELECT sum(val) FROM p1_c2 WHERE id < 9000"
8340+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
8341+ dynsql2
8342+---------
8343+ 9900
8344+(1 row)
8345+
8346+SELECT pg_sleep(1);
8347+ pg_sleep
8348+----------
8349+
8350+(1 row)
8351+
8352+-- the index scan happened while planning.
8353+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
8354+ relname | seq_scan | idx_scan
8355+---------+----------+----------
8356+ p1_c1 | 0 | 1
8357+ p1_c2 | 0 | 2
8358+(2 rows)
8359+
81818360 --
81828361 -- Rows hint tests
81838362 --
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -4590,7 +4590,7 @@ void plpgsql_query_erase_callback(ResourceReleasePhase phase,
45904590 bool isTopLevel,
45914591 void *arg)
45924592 {
4593- if (phase != RESOURCE_RELEASE_AFTER_LOCKS)
4593+ if (!isTopLevel || phase != RESOURCE_RELEASE_AFTER_LOCKS)
45944594 return;
45954595 /* Cancel plpgsql nest level*/
45964596 plpgsql_recurse_level = 0;
--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -947,6 +947,67 @@ SELECT * FROM testfunc() LIMIT 1;
947947 DROP FUNCTION testfunc();
948948 DROP EXTENSION pg_hint_plan;
949949
950+CREATE FUNCTION reset_stats_and_wait() RETURNS void AS $$
951+DECLARE
952+ rows int;
953+BEGIN
954+ rows = 1;
955+ while rows > 0 LOOP
956+ PERFORM pg_stat_reset();
957+ PERFORM pg_sleep(0.5);
958+ SELECT sum(seq_scan + idx_scan) from pg_stat_user_tables into rows;
959+ END LOOP;
960+END;
961+$$ LANGUAGE plpgsql;
962+
963+-- Dynamic query in pl/pgsql
964+CREATE OR REPLACE FUNCTION dynsql1(x int) RETURNS int AS $$
965+DECLARE c int;
966+BEGIN
967+ EXECUTE '/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1'
968+ INTO c USING x;
969+ RETURN c;
970+END;
971+$$ VOLATILE LANGUAGE plpgsql;
972+vacuum analyze t1;
973+SET pg_hint_plan.enable_hint = false;
974+SELECT reset_stats_and_wait();
975+SELECT dynsql1(9000);
976+SELECT pg_sleep(1);
977+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';
978+SET pg_hint_plan.enable_hint = true;
979+SELECT reset_stats_and_wait();
980+SELECT dynsql1(9000);
981+SELECT pg_sleep(1);
982+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';
983+
984+-- Looped dynamic query in pl/pgsql
985+CREATE OR REPLACE FUNCTION dynsql2(x int, OUT r int) AS $$
986+DECLARE
987+ c text;
988+ s int;
989+BEGIN
990+ r := 0;
991+ FOR c IN SELECT f.f FROM (VALUES ('p1_c1'), ('p1_c2')) f(f) LOOP
992+ FOR s IN EXECUTE '/*+ IndexScan(' || c || ' ' || c || '_pkey) */ SELECT sum(val) FROM ' || c || ' WHERE id < ' || x LOOP
993+ r := r + s;
994+ END LOOP;
995+ END LOOP;
996+END;
997+$$ VOLATILE LANGUAGE plpgsql;
998+SET pg_hint_plan.enable_hint = false;
999+SELECT reset_stats_and_wait();
1000+SELECT dynsql2(9000);
1001+SELECT pg_sleep(1);
1002+-- one of the index scans happened while planning.
1003+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
1004+SET pg_hint_plan.enable_hint = true;
1005+SELECT reset_stats_and_wait();
1006+SELECT dynsql2(9000);
1007+SELECT pg_sleep(1);
1008+-- the index scan happened while planning.
1009+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
1010+
9501011 --
9511012 -- Rows hint tests
9521013 --
旧リポジトリブラウザで表示