LOAD 'pg_hint_plan';
+-- We cannot do ALTER USER current_user SET ...
+DELETE FROM pg_db_role_setting WHERE setrole = (SELECT oid FROM pg_roles WHERE rolname = current_user);
+INSERT INTO pg_db_role_setting (SELECT 0, (SELECT oid FROM pg_roles WHERE rolname = current_user), '{client_min_messages=log,pg_hint_plan.debug_print=on}');
+ALTER SYSTEM SET session_preload_libraries TO 'pg_hint_plan';
+SELECT pg_reload_conf();
SET pg_hint_plan.enable_hint TO on;
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
SET search_path TO public;
+SET max_parallel_workers_per_gather TO 0;
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1;
-- No. S-2-4-2
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
/*+SeqScan(t1 t2)*/
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
/*+SeqScan(*VALUES*)*/
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
----
---- No. S-3-1 scan method hint
/*+BitmapScan(ti1 ti1_ts)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
--- No. S-3-4-10
+-- No. S-3-4-21
/*+BitmapScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
--- No. S-3-4-11
+-- No. S-3-4-22
/*+BitmapScan(ti1 ti1_c2_key)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
----
-- No. S-3-5-1
-/*+IndexScan(ti1 ti1_pred)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+IndexScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
-- No. S-3-5-2
-/*+BitmapScan(ti1 ti1_pred)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+BitmapScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
-- No. S-3-5-3
-/*+IndexOnlyScan(ti1 ti1_pred)*/
-EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+IndexOnlyScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
-- No. S-3-5-4
-/*+IndexScan(ti1 not_exist)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+IndexScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
-- No. S-3-5-5
-/*+BitmapScan(ti1 not_exist)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+BitmapScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
-- No. S-3-5-6
-/*+IndexOnlyScan(ti1 not_exist)*/
-EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+IndexOnlyScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
-- No. S-3-5-7
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-/*+TidScan(t1)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+\! psql contrib_regression -c "/*+TidScan(t1)*/ EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1" | grep -v "Planning time:"
----
---- No. S-3-6 query structure
SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
----
----- No. S-3-8 inheritance table select type
+---- No. S-3-8 inheritance table select/update type
----
-- No. S-3-8-1
/*+IndexScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
+-- No. S-3-8-3
+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
+/*+IndexScan(p1)*/
+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
+/*+IndexScan(p1 p1_pkey)*/
+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
+
+-- No. S-3-8-4
+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
+/*+IndexScan(p1)*/
+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
+/*+IndexScan(p1 p1_pkey)*/
+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
+
----
---- No. S-3-9 inheritance table number
----
EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
-- No. S-3-10-3
+\o results/ut-S.tmpout
EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
-/*+IndexScan(p1 p1_parent)*/
-EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+
+\o results/ut-S.tmpout
+/*+IndexScan(p1 p1_parent)*/ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+
-- No. S-3-10-4
-/*+IndexScan(p1 p1_i2)*/
-EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1;
+\o results/ut-S.tmpout
+/*+IndexScan(p1 p1_i2)*/ EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1;
+\o
+\! sql/maskout.sh results/ut-S.tmpout
-- No. S-3-10-5
-/*+IndexScan(p2 p2c1_pkey)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1;
+\o results/ut-S.tmpout
+/*+IndexScan(p2 p2c1_pkey)*/ EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1;
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+
----
---- No. S-3-12 specified same table
-- No. S-3-15-5
/*+IndexScan(ti1 not_exist1 not_exist2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+
+DELETE FROM pg_db_role_setting WHERE setrole = (SELECT oid FROM pg_roles WHERE rolname = current_user);
+
+ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
+SELECT pg_reload_conf();
+\! rm results/ut-S.tmpout