X-Git-Url: http://git.osdn.net/view?a=blobdiff_plain;f=expected%2Fut-S.out;h=0f36d22ab0dc4dd8441770eda7cc13991573e50a;hb=7392b374d10a0526e6d4209685cdc1d6ada36ab7;hp=8c19e92ce798f27cbbdc8454bf67c4a985ec684a;hpb=2fbe0679794aded5d9c1f3aea74629f9a250818c;p=pghintplan%2Fpg_hint_plan.git diff --git a/expected/ut-S.out b/expected/ut-S.out index 8c19e92..0f36d22 100644 --- a/expected/ut-S.out +++ b/expected/ut-S.out @@ -3,12 +3,17 @@ LOAD 'pg_hint_plan'; 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'; -\! pg_ctl reload -server signaled +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 --------------------- @@ -372,12 +377,12 @@ error hint: -- No. S-1-5-2 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------- Append - -> Seq Scan on p1 + -> Seq Scan on p1 p1_1 Filter: (c1 = 1) - -> Seq Scan on p1c1 + -> Seq Scan on p1c1 p1_2 Filter: (c1 = 1) (5 rows) @@ -390,12 +395,12 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------- + QUERY PLAN +-------------------------------------------- Append - -> Index Scan using p1_i on p1 + -> Index Scan using p1_i on p1 p1_1 Index Cond: (c1 = 1) - -> Index Scan using p1c1_i on p1c1 + -> Index Scan using p1c1_i on p1c1 p1_2 Index Cond: (c1 = 1) (5 rows) @@ -425,9 +430,9 @@ error hint: -- No. S-1-5-4 CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL); EXPLAIN (COSTS false) SELECT * FROM tm1 WHERE tm1.c1 = 1; - QUERY PLAN ----------------------------------- - Index Scan using tm1_pkey on tm1 + QUERY PLAN +------------------------------------ + Index Scan using tm1_c1_idx on tm1 Index Cond: (c1 = 1) (2 rows) @@ -451,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)*/ @@ -463,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 @@ -535,20 +540,18 @@ error hint: -- No. S-1-5-9 EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = 1 AND t1.c1 = c1.c1; - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Nested Loop - CTE c1 - -> Result - InitPlan 1 (returns $0) - -> Limit - -> Index Only Scan using t1_i1 on t1 t1_1 - Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) - -> CTE Scan on c1 - Filter: (c1 = 1) -(11 rows) + -> Result + One-Time Filter: ($0 = 1) + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan using t1_i1 on t1 t1_1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) +(9 rows) /*+SeqScan(c1)*/ EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1) @@ -560,20 +563,18 @@ SeqScan(c1) duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Nested Loop - CTE c1 - -> Result - InitPlan 1 (returns $0) - -> Limit - -> Index Only Scan using t1_i1 on t1 t1_1 - Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) - -> CTE Scan on c1 - Filter: (c1 = 1) -(11 rows) + -> Result + One-Time Filter: ($0 = 1) + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan using t1_i1 on t1 t1_1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) +(9 rows) -- No. S-1-5-10 EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1; @@ -637,7 +638,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 @@ -648,9 +648,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 @@ -661,8 +660,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) @@ -741,7 +740,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 @@ -752,11 +750,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 @@ -767,9 +764,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 @@ -780,8 +776,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) @@ -877,7 +873,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 @@ -888,8 +883,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) */ @@ -931,7 +926,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 @@ -942,8 +936,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) */ @@ -992,7 +986,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 @@ -1003,9 +996,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 @@ -1017,8 +1009,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) @@ -1098,7 +1090,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 @@ -1109,11 +1100,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 @@ -1124,9 +1114,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 @@ -1138,8 +1127,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,41 +1229,36 @@ SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1 ; - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Merge Join - Merge Cond: (b1t1.c1 = b1t3.c1) - -> Index Only Scan using t1_i1 on t1 b1t1 - -> Index Only Scan using t3_i1 on t3 b1t3 - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - -> Index Only Scan using t4_i1 on t4 b1t4 - Index Cond: (c1 = b1t3.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) + Hash Cond: (bmt2.c1 = (max(b1t1.c1))) -> Seq Scan on t2 bmt2 -> Hash - -> CTE Scan on c1 - -> Index Only Scan using t3_i1 on t3 bmt3 + -> Aggregate + -> Nested Loop + -> Merge Join + Merge Cond: (b1t1.c1 = b1t2.c1) + -> Merge Join + Merge Cond: (b1t1.c1 = b1t3.c1) + -> Index Only Scan using t1_i1 on t1 b1t1 + -> Index Only Scan using t3_i1 on t3 b1t3 + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + -> Index Only Scan using t4_i1 on t4 b1t4 + Index Cond: (c1 = b1t1.c1) + -> 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) +(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) @@ -1306,43 +1290,45 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------ Aggregate - CTE c1 - -> Aggregate - -> Hash Join - Hash Cond: (b1t1.c1 = b1t2.c1) - -> Seq Scan on t1 b1t1 - -> Hash - -> Nested Loop - Join Filter: (b1t2.c1 = b1t3.c1) - -> Nested Loop - -> Seq Scan on t2 b1t2 - -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t2.c1) - -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t2.c1) - -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t4.c1) -> Hash Join Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 -> Hash -> Nested Loop - -> Nested Loop - -> Hash Join - Hash Cond: (bmt1.c1 = c1.c1) - -> Seq Scan on t1 bmt1 - -> Hash - -> CTE Scan on c1 - -> Index Scan using t2_pkey on t2 bmt2 - Index Cond: (c1 = bmt1.c1) + -> Merge Join + Merge Cond: (bmt1.c1 = (max(b1t1.c1))) + -> Sort + Sort Key: bmt1.c1 + -> Hash Join + Hash Cond: (bmt1.c1 = bmt2.c1) + -> Seq Scan on t1 bmt1 + -> Hash + -> Index Scan using t2_pkey on t2 bmt2 + -> Sort + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Hash Join + Hash Cond: (b1t1.c1 = b1t2.c1) + -> Seq Scan on t1 b1t1 + -> Hash + -> Nested Loop + Join Filter: (b1t2.c1 = b1t3.c1) + -> Nested Loop + -> Seq Scan on t2 b1t2 + -> Bitmap Heap Scan on t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) + -> Index Scan using t3_pkey on t3 b1t3 + Index Cond: (c1 = b1t4.c1) -> Bitmap Heap Scan on t3 bmt3 Recheck Cond: (c1 = bmt1.c1) -> Bitmap Index Scan on t3_pkey Index Cond: (c1 = bmt1.c1) -(34 rows) +(36 rows) -- No. S-2-1-8 EXPLAIN (COSTS false) @@ -1358,59 +1344,51 @@ SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 AND bmt1.c1 = c1.c1 AND bmt1.c1 = c2.c1 ; - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Merge Join - Merge Cond: (b1t1.c1 = b1t3.c1) - -> Index Only Scan using t1_i1 on t1 b1t1 - -> Index Only Scan using t3_i1 on t3 b1t3 - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - -> Index Only Scan using t4_i1 on t4 b1t4 - Index Cond: (c1 = b1t3.c1) - CTE c2 - -> Aggregate - -> Nested Loop - Join Filter: (b2t1.c1 = b2t4.c1) - -> Merge Join - Merge Cond: (b2t1.c1 = b2t2.c1) - -> Merge Join - Merge Cond: (b2t1.c1 = b2t3.c1) - -> Index Only Scan using t1_i1 on t1 b2t1 - -> Index Only Scan using t3_i1 on t3 b2t3 - -> Sort - Sort Key: b2t2.c1 - -> Seq Scan on t2 b2t2 - -> Index Only Scan using t4_i1 on t4 b2t4 - Index Cond: (c1 = b2t3.c1) -> Nested Loop - Join Filter: (bmt1.c1 = c2.c1) + Join Filter: (bmt1.c1 = (max(b2t1.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) + Hash Cond: (bmt2.c1 = (max(b1t1.c1))) -> Seq Scan on t2 bmt2 -> Hash - -> CTE Scan on c1 - -> Index Only Scan using t3_i1 on t3 bmt3 + -> Aggregate + -> Nested Loop + -> Merge Join + Merge Cond: (b1t1.c1 = b1t2.c1) + -> Merge Join + Merge Cond: (b1t1.c1 = b1t3.c1) + -> Index Only Scan using t1_i1 on t1 b1t1 + -> Index Only Scan using t3_i1 on t3 b1t3 + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + -> Index Only Scan using t4_i1 on t4 b1t4 + Index Cond: (c1 = b1t1.c1) + -> 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) - -> CTE Scan on c2 -(50 rows) + Index Cond: (c1 = bmt1.c1) + -> Aggregate + -> Nested Loop + -> Merge Join + Merge Cond: (b2t1.c1 = b2t2.c1) + -> Merge Join + Merge Cond: (b2t1.c1 = b2t3.c1) + -> Index Only Scan using t1_i1 on t1 b2t1 + -> Index Only Scan using t3_i1 on t3 b2t3 + -> Sort + Sort Key: b2t2.c1 + -> Seq Scan on t2 b2t2 + -> Index Only Scan using t4_i1 on t4 b2t4 + Index Cond: (c1 = b2t1.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) @@ -1453,63 +1431,62 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Hash Join - Hash Cond: (b1t1.c1 = b1t2.c1) - -> Seq Scan on t1 b1t1 - -> Hash - -> Nested Loop - Join Filter: (b1t2.c1 = b1t3.c1) - -> Nested Loop - -> Seq Scan on t2 b1t2 - -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t2.c1) - -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t2.c1) - -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t4.c1) - CTE c2 - -> Aggregate - -> Hash Join - Hash Cond: (b2t3.c1 = b2t1.c1) - -> Seq Scan on t3 b2t3 - -> Hash - -> Merge Join - Merge Cond: (b2t1.c1 = b2t2.c1) - -> Nested Loop - -> Index Scan using t4_pkey on t4 b2t4 - -> Bitmap Heap Scan on t1 b2t1 - Recheck Cond: (c1 = b2t4.c1) - -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = b2t4.c1) - -> Sort - Sort Key: b2t2.c1 - -> Seq Scan on t2 b2t2 -> Hash Join Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 -> Hash -> Nested Loop - Join Filter: (bmt1.c1 = c2.c1) -> Nested Loop -> Nested Loop - -> Hash Join - Hash Cond: (bmt1.c1 = c1.c1) - -> Seq Scan on t1 bmt1 - -> Hash - -> CTE Scan on c1 - -> Index Scan using t2_pkey on t2 bmt2 - Index Cond: (c1 = bmt1.c1) - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) - -> Bitmap Index Scan on t3_pkey - Index Cond: (c1 = bmt1.c1) - -> CTE Scan on c2 -(54 rows) + Join Filter: ((max(b1t1.c1)) = bmt1.c1) + -> Merge Join + Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1))) + -> Sort + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Hash Join + Hash Cond: (b1t1.c1 = b1t2.c1) + -> Seq Scan on t1 b1t1 + -> Hash + -> Nested Loop + Join Filter: (b1t2.c1 = b1t3.c1) + -> Nested Loop + -> Seq Scan on t2 b1t2 + -> Bitmap Heap Scan on t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) + -> Index Scan using t3_pkey on t3 b1t3 + Index Cond: (c1 = b1t4.c1) + -> Sort + Sort Key: (max(b2t1.c1)) + -> Aggregate + -> Hash Join + Hash Cond: (b2t3.c1 = b2t1.c1) + -> Seq Scan on t3 b2t3 + -> Hash + -> Merge Join + Merge Cond: (b2t1.c1 = b2t2.c1) + -> Nested Loop + -> Index Scan using t4_pkey on t4 b2t4 + -> Bitmap Heap Scan on t1 b2t1 + Recheck Cond: (c1 = b2t4.c1) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = b2t4.c1) + -> Sort + Sort Key: b2t2.c1 + -> Seq Scan on t2 b2t2 + -> Seq Scan on t1 bmt1 + -> Index Scan using t2_pkey on t2 bmt2 + Index Cond: (c1 = bmt1.c1) + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt1.c1) +(53 rows) ---- ---- No. S-2-2 the number of the tables per quiry block @@ -1530,33 +1507,26 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1 QUERY PLAN ----------------------------------------------------------------- Aggregate - CTE c1 + InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Tid Scan on t1 b1t1 + -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 4 (returns $3) -> Result InitPlan 3 (returns $2) -> Limit - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 6 (returns $5) - -> Result - InitPlan 5 (returns $4) - -> Limit -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 <> $5) AND (c1 = 1)) - -> CTE Scan on c1 -(27 rows) + Filter: ((c1 <> $3) AND (c1 = 1)) + -> Result +(20 rows) /*+SeqScan(bmt1) TidScan(b1t1) @@ -1592,34 +1562,27 @@ error hint: QUERY PLAN --------------------------------------------------------------------------- Aggregate - CTE c1 + InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 4 (returns $3) - -> Result - InitPlan 3 (returns $2) - -> 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 6 (returns $5) + InitPlan 4 (returns $3) -> Result - InitPlan 5 (returns $4) + InitPlan 3 (returns $2) -> Limit -> Index Scan using t1_pkey on t1 b3t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) Filter: (ctid = '(1,1)'::tid) -> Nested Loop -> Seq Scan on t1 bmt1 - Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid) AND (c1 = 1)) - -> CTE Scan on c1 -(28 rows) + Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid) AND (c1 = 1)) + -> Result +(21 rows) -- No. S-2-2-2 EXPLAIN (COSTS false) @@ -1634,18 +1597,10 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t2.c1) @@ -1653,7 +1608,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 b2t2 Filter: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t2.c1) @@ -1666,11 +1621,17 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3 Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) + Filter: (c1 <> $1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(34 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) +(32 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey) TidScan(b1t1)SeqScan(b1t2) @@ -1710,15 +1671,7 @@ error hint: QUERY PLAN -------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $2) + InitPlan 1 (returns $1) -> Aggregate -> Nested Loop -> Tid Scan on t2 b2t2 @@ -1728,7 +1681,7 @@ error hint: Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t1_pkey Index Cond: (c1 = b2t2.c1) - InitPlan 3 (returns $4) + InitPlan 2 (returns $3) -> Aggregate -> Nested Loop -> Index Scan using t1_pkey on t1 b3t1 @@ -1740,13 +1693,19 @@ 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)) + Filter: ((c1 <> $3) 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) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) +(35 rows) -- No. S-2-2-3 EXPLAIN (COSTS false) @@ -1761,26 +1720,10 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Aggregate - 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 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t4.c1) @@ -1796,7 +1739,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 b2t4 TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t4.c1) @@ -1813,7 +1756,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 -> Tid Scan on t4 b3t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -1822,15 +1765,29 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) + Filter: (c1 <> $1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(67 rows) + -> 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 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) +(65 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) @@ -1849,12 +1806,12 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)' ) ; -LOG: available indexes for IndexScan(b1t3): t3_pkey -LOG: available indexes for BitmapScan(b1t4): t4_pkey LOG: available indexes for BitmapScan(b2t1): t1_pkey LOG: available indexes for IndexScan(b2t4): t4_pkey LOG: available indexes for IndexScan(b3t1): t1_pkey LOG: available indexes for BitmapScan(b3t2): t2_pkey +LOG: available indexes for IndexScan(b1t3): t3_pkey +LOG: available indexes for BitmapScan(b1t4): t4_pkey LOG: available indexes for IndexScan(bmt2): t2_pkey LOG: available indexes for BitmapScan(bmt3): t3_pkey LOG: pg_hint_plan: @@ -1882,80 +1839,86 @@ error hint: QUERY PLAN -------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - -> Nested Loop - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t1.c1) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t1.c1) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t1.c1) - InitPlan 2 (returns $4) + InitPlan 1 (returns $2) -> 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 2 (returns $5) -> 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) + Join Filter: (bmt1.c1 = (max(b1t1.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: ((c1 <> $5) 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 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) + -> 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 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t3_pkey on t3 b1t3 + Index Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Heap Scan on t4 b1t4 + Recheck Cond: (c1 = b1t2.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) - -> CTE Scan on c1 -(74 rows) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) +(80 rows) -- No. S-2-2-4 EXPLAIN (COSTS false) @@ -1970,38 +1933,22 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Aggregate - 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 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Limit -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 4 (returns $3) + InitPlan 3 (returns $3) -> Aggregate -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -2017,8 +1964,22 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(46 rows) + -> 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 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) +(44 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) @@ -2037,12 +1998,12 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' ) ; -LOG: available indexes for IndexScan(b1t3): t3_pkey -LOG: available indexes for BitmapScan(b1t4): t4_pkey LOG: available indexes for BitmapScan(b2t1): t1_pkey LOG: available indexes for BitmapScan(b2t1): t1_pkey LOG: available indexes for IndexScan(b3t1): t1_pkey LOG: available indexes for IndexScan(b3t1): t1_pkey +LOG: available indexes for IndexScan(b1t3): t3_pkey +LOG: available indexes for BitmapScan(b1t4): t4_pkey LOG: available indexes for IndexScan(bmt2): t2_pkey LOG: available indexes for BitmapScan(bmt3): t3_pkey LOG: pg_hint_plan: @@ -2064,60 +2025,62 @@ error hint: QUERY PLAN -------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - -> Nested Loop - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t1.c1) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t1.c1) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t1.c1) - InitPlan 3 (returns $3) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $2) + InitPlan 1 (returns $0) -> 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 4 (returns $3) -> Result - InitPlan 4 (returns $4) + InitPlan 3 (returns $2) -> Limit -> Index Scan Backward using t1_pkey on t1 b3t1 Index Cond: (c1 IS NOT NULL) Filter: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.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: ((c1 <> $3) 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 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) + -> 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 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t3_pkey on t3 b1t3 + Index Cond: (c1 = b1t2.c1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Heap Scan on t4 b1t4 + Recheck Cond: (c1 = b1t2.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) - -> CTE Scan on c1 -(54 rows) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) +(56 rows) ---- ---- No. S-2-3 RULE or VIEW @@ -2167,7 +2130,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 @@ -2178,14 +2143,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 @@ -2231,7 +2196,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 @@ -2242,14 +2209,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)'; @@ -2329,7 +2296,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 @@ -2340,17 +2309,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 @@ -2361,14 +2332,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 @@ -2456,7 +2427,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 @@ -2467,35 +2440,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)'; @@ -2608,7 +2583,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 @@ -2619,17 +2596,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 @@ -2640,17 +2619,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 @@ -2661,14 +2642,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 @@ -2806,7 +2787,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 @@ -2817,56 +2800,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; @@ -2995,11 +2982,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; @@ -3010,11 +2996,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; @@ -3025,25 +3010,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: @@ -3052,16 +3037,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: @@ -3069,13 +3055,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 @@ -3765,18 +3752,17 @@ 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) - "ti1_uniq" UNIQUE, btree (c1) "ti1_btree" btree (c1) + "ti1_c2_key" UNIQUE CONSTRAINT, btree (c2) "ti1_expr" btree ((c1 < 100)) "ti1_gin" gin (c1) "ti1_gist" gist (c1) @@ -3788,13 +3774,14 @@ Indexes: "ti1_multi" btree (c1, c2, c3, c4) "ti1_pred" btree (lower(c4)) "ti1_ts" gin (to_tsvector('english'::regconfig, c4)) + "ti1_uniq" UNIQUE, btree (c1) 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)'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- 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 @@ -3812,7 +3799,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 @@ -3830,7 +3817,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 @@ -3848,7 +3835,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 @@ -3866,7 +3853,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 @@ -3880,11 +3867,11 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- 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: ((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 @@ -3920,7 +3907,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 @@ -3938,7 +3925,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 @@ -3956,7 +3943,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 @@ -3974,7 +3961,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 @@ -3992,7 +3979,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 @@ -4010,7 +3997,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) @@ -4030,7 +4017,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) @@ -4050,7 +4037,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) @@ -4070,7 +4057,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) @@ -4089,7 +4076,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) @@ -4129,7 +4116,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) @@ -4149,7 +4136,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) @@ -4189,7 +4176,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_pkey Index Cond: (c1 < 100) (5 rows) @@ -4209,7 +4196,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) @@ -4280,9 +4267,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) @@ -4334,8 +4321,7 @@ error hint: ----------------------------------- Index Scan using ti1_expr on ti1 Index Cond: ((c1 < 100) = true) - Filter: (c1 < 100) -(3 rows) +(2 rows) -- No. S-3-4-28 EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1'; @@ -4489,7 +4475,8 @@ error hint: ---- No. S-3-5 not used index ---- -- No. S-3-5-1 -\! psql contrib_regression -c "/*+IndexScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" +\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: @@ -4498,14 +4485,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) -(3 rows) -- No. S-3-5-2 -\! psql contrib_regression -c "/*+BitmapScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" +\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: @@ -4514,14 +4503,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) -(3 rows) -- No. S-3-5-3 -\! psql contrib_regression -c "/*+IndexOnlyScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" +\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: @@ -4530,14 +4521,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) -(3 rows) -- No. S-3-5-4 -\! psql contrib_regression -c "/*+IndexScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" +\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: @@ -4546,14 +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) -(3 rows) -- No. S-3-5-5 -\! psql contrib_regression -c "/*+BitmapScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" +\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: @@ -4562,14 +4557,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) -(3 rows) -- No. S-3-5-6 -\! psql contrib_regression -c "/*+IndexOnlyScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" +\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 +4575,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) -(3 rows) -- No. S-3-5-7 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; @@ -4592,7 +4590,8 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; Index Cond: (c1 = 1) (2 rows) -\! psql contrib_regression -c "/*+TidScan(t1)*/ EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1" | grep -v "Planning time:" +\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) @@ -4600,11 +4599,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) -(3 rows) ---- ---- No. S-3-6 query structure @@ -4691,7 +4691,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) @@ -4704,11 +4704,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) */ @@ -4722,7 +4720,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) @@ -4735,7 +4732,7 @@ error hint: QUERY PLAN --------------------------------------------------------------------- - Result + Aggregate InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) @@ -4748,13 +4745,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) @@ -4764,24 +4759,17 @@ SELECT max(b3t1.c1), ( SELECT max(b2t1.c1) FROM s1.t1 b2t1 JOIN s1.t2 b2t2 ON(b2t1.c1 = b2t2.c1) WHERE b2t1.c1 = 1 ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = ( SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE b4t1.c1 = 1); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------- Aggregate - CTE cte1 - -> Aggregate - -> Nested Loop - -> Index Only Scan using t1_i1 on t1 b1t1 - Index Cond: (c1 = 1) - -> Seq Scan on t2 b1t2 - Filter: (c1 = 1) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop -> Index Only Scan using t1_i1 on t1 b2t1 Index Cond: (c1 = 1) -> Seq Scan on t2 b2t2 Filter: (c1 = 1) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop -> Index Only Scan using t1_i1 on t1 b4t1 @@ -4791,12 +4779,17 @@ SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE -> Nested Loop -> Nested Loop -> Index Only Scan using t1_i1 on t1 b3t1 - Index Cond: (c1 = $2) + Index Cond: (c1 = $1) -> Seq Scan on t2 b3t2 - Filter: (c1 = $2) - -> CTE Scan on cte1 - Filter: (c1 = $2) -(30 rows) + Filter: (c1 = $1) + -> Aggregate + Filter: (max(b1t1.c1) = $1) + -> Nested Loop + -> Index Only Scan using t1_i1 on t1 b1t1 + Index Cond: (c1 = 1) + -> Seq Scan on t2 b1t2 + Filter: (c1 = 1) +(28 rows) /*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1) TidScan(b1t2)SeqScan(b2t2)IndexScan(b3t2 t2_pkey)BitmapScan(b4t2 t2_pkey) @@ -4829,21 +4822,14 @@ error hint: QUERY PLAN --------------------------------------------------------- Aggregate - CTE cte1 - -> Aggregate - -> Nested Loop - -> Seq Scan on t1 b1t1 - Filter: (c1 = 1) - -> Seq Scan on t2 b1t2 - Filter: (c1 = 1) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop -> Index Scan using t1_pkey on t1 b2t1 Index Cond: (c1 = 1) -> Seq Scan on t2 b2t2 Filter: (c1 = 1) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop -> Seq Scan on t1 b4t1 @@ -4855,14 +4841,19 @@ error hint: -> Nested Loop -> Nested Loop -> Bitmap Heap Scan on t1 b3t1 - Recheck Cond: (c1 = $2) + Recheck Cond: (c1 = $1) -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = $2) + Index Cond: (c1 = $1) -> Index Scan using t2_pkey on t2 b3t2 - Index Cond: (c1 = $2) - -> CTE Scan on cte1 - Filter: (c1 = $2) -(34 rows) + Index Cond: (c1 = $1) + -> Aggregate + Filter: (max(b1t1.c1) = $1) + -> Nested Loop + -> Seq Scan on t1 b1t1 + Filter: (c1 = 1) + -> Seq Scan on t2 b1t2 + Filter: (c1 = 1) +(32 rows) -- No. S-3-7-3 EXPLAIN (COSTS false) @@ -4875,34 +4866,32 @@ SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1); QUERY PLAN --------------------------------------------------------------------- Aggregate - CTE cte1 - -> Aggregate - -> Nested Loop - -> Index Only Scan using t1_i1 on t1 b1t1 - Index Cond: (c1 = 1) - -> Seq Scan on t2 b1t2 - Filter: (c1 = 1) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Limit -> Index Only Scan using t1_i1 on t1 b2t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 5 (returns $4) + InitPlan 4 (returns $3) -> Result - InitPlan 4 (returns $3) + InitPlan 3 (returns $2) -> Limit -> Index Only Scan using t1_i1 on t1 b4t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Nested Loop -> Index Only Scan using t1_i1 on t1 b3t1 - Index Cond: (c1 = $4) + Index Cond: (c1 = $3) -> Seq Scan on t2 b3t2 - Filter: (c1 = $4) - -> CTE Scan on cte1 - Filter: (c1 = $4) -(28 rows) + Filter: (c1 = $3) + -> Aggregate + Filter: (max(b1t1.c1) = $3) + -> Nested Loop + -> Index Only Scan using t1_i1 on t1 b1t1 + Index Cond: (c1 = 1) + -> Seq Scan on t2 b1t2 + Filter: (c1 = 1) +(26 rows) /*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1) TidScan(b1t2)IndexScan(b3t2 t2_pkey) @@ -4933,39 +4922,37 @@ error hint: QUERY PLAN --------------------------------------------------------------------- Aggregate - CTE cte1 - -> Aggregate - -> Nested Loop - -> Seq Scan on t1 b1t1 - Filter: (c1 = 1) - -> Seq Scan on t2 b1t2 - Filter: (c1 = 1) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Limit -> Index Scan using t1_pkey on t1 b2t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 5 (returns $4) + InitPlan 4 (returns $3) -> Result - InitPlan 4 (returns $3) + InitPlan 3 (returns $2) -> Limit -> Seq Scan on t1 b4t1 Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Nested Loop -> Bitmap Heap Scan on t1 b3t1 - Recheck Cond: (c1 = $4) + Recheck Cond: (c1 = $3) -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = $4) + Index Cond: (c1 = $3) -> Index Scan using t2_pkey on t2 b3t2 - Index Cond: (c1 = $4) - -> CTE Scan on cte1 - Filter: (c1 = $4) -(30 rows) + Index Cond: (c1 = $3) + -> Aggregate + Filter: (max(b1t1.c1) = $3) + -> Nested Loop + -> Seq Scan on t1 b1t1 + Filter: (c1 = 1) + -> Seq Scan on t2 b1t2 + Filter: (c1 = 1) +(28 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; @@ -4992,12 +4979,12 @@ error hint: -- No. S-3-8-2 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------- Append - -> Seq Scan on p1 + -> Seq Scan on p1 p1_1 Filter: (c1 = 1) - -> Seq Scan on p1c1 + -> Seq Scan on p1c1 p1_2 Filter: (c1 = 1) (5 rows) @@ -5010,26 +4997,125 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------- + QUERY PLAN +-------------------------------------------- Append - -> Index Scan using p1_i on p1 + -> Index Scan using p1_i on p1 p1_1 Index Cond: (c1 = 1) - -> Index Scan using p1c1_i on p1c1 + -> Index Scan using p1c1_i on p1c1 p1_2 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 p1_1 + -> Seq Scan on p1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1_1 + 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 p1_1 + -> Index Scan using p1_i on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1_1 + 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: 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 p1_1 + -> Index Scan using p1_pkey on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_pkey on p1c1 p1_1 + Index Cond: (c1 = 1) +(7 rows) + ---- ---- No. S-3-9 inheritance table number ---- -- No. S-3-9-1 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------- Append - -> Seq Scan on p1 + -> Seq Scan on p1 p1_1 Filter: (c1 = 1) - -> Seq Scan on p1c1 + -> Seq Scan on p1c1 p1_2 Filter: (c1 = 1) (5 rows) @@ -5042,25 +5128,25 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------- + QUERY PLAN +-------------------------------------------- Append - -> Index Scan using p1_i on p1 + -> Index Scan using p1_i on p1 p1_1 Index Cond: (c1 = 1) - -> Index Scan using p1c1_i on p1c1 + -> Index Scan using p1c1_i on p1c1 p1_2 Index Cond: (c1 = 1) (5 rows) -- No. S-3-9-2 EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------- Append - -> Seq Scan on p2 + -> Seq Scan on p2 p2_1 Filter: (c1 = 1) - -> Seq Scan on p2c1 + -> Seq Scan on p2c1 p2_2 Filter: (c1 = 1) - -> Seq Scan on p2c1c1 + -> Seq Scan on p2c1c1 p2_3 Filter: (c1 = 1) (7 rows) @@ -5073,14 +5159,14 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------- + QUERY PLAN +------------------------------------------------ Append - -> Index Scan using p2_i on p2 + -> Index Scan using p2_i on p2 p2_1 Index Cond: (c1 = 1) - -> Index Scan using p2c1_i on p2c1 + -> Index Scan using p2c1_i on p2c1 p2_2 Index Cond: (c1 = 1) - -> Index Scan using p2c1c1_i on p2c1c1 + -> Index Scan using p2c1c1_i on p2c1c1 p2_3 Index Cond: (c1 = 1) (7 rows) @@ -5088,14 +5174,14 @@ error hint: ---- No. S-3-10 inheritance table specified table ---- EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------- Append - -> Seq Scan on p2 + -> Seq Scan on p2 p2_1 Filter: (c1 = 1) - -> Seq Scan on p2c1 + -> Seq Scan on p2c1 p2_2 Filter: (c1 = 1) - -> Seq Scan on p2c1c1 + -> Seq Scan on p2c1c1 p2_3 Filter: (c1 = 1) (7 rows) @@ -5109,14 +5195,14 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------- + QUERY PLAN +------------------------------------------------ Append - -> Index Scan using p2_i on p2 + -> Index Scan using p2_i on p2 p2_1 Index Cond: (c1 = 1) - -> Index Scan using p2c1_i on p2c1 + -> Index Scan using p2c1_i on p2c1 p2_2 Index Cond: (c1 = 1) - -> Index Scan using p2c1c1_i on p2c1c1 + -> Index Scan using p2c1c1_i on p2c1c1 p2_3 Index Cond: (c1 = 1) (7 rows) @@ -5125,38 +5211,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 + -> Seq Scan on p2 p2_1 Filter: (c1 = 1) - -> Seq Scan on p2c1 - Filter: (c1 = 1) - -> Seq Scan on p2c1c1 + -> Index Scan using p2c1_i on p2c1 p2_2 + Index Cond: (c1 = 1) + -> Seq Scan on p2c1c1 p2_3 Filter: (c1 = 1) (7 rows) -- No. S-3-10-3 -\! psql contrib_regression -c "EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10" | grep -v "Planning time:" - 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) +\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=xxx..xxx rows=4 width=xxx) + -> Seq Scan on p1 p1_1 (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 p1_2 (cost=xxx..xxx rows=3 width=xxx) Filter: ((c1 < 10) AND ((c2 * 2) < 100)) -(6 rows) -\! psql contrib_regression -c "/*+IndexScan(p1 p1_parent)*/ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10" | grep -v "Planning time:" +\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) @@ -5164,17 +5251,19 @@ not used hint: duplication hint: error hint: - 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) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +---------------- + Append (cost=xxx..xxx rows=4 width=xxx) + -> Index Scan using p1_parent on p1 p1_1 (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 p1_2 (cost=xxx..xxx rows=3 width=xxx) Filter: ((c2 * 2) < 100) -(6 rows) -- No. S-3-10-4 -\! psql contrib_regression -c "/*+IndexScan(p1 p1_i2)*/ EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1" | grep -v "Planning time:" +\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): @@ -5186,31 +5275,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 p1_1 (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 p1_2 (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 p1_3 (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 p1_4 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c2 = 1) -(10 rows) -- No. S-3-10-5 -\! psql contrib_regression -c "/*+IndexScan(p2 p2c1_pkey)*/ EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1" | grep -v "Planning time:" +\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) @@ -5218,16 +5302,17 @@ not used hint: duplication hint: error hint: - 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) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +---------------- + Append (cost={inf}..{inf} rows=3 width=xxx) + -> Seq Scan on p2 p2_1 (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 p2_2 (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 p2_3 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 1) -(8 rows) ---- ---- No. S-3-12 specified same table @@ -5235,7 +5320,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: @@ -5257,7 +5342,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: @@ -5280,9 +5365,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: @@ -5305,9 +5390,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: @@ -5350,7 +5435,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: @@ -5369,7 +5454,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: @@ -5517,7 +5602,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: @@ -5536,7 +5621,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: @@ -5572,7 +5657,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: @@ -5591,7 +5676,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: @@ -5627,7 +5712,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: @@ -5646,7 +5731,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: @@ -5682,7 +5767,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: @@ -5701,7 +5786,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: @@ -5737,7 +5822,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: @@ -5756,7 +5841,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: @@ -5841,7 +5926,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: @@ -5859,7 +5944,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: @@ -5940,8 +6025,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) @@ -5949,12 +6032,12 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------- + QUERY PLAN +----------------------------------------------- Append - -> Index Scan using p1_pkey on p1 + -> Index Scan using p1_pkey on p1 p1_1 Index Cond: (c1 = 1) - -> Index Scan using p1c1_pkey on p1c1 + -> Index Scan using p1c1_pkey on p1c1 p1_2 Index Cond: (c1 = 1) (5 rows) @@ -5963,8 +6046,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) @@ -5972,12 +6053,12 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------- + QUERY PLAN +-------------------------------------------- Append - -> Index Scan using p1_i on p1 + -> Index Scan using p1_i on p1 p1_1 Index Cond: (c1 = 1) - -> Index Scan using p1c1_i on p1c1 + -> Index Scan using p1c1_i on p1c1 p1_2 Index Cond: (c1 = 1) (5 rows) @@ -5986,8 +6067,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) @@ -5995,12 +6074,12 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------- Append - -> Seq Scan on p1 + -> Seq Scan on p1 p1_1 Filter: (c1 = 1) - -> Seq Scan on p1c1 + -> Seq Scan on p1c1 p1_2 Filter: (c1 = 1) (5 rows) @@ -6094,5 +6173,10 @@ error hint: 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; -\! pg_ctl reload -server signaled +SELECT pg_reload_conf(); + pg_reload_conf +---------------- + t +(1 row) + +\! rm results/ut-S.tmpout