• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revisionfc1926d6aef32590f29eb648119dbbafadd57484 (tree)
Time2020-02-13 19:39:37
AuthorKyotaro Horiguchi <horikyota.ntt@gmai...>
CommiterKyotaro Horiguchi

Log Message

Stabilize regression test and catching-up to the core.

Further unstability is found about stats reset. Inserted more 1 second
sleeps to stabilize it. Some planner fix (maybe 72a626e688) seems to
have changed its behvavior. The commit 041ad9a66d changes the result
of A-8-4-10 in ut-A. The commit 88275ac199, which changed the category
of client_min_messages, affected ut-A's result. Follow the changes.

Most of this fix is comes from the pull request
https://github.com/ossc-db/pg_hint_plan/pull/28 by Dagouhan (smoon
<30999182+Dagouhan@users.noreply.github.com>).

Change Summary

Incremental Difference

--- a/expected/init.out
+++ b/expected/init.out
@@ -164,8 +164,9 @@ SELECT name, setting, category
164164 OR name = 'client_min_messages'
165165 ORDER BY category, name;
166166 SELECT * FROM settings;
167- name | setting | category
168----------------------------+-----------+---------------------------------------------
167+ name | setting | category
168+---------------------------+-----------+-------------------------------------------------
169+ client_min_messages | notice | Client Connection Defaults / Statement Behavior
169170 geqo | on | Query Tuning / Genetic Query Optimizer
170171 geqo_effort | 5 | Query Tuning / Genetic Query Optimizer
171172 geqo_generations | 0 | Query Tuning / Genetic Query Optimizer
@@ -195,7 +196,6 @@ SELECT * FROM settings;
195196 enable_seqscan | on | Query Tuning / Planner Method Configuration
196197 enable_sort | on | Query Tuning / Planner Method Configuration
197198 enable_tidscan | on | Query Tuning / Planner Method Configuration
198- client_min_messages | notice | Reporting and Logging / When to Log
199199 (30 rows)
200200
201201 ANALYZE;
--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -10,13 +10,13 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
1010 (4 rows)
1111
1212 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
13- QUERY PLAN
14--------------------------------------------
15- Merge Join
16- Merge Cond: (t2.val = t1.val)
17- -> Index Scan using t2_val on t2
18- -> Materialize
19- -> Index Scan using t1_val on t1
13+ QUERY PLAN
14+--------------------------------
15+ Hash Join
16+ Hash Cond: (t2.val = t1.val)
17+ -> Seq Scan on t2
18+ -> Hash
19+ -> Seq Scan on t1
2020 (5 rows)
2121
2222 LOAD 'pg_hint_plan';
@@ -31,13 +31,13 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
3131 (4 rows)
3232
3333 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
34- QUERY PLAN
35--------------------------------------------
36- Merge Join
37- Merge Cond: (t2.val = t1.val)
38- -> Index Scan using t2_val on t2
39- -> Materialize
40- -> Index Scan using t1_val on t1
34+ QUERY PLAN
35+--------------------------------
36+ Hash Join
37+ Hash Cond: (t2.val = t1.val)
38+ -> Seq Scan on t2
39+ -> Hash
40+ -> Seq Scan on t1
4141 (5 rows)
4242
4343 /*+ Test (t1 t2) */
@@ -8187,6 +8187,12 @@ END;
81878187 $$ VOLATILE LANGUAGE plpgsql;
81888188 vacuum analyze t1;
81898189 SET pg_hint_plan.enable_hint = false;
8190+SELECT pg_sleep(1);
8191+ pg_sleep
8192+----------
8193+
8194+(1 row)
8195+
81908196 SELECT reset_stats_and_wait();
81918197 reset_stats_and_wait
81928198 ----------------------
@@ -8212,6 +8218,12 @@ SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_
82128218 (1 row)
82138219
82148220 SET pg_hint_plan.enable_hint = true;
8221+SELECT pg_sleep(1);
8222+ pg_sleep
8223+----------
8224+
8225+(1 row)
8226+
82158227 SELECT reset_stats_and_wait();
82168228 reset_stats_and_wait
82178229 ----------------------
@@ -8260,6 +8272,12 @@ BEGIN
82608272 END;
82618273 $$ VOLATILE LANGUAGE plpgsql;
82628274 SET pg_hint_plan.enable_hint = false;
8275+SELECT pg_sleep(1);
8276+ pg_sleep
8277+----------
8278+
8279+(1 row)
8280+
82638281 SELECT reset_stats_and_wait();
82648282 reset_stats_and_wait
82658283 ----------------------
@@ -8287,6 +8305,12 @@ SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = '
82878305 (2 rows)
82888306
82898307 SET pg_hint_plan.enable_hint = true;
8308+SELECT pg_sleep(1);
8309+ pg_sleep
8310+----------
8311+
8312+(1 row)
8313+
82908314 SELECT reset_stats_and_wait();
82918315 reset_stats_and_wait
82928316 ----------------------
--- a/expected/ut-A.out
+++ b/expected/ut-A.out
@@ -2099,6 +2099,8 @@ ERROR: pg_hint_plan: hint syntax error at or near ""
20992099 DETAIL: Opening parenthesis is necessary.
21002100 SET client_min_messages TO fatal;
21012101 /*+Set*/SELECT 1;
2102+ERROR: pg_hint_plan: hint syntax error at or near ""
2103+DETAIL: Opening parenthesis is necessary.
21022104 -- No. A-8-4-11
21032105 RESET client_min_messages;
21042106 SET pg_hint_plan.parse_messages TO DEFAULT;
@@ -3309,6 +3311,7 @@ NestLoop(t1 t1)
33093311 SELECT name, setting FROM settings;
33103312 name | setting
33113313 ---------------------------+-----------
3314+ client_min_messages | log
33123315 geqo | on
33133316 geqo_effort | 5
33143317 geqo_generations | 0
@@ -3338,7 +3341,6 @@ SELECT name, setting FROM settings;
33383341 enable_seqscan | on
33393342 enable_sort | on
33403343 enable_tidscan | on
3341- client_min_messages | log
33423344 (30 rows)
33433345
33443346 SET pg_hint_plan.parse_messages TO error;
@@ -3349,6 +3351,7 @@ DETAIL: Relation name "t1" is duplicated.
33493351 SELECT name, setting FROM settings;
33503352 name | setting
33513353 ---------------------------+-----------
3354+ client_min_messages | log
33523355 geqo | on
33533356 geqo_effort | 5
33543357 geqo_generations | 0
@@ -3378,7 +3381,6 @@ SELECT name, setting FROM settings;
33783381 enable_seqscan | on
33793382 enable_sort | on
33803383 enable_tidscan | on
3381- client_min_messages | log
33823384 (30 rows)
33833385
33843386 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
@@ -3408,6 +3410,7 @@ error hint:
34083410 SELECT name, setting FROM settings;
34093411 name | setting
34103412 ---------------------------+-----------
3413+ client_min_messages | log
34113414 geqo | on
34123415 geqo_effort | 5
34133416 geqo_generations | 0
@@ -3437,7 +3440,6 @@ SELECT name, setting FROM settings;
34373440 enable_seqscan | on
34383441 enable_sort | on
34393442 enable_tidscan | on
3440- client_min_messages | log
34413443 (30 rows)
34423444
34433445 SET pg_hint_plan.parse_messages TO error;
@@ -3448,6 +3450,7 @@ DETAIL: Relation name "t1" is duplicated.
34483450 SELECT name, setting FROM settings;
34493451 name | setting
34503452 ---------------------------+-----------
3453+ client_min_messages | log
34513454 geqo | on
34523455 geqo_effort | 5
34533456 geqo_generations | 0
@@ -3477,7 +3480,6 @@ SELECT name, setting FROM settings;
34773480 enable_seqscan | on
34783481 enable_sort | on
34793482 enable_tidscan | on
3480- client_min_messages | log
34813483 (30 rows)
34823484
34833485 EXPLAIN (COSTS false) EXECUTE p1;
@@ -3496,6 +3498,7 @@ EXPLAIN (COSTS false) EXECUTE p1;
34963498 SELECT name, setting FROM settings;
34973499 name | setting
34983500 ---------------------------+-----------
3501+ client_min_messages | log
34993502 geqo | on
35003503 geqo_effort | 5
35013504 geqo_generations | 0
@@ -3525,7 +3528,6 @@ SELECT name, setting FROM settings;
35253528 enable_seqscan | on
35263529 enable_sort | on
35273530 enable_tidscan | on
3528- client_min_messages | log
35293531 (30 rows)
35303532
35313533 SET pg_hint_plan.parse_messages TO error;
@@ -3567,6 +3569,7 @@ EXPLAIN (COSTS false) EXECUTE p1;
35673569 SELECT name, setting FROM settings;
35683570 name | setting
35693571 ---------------------------+-----------
3572+ client_min_messages | log
35703573 geqo | on
35713574 geqo_effort | 5
35723575 geqo_generations | 0
@@ -3596,7 +3599,6 @@ SELECT name, setting FROM settings;
35963599 enable_seqscan | on
35973600 enable_sort | on
35983601 enable_tidscan | on
3599- client_min_messages | log
36003602 (30 rows)
36013603
36023604 -- No. A-12-1-4
@@ -3604,6 +3606,7 @@ SELECT name, setting FROM settings;
36043606 SELECT name, setting FROM settings;
36053607 name | setting
36063608 ---------------------------+-----------
3609+ client_min_messages | log
36073610 geqo | on
36083611 geqo_effort | 5
36093612 geqo_generations | 0
@@ -3633,7 +3636,6 @@ SELECT name, setting FROM settings;
36333636 enable_seqscan | on
36343637 enable_sort | on
36353638 enable_tidscan | on
3636- client_min_messages | log
36373639 (30 rows)
36383640
36393641 SET pg_hint_plan.parse_messages TO error;
@@ -3653,6 +3655,7 @@ EXPLAIN (COSTS false) EXECUTE p1;
36533655 SELECT name, setting FROM settings;
36543656 name | setting
36553657 ---------------------------+-----------
3658+ client_min_messages | log
36563659 geqo | on
36573660 geqo_effort | 5
36583661 geqo_generations | 0
@@ -3682,7 +3685,6 @@ SELECT name, setting FROM settings;
36823685 enable_seqscan | on
36833686 enable_sort | on
36843687 enable_tidscan | on
3685- client_min_messages | log
36863688 (30 rows)
36873689
36883690 DEALLOCATE p1;
@@ -3717,6 +3719,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
37173719 SELECT name, setting FROM settings;
37183720 name | setting
37193721 ---------------------------+-----------
3722+ client_min_messages | log
37203723 geqo | on
37213724 geqo_effort | 5
37223725 geqo_generations | 0
@@ -3746,7 +3749,6 @@ SELECT name, setting FROM settings;
37463749 enable_seqscan | on
37473750 enable_sort | on
37483751 enable_tidscan | on
3749- client_min_messages | log
37503752 (30 rows)
37513753
37523754 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
@@ -3773,6 +3775,7 @@ error hint:
37733775 SELECT name, setting FROM settings;
37743776 name | setting
37753777 ---------------------------+-----------
3778+ client_min_messages | log
37763779 geqo | on
37773780 geqo_effort | 5
37783781 geqo_generations | 0
@@ -3802,7 +3805,6 @@ SELECT name, setting FROM settings;
38023805 enable_seqscan | on
38033806 enable_sort | on
38043807 enable_tidscan | on
3805- client_min_messages | log
38063808 (30 rows)
38073809
38083810 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
@@ -3831,6 +3833,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
38313833 SELECT name, setting FROM settings;
38323834 name | setting
38333835 ---------------------------+-----------
3836+ client_min_messages | log
38343837 geqo | on
38353838 geqo_effort | 5
38363839 geqo_generations | 0
@@ -3860,7 +3863,6 @@ SELECT name, setting FROM settings;
38603863 enable_seqscan | on
38613864 enable_sort | on
38623865 enable_tidscan | on
3863- client_min_messages | log
38643866 (30 rows)
38653867
38663868 BEGIN;
@@ -3890,6 +3892,7 @@ BEGIN;
38903892 SELECT name, setting FROM settings;
38913893 name | setting
38923894 ---------------------------+-----------
3895+ client_min_messages | log
38933896 geqo | on
38943897 geqo_effort | 5
38953898 geqo_generations | 0
@@ -3919,7 +3922,6 @@ SELECT name, setting FROM settings;
39193922 enable_seqscan | on
39203923 enable_sort | on
39213924 enable_tidscan | on
3922- client_min_messages | log
39233925 (30 rows)
39243926
39253927 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
@@ -3949,6 +3951,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
39493951 SELECT name, setting FROM settings;
39503952 name | setting
39513953 ---------------------------+-----------
3954+ client_min_messages | log
39523955 geqo | on
39533956 geqo_effort | 5
39543957 geqo_generations | 0
@@ -3978,7 +3981,6 @@ SELECT name, setting FROM settings;
39783981 enable_seqscan | on
39793982 enable_sort | on
39803983 enable_tidscan | on
3981- client_min_messages | log
39823984 (30 rows)
39833985
39843986 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
@@ -4009,6 +4011,7 @@ LOAD 'pg_hint_plan';
40094011 SELECT name, setting FROM settings;
40104012 name | setting
40114013 ---------------------------+-----------
4014+ client_min_messages | notice
40124015 geqo | on
40134016 geqo_effort | 5
40144017 geqo_generations | 0
@@ -4038,7 +4041,6 @@ SELECT name, setting FROM settings;
40384041 enable_seqscan | on
40394042 enable_sort | on
40404043 enable_tidscan | on
4041- client_min_messages | notice
40424044 (30 rows)
40434045
40444046 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -966,11 +966,13 @@ END;
966966 $$ VOLATILE LANGUAGE plpgsql;
967967 vacuum analyze t1;
968968 SET pg_hint_plan.enable_hint = false;
969+SELECT pg_sleep(1);
969970 SELECT reset_stats_and_wait();
970971 SELECT dynsql1(9000);
971972 SELECT pg_sleep(1);
972973 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';
973974 SET pg_hint_plan.enable_hint = true;
975+SELECT pg_sleep(1);
974976 SELECT reset_stats_and_wait();
975977 SELECT dynsql1(9000);
976978 SELECT pg_sleep(1);
@@ -991,12 +993,14 @@ BEGIN
991993 END;
992994 $$ VOLATILE LANGUAGE plpgsql;
993995 SET pg_hint_plan.enable_hint = false;
996+SELECT pg_sleep(1);
994997 SELECT reset_stats_and_wait();
995998 SELECT dynsql2(9000);
996999 SELECT pg_sleep(1);
9971000 -- one of the index scans happened while planning.
9981001 SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
9991002 SET pg_hint_plan.enable_hint = true;
1003+SELECT pg_sleep(1);
10001004 SELECT reset_stats_and_wait();
10011005 SELECT dynsql2(9000);
10021006 SELECT pg_sleep(1);
Show on old repository browser