X-Git-Url: http://git.osdn.net/view?a=blobdiff_plain;f=expected%2Fut-S.out;h=0f36d22ab0dc4dd8441770eda7cc13991573e50a;hb=7392b374d10a0526e6d4209685cdc1d6ada36ab7;hp=a2245381e99f039e12cc7f299249f24ab3644cf2;hpb=fea8a14e53112efcf94039940f76a2c7482c21d0;p=pghintplan%2Fpg_hint_plan.git diff --git a/expected/ut-S.out b/expected/ut-S.out index a224538..0f36d22 100644 --- a/expected/ut-S.out +++ b/expected/ut-S.out @@ -1,8 +1,19 @@ LOAD 'pg_hint_plan'; +-- We cannot do ALTER USER current_user SET ... +DELETE FROM pg_db_role_setting WHERE setrole = (SELECT oid FROM pg_roles WHERE rolname = current_user); +INSERT INTO pg_db_role_setting (SELECT 0, (SELECT oid FROM pg_roles WHERE rolname = current_user), '{client_min_messages=log,pg_hint_plan.debug_print=on}'); +ALTER SYSTEM SET session_preload_libraries TO 'pg_hint_plan'; +SELECT pg_reload_conf(); + pg_reload_conf +---------------- + t +(1 row) + SET pg_hint_plan.enable_hint TO on; SET pg_hint_plan.debug_print TO on; SET client_min_messages TO LOG; SET search_path TO public; +SET max_parallel_workers_per_gather TO 0; EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; QUERY PLAN --------------------- @@ -366,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) @@ -384,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) @@ -419,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) @@ -445,7 +456,7 @@ EXPLAIN (COSTS false) SELECT * FROM pg_catalog.pg_class WHERE oid = 1; QUERY PLAN ------------------------------------------------- Index Scan using pg_class_oid_index on pg_class - Index Cond: (oid = 1::oid) + Index Cond: (oid = '1'::oid) (2 rows) /*+SeqScan(pg_class)*/ @@ -457,10 +468,10 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------- Seq Scan on pg_class - Filter: (oid = 1::oid) + Filter: (oid = '1'::oid) (2 rows) -- No. S-1-5-6 @@ -529,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) @@ -554,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; @@ -628,32 +635,32 @@ SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1 QUERY PLAN ------------------------------------------------------------------------ Aggregate - InitPlan 1 (returns $0) + InitPlan 1 (returns $1) -> Aggregate - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) + -> Nested Loop -> Merge Join - Merge Cond: (b1t1.c1 = b1t4.c1) + 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 - -> Index Only Scan using t4_i1 on t4 b1t4 - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - -> Merge Join - Merge Cond: (bmt1.c1 = bmt2.c1) + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + -> Index Only Scan using t4_i1 on t4 b1t4 + Index Cond: (c1 = b1t1.c1) + -> Nested Loop -> Merge Join - Merge Cond: (bmt1.c1 = bmt4.c1) + Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 -> Index Only Scan using t3_i1 on t3 bmt3 - -> Index Only Scan using t4_i1 on t4 bmt4 - -> Sort - Sort Key: bmt2.c1 - -> Seq Scan on t2 bmt2 + -> Sort + Sort Key: bmt2.c1 + -> Seq Scan on t2 bmt2 + -> Index Only Scan using t4_i1 on t4 bmt4 + Index Cond: (c1 = bmt1.c1) (27 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) @@ -683,39 +690,39 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------- Aggregate InitPlan 1 (returns $2) -> Aggregate -> Hash Join - Hash Cond: (b1t2.c1 = b1t1.c1) - -> 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 Cond: (b1t1.c1 = b1t2.c1) + -> Seq Scan on t1 b1t1 -> Hash - -> Seq Scan on t1 b1t1 + -> 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: (bmt1.c1 = bmt4.c1) - -> Nested Loop - -> Hash Join - Hash Cond: (bmt1.c1 = bmt2.c1) - -> Seq Scan on t1 bmt1 - -> Hash - -> Index Scan using t2_pkey on t2 bmt2 - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) - -> Bitmap Index Scan on t3_pkey - Index Cond: (c1 = bmt1.c1) + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 -> Hash - -> Seq Scan on t4 bmt4 + -> Nested Loop + -> Hash Join + Hash Cond: (bmt1.c1 = bmt2.c1) + -> Seq Scan on t1 bmt1 + -> Hash + -> Index Scan using t2_pkey on t2 bmt2 + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt1.c1) (31 rows) -- No. S-2-1-2 @@ -730,46 +737,46 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 QUERY PLAN ------------------------------------------------------------------------ Aggregate - InitPlan 1 (returns $0) + InitPlan 1 (returns $1) -> Aggregate - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) + -> Nested Loop -> Merge Join - Merge Cond: (b1t1.c1 = b1t4.c1) + 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 - -> Index Only Scan using t4_i1 on t4 b1t4 - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - InitPlan 2 (returns $1) + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + -> Index Only Scan using t4_i1 on t4 b1t4 + Index Cond: (c1 = b1t1.c1) + InitPlan 2 (returns $3) -> Aggregate - -> Merge Join - Merge Cond: (b2t1.c1 = b2t2.c1) + -> Nested Loop -> Merge Join - Merge Cond: (b2t1.c1 = b2t4.c1) + 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 - -> Index Only Scan using t4_i1 on t4 b2t4 - -> Sort - Sort Key: b2t2.c1 - -> Seq Scan on t2 b2t2 - -> Merge Join - Merge Cond: (bmt1.c1 = bmt2.c1) + -> Sort + Sort Key: b2t2.c1 + -> Seq Scan on t2 b2t2 + -> Index Only Scan using t4_i1 on t4 b2t4 + Index Cond: (c1 = b2t1.c1) + -> Nested Loop -> Merge Join - Merge Cond: (bmt1.c1 = bmt4.c1) + Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 -> Index Only Scan using t3_i1 on t3 bmt3 - -> Index Only Scan using t4_i1 on t4 bmt4 - -> Sort - Sort Key: bmt2.c1 - -> Seq Scan on t2 bmt2 + -> Sort + Sort Key: bmt2.c1 + -> Seq Scan on t2 bmt2 + -> Index Only Scan using t4_i1 on t4 bmt4 + Index Cond: (c1 = bmt1.c1) (41 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) @@ -808,56 +815,56 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------- Aggregate InitPlan 1 (returns $2) -> Aggregate -> Hash Join - Hash Cond: (b1t2.c1 = b1t1.c1) - -> 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 Cond: (b1t1.c1 = b1t2.c1) + -> Seq Scan on t1 b1t1 -> Hash - -> Seq Scan on t1 b1t1 + -> 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) InitPlan 2 (returns $4) -> Aggregate -> Hash Join - Hash Cond: (b2t1.c1 = b2t3.c1) - -> 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 Cond: (b2t3.c1 = b2t1.c1) + -> Seq Scan on t3 b2t3 -> Hash - -> Seq Scan on t3 b2t3 + -> 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: (bmt1.c1 = bmt4.c1) - -> Nested Loop - -> Hash Join - Hash Cond: (bmt1.c1 = bmt2.c1) - -> Seq Scan on t1 bmt1 - -> Hash - -> Index Scan using t2_pkey on t2 bmt2 - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) - -> Bitmap Index Scan on t3_pkey - Index Cond: (c1 = bmt1.c1) + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 -> Hash - -> Seq Scan on t4 bmt4 + -> Nested Loop + -> Hash Join + Hash Cond: (bmt1.c1 = bmt2.c1) + -> Seq Scan on t1 bmt1 + -> Hash + -> Index Scan using t2_pkey on t2 bmt2 + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt1.c1) (48 rows) -- No. S-2-1-3 @@ -865,18 +872,18 @@ EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt QUERY PLAN ---------------------------------------------------------------- Aggregate - -> Merge Join - Merge Cond: (bmt1.c1 = bmt2.c1) + -> Nested Loop -> Merge Join - Merge Cond: (bmt1.c1 = bmt4.c1) + Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 -> Index Only Scan using t3_i1 on t3 bmt3 - -> Index Only Scan using t4_i1 on t4 bmt4 - -> Sort - Sort Key: bmt2.c1 - -> Seq Scan on t2 bmt2 + -> Sort + Sort Key: bmt2.c1 + -> Seq Scan on t2 bmt2 + -> Index Only Scan using t4_i1 on t4 bmt4 + Index Cond: (c1 = bmt1.c1) (13 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) @@ -894,23 +901,23 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Aggregate -> Hash Join - Hash Cond: (bmt1.c1 = bmt4.c1) - -> Nested Loop - -> Hash Join - Hash Cond: (bmt1.c1 = bmt2.c1) - -> Seq Scan on t1 bmt1 - -> Hash - -> Index Scan using t2_pkey on t2 bmt2 - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) - -> Bitmap Index Scan on t3_pkey - Index Cond: (c1 = bmt1.c1) + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 -> Hash - -> Seq Scan on t4 bmt4 + -> Nested Loop + -> Hash Join + Hash Cond: (bmt1.c1 = bmt2.c1) + -> Seq Scan on t1 bmt1 + -> Hash + -> Index Scan using t2_pkey on t2 bmt2 + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt1.c1) (15 rows) -- No. S-2-1-4 @@ -918,18 +925,18 @@ EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT * QUERY PLAN ---------------------------------------------------------------- Aggregate - -> Merge Join - Merge Cond: (bmt1.c1 = bmt2.c1) + -> Nested Loop -> Merge Join - Merge Cond: (bmt1.c1 = bmt4.c1) + Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 -> Index Only Scan using t3_i1 on t3 bmt3 - -> Index Only Scan using t4_i1 on t4 bmt4 - -> Sort - Sort Key: bmt2.c1 - -> Seq Scan on t2 bmt2 + -> Sort + Sort Key: bmt2.c1 + -> Seq Scan on t2 bmt2 + -> Index Only Scan using t4_i1 on t4 bmt4 + Index Cond: (c1 = bmt1.c1) (13 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) @@ -947,23 +954,23 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Aggregate -> Hash Join - Hash Cond: (bmt1.c1 = bmt4.c1) - -> Nested Loop - -> Hash Join - Hash Cond: (bmt1.c1 = bmt2.c1) - -> Seq Scan on t1 bmt1 - -> Hash - -> Index Scan using t2_pkey on t2 bmt2 - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) - -> Bitmap Index Scan on t3_pkey - Index Cond: (c1 = bmt1.c1) + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 -> Hash - -> Seq Scan on t4 bmt4 + -> Nested Loop + -> Hash Join + Hash Cond: (bmt1.c1 = bmt2.c1) + -> Seq Scan on t1 bmt1 + -> Hash + -> Index Scan using t2_pkey on t2 bmt2 + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt1.c1) (15 rows) -- No. S-2-1-5 @@ -976,33 +983,33 @@ SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1 QUERY PLAN ------------------------------------------------------------------------ Aggregate - InitPlan 1 (returns $0) + InitPlan 1 (returns $1) -> Aggregate - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) + -> Nested Loop -> Merge Join - Merge Cond: (b1t1.c1 = b1t4.c1) + 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 - -> Index Only Scan using t4_i1 on t4 b1t4 - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - -> Merge Join - Merge Cond: (bmt1.c1 = bmt2.c1) + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + -> Index Only Scan using t4_i1 on t4 b1t4 + Index Cond: (c1 = b1t1.c1) + -> Nested Loop -> Merge Join - Merge Cond: (bmt1.c1 = bmt4.c1) + Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 - Filter: (c1 <> $0) + Filter: (c1 <> $1) -> Index Only Scan using t3_i1 on t3 bmt3 - -> Index Only Scan using t4_i1 on t4 bmt4 - -> Sort - Sort Key: bmt2.c1 - -> Seq Scan on t2 bmt2 + -> Sort + Sort Key: bmt2.c1 + -> Seq Scan on t2 bmt2 + -> Index Only Scan using t4_i1 on t4 bmt4 + Index Cond: (c1 = bmt1.c1) (28 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) @@ -1032,40 +1039,40 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------- Aggregate InitPlan 1 (returns $2) -> Aggregate -> Hash Join - Hash Cond: (b1t2.c1 = b1t1.c1) - -> 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 Cond: (b1t1.c1 = b1t2.c1) + -> Seq Scan on t1 b1t1 -> Hash - -> Seq Scan on t1 b1t1 + -> 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: (bmt1.c1 = bmt4.c1) - -> Nested Loop - -> Hash Join - Hash Cond: (bmt1.c1 = bmt2.c1) - -> Seq Scan on t1 bmt1 - Filter: (c1 <> $2) - -> Hash - -> Index Scan using t2_pkey on t2 bmt2 - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) - -> Bitmap Index Scan on t3_pkey - Index Cond: (c1 = bmt1.c1) + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 -> Hash - -> Seq Scan on t4 bmt4 + -> Nested Loop + -> Hash Join + Hash Cond: (bmt1.c1 = bmt2.c1) + -> Seq Scan on t1 bmt1 + Filter: (c1 <> $2) + -> Hash + -> Index Scan using t2_pkey on t2 bmt2 + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt1.c1) (32 rows) -- No. S-2-1-6 @@ -1080,47 +1087,47 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 QUERY PLAN ------------------------------------------------------------------------ Aggregate - InitPlan 1 (returns $0) + InitPlan 1 (returns $1) -> Aggregate - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) + -> Nested Loop -> Merge Join - Merge Cond: (b1t1.c1 = b1t4.c1) + 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 - -> Index Only Scan using t4_i1 on t4 b1t4 - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - InitPlan 2 (returns $1) + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + -> Index Only Scan using t4_i1 on t4 b1t4 + Index Cond: (c1 = b1t1.c1) + InitPlan 2 (returns $3) -> Aggregate - -> Merge Join - Merge Cond: (b2t1.c1 = b2t2.c1) + -> Nested Loop -> Merge Join - Merge Cond: (b2t1.c1 = b2t4.c1) + 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 - -> Index Only Scan using t4_i1 on t4 b2t4 - -> Sort - Sort Key: b2t2.c1 - -> Seq Scan on t2 b2t2 - -> Merge Join - Merge Cond: (bmt1.c1 = bmt2.c1) + -> Sort + Sort Key: b2t2.c1 + -> Seq Scan on t2 b2t2 + -> Index Only Scan using t4_i1 on t4 b2t4 + Index Cond: (c1 = b2t1.c1) + -> Nested Loop -> Merge Join - Merge Cond: (bmt1.c1 = bmt4.c1) + Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 - Filter: ((c1 <> $0) AND (c1 <> $1)) + Filter: ((c1 <> $1) AND (c1 <> $3)) -> Index Only Scan using t3_i1 on t3 bmt3 - -> Index Only Scan using t4_i1 on t4 bmt4 - -> Sort - Sort Key: bmt2.c1 - -> Seq Scan on t2 bmt2 + -> Sort + Sort Key: bmt2.c1 + -> Seq Scan on t2 bmt2 + -> Index Only Scan using t4_i1 on t4 bmt4 + Index Cond: (c1 = bmt1.c1) (42 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) @@ -1159,57 +1166,57 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------- Aggregate InitPlan 1 (returns $2) -> Aggregate -> Hash Join - Hash Cond: (b1t2.c1 = b1t1.c1) - -> 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 Cond: (b1t1.c1 = b1t2.c1) + -> Seq Scan on t1 b1t1 -> Hash - -> Seq Scan on t1 b1t1 + -> 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) InitPlan 2 (returns $4) -> Aggregate -> Hash Join - Hash Cond: (b2t1.c1 = b2t3.c1) - -> 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 Cond: (b2t3.c1 = b2t1.c1) + -> Seq Scan on t3 b2t3 -> Hash - -> Seq Scan on t3 b2t3 + -> 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: (bmt1.c1 = bmt4.c1) - -> Nested Loop - -> Hash Join - Hash Cond: (bmt1.c1 = bmt2.c1) - -> Seq Scan on t1 bmt1 - Filter: ((c1 <> $2) AND (c1 <> $4)) - -> Hash - -> Index Scan using t2_pkey on t2 bmt2 - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) - -> Bitmap Index Scan on t3_pkey - Index Cond: (c1 = bmt1.c1) + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 -> Hash - -> Seq Scan on t4 bmt4 + -> Nested Loop + -> Hash Join + Hash Cond: (bmt1.c1 = bmt2.c1) + -> Seq Scan on t1 bmt1 + Filter: ((c1 <> $2) AND (c1 <> $4)) + -> Hash + -> Index Scan using t2_pkey on t2 bmt2 + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt1.c1) (49 rows) -- No. S-2-1-7 @@ -1222,40 +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 - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Merge Join - Merge Cond: (b1t1.c1 = b1t4.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 - -> Index Only Scan using t4_i1 on t4 b1t4 - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 -> 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) -(31 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) @@ -1287,42 +1290,45 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------ Aggregate - CTE c1 - -> Aggregate - -> Hash Join - Hash Cond: (b1t2.c1 = b1t1.c1) - -> 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 - -> Seq Scan on t1 b1t1 - -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.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) + -> Hash Join + Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 -(33 rows) + -> Hash + -> Nested Loop + -> 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) +(36 rows) -- No. S-2-1-8 EXPLAIN (COSTS false) @@ -1338,57 +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 - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Merge Join - Merge Cond: (b1t1.c1 = b1t4.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 - -> Index Only Scan using t4_i1 on t4 b1t4 - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - CTE c2 - -> Aggregate - -> Merge Join - Merge Cond: (b2t1.c1 = b2t2.c1) - -> Merge Join - Merge Cond: (b2t1.c1 = b2t4.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 - -> Index Only Scan using t4_i1 on t4 b2t4 - -> Sort - Sort Key: b2t2.c1 - -> Seq Scan on t2 b2t2 -> 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 -(48 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) @@ -1431,61 +1431,61 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Hash Join - Hash Cond: (b1t2.c1 = b1t1.c1) - -> 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 - -> Seq Scan on t1 b1t1 - CTE c2 - -> Aggregate - -> Hash Join - Hash Cond: (b2t1.c1 = b2t3.c1) - -> 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 - -> Seq Scan on t3 b2t3 - -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) - -> Nested Loop - Join Filter: (bmt1.c1 = c2.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) + -> Nested Loop + 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 - -> 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 - -> Seq Scan on t4 bmt4 (53 rows) ---- @@ -1507,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) @@ -1569,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) @@ -1611,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) @@ -1630,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) @@ -1643,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) @@ -1687,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 @@ -1705,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 @@ -1717,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) @@ -1738,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) @@ -1773,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) @@ -1790,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 @@ -1799,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) @@ -1826,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: @@ -1859,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) @@ -1947,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 @@ -1994,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) @@ -2014,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: @@ -2041,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 @@ -2144,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 @@ -2155,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 @@ -2208,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 @@ -2219,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)'; @@ -2306,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 @@ -2317,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 @@ -2338,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 @@ -2433,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 @@ -2444,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)'; @@ -2585,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 @@ -2596,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 @@ -2617,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 @@ -2638,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 @@ -2783,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 @@ -2794,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; @@ -2972,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; @@ -2987,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; @@ -3002,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: @@ -3029,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: @@ -3046,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 @@ -3548,7 +3558,7 @@ SET enable_tidscan TO off; EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)'; QUERY PLAN --------------------------------- - Index Scan using ti1_i2 on ti1 + Index Scan using ti1_i4 on ti1 Index Cond: (c2 = 1) Filter: (ctid = '(1,1)'::tid) (3 rows) @@ -3560,7 +3570,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)'; Bitmap Heap Scan on ti1 Recheck Cond: (c2 = 1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on ti1_i2 + -> Bitmap Index Scan on ti1_i4 Index Cond: (c2 = 1) (5 rows) @@ -3604,7 +3614,7 @@ error hint: QUERY PLAN --------------------------------- - Index Scan using ti1_i2 on ti1 + Index Scan using ti1_i3 on ti1 Index Cond: (c2 = 1) Filter: (ctid = '(1,1)'::tid) (3 rows) @@ -3622,7 +3632,7 @@ error hint: QUERY PLAN --------------------------------- - Index Scan using ti1_i2 on ti1 + Index Scan using ti1_i4 on ti1 Index Cond: (c2 = 1) Filter: (ctid = '(1,1)'::tid) (3 rows) @@ -3663,7 +3673,7 @@ error hint: Bitmap Heap Scan on ti1 Recheck Cond: (c2 = 1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on ti1_i2 + -> Bitmap Index Scan on ti1_i3 Index Cond: (c2 = 1) (5 rows) @@ -3683,7 +3693,7 @@ error hint: Bitmap Heap Scan on ti1 Recheck Cond: (c2 = 1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on ti1_i2 + -> Bitmap Index Scan on ti1_i4 Index Cond: (c2 = 1) (5 rows) @@ -3742,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) @@ -3765,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 @@ -3789,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 @@ -3807,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 @@ -3825,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 @@ -3843,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 @@ -3857,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 @@ -3897,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 @@ -3915,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 @@ -3933,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 @@ -3951,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 @@ -3969,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 @@ -3987,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) @@ -4007,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) @@ -4027,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) @@ -4047,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) @@ -4066,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) @@ -4106,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) @@ -4126,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) @@ -4151,7 +4161,7 @@ error hint: Index Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) (5 rows) --- No. S-3-4-10 +-- No. S-3-4-21 /*+BitmapScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)'; LOG: available indexes for BitmapScan(ti1): ti1_pkey @@ -4166,12 +4176,12 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_pkey Index Cond: (c1 < 100) (5 rows) --- No. S-3-4-11 +-- No. S-3-4-22 /*+BitmapScan(ti1 ti1_c2_key)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)'; LOG: available indexes for BitmapScan(ti1): ti1_c2_key @@ -4186,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) @@ -4217,9 +4227,9 @@ error hint: -- No. S-3-4-24 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; - QUERY PLAN ---------------------------------------- - Index Only Scan using ti1_uniq on ti1 + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 Index Cond: (c1 = 1) (2 rows) @@ -4257,17 +4267,17 @@ 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) -- No. S-3-4-26 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; - QUERY PLAN ---------------------------------------- - Index Only Scan using ti1_uniq on ti1 + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 Index Cond: (c1 = 1) (2 rows) @@ -4281,11 +4291,13 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------- - Seq Scan on ti1 - Filter: (c1 = 1) -(2 rows) + QUERY PLAN +------------------------------------ + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 = 1) + -> Bitmap Index Scan on ti1_gin + Index Cond: (c1 = 1) +(4 rows) -- No. S-3-4-27 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 100; @@ -4309,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'; @@ -4404,11 +4415,13 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------------------- - Seq Scan on ti1 - Filter: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) -(2 rows) + QUERY PLAN +----------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) + -> Bitmap Index Scan on ti1_ts + Index Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) +(4 rows) -- No. S-3-4-32 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; @@ -4462,8 +4475,8 @@ error hint: ---- No. S-3-5 not used index ---- -- No. S-3-5-1 -/*+IndexScan(ti1 ti1_pred)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+IndexScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for IndexScan(ti1): ti1_pred LOG: pg_hint_plan: used hint: @@ -4472,15 +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) -(2 rows) -- No. S-3-5-2 -/*+BitmapScan(ti1 ti1_pred)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+BitmapScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for BitmapScan(ti1): ti1_pred LOG: pg_hint_plan: used hint: @@ -4489,15 +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) -(2 rows) -- No. S-3-5-3 -/*+IndexOnlyScan(ti1 ti1_pred)*/ -EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+IndexOnlyScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for IndexOnlyScan(ti1): ti1_pred LOG: pg_hint_plan: used hint: @@ -4506,15 +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) -(2 rows) -- No. S-3-5-4 -/*+IndexScan(ti1 not_exist)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+IndexScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for IndexScan(ti1): LOG: pg_hint_plan: used hint: @@ -4523,15 +4539,16 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------- - Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15) +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +---------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) -(2 rows) -- No. S-3-5-5 -/*+BitmapScan(ti1 not_exist)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+BitmapScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for BitmapScan(ti1): LOG: pg_hint_plan: used hint: @@ -4540,15 +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) -(2 rows) -- No. S-3-5-6 -/*+IndexOnlyScan(ti1 not_exist)*/ -EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100; +\o results/ut-S.tmpout +/*+IndexOnlyScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100; LOG: available indexes for IndexOnlyScan(ti1): LOG: pg_hint_plan: used hint: @@ -4557,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) -(2 rows) -- No. S-3-5-7 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; @@ -4571,8 +4590,8 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; Index Cond: (c1 = 1) (2 rows) -/*+TidScan(t1)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +\o results/ut-S.tmpout +/*+TidScan(t1)*/ EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1; LOG: pg_hint_plan: used hint: TidScan(t1) @@ -4580,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) -(2 rows) ---- ---- No. S-3-6 query structure @@ -4659,7 +4679,7 @@ error hint: (6 rows) ---- ----- No. S-3-7 query structure +---- No. S-3-7 number of tables in a query block ---- -- No. S-3-7-1 EXPLAIN (COSTS false) @@ -4671,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) @@ -4684,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) */ @@ -4702,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) @@ -4715,7 +4732,7 @@ error hint: QUERY PLAN --------------------------------------------------------------------- - Result + Aggregate InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) @@ -4728,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) @@ -4744,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 @@ -4771,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) @@ -4809,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 @@ -4835,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) @@ -4855,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) @@ -4913,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; @@ -4972,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) @@ -4990,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) @@ -5022,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) @@ -5053,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) @@ -5068,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) @@ -5089,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) @@ -5105,39 +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 - Filter: (c1 = 1) - -> Seq Scan on p2c1 + -> Seq Scan on p2 p2_1 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 +\o results/ut-S.tmpout EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10; - 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 +\! 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)) -(5 rows) -/*+IndexScan(p1 p1_parent)*/ -EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10; +\o results/ut-S.tmpout +/*+IndexScan(p1 p1_parent)*/ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10; LOG: available indexes for IndexScan(p1): p1_parent LOG: available indexes for IndexScan(p1c1): p1c1_c4_expr_idx -LOG: available indexes for IndexScan(p1c2): p1c2_c4_expr_idx -LOG: available indexes for IndexScan(p1c3): p1c3_c4_expr_idx LOG: pg_hint_plan: used hint: IndexScan(p1 p1_parent) @@ -5145,18 +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) -(5 rows) -- No. S-3-10-4 -/*+IndexScan(p1 p1_i2)*/ -EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1; +\o results/ut-S.tmpout +/*+IndexScan(p1 p1_i2)*/ EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1; LOG: available indexes for IndexScan(p1): p1_i2 LOG: available indexes for IndexScan(p1c1): LOG: available indexes for IndexScan(p1c2): @@ -5168,32 +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) -(9 rows) -- No. S-3-10-5 -/*+IndexScan(p2 p2c1_pkey)*/ -EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1; +\o results/ut-S.tmpout +/*+IndexScan(p2 p2c1_pkey)*/ EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1; LOG: available indexes for IndexScan(p2): LOG: available indexes for IndexScan(p2c1): p2c1_pkey -LOG: available indexes for IndexScan(p2c2): -LOG: available indexes for IndexScan(p2c3): LOG: available indexes for IndexScan(p2c1c1): -LOG: available indexes for IndexScan(p2c1c2): -LOG: available indexes for IndexScan(p2c2c1): -LOG: available indexes for IndexScan(p2c2c2): -LOG: available indexes for IndexScan(p2c3c1): -LOG: available indexes for IndexScan(p2c3c2): LOG: pg_hint_plan: used hint: IndexScan(p2 p2c1_pkey) @@ -5201,24 +5302,25 @@ 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) -(7 rows) ---- ----- No. S-3-11 specified same table +---- No. S-3-12 specified same table ---- --- No. S-3-11-1 +-- 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: @@ -5233,14 +5335,14 @@ error hint: Bitmap Heap Scan on ti1 Recheck Cond: (c1 = 1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on ti1_uniq + -> Bitmap Index Scan on ti1_hash Index Cond: (c1 = 1) (5 rows) --- No. S-3-11-2 +-- 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: @@ -5260,12 +5362,12 @@ error hint: Index Cond: (c1 = 1) (5 rows) --- No. S-3-11-3 +-- 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: @@ -5281,16 +5383,16 @@ error hint: Bitmap Heap Scan on ti1 Recheck Cond: (c1 = 1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on ti1_uniq + -> Bitmap Index Scan on ti1_hash Index Cond: (c1 = 1) (5 rows) --- No. S-3-11-4 +-- 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: @@ -5312,9 +5414,9 @@ error hint: (5 rows) ---- ----- No. S-3-12 message output +---- No. S-3-13 message output of hint ---- --- No. S-3-12-1 +-- No. S-3-13-1 /*+SeqScan(ti1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: pg_hint_plan: @@ -5330,10 +5432,10 @@ error hint: Filter: ((c1 = 1) AND (ctid = '(1,1)'::tid)) (2 rows) --- No. S-3-12-2 +-- 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: @@ -5349,10 +5451,10 @@ SeqScan(ti1 ti1_pkey) Filter: (c1 = 1) (3 rows) --- No. S-3-12-3 +-- 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: @@ -5368,7 +5470,7 @@ SeqScan(ti1 ti1_pkey ti1_btree) Filter: (c1 = 1) (3 rows) --- No. S-3-12-4 +-- No. S-3-13-4 /*+IndexScan(ti1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: pg_hint_plan: @@ -5380,12 +5482,12 @@ error hint: QUERY PLAN ---------------------------------- - Index Scan using ti1_uniq on ti1 + Index Scan using ti1_hash on ti1 Index Cond: (c1 = 1) Filter: (ctid = '(1,1)'::tid) (3 rows) --- No. S-3-12-5 +-- No. S-3-13-5 /*+IndexScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: available indexes for IndexScan(ti1): ti1_pkey @@ -5403,7 +5505,7 @@ error hint: Filter: (ctid = '(1,1)'::tid) (3 rows) --- No. S-3-12-6 +-- No. S-3-13-6 /*+IndexScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: available indexes for IndexScan(ti1): ti1_btree ti1_pkey @@ -5421,7 +5523,7 @@ error hint: Filter: (ctid = '(1,1)'::tid) (3 rows) --- No. S-3-12-7 +-- No. S-3-13-7 /*+BitmapScan(ti1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: pg_hint_plan: @@ -5436,11 +5538,11 @@ error hint: Bitmap Heap Scan on ti1 Recheck Cond: (c1 = 1) Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on ti1_uniq + -> Bitmap Index Scan on ti1_hash Index Cond: (c1 = 1) (5 rows) --- No. S-3-12-8 +-- No. S-3-13-8 /*+BitmapScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: available indexes for BitmapScan(ti1): ti1_pkey @@ -5460,7 +5562,7 @@ error hint: Index Cond: (c1 = 1) (5 rows) --- No. S-3-12-9 +-- No. S-3-13-9 /*+BitmapScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: available indexes for BitmapScan(ti1): ti1_btree ti1_pkey @@ -5480,7 +5582,7 @@ error hint: Index Cond: (c1 = 1) (5 rows) --- No. S-3-12-10 +-- No. S-3-13-10 /*+TidScan(ti1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: pg_hint_plan: @@ -5497,10 +5599,10 @@ error hint: Filter: (c1 = 1) (3 rows) --- No. S-3-12-11 +-- 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: @@ -5516,10 +5618,10 @@ TidScan(ti1 ti1_pkey) Filter: (c1 = 1) (3 rows) --- No. S-3-12-12 +-- 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: @@ -5535,7 +5637,7 @@ TidScan(ti1 ti1_pkey ti1_btree) Filter: (c1 = 1) (3 rows) --- No. S-3-12-13 +-- No. S-3-13-13 /*+NoSeqScan(ti1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: pg_hint_plan: @@ -5552,10 +5654,10 @@ error hint: Filter: (c1 = 1) (3 rows) --- No. S-3-12-14 +-- 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: @@ -5571,10 +5673,10 @@ NoSeqScan(ti1 ti1_pkey) Filter: (c1 = 1) (3 rows) --- No. S-3-12-15 +-- 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: @@ -5590,7 +5692,7 @@ NoSeqScan(ti1 ti1_pkey ti1_btree) Filter: (c1 = 1) (3 rows) --- No. S-3-12-16 +-- No. S-3-13-16 /*+NoIndexScan(ti1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: pg_hint_plan: @@ -5607,10 +5709,10 @@ error hint: Filter: (c1 = 1) (3 rows) --- No. S-3-12-17 +-- 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: @@ -5626,10 +5728,10 @@ NoIndexScan(ti1 ti1_pkey) Filter: (c1 = 1) (3 rows) --- No. S-3-12-18 +-- 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: @@ -5645,7 +5747,7 @@ NoIndexScan(ti1 ti1_pkey ti1_btree) Filter: (c1 = 1) (3 rows) --- No. S-3-12-19 +-- No. S-3-13-19 /*+NoBitmapScan(ti1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: pg_hint_plan: @@ -5662,10 +5764,10 @@ error hint: Filter: (c1 = 1) (3 rows) --- No. S-3-12-20 +-- 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: @@ -5681,10 +5783,10 @@ NoBitmapScan(ti1 ti1_pkey) Filter: (c1 = 1) (3 rows) --- No. S-3-12-21 +-- 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: @@ -5700,7 +5802,7 @@ NoBitmapScan(ti1 ti1_pkey ti1_btree) Filter: (c1 = 1) (3 rows) --- No. S-3-12-22 +-- No. S-3-13-22 /*+NoTidScan(ti1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: pg_hint_plan: @@ -5712,15 +5814,15 @@ error hint: QUERY PLAN ---------------------------------- - Index Scan using ti1_uniq on ti1 + Index Scan using ti1_hash on ti1 Index Cond: (c1 = 1) Filter: (ctid = '(1,1)'::tid) (3 rows) --- No. S-3-12-23 +-- 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: @@ -5736,10 +5838,10 @@ NoTidScan(ti1 ti1_pkey) Filter: (c1 = 1) (3 rows) --- No. S-3-12-24 +-- 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: @@ -5755,7 +5857,7 @@ NoTidScan(ti1 ti1_pkey ti1_btree) Filter: (c1 = 1) (3 rows) --- No. S-3-12-25 +-- No. S-3-13-25 /*+IndexOnlyScan(ti1)*/ EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; LOG: pg_hint_plan: @@ -5771,7 +5873,7 @@ error hint: Index Cond: (c1 >= 1) (2 rows) --- No. S-3-12-26 +-- No. S-3-13-26 /*+IndexOnlyScan(ti1 ti1_pkey)*/ EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; LOG: available indexes for IndexOnlyScan(ti1): ti1_pkey @@ -5788,7 +5890,7 @@ error hint: Index Cond: (c1 >= 1) (2 rows) --- No. S-3-12-27 +-- No. S-3-13-27 /*+IndexOnlyScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; LOG: available indexes for IndexOnlyScan(ti1): ti1_btree ti1_pkey @@ -5805,7 +5907,7 @@ error hint: Index Cond: (c1 >= 1) (2 rows) --- No. S-3-12-28 +-- No. S-3-13-28 /*+NoIndexOnlyScan(ti1)*/ EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; LOG: pg_hint_plan: @@ -5817,14 +5919,14 @@ error hint: QUERY PLAN ---------------------------------- - Index Scan using ti1_uniq on ti1 + Index Scan using ti1_hash on ti1 Index Cond: (c1 = 1) (2 rows) --- No. S-3-12-29 +-- 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: @@ -5833,16 +5935,16 @@ duplication hint: error hint: NoIndexOnlyScan(ti1 ti1_pkey) - QUERY PLAN ---------------------------------------- - Index Only Scan using ti1_uniq on ti1 + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 Index Cond: (c1 = 1) (2 rows) --- No. S-3-12-30 +-- 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: @@ -5851,23 +5953,23 @@ duplication hint: error hint: NoIndexOnlyScan(ti1 ti1_pkey ti1_btree) - QUERY PLAN ---------------------------------------- - Index Only Scan using ti1_uniq on ti1 + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 Index Cond: (c1 = 1) (2 rows) ---- ----- No. S-3-13 message output +---- No. S-3-14 regular expression ---- EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; QUERY PLAN -------------------------------- - Index Scan using ti1_i2 on ti1 + Index Scan using ti1_i4 on ti1 Index Cond: (c2 = 1) (2 rows) --- No. S-3-13-1 +-- No. S-3-14-1 /*+IndexScanRegexp(ti1 ti1_.*_key)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; LOG: available indexes for IndexScanRegexp(ti1): ti1_c2_key @@ -5884,7 +5986,7 @@ error hint: Index Cond: (c2 = 1) (2 rows) --- No. S-3-13-2 +-- No. S-3-14-2 /*+IndexScanRegexp(ti1 ti1_i.)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; LOG: available indexes for IndexScanRegexp(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1 @@ -5897,11 +5999,11 @@ error hint: QUERY PLAN -------------------------------- - Index Scan using ti1_i2 on ti1 + Index Scan using ti1_i4 on ti1 Index Cond: (c2 = 1) (2 rows) --- No. S-3-13-3 +-- No. S-3-14-3 /*+IndexScanRegexp(ti1 no.*_exist)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; LOG: available indexes for IndexScanRegexp(ti1): @@ -5918,13 +6020,11 @@ error hint: Filter: (c2 = 1) (2 rows) --- No. S-3-13-4 +-- No. S-3-14-4 /*+IndexScanRegexp(p1 .*pkey)*/ 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) @@ -5932,22 +6032,20 @@ 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) --- No. S-3-13-5 +-- No. S-3-14-5 /*+IndexScanRegexp(p1 p1.*i)*/ 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) @@ -5955,22 +6053,20 @@ 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-13-6 +-- No. S-3-14-6 /*+IndexScanRegexp(p1 no.*_exist)*/ 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) @@ -5978,19 +6074,19 @@ 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) ---- ----- No. S-3-14 message output +---- No. S-3-15 message output of index candidate ---- --- No. S-3-14-1 +-- No. S-3-15-1 /*+IndexScan(ti1 ti1_i1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; LOG: available indexes for IndexScan(ti1): ti1_i1 @@ -6007,7 +6103,7 @@ error hint: Index Cond: (c2 = 1) (2 rows) --- No. S-3-14-2 +-- No. S-3-15-2 /*+IndexScan(ti1 not_exist)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; LOG: available indexes for IndexScan(ti1): @@ -6024,7 +6120,7 @@ error hint: Filter: (c2 = 1) (2 rows) --- No. S-3-14-3 +-- No. S-3-15-3 /*+IndexScan(ti1 ti1_i1 ti1_i2)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; LOG: available indexes for IndexScan(ti1): ti1_i2 ti1_i1 @@ -6041,7 +6137,7 @@ error hint: Index Cond: (c2 = 1) (2 rows) --- No. S-3-14-4 +-- No. S-3-15-4 /*+IndexScan(ti1 ti1_i1 not_exist)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; LOG: available indexes for IndexScan(ti1): ti1_i1 @@ -6058,7 +6154,7 @@ error hint: Index Cond: (c2 = 1) (2 rows) --- No. S-3-14-5 +-- No. S-3-15-5 /*+IndexScan(ti1 not_exist1 not_exist2)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; LOG: available indexes for IndexScan(ti1): @@ -6075,3 +6171,12 @@ error hint: Filter: (c2 = 1) (2 rows) +DELETE FROM pg_db_role_setting WHERE setrole = (SELECT oid FROM pg_roles WHERE rolname = current_user); +ALTER SYSTEM SET session_preload_libraries TO DEFAULT; +SELECT pg_reload_conf(); + pg_reload_conf +---------------- + t +(1 row) + +\! rm results/ut-S.tmpout