X-Git-Url: http://git.osdn.net/view?a=blobdiff_plain;f=expected%2Fut-S.out;h=e8dae590351adbf0f78c559041b79f719bf0a4fb;hb=c1c3c56eb2ee8fb1fd46e062c94902f17043cc9f;hp=7b5bc1a837bd86edabec57a46f273695cf8fd2f4;hpb=d87e00c07964d48098b8199ef7fd2371624574d7;p=pghintplan%2Fpg_hint_plan.git diff --git a/expected/ut-S.out b/expected/ut-S.out index 7b5bc1a..e8dae59 100644 --- a/expected/ut-S.out +++ b/expected/ut-S.out @@ -1,8 +1,19 @@ 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(); + pg_reload_conf +---------------- + t +(1 row) + 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; QUERY PLAN --------------------- @@ -445,7 +456,7 @@ EXPLAIN (COSTS false) SELECT * FROM pg_catalog.pg_class WHERE oid = 1; QUERY PLAN ------------------------------------------------- Index Scan using pg_class_oid_index on pg_class - Index Cond: (oid = 1::oid) + Index Cond: (oid = '1'::oid) (2 rows) /*+SeqScan(pg_class)*/ @@ -457,10 +468,10 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------- Seq Scan on pg_class - Filter: (oid = 1::oid) + Filter: (oid = '1'::oid) (2 rows) -- No. S-1-5-6 @@ -631,7 +642,6 @@ SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1 InitPlan 1 (returns $1) -> Aggregate -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) -> Merge Join Merge Cond: (b1t1.c1 = b1t2.c1) -> Merge Join @@ -642,9 +652,8 @@ SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1 Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 -> Index Only Scan using t4_i1 on t4 b1t4 - Index Cond: (c1 = b1t3.c1) + Index Cond: (c1 = b1t1.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join @@ -655,8 +664,8 @@ SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1 Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 -> Index Only Scan using t4_i1 on t4 bmt4 - Index Cond: (c1 = bmt3.c1) -(29 rows) + Index Cond: (c1 = bmt1.c1) +(27 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) @@ -735,7 +744,6 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 InitPlan 1 (returns $1) -> Aggregate -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) -> Merge Join Merge Cond: (b1t1.c1 = b1t2.c1) -> Merge Join @@ -746,11 +754,10 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 -> Index Only Scan using t4_i1 on t4 b1t4 - Index Cond: (c1 = b1t3.c1) + Index Cond: (c1 = b1t1.c1) InitPlan 2 (returns $3) -> Aggregate -> Nested Loop - Join Filter: (b2t1.c1 = b2t4.c1) -> Merge Join Merge Cond: (b2t1.c1 = b2t2.c1) -> Merge Join @@ -761,9 +768,8 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 -> Index Only Scan using t4_i1 on t4 b2t4 - Index Cond: (c1 = b2t3.c1) + Index Cond: (c1 = b2t1.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join @@ -774,8 +780,8 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 -> Index Only Scan using t4_i1 on t4 bmt4 - Index Cond: (c1 = bmt3.c1) -(44 rows) + Index Cond: (c1 = bmt1.c1) +(41 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) @@ -871,7 +877,6 @@ EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt ---------------------------------------------------------------- Aggregate -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join @@ -882,8 +887,8 @@ EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 -> Index Only Scan using t4_i1 on t4 bmt4 - Index Cond: (c1 = bmt3.c1) -(14 rows) + Index Cond: (c1 = bmt1.c1) +(13 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) */ @@ -925,7 +930,6 @@ EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT * ---------------------------------------------------------------- Aggregate -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join @@ -936,8 +940,8 @@ EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT * Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 -> Index Only Scan using t4_i1 on t4 bmt4 - Index Cond: (c1 = bmt3.c1) -(14 rows) + Index Cond: (c1 = bmt1.c1) +(13 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) */ @@ -986,7 +990,6 @@ SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1 InitPlan 1 (returns $1) -> Aggregate -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) -> Merge Join Merge Cond: (b1t1.c1 = b1t2.c1) -> Merge Join @@ -997,9 +1000,8 @@ SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1 Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 -> Index Only Scan using t4_i1 on t4 b1t4 - Index Cond: (c1 = b1t3.c1) + Index Cond: (c1 = b1t1.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join @@ -1011,8 +1013,8 @@ SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1 Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 -> Index Only Scan using t4_i1 on t4 bmt4 - Index Cond: (c1 = bmt3.c1) -(30 rows) + Index Cond: (c1 = bmt1.c1) +(28 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) @@ -1092,7 +1094,6 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 InitPlan 1 (returns $1) -> Aggregate -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) -> Merge Join Merge Cond: (b1t1.c1 = b1t2.c1) -> Merge Join @@ -1103,11 +1104,10 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 -> Index Only Scan using t4_i1 on t4 b1t4 - Index Cond: (c1 = b1t3.c1) + Index Cond: (c1 = b1t1.c1) InitPlan 2 (returns $3) -> Aggregate -> Nested Loop - Join Filter: (b2t1.c1 = b2t4.c1) -> Merge Join Merge Cond: (b2t1.c1 = b2t2.c1) -> Merge Join @@ -1118,9 +1118,8 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 -> Index Only Scan using t4_i1 on t4 b2t4 - Index Cond: (c1 = b2t3.c1) + Index Cond: (c1 = b2t1.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join @@ -1132,8 +1131,8 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 -> Index Only Scan using t4_i1 on t4 bmt4 - Index Cond: (c1 = bmt3.c1) -(45 rows) + Index Cond: (c1 = bmt1.c1) +(42 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) @@ -1240,7 +1239,6 @@ AND bmt1.c1 = c1.c1 CTE c1 -> Aggregate -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) -> Merge Join Merge Cond: (b1t1.c1 = b1t2.c1) -> Merge Join @@ -1251,24 +1249,22 @@ AND bmt1.c1 = c1.c1 Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 -> Index Only Scan using t4_i1 on t4 b1t4 - Index Cond: (c1 = b1t3.c1) + Index Cond: (c1 = b1t1.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop - Join Filter: (bmt2.c1 = bmt1.c1) -> Nested Loop -> Hash Join Hash Cond: (bmt2.c1 = c1.c1) -> Seq Scan on t2 bmt2 -> Hash -> CTE Scan on c1 - -> Index Only Scan using t3_i1 on t3 bmt3 + -> Index Only Scan using t1_i1 on t1 bmt1 Index Cond: (c1 = bmt2.c1) - -> Index Only Scan using t1_i1 on t1 bmt1 - Index Cond: (c1 = bmt3.c1) + -> Index Only Scan using t3_i1 on t3 bmt3 + Index Cond: (c1 = bmt1.c1) -> Index Only Scan using t4_i1 on t4 bmt4 - Index Cond: (c1 = bmt3.c1) -(32 rows) + Index Cond: (c1 = bmt1.c1) +(29 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) @@ -1358,7 +1354,6 @@ AND bmt1.c1 = c2.c1 CTE c1 -> Aggregate -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) -> Merge Join Merge Cond: (b1t1.c1 = b1t2.c1) -> Merge Join @@ -1369,11 +1364,10 @@ AND bmt1.c1 = c2.c1 Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 -> Index Only Scan using t4_i1 on t4 b1t4 - Index Cond: (c1 = b1t3.c1) + Index Cond: (c1 = b1t1.c1) CTE c2 -> Aggregate -> Nested Loop - Join Filter: (b2t1.c1 = b2t4.c1) -> Merge Join Merge Cond: (b2t1.c1 = b2t2.c1) -> Merge Join @@ -1384,27 +1378,25 @@ AND bmt1.c1 = c2.c1 Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 -> Index Only Scan using t4_i1 on t4 b2t4 - Index Cond: (c1 = b2t3.c1) + Index Cond: (c1 = b2t1.c1) -> Nested Loop Join Filter: (bmt1.c1 = c2.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop - Join Filter: (bmt2.c1 = bmt1.c1) -> Nested Loop -> Hash Join Hash Cond: (bmt2.c1 = c1.c1) -> Seq Scan on t2 bmt2 -> Hash -> CTE Scan on c1 - -> Index Only Scan using t3_i1 on t3 bmt3 + -> Index Only Scan using t1_i1 on t1 bmt1 Index Cond: (c1 = bmt2.c1) - -> Index Only Scan using t1_i1 on t1 bmt1 - Index Cond: (c1 = bmt3.c1) + -> Index Only Scan using t3_i1 on t3 bmt3 + Index Cond: (c1 = bmt1.c1) -> Index Only Scan using t4_i1 on t4 bmt4 - Index Cond: (c1 = bmt3.c1) + Index Cond: (c1 = bmt1.c1) -> CTE Scan on c2 -(50 rows) +(46 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) @@ -1734,10 +1726,10 @@ error hint: Index Cond: (c1 = b3t1.c1) -> Nested Loop -> Nested Loop + Join Filter: (bmt1.c1 = bmt2.c1) -> Seq Scan on t1 bmt1 Filter: ((c1 <> $4) AND (ctid = '(1,1)'::tid)) -> Index Scan using t2_pkey on t2 bmt2 - Index Cond: (c1 = bmt1.c1) Filter: (ctid = '(1,1)'::tid) -> CTE Scan on c1 (37 rows) @@ -1879,7 +1871,9 @@ error hint: CTE c1 -> Aggregate -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Tid Scan on t1 b1t1 @@ -1887,69 +1881,75 @@ error hint: -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t1.c1) + Index Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t1.c1) + Recheck Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t1.c1) - InitPlan 2 (returns $4) + Index Cond: (c1 = b1t2.c1) + InitPlan 2 (returns $5) -> Aggregate -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) -> Nested Loop - Join Filter: (b2t1.c1 = b2t3.c1) + Join Filter: (b2t2.c1 = b2t1.c1) -> Nested Loop + Join Filter: (b2t2.c1 = b2t3.c1) -> Tid Scan on t2 b2t2 TID Cond: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t1 b2t1 - Recheck Cond: (c1 = b2t2.c1) + -> Seq Scan on t3 b2t3 Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = b2t2.c1) - -> Seq Scan on t3 b2t3 + -> Bitmap Heap Scan on t1 b2t1 + Recheck Cond: (c1 = b2t3.c1) Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = b2t3.c1) -> Index Scan using t4_pkey on t4 b2t4 - Index Cond: (c1 = b2t1.c1) + Index Cond: (c1 = b2t2.c1) Filter: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $7) + InitPlan 3 (returns $8) -> Aggregate -> Nested Loop - Join Filter: (b3t1.c1 = b3t4.c1) + Join Filter: (b3t1.c1 = b3t2.c1) -> Nested Loop + Join Filter: (b3t3.c1 = b3t1.c1) -> Nested Loop + Join Filter: (b3t3.c1 = b3t4.c1) -> Tid Scan on t3 b3t3 TID Cond: (ctid = '(1,1)'::tid) - -> Index Scan using t1_pkey on t1 b3t1 - Index Cond: (c1 = b3t3.c1) + -> Seq Scan on t4 b3t4 Filter: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t2 b3t2 - Recheck Cond: (c1 = b3t1.c1) + -> Index Scan using t1_pkey on t1 b3t1 + Index Cond: (c1 = b3t4.c1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t2_pkey - Index Cond: (c1 = b3t1.c1) - -> Seq Scan on t4 b3t4 + -> Bitmap Heap Scan on t2 b3t2 + Recheck Cond: (c1 = b3t3.c1) Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t2_pkey + Index Cond: (c1 = b3t3.c1) -> Nested Loop Join Filter: (bmt1.c1 = c1.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) + Join Filter: (bmt1.c1 = bmt3.c1) -> Nested Loop + Join Filter: (bmt1.c1 = bmt2.c1) -> Nested Loop + Join Filter: (bmt1.c1 = bmt4.c1) -> Seq Scan on t1 bmt1 - Filter: ((c1 <> $7) AND (ctid = '(1,1)'::tid)) - -> Index Scan using t2_pkey on t2 bmt2 - Index Cond: (c1 = bmt1.c1) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) + Filter: ((c1 <> $8) AND (ctid = '(1,1)'::tid)) + -> Tid Scan on t4 bmt4 + TID Cond: (ctid = '(1,1)'::tid) + -> Index Scan using t2_pkey on t2 bmt2 + Index Cond: (c1 = bmt4.c1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t3_pkey - Index Cond: (c1 = bmt1.c1) - -> Tid Scan on t4 bmt4 - TID Cond: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt2.c1) -> CTE Scan on c1 -(74 rows) +(82 rows) -- No. S-2-2-4 EXPLAIN (COSTS false) @@ -1990,7 +1990,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 4 (returns $3) + InitPlan 4 (returns $4) -> Aggregate -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) @@ -2004,7 +2004,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $3) + Filter: (c1 <> $4) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t3 bmt3 @@ -2061,7 +2061,9 @@ error hint: CTE c1 -> Aggregate -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Tid Scan on t1 b1t1 @@ -2069,25 +2071,25 @@ error hint: -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t1.c1) + Index Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t1.c1) + Recheck Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t1.c1) - InitPlan 3 (returns $3) + Index Cond: (c1 = b1t2.c1) + InitPlan 3 (returns $4) -> Result - InitPlan 2 (returns $2) + InitPlan 2 (returns $3) -> Limit -> Bitmap Heap Scan on t1 b2t1 Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1)) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t1_pkey Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 5 (returns $5) + InitPlan 5 (returns $6) -> Result - InitPlan 4 (returns $4) + InitPlan 4 (returns $5) -> Limit -> Index Scan Backward using t1_pkey on t1 b3t1 Index Cond: (c1 IS NOT NULL) @@ -2095,23 +2097,25 @@ error hint: -> Nested Loop Join Filter: (bmt1.c1 = c1.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) + Join Filter: (bmt1.c1 = bmt3.c1) -> Nested Loop + Join Filter: (bmt1.c1 = bmt2.c1) -> Nested Loop + Join Filter: (bmt1.c1 = bmt4.c1) -> Seq Scan on t1 bmt1 - Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid)) - -> Index Scan using t2_pkey on t2 bmt2 - Index Cond: (c1 = bmt1.c1) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) + Filter: ((c1 <> $6) AND (ctid = '(1,1)'::tid)) + -> Tid Scan on t4 bmt4 + TID Cond: (ctid = '(1,1)'::tid) + -> Index Scan using t2_pkey on t2 bmt2 + Index Cond: (c1 = bmt4.c1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t3_pkey - Index Cond: (c1 = bmt1.c1) - -> Tid Scan on t4 bmt4 - TID Cond: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt2.c1) -> CTE Scan on c1 -(54 rows) +(58 rows) ---- ---- No. S-2-3 RULE or VIEW @@ -2161,7 +2165,9 @@ error hint: ------------------------------------------------------------------------------ Aggregate -> Nested Loop + Join Filter: (t1.c1 = t4.c1) -> Nested Loop + Join Filter: (t1.c1 = t3.c1) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Nested Loop @@ -2172,14 +2178,14 @@ error hint: -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 - Index Cond: (c1 = t1.c1) + Index Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 - Recheck Cond: (c1 = t1.c1) + Recheck Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = t1.c1) -(20 rows) + Index Cond: (c1 = t2.c1) +(22 rows) EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; QUERY PLAN @@ -2225,7 +2231,9 @@ error hint: ------------------------------------------------------------------------------ Aggregate -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Nested Loop @@ -2236,14 +2244,14 @@ error hint: -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t1.c1) + Index Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t1.c1) + Recheck Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t1.c1) -(20 rows) + Index Cond: (c1 = b1t2.c1) +(22 rows) -- No. S-2-3-2 EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; @@ -2323,7 +2331,9 @@ error hint: ------------------------------------------------------------------------------ Aggregate -> Nested Loop + Join Filter: (t1.c1 = t4.c1) -> Nested Loop + Join Filter: (t1.c1 = t3.c1) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Nested Loop @@ -2334,17 +2344,19 @@ error hint: -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 - Index Cond: (c1 = t1.c1) + Index Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 - Recheck Cond: (c1 = t1.c1) + Recheck Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = t1.c1) + Index Cond: (c1 = t2.c1) Aggregate -> Nested Loop + Join Filter: (t1.c1 = t4.c1) -> Nested Loop + Join Filter: (t1.c1 = t3.c1) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Nested Loop @@ -2355,14 +2367,14 @@ error hint: -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 - Index Cond: (c1 = t1.c1) + Index Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 - Recheck Cond: (c1 = t1.c1) + Recheck Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = t1.c1) -(41 rows) + Index Cond: (c1 = t2.c1) +(45 rows) EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; QUERY PLAN @@ -2450,7 +2462,9 @@ error hint: ------------------------------------------------------------------------------ Aggregate -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Nested Loop @@ -2461,35 +2475,37 @@ error hint: -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t1.c1) + Index Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t1.c1) + Recheck Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t1.c1) + Index Cond: (c1 = b1t2.c1) Aggregate -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) -> Nested Loop - Join Filter: (b2t1.c1 = b2t3.c1) + Join Filter: (b2t2.c1 = b2t1.c1) -> Nested Loop + Join Filter: (b2t2.c1 = b2t3.c1) -> Nested Loop -> Seq Scan on r2_ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) -> Tid Scan on t2 b2t2 TID Cond: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t1 b2t1 - Recheck Cond: (c1 = b2t2.c1) + -> Seq Scan on t3 b2t3 Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = b2t2.c1) - -> Seq Scan on t3 b2t3 + -> Bitmap Heap Scan on t1 b2t1 + Recheck Cond: (c1 = b2t3.c1) Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = b2t3.c1) -> Index Scan using t4_pkey on t4 b2t4 - Index Cond: (c1 = b2t1.c1) + Index Cond: (c1 = b2t2.c1) Filter: (ctid = '(1,1)'::tid) -(41 rows) +(45 rows) -- No. S-2-3-3 EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; @@ -2602,7 +2618,9 @@ error hint: ------------------------------------------------------------------------------ Aggregate -> Nested Loop + Join Filter: (t1.c1 = t4.c1) -> Nested Loop + Join Filter: (t1.c1 = t3.c1) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Nested Loop @@ -2613,17 +2631,19 @@ error hint: -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 - Index Cond: (c1 = t1.c1) + Index Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 - Recheck Cond: (c1 = t1.c1) + Recheck Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = t1.c1) + Index Cond: (c1 = t2.c1) Aggregate -> Nested Loop + Join Filter: (t1.c1 = t4.c1) -> Nested Loop + Join Filter: (t1.c1 = t3.c1) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Nested Loop @@ -2634,17 +2654,19 @@ error hint: -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 - Index Cond: (c1 = t1.c1) + Index Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 - Recheck Cond: (c1 = t1.c1) + Recheck Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = t1.c1) + Index Cond: (c1 = t2.c1) Aggregate -> Nested Loop + Join Filter: (t1.c1 = t4.c1) -> Nested Loop + Join Filter: (t1.c1 = t3.c1) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Nested Loop @@ -2655,14 +2677,14 @@ error hint: -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 - Index Cond: (c1 = t1.c1) + Index Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 - Recheck Cond: (c1 = t1.c1) + Recheck Cond: (c1 = t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = t1.c1) -(62 rows) + Index Cond: (c1 = t2.c1) +(68 rows) EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; QUERY PLAN @@ -2800,7 +2822,9 @@ error hint: ------------------------------------------------------------------------------ Aggregate -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Nested Loop @@ -2811,56 +2835,60 @@ error hint: -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t1.c1) + Index Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t1.c1) + Recheck Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t1.c1) + Index Cond: (c1 = b1t2.c1) Aggregate -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) -> Nested Loop - Join Filter: (b2t1.c1 = b2t3.c1) + Join Filter: (b2t2.c1 = b2t1.c1) -> Nested Loop + Join Filter: (b2t2.c1 = b2t3.c1) -> Nested Loop -> Seq Scan on r3_ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) -> Tid Scan on t2 b2t2 TID Cond: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t1 b2t1 - Recheck Cond: (c1 = b2t2.c1) + -> Seq Scan on t3 b2t3 Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = b2t2.c1) - -> Seq Scan on t3 b2t3 + -> Bitmap Heap Scan on t1 b2t1 + Recheck Cond: (c1 = b2t3.c1) Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = b2t3.c1) -> Index Scan using t4_pkey on t4 b2t4 - Index Cond: (c1 = b2t1.c1) + Index Cond: (c1 = b2t2.c1) Filter: (ctid = '(1,1)'::tid) Aggregate -> Nested Loop - Join Filter: (b3t1.c1 = b3t4.c1) + Join Filter: (b3t1.c1 = b3t2.c1) -> Nested Loop + Join Filter: (b3t3.c1 = b3t1.c1) -> Nested Loop + Join Filter: (b3t3.c1 = b3t4.c1) -> Nested Loop -> Seq Scan on r3_ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) -> Tid Scan on t3 b3t3 TID Cond: (ctid = '(1,1)'::tid) - -> Index Scan using t1_pkey on t1 b3t1 - Index Cond: (c1 = b3t3.c1) + -> Seq Scan on t4 b3t4 Filter: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t2 b3t2 - Recheck Cond: (c1 = b3t1.c1) + -> Index Scan using t1_pkey on t1 b3t1 + Index Cond: (c1 = b3t4.c1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t2_pkey - Index Cond: (c1 = b3t1.c1) - -> Seq Scan on t4 b3t4 + -> Bitmap Heap Scan on t2 b3t2 + Recheck Cond: (c1 = b3t3.c1) Filter: (ctid = '(1,1)'::tid) -(62 rows) + -> Bitmap Index Scan on t2_pkey + Index Cond: (c1 = b3t3.c1) +(68 rows) -- No. S-2-3-4 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1; @@ -2989,11 +3017,10 @@ error hint: ---- -- No. S-2-4-1 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1; - QUERY PLAN ---------------------------- - Values Scan on "*VALUES*" - Filter: (column1 = 1) -(2 rows) + QUERY PLAN +------------ + Result +(1 row) /*+SeqScan(t1)*/ EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1; @@ -3004,11 +3031,10 @@ SeqScan(t1) duplication hint: error hint: - QUERY PLAN ---------------------------- - Values Scan on "*VALUES*" - Filter: (column1 = 1) -(2 rows) + QUERY PLAN +------------ + Result +(1 row) /*+SeqScan(*VALUES*)*/ EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1; @@ -3019,25 +3045,25 @@ SeqScan(*VALUES*) duplication hint: error hint: - QUERY PLAN ---------------------------- - Values Scan on "*VALUES*" - Filter: (column1 = 1) -(2 rows) + QUERY PLAN +------------ + Result +(1 row) -- 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; - QUERY PLAN ------------------------------------------------------------- - Nested Loop - Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1) +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; + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1) -> Values Scan on "*VALUES*" - -> Values Scan on "*VALUES*_1" -(4 rows) + -> Hash + -> Values Scan on "*VALUES*_1" +(5 rows) /*+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; -INFO: hint syntax error at or near "" +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; +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: SeqScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -3046,16 +3072,17 @@ duplication hint: error hint: SeqScan(t1 t2) - QUERY PLAN ------------------------------------------------------------- - Nested Loop - Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1) + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1) -> Values Scan on "*VALUES*" - -> Values Scan on "*VALUES*_1" -(4 rows) + -> Hash + -> Values Scan on "*VALUES*_1" +(5 rows) /*+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; LOG: pg_hint_plan: used hint: not used hint: @@ -3063,13 +3090,14 @@ SeqScan(*VALUES*) duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------- - Nested Loop - Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1) + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1) -> Values Scan on "*VALUES*" - -> Values Scan on "*VALUES*_1" -(4 rows) + -> Hash + -> Values Scan on "*VALUES*_1" +(5 rows) ---- ---- No. S-3-1 scan method hint @@ -3759,13 +3787,13 @@ error hint: ---- No. S-3-4 index type ---- \d s1.ti1 - Table "s1.ti1" - Column | Type | Modifiers ---------+---------+----------- - c1 | integer | not null - c2 | integer | - c3 | integer | - c4 | text | + Table "s1.ti1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + c1 | integer | | not null | + c2 | integer | | | + c3 | integer | | | + c4 | text | | | Indexes: "ti1_pkey" PRIMARY KEY, btree (c1) "ti1_c2_key" UNIQUE CONSTRAINT, btree (c2) @@ -3788,7 +3816,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c ----------------------------------------------------------------------------------------------------------------------------------------- Tid Scan on ti1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 < 100) AND (c2 = 1) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-1 @@ -3806,7 +3834,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_btree on ti1 Index Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-2 @@ -3824,7 +3852,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_hash on ti1 Index Cond: (c1 = 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-3 @@ -3842,7 +3870,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_gist on ti1 Index Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-4 @@ -3860,7 +3888,7 @@ error hint: ----------------------------------------------------------------------------------------------------------------------------------------- Tid Scan on ti1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 < 100) AND (c2 = 1) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-5 @@ -3878,7 +3906,7 @@ error hint: ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_expr on ti1 Index Cond: ((c1 < 100) = true) - Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-6 @@ -3914,7 +3942,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_uniq on ti1 Index Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-8 @@ -3932,7 +3960,7 @@ error hint: --------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_multi on ti1 Index Cond: ((c1 < 100) AND (c2 = 1)) - Filter: ((ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-9 @@ -3950,7 +3978,7 @@ error hint: ----------------------------------------------------------------------------------------------------------------------------------------- Tid Scan on ti1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 < 100) AND (c2 = 1) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-10 @@ -3968,7 +3996,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_pkey on ti1 Index Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-11 @@ -3986,7 +4014,7 @@ error hint: ------------------------------------------------------------------------------------------------------------------------------------------------------ Index Scan using ti1_c2_key on ti1 Index Cond: (c2 = 1) - Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-12 @@ -4004,7 +4032,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_btree Index Cond: (c1 < 100) (5 rows) @@ -4024,7 +4052,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 = 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_hash Index Cond: (c1 = 100) (5 rows) @@ -4044,7 +4072,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_gist Index Cond: (c1 < 100) (5 rows) @@ -4064,7 +4092,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_gin Index Cond: (c1 < 100) (5 rows) @@ -4083,7 +4111,7 @@ error hint: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 - Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_expr Index Cond: ((c1 < 100) = true) (4 rows) @@ -4123,7 +4151,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_uniq Index Cond: (c1 < 100) (5 rows) @@ -4143,7 +4171,7 @@ error hint: --------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: ((c1 < 100) AND (c2 = 1)) - Filter: ((ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_multi Index Cond: ((c1 < 100) AND (c2 = 1)) (5 rows) @@ -4168,7 +4196,7 @@ error hint: Index Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) (5 rows) --- 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)'; LOG: available indexes for BitmapScan(ti1): ti1_pkey @@ -4183,12 +4211,12 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_pkey Index Cond: (c1 < 100) (5 rows) --- 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)'; LOG: available indexes for BitmapScan(ti1): ti1_c2_key @@ -4203,7 +4231,7 @@ error hint: ------------------------------------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on ti1 Recheck Cond: (c2 = 1) - Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_c2_key Index Cond: (c2 = 1) (5 rows) @@ -4274,9 +4302,9 @@ not used hint: duplication hint: error hint: - QUERY PLAN ----------------------------------- - Index Scan using ti1_gist on ti1 + QUERY PLAN +--------------------------------------- + Index Only Scan using ti1_gist on ti1 Index Cond: (c1 < 1) (2 rows) @@ -4483,8 +4511,8 @@ error hint: ---- No. S-3-5 not used index ---- -- No. S-3-5-1 -/*+IndexScan(ti1 ti1_pred)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+IndexScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for IndexScan(ti1): ti1_pred LOG: pg_hint_plan: used hint: @@ -4493,15 +4521,16 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------- - Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +------------------------------------------------------------------------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) -(2 rows) -- No. S-3-5-2 -/*+BitmapScan(ti1 ti1_pred)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+BitmapScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for BitmapScan(ti1): ti1_pred LOG: pg_hint_plan: used hint: @@ -4510,15 +4539,16 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------- - Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +------------------------------------------------------------------------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) -(2 rows) -- No. S-3-5-3 -/*+IndexOnlyScan(ti1 ti1_pred)*/ -EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+IndexOnlyScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for IndexOnlyScan(ti1): ti1_pred LOG: pg_hint_plan: used hint: @@ -4527,15 +4557,16 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------ - Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=4) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +------------------------------------------------------------------------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) -(2 rows) -- No. S-3-5-4 -/*+IndexScan(ti1 not_exist)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+IndexScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for IndexScan(ti1): LOG: pg_hint_plan: used hint: @@ -4544,15 +4575,16 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------- - Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +------------------------------------------------------------------------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) -(2 rows) -- No. S-3-5-5 -/*+BitmapScan(ti1 not_exist)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+BitmapScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for BitmapScan(ti1): LOG: pg_hint_plan: used hint: @@ -4561,15 +4593,16 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------- - Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +------------------------------------------------------------------------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) -(2 rows) -- No. S-3-5-6 -/*+IndexOnlyScan(ti1 not_exist)*/ -EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+IndexOnlyScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for IndexOnlyScan(ti1): LOG: pg_hint_plan: used hint: @@ -4578,11 +4611,12 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------ - Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=4) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +------------------------------------------------------------------------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) -(2 rows) -- No. S-3-5-7 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; @@ -4592,8 +4626,8 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; Index Cond: (c1 = 1) (2 rows) -/*+TidScan(t1)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +\o results/ut-S.tmpout +/*+TidScan(t1)*/ EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1; LOG: pg_hint_plan: used hint: TidScan(t1) @@ -4601,11 +4635,12 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------ - Seq Scan on t1 (cost=10000000000.00..10000000018.50 rows=1 width=15) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +------------------------------------------------------------------------------- + Seq Scan on t1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 1) -(2 rows) ---- ---- No. S-3-6 query structure @@ -4692,7 +4727,7 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1 SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1); QUERY PLAN --------------------------------------------------------------------- - Result + Aggregate InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) @@ -4705,11 +4740,9 @@ SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1); -> Limit -> Index Only Scan using t1_i1 on t1 b4t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 5 (returns $4) - -> Limit - -> Index Only Scan using t1_i1 on t1 b3t1 - Index Cond: ((c1 IS NOT NULL) AND (c1 = $3)) -(17 rows) + -> Index Only Scan using t1_i1 on t1 b3t1 + Index Cond: (c1 = $3) +(15 rows) /*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1) */ @@ -4723,7 +4756,6 @@ SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1); LOG: available indexes for IndexScan(b2t1): t1_pkey LOG: available indexes for IndexScan(b2t1): t1_pkey LOG: available indexes for BitmapScan(b3t1): t1_pkey -LOG: available indexes for BitmapScan(b3t1): t1_pkey LOG: pg_hint_plan: used hint: IndexScan(b2t1 t1_pkey) @@ -4736,7 +4768,7 @@ error hint: QUERY PLAN --------------------------------------------------------------------- - Result + Aggregate InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) @@ -4749,13 +4781,11 @@ error hint: -> Limit -> Seq Scan on t1 b4t1 Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 5 (returns $4) - -> Limit - -> Bitmap Heap Scan on t1 b3t1 - Recheck Cond: ((c1 IS NOT NULL) AND (c1 = $3)) - -> Bitmap Index Scan on t1_pkey - Index Cond: ((c1 IS NOT NULL) AND (c1 = $3)) -(19 rows) + -> Bitmap Heap Scan on t1 b3t1 + Recheck Cond: (c1 = $3) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = $3) +(17 rows) -- No. S-3-7-2 EXPLAIN (COSTS false) @@ -4966,7 +4996,7 @@ error hint: (30 rows) ---- ----- No. S-3-8 inheritance table select type +---- No. S-3-8 inheritance table select/update type ---- -- No. S-3-8-1 EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1; @@ -5020,6 +5050,103 @@ error hint: Index Cond: (c1 = 1) (5 rows) +-- No. S-3-8-3 +EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1; + QUERY PLAN +-------------------------- + Update on p1 + -> Seq Scan on p1 + Filter: (c1 = 1) +(3 rows) + +/*+IndexScan(p1)*/ +EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(p1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Update on p1 + -> Index Scan using p1_i on p1 + Index Cond: (c1 = 1) +(3 rows) + +/*+IndexScan(p1 p1_pkey)*/ +EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1; +LOG: available indexes for IndexScan(p1): p1_pkey +LOG: pg_hint_plan: +used hint: +IndexScan(p1 p1_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------- + Update on p1 + -> Index Scan using p1_pkey on p1 + Index Cond: (c1 = 1) +(3 rows) + +-- No. S-3-8-4 +EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; + QUERY PLAN +-------------------------- + Update on p1 + Update on p1 + Update on p1c1 + -> Seq Scan on p1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 + Filter: (c1 = 1) +(7 rows) + +/*+IndexScan(p1)*/ +EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(p1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------- + Update on p1 + Update on p1 + Update on p1c1 + -> Index Scan using p1_i on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 + Index Cond: (c1 = 1) +(7 rows) + +/*+IndexScan(p1 p1_pkey)*/ +EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; +LOG: available indexes for IndexScan(p1): p1_pkey +LOG: available indexes for IndexScan(p1c1): p1c1_pkey +LOG: pg_hint_plan: +used hint: +IndexScan(p1 p1_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------ + Update on p1 + Update on p1 + Update on p1c1 + -> Index Scan using p1_pkey on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_pkey on p1c1 + Index Cond: (c1 = 1) +(7 rows) + ---- ---- No. S-3-9 inheritance table number ---- @@ -5126,39 +5253,39 @@ error hint: EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1; LOG: pg_hint_plan: used hint: -not used hint: IndexScan(p2c1) +not used hint: duplication hint: error hint: - QUERY PLAN --------------------------- + QUERY PLAN +--------------------------------------- Append -> Seq Scan on p2 Filter: (c1 = 1) - -> Seq Scan on p2c1 - Filter: (c1 = 1) + -> Index Scan using p2c1_i on p2c1 + Index Cond: (c1 = 1) -> Seq Scan on p2c1c1 Filter: (c1 = 1) (7 rows) -- No. S-3-10-3 +\o results/ut-S.tmpout EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10; +\o +\! sql/maskout.sh results/ut-S.tmpout QUERY PLAN ---------------------------------------------------------- - Append (cost=0.00..2.75 rows=4 width=10) - -> Seq Scan on p1 (cost=0.00..0.00 rows=1 width=32) + Append (cost=xxx..xxx rows=4 width=xxx) + -> Seq Scan on p1 (cost=xxx..xxx rows=1 width=xxx) Filter: ((c1 < 10) AND ((c2 * 2) < 100)) - -> Seq Scan on p1c1 (cost=0.00..2.75 rows=3 width=2) + -> Seq Scan on p1c1 (cost=xxx..xxx rows=3 width=xxx) Filter: ((c1 < 10) AND ((c2 * 2) < 100)) -(5 rows) -/*+IndexScan(p1 p1_parent)*/ -EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10; +\o results/ut-S.tmpout +/*+IndexScan(p1 p1_parent)*/ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10; LOG: available indexes for IndexScan(p1): p1_parent LOG: available indexes for IndexScan(p1c1): p1c1_c4_expr_idx -LOG: available indexes for IndexScan(p1c2): p1c2_c4_expr_idx -LOG: available indexes for IndexScan(p1c3): p1c3_c4_expr_idx LOG: pg_hint_plan: used hint: IndexScan(p1 p1_parent) @@ -5166,18 +5293,19 @@ not used hint: duplication hint: error hint: +\o +\! sql/maskout.sh results/ut-S.tmpout QUERY PLAN ------------------------------------------------------------------------------------ - Append (cost=0.12..20.48 rows=4 width=10) - -> Index Scan using p1_parent on p1 (cost=0.12..8.15 rows=1 width=32) + Append (cost=xxx..xxx rows=4 width=xxx) + -> Index Scan using p1_parent on p1 (cost=xxx..xxx rows=1 width=xxx) Filter: ((c2 * 2) < 100) - -> Index Scan using p1c1_c4_expr_idx on p1c1 (cost=0.14..12.34 rows=3 width=2) + -> Index Scan using p1c1_c4_expr_idx on p1c1 (cost=xxx..xxx rows=3 width=xxx) Filter: ((c2 * 2) < 100) -(5 rows) -- 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; LOG: available indexes for IndexScan(p1): p1_i2 LOG: available indexes for IndexScan(p1c1): LOG: available indexes for IndexScan(p1c2): @@ -5189,32 +5317,26 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------- - Append (cost=0.12..30000000014.89 rows=4 width=4) - -> Index Scan using p1_i2 on p1 (cost=0.12..8.14 rows=1 width=4) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +------------------------------------------------------------------------------- + Append (cost=xxx..xxx rows=4 width=xxx) + -> Index Scan using p1_i2 on p1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c2 = 1) - -> Seq Scan on p1c1 (cost=10000000000.00..10000000002.25 rows=1 width=4) + -> Seq Scan on p1c1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c2 = 1) - -> Seq Scan on p1c2 (cost=10000000000.00..10000000002.25 rows=1 width=4) + -> Seq Scan on p1c2 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c2 = 1) - -> Seq Scan on p1c3 (cost=10000000000.00..10000000002.25 rows=1 width=4) + -> Seq Scan on p1c3 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c2 = 1) -(9 rows) -- 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; LOG: available indexes for IndexScan(p2): LOG: available indexes for IndexScan(p2c1): p2c1_pkey -LOG: available indexes for IndexScan(p2c2): -LOG: available indexes for IndexScan(p2c3): LOG: available indexes for IndexScan(p2c1c1): -LOG: available indexes for IndexScan(p2c1c2): -LOG: available indexes for IndexScan(p2c2c1): -LOG: available indexes for IndexScan(p2c2c2): -LOG: available indexes for IndexScan(p2c3c1): -LOG: available indexes for IndexScan(p2c3c2): LOG: pg_hint_plan: used hint: IndexScan(p2 p2c1_pkey) @@ -5222,16 +5344,17 @@ not used hint: duplication hint: error hint: +\o +\! sql/maskout.sh results/ut-S.tmpout QUERY PLAN --------------------------------------------------------------------------------- - Append (cost=10000000000.00..20000000009.77 rows=3 width=34) - -> Seq Scan on p2 (cost=10000000000.00..10000000000.00 rows=1 width=44) + Append (cost={inf}..{inf} rows=3 width=xxx) + -> Seq Scan on p2 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 1) - -> Index Scan using p2c1_pkey on p2c1 (cost=0.12..8.14 rows=1 width=44) + -> Index Scan using p2c1_pkey on p2c1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = 1) - -> Seq Scan on p2c1c1 (cost=10000000000.00..10000000001.62 rows=1 width=14) + -> Seq Scan on p2c1c1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 1) -(7 rows) ---- ---- No. S-3-12 specified same table @@ -5239,7 +5362,7 @@ error hint: -- No. S-3-12-1 /*+IndexScan(ti1) BitmapScan(ti1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "IndexScan(ti1) BitmapScan(ti1)" +INFO: pg_hint_plan: hint syntax error at or near "IndexScan(ti1) BitmapScan(ti1)" DETAIL: Conflict scan method hint. LOG: pg_hint_plan: used hint: @@ -5261,7 +5384,7 @@ error hint: -- No. S-3-12-2 /*+IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)" +INFO: pg_hint_plan: hint syntax error at or near "IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)" DETAIL: Conflict scan method hint. LOG: available indexes for BitmapScan(ti1): ti1_btree LOG: pg_hint_plan: @@ -5284,9 +5407,9 @@ error hint: -- No. S-3-12-3 /*+BitmapScan(ti1) IndexScan(ti1) BitmapScan(ti1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "BitmapScan(ti1) IndexScan(ti1) BitmapScan(ti1)" +INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(ti1) IndexScan(ti1) BitmapScan(ti1)" DETAIL: Conflict scan method hint. -INFO: hint syntax error at or near "IndexScan(ti1) BitmapScan(ti1)" +INFO: pg_hint_plan: hint syntax error at or near "IndexScan(ti1) BitmapScan(ti1)" DETAIL: Conflict scan method hint. LOG: pg_hint_plan: used hint: @@ -5309,9 +5432,9 @@ error hint: -- No. S-3-12-4 /*+BitmapScan(ti1 ti1_hash) IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "BitmapScan(ti1 ti1_hash) IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)" +INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(ti1 ti1_hash) IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)" DETAIL: Conflict scan method hint. -INFO: hint syntax error at or near "IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)" +INFO: pg_hint_plan: hint syntax error at or near "IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)" DETAIL: Conflict scan method hint. LOG: available indexes for BitmapScan(ti1): ti1_btree LOG: pg_hint_plan: @@ -5354,7 +5477,7 @@ error hint: -- No. S-3-13-2 /*+SeqScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: SeqScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5373,7 +5496,7 @@ SeqScan(ti1 ti1_pkey) -- No. S-3-13-3 /*+SeqScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: SeqScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5521,7 +5644,7 @@ error hint: -- No. S-3-13-11 /*+TidScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: TidScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5540,7 +5663,7 @@ TidScan(ti1 ti1_pkey) -- No. S-3-13-12 /*+TidScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: TidScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5576,7 +5699,7 @@ error hint: -- No. S-3-13-14 /*+NoSeqScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: NoSeqScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5595,7 +5718,7 @@ NoSeqScan(ti1 ti1_pkey) -- No. S-3-13-15 /*+NoSeqScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: NoSeqScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5631,7 +5754,7 @@ error hint: -- No. S-3-13-17 /*+NoIndexScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: NoIndexScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5650,7 +5773,7 @@ NoIndexScan(ti1 ti1_pkey) -- No. S-3-13-18 /*+NoIndexScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: NoIndexScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5686,7 +5809,7 @@ error hint: -- No. S-3-13-20 /*+NoBitmapScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: NoBitmapScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5705,7 +5828,7 @@ NoBitmapScan(ti1 ti1_pkey) -- No. S-3-13-21 /*+NoBitmapScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: NoBitmapScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5741,7 +5864,7 @@ error hint: -- No. S-3-13-23 /*+NoTidScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: NoTidScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5760,7 +5883,7 @@ NoTidScan(ti1 ti1_pkey) -- No. S-3-13-24 /*+NoTidScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: NoTidScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5845,7 +5968,7 @@ error hint: -- No. S-3-13-29 /*+NoIndexOnlyScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: NoIndexOnlyScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5863,7 +5986,7 @@ NoIndexOnlyScan(ti1 ti1_pkey) -- No. S-3-13-30 /*+NoIndexOnlyScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; -INFO: hint syntax error at or near "" +INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: NoIndexOnlyScan hint accepts only one relation. LOG: pg_hint_plan: used hint: @@ -5944,8 +6067,6 @@ error hint: EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; LOG: available indexes for IndexScanRegexp(p1): p1_pkey LOG: available indexes for IndexScanRegexp(p1c1): p1c1_pkey -LOG: available indexes for IndexScanRegexp(p1c2): p1c2_pkey -LOG: available indexes for IndexScanRegexp(p1c3): p1c3_pkey LOG: pg_hint_plan: used hint: IndexScanRegexp(p1 .*pkey) @@ -5967,8 +6088,6 @@ error hint: EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; LOG: available indexes for IndexScanRegexp(p1): p1_i2 p1_i LOG: available indexes for IndexScanRegexp(p1c1): p1c1_i p1c1_c4_expr_idx -LOG: available indexes for IndexScanRegexp(p1c2): p1c2_i p1c2_c4_expr_idx -LOG: available indexes for IndexScanRegexp(p1c3): p1c3_i p1c3_c4_expr_idx LOG: pg_hint_plan: used hint: IndexScanRegexp(p1 p1.*i) @@ -5990,8 +6109,6 @@ error hint: EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; LOG: available indexes for IndexScanRegexp(p1): LOG: available indexes for IndexScanRegexp(p1c1): -LOG: available indexes for IndexScanRegexp(p1c2): -LOG: available indexes for IndexScanRegexp(p1c3): LOG: pg_hint_plan: used hint: IndexScanRegexp(p1 no.*_exist) @@ -6096,3 +6213,12 @@ error hint: Filter: (c2 = 1) (2 rows) +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(); + pg_reload_conf +---------------- + t +(1 row) + +\! rm results/ut-S.tmpout