• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: コミット

firtst release


コミットメタ情報

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