X-Git-Url: http://git.sourceforge.jp/view?a=blobdiff_plain;f=expected%2Fut-S.out;h=7ab53b2ffd589f731be41d1520b8ad9bf6dee434;hb=HEAD;hp=a2b6804d13bf64a8140c419bba2e4375b45a2b1a;hpb=6679c84ec3adf125cc5135024991bc01a33aae95;p=pghintplan%2Fpg_hint_plan.git diff --git a/expected/ut-S.out b/expected/ut-S.out index a2b6804..4e36451 100644 --- a/expected/ut-S.out +++ b/expected/ut-S.out @@ -1,8 +1,19 @@ LOAD 'pg_hint_plan'; -SET pg_hint_plan.enable TO on; +-- 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 --------------------- @@ -11,9 +22,9 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; (2 rows) EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) (2 rows) @@ -63,9 +74,9 @@ SeqScan(t1) duplication hint: error hint: - QUERY PLAN ------------------------------------- - Index Scan using t1_pkey on t1 t_1 + QUERY PLAN +---------------------------------- + Index Scan using t1_i1 on t1 t_1 Index Cond: (c1 = 1) (2 rows) @@ -114,9 +125,9 @@ SeqScan(s1.t1) duplication hint: error hint: - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) (2 rows) @@ -149,9 +160,9 @@ SeqScan(t2) duplication hint: error hint: - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) (2 rows) @@ -160,10 +171,10 @@ error hint: ---- -- No. S-1-4-1 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = 1 AND t1.c1 = t2.c1; - QUERY PLAN --------------------------------------- + QUERY PLAN +------------------------------------ Nested Loop - -> Index Scan using t1_pkey on t1 + -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) -> Seq Scan on t2 Filter: (c1 = 1) @@ -189,50 +200,56 @@ error hint: -- No. S-1-4-2 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2.t1.c1; - QUERY PLAN --------------------------------------- + QUERY PLAN +------------------------------------ Nested Loop - -> Index Scan using t1_pkey on t1 + -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) - -> Seq Scan on t1 + -> Seq Scan on t1 t1_1 Filter: (c1 = 1) (5 rows) -/*+IndexScan(t1)*/ +/*+BitmapScan(t1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2.t1.c1; LOG: pg_hint_plan: used hint: -IndexScan(t1) +BitmapScan(t1) not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------------- + QUERY PLAN +------------------------------------------ Nested Loop - -> Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) - -> Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) -(5 rows) + -> Bitmap Heap Scan on t1 + Recheck Cond: (c1 = 1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 = 1) + -> Bitmap Heap Scan on t1 t1_1 + Recheck Cond: (c1 = 1) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = 1) +(9 rows) -/*+SeqScan(t1)*/ +/*+BitmapScan(t1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2t1.c1; LOG: pg_hint_plan: used hint: -SeqScan(t1) +BitmapScan(t1) not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------- + QUERY PLAN +---------------------------------------- Nested Loop - -> Seq Scan on t1 - Filter: (c1 = 1) + -> Bitmap Heap Scan on t1 + Recheck Cond: (c1 = 1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 = 1) -> Seq Scan on t1 s2t1 Filter: (c1 = 1) -(5 rows) +(7 rows) /*+BitmapScan(s2t1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2t1.c1; @@ -246,7 +263,7 @@ error hint: QUERY PLAN ------------------------------------------ Nested Loop - -> Index Scan using t1_pkey on t1 + -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) -> Bitmap Heap Scan on t1 s2t1 Recheck Cond: (c1 = 1) @@ -258,78 +275,86 @@ error hint: EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 WHERE s1.t1.c1 = 1) FROM s1.t1 WHERE s1.t1.c1 = 1; QUERY PLAN --------------------------------------------------------------------- - Index Scan using t1_pkey on t1 + Index Only Scan using t1_i1 on t1 Index Cond: (c1 = 1) InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Index Scan using t1_pkey on t1 + -> Index Only Scan using t1_i1 on t1 t1_1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) (8 rows) -/*+SeqScan(t1)*/ +/*+BitmapScan(t1)*/ EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 WHERE s1.t1.c1 = 1) FROM s1.t1 WHERE s1.t1.c1 = 1; LOG: pg_hint_plan: used hint: -SeqScan(t1) +BitmapScan(t1) not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------ - Seq Scan on t1 - Filter: (c1 = 1) + QUERY PLAN +--------------------------------------------------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 = 1) InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Seq Scan on t1 - Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -(8 rows) + -> Bitmap Heap Scan on t1 t1_1 + Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1)) + -> Bitmap Index Scan on t1_i1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 = 1) +(12 rows) -/*+SeqScan(t11)*/ +/*+BitmapScan(t11)*/ EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 t11 WHERE t11.c1 = 1) FROM s1.t1 t12 WHERE t12.c1 = 1; LOG: pg_hint_plan: used hint: -SeqScan(t11) +BitmapScan(t11) not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------ - Index Scan using t1_pkey on t1 t12 + QUERY PLAN +--------------------------------------------------------------------------- + Index Only Scan using t1_i1 on t1 t12 Index Cond: (c1 = 1) InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Seq Scan on t1 t11 - Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -(8 rows) + -> Bitmap Heap Scan on t1 t11 + Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1)) + -> Bitmap Index Scan on t1_i1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) +(10 rows) -/*+SeqScan(t12)*/ +/*+BitmapScan(t12)*/ EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 t11 WHERE t11.c1 = 1) FROM s1.t1 t12 WHERE t12.c1 = 1; LOG: pg_hint_plan: used hint: -SeqScan(t12) +BitmapScan(t12) not used hint: duplication hint: error hint: QUERY PLAN --------------------------------------------------------------------- - Seq Scan on t1 t12 - Filter: (c1 = 1) + Bitmap Heap Scan on t1 t12 + Recheck Cond: (c1 = 1) InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Index Scan using t1_pkey on t1 t11 + -> Index Only Scan using t1_i1 on t1 t11 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -(8 rows) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 = 1) +(10 rows) ---- ---- No. S-1-5 object type for the hint @@ -352,15 +377,14 @@ error hint: -- No. S-1-5-2 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1; - QUERY PLAN ---------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (c1 = 1) - -> Seq Scan on p1c1 p1 - Filter: (c1 = 1) -(6 rows) + QUERY PLAN +----------------------------- + Append + -> Seq Scan on p1 p1_1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1_2 + Filter: (c1 = 1) +(5 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1; @@ -371,15 +395,14 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------- - Result - -> Append - -> Index Scan using p1_pkey on p1 - Index Cond: (c1 = 1) - -> Index Scan using p1c1_pkey on p1c1 p1 - Index Cond: (c1 = 1) -(6 rows) + QUERY PLAN +-------------------------------------------- + Append + -> Index Scan using p1_i on p1 p1_1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1_2 + Index Cond: (c1 = 1) +(5 rows) -- No. S-1-5-3 EXPLAIN (COSTS false) SELECT * FROM s1.ul1 WHERE ul1.c1 = 1; @@ -406,11 +429,10 @@ error hint: -- No. S-1-5-4 CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tm1_pkey" for table "tm1" 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) @@ -434,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)*/ @@ -446,14 +468,14 @@ 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 --- refer fdw.sql +-- refer ut-fdw.sql -- No. S-1-5-7 EXPLAIN (COSTS false) SELECT * FROM s1.f1() AS ft1 WHERE ft1.c1 = 1; QUERY PLAN @@ -518,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 Scan using t1_pkey on t1 - Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) - -> Index Scan using t1_pkey on t1 + -> 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) @@ -543,26 +563,24 @@ SeqScan(c1) duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Nested Loop - CTE c1 - -> Result - InitPlan 1 (returns $0) - -> Limit - -> Index Scan using t1_pkey on t1 - Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) - -> Index Scan using t1_pkey on t1 + -> 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; - QUERY PLAN -------------------------------------- - Index Scan using t1_pkey on t1 v1t1 + QUERY PLAN +----------------------------------- + Index Scan using t1_i1 on t1 v1t1 Index Cond: (c1 = 1) (2 rows) @@ -575,17 +593,17 @@ SeqScan(v1) duplication hint: error hint: - QUERY PLAN -------------------------------------- - Index Scan using t1_pkey on t1 v1t1 + QUERY PLAN +----------------------------------- + Index Scan using t1_i1 on t1 v1t1 Index Cond: (c1 = 1) (2 rows) -- No. S-1-5-11 EXPLAIN (COSTS false) SELECT * FROM (SELECT * FROM s1.t1 WHERE t1.c1 = 1) AS s1 WHERE s1.c1 = 1; - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) (2 rows) @@ -598,396 +616,5568 @@ SeqScan(s1) duplication hint: error hint: - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) (2 rows) ---- ----- No. S-3-1 scan method hint +---- No. S-2-1 some complexity query blocks ---- --- No. S-3-1-1 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 >= 1) -(2 rows) +-- No. S-2-1-1 +EXPLAIN (COSTS false) +SELECT max(bmt1.c1), ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.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 +; + QUERY PLAN +------------------------------------------------------------------------ + Aggregate + InitPlan 1 (returns $1) + -> 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) + -> Nested Loop + -> Merge Join + 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 + -> 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(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) +TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) +*/ +EXPLAIN (COSTS false) +SELECT max(bmt1.c1), ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.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 +; +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: used hint: -SeqScan(t1) +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +BitmapScan(bmt3 t3_pkey) +TidScan(bmt4) not used hint: duplication hint: error hint: - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 >= 1) -(2 rows) + QUERY PLAN +-------------------------------------------------------------------------- + Aggregate + InitPlan 1 (returns $2) + -> Aggregate + -> Hash Join + Hash Cond: (b1t1.c1 = b1t2.c1) + -> Seq Scan on t1 b1t1 + -> Hash + -> Nested Loop + Join Filter: (b1t2.c1 = b1t3.c1) + -> Nested Loop + -> Seq Scan on t2 b1t2 + -> Bitmap Heap Scan on t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) + -> Index Scan using t3_pkey on t3 b1t3 + Index Cond: (c1 = b1t4.c1) + -> Hash Join + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 + -> Hash + -> Nested Loop + -> 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-3-1-2 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) -(2 rows) +-- No. S-2-1-2 +EXPLAIN (COSTS false) +SELECT max(bmt1.c1), ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 +), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.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 +; + QUERY PLAN +------------------------------------------------------------------------ + Aggregate + InitPlan 1 (returns $1) + -> 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) + InitPlan 2 (returns $3) + -> 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) + -> Nested Loop + -> Merge Join + 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 + -> 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(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) +TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey) +*/ +EXPLAIN (COSTS false) +SELECT max(bmt1.c1), ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 +), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.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 +; +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(bmt2): t2_pkey +LOG: available indexes for BitmapScan(bmt3): t3_pkey LOG: pg_hint_plan: used hint: -SeqScan(t1) +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey) +TidScan(b2t2) +SeqScan(b2t3) +IndexScan(b2t4 t4_pkey) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +BitmapScan(bmt3 t3_pkey) +TidScan(bmt4) not used hint: duplication hint: error hint: - QUERY PLAN --------------------- - Seq Scan on t1 - Filter: (c1 = 1) -(2 rows) + QUERY PLAN +--------------------------------------------------------------------------- + Aggregate + InitPlan 1 (returns $2) + -> 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) + InitPlan 2 (returns $4) + -> Aggregate + -> Hash Join + Hash Cond: (b2t3.c1 = b2t1.c1) + -> Seq Scan on t3 b2t3 + -> Hash + -> Merge Join + Merge Cond: (b2t1.c1 = b2t2.c1) + -> Nested Loop + -> Index Scan using t4_pkey on t4 b2t4 + -> Bitmap Heap Scan on t1 b2t1 + Recheck Cond: (c1 = b2t4.c1) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = b2t4.c1) + -> Sort + Sort Key: b2t2.c1 + -> Seq Scan on t2 b2t2 + -> Hash Join + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 + -> Hash + -> Nested Loop + -> 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-3-1-3 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) -(2 rows) +-- No. S-2-1-3 +EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, (SELECT * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = sbmt4.c1; + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Nested Loop + -> Merge Join + 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 + -> 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) -/*+IndexScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) +*/ +EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, (SELECT * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = sbmt4.c1; +LOG: available indexes for IndexScan(bmt2): t2_pkey +LOG: available indexes for BitmapScan(bmt3): t3_pkey LOG: pg_hint_plan: used hint: -IndexScan(t1) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +BitmapScan(bmt3 t3_pkey) +TidScan(bmt4) not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) -(2 rows) + QUERY PLAN +------------------------------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 + -> Hash + -> 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-3-1-4 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 >= 1) -(2 rows) +-- No. S-2-1-4 +EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT * FROM s1.t3 bmt3) sbmt3, (SELECT * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Nested Loop + -> Merge Join + 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 + -> 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) -/*+IndexScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) +*/ +EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT * FROM s1.t3 bmt3) sbmt3, (SELECT * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; +LOG: available indexes for IndexScan(bmt2): t2_pkey +LOG: available indexes for BitmapScan(bmt3): t3_pkey LOG: pg_hint_plan: used hint: -IndexScan(t1) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +BitmapScan(bmt3 t3_pkey) +TidScan(bmt4) not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 >= 1) -(2 rows) + QUERY PLAN +------------------------------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 + -> Hash + -> 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-3-1-5 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; - QUERY PLAN ---------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c3 < 10) - -> Bitmap Index Scan on t1_i - Index Cond: (c3 < 10) -(4 rows) +-- No. S-2-1-5 +EXPLAIN (COSTS false) +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 <> ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 +) +; + QUERY PLAN +------------------------------------------------------------------------ + Aggregate + InitPlan 1 (returns $1) + -> 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) + -> Nested Loop + -> Merge Join + 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 <> $1) + -> Index Only Scan using t3_i1 on t3 bmt3 + -> 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) -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; +/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) +TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) +*/ +EXPLAIN (COSTS false) +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 <> ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 +) +; +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: used hint: -BitmapScan(t1) +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +BitmapScan(bmt3 t3_pkey) +TidScan(bmt4) not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c3 < 10) - -> Bitmap Index Scan on t1_i - Index Cond: (c3 < 10) -(4 rows) + QUERY PLAN +-------------------------------------------------------------------------- + Aggregate + InitPlan 1 (returns $2) + -> Aggregate + -> Hash Join + Hash Cond: (b1t1.c1 = b1t2.c1) + -> Seq Scan on t1 b1t1 + -> Hash + -> Nested Loop + Join Filter: (b1t2.c1 = b1t3.c1) + -> Nested Loop + -> Seq Scan on t2 b1t2 + -> Bitmap Heap Scan on t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) + -> Index Scan using t3_pkey on t3 b1t3 + Index Cond: (c1 = b1t4.c1) + -> Hash Join + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 + -> Hash + -> Nested Loop + -> 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-3-1-6 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) -(2 rows) +-- No. S-2-1-6 +EXPLAIN (COSTS false) +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 <> ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 +) AND bmt1.c1 <> ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 +) +; + QUERY PLAN +------------------------------------------------------------------------ + Aggregate + InitPlan 1 (returns $1) + -> 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) + InitPlan 2 (returns $3) + -> 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) + -> Nested Loop + -> Merge Join + 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 <> $1) AND (c1 <> $3)) + -> Index Only Scan using t3_i1 on t3 bmt3 + -> 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) -/*+BitmapScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) +TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey) +*/ +EXPLAIN (COSTS false) +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 <> ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 +) AND bmt1.c1 <> ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 +) +; +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(bmt2): t2_pkey +LOG: available indexes for BitmapScan(bmt3): t3_pkey LOG: pg_hint_plan: used hint: -BitmapScan(t1) +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey) +TidScan(b2t2) +SeqScan(b2t3) +IndexScan(b2t4 t4_pkey) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +BitmapScan(bmt3 t3_pkey) +TidScan(bmt4) not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c1 = 1) - -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = 1) -(4 rows) + QUERY PLAN +--------------------------------------------------------------------------- + Aggregate + InitPlan 1 (returns $2) + -> 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) + InitPlan 2 (returns $4) + -> Aggregate + -> Hash Join + Hash Cond: (b2t3.c1 = b2t1.c1) + -> Seq Scan on t3 b2t3 + -> Hash + -> Merge Join + Merge Cond: (b2t1.c1 = b2t2.c1) + -> Nested Loop + -> Index Scan using t4_pkey on t4 b2t4 + -> Bitmap Heap Scan on t1 b2t1 + Recheck Cond: (c1 = b2t4.c1) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = b2t4.c1) + -> Sort + Sort Key: b2t2.c1 + -> Seq Scan on t2 b2t2 + -> Hash Join + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 + -> Hash + -> Nested Loop + -> 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-3-1-7 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; - QUERY PLAN ------------------------------------ - Tid Scan on t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 = 1) -(3 rows) +-- No. S-2-1-7 +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 +) +SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 +, c1 + WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 +AND bmt1.c1 = c1.c1 +; + QUERY PLAN +---------------------------------------------------------------------------------------------------- + Aggregate + -> Nested Loop + -> Nested Loop + -> Nested Loop + -> Hash Join + Hash Cond: (bmt2.c1 = (max(b1t1.c1))) + -> Seq Scan on t2 bmt2 + -> Hash + -> 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 t3_i1 on t3 bmt3 + Index Cond: (c1 = bmt1.c1) + -> Index Only Scan using t4_i1 on t4 bmt4 + Index Cond: (c1 = bmt1.c1) +(27 rows) -/*+TidScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; +/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) +TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) +*/ +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 +) +SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 +, c1 + WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 +AND bmt1.c1 = c1.c1 +; +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: used hint: -TidScan(t1) +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +BitmapScan(bmt3 t3_pkey) +TidScan(bmt4) not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------ - Tid Scan on t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 = 1) -(3 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------------ + Aggregate + -> Hash Join + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 + -> 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-3-1-8 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)'); - QUERY PLAN -------------------------------------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) - Filter: (ctid = ANY ('{"(1,1)","(2,2)","(3,3)"}'::tid[])) -(3 rows) +-- No. S-2-1-8 +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 +) +, c2 (c1) AS ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 +) +SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 +, c1, c2 + WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 +AND bmt1.c1 = c1.c1 +AND bmt1.c1 = c2.c1 +; + QUERY PLAN +---------------------------------------------------------------------------------------------------------- + Aggregate + -> Nested Loop + Join Filter: (bmt1.c1 = (max(b2t1.c1))) + -> Nested Loop + -> Nested Loop + -> Nested Loop + -> Hash Join + Hash Cond: (bmt2.c1 = (max(b1t1.c1))) + -> Seq Scan on t2 bmt2 + -> Hash + -> 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 t3_i1 on t3 bmt3 + Index Cond: (c1 = bmt1.c1) + -> Index Only Scan using t4_i1 on t4 bmt4 + 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) -/*+TidScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)'); +/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) +TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey) +*/ +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 +) +, c2 (c1) AS ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 +) +SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 +, c1, c2 + WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 +AND bmt1.c1 = c1.c1 +AND bmt1.c1 = c2.c1 +; +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(bmt2): t2_pkey +LOG: available indexes for BitmapScan(bmt3): t3_pkey LOG: pg_hint_plan: used hint: -TidScan(t1) +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey) +TidScan(b2t2) +SeqScan(b2t3) +IndexScan(b2t4 t4_pkey) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +BitmapScan(bmt3 t3_pkey) +TidScan(bmt4) not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------- - Tid Scan on t1 - TID Cond: (ctid = ANY ('{"(1,1)","(2,2)","(3,3)"}'::tid[])) - Filter: (c1 = 1) -(3 rows) - --- No. S-3-1-9 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 >= 1) -(2 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (bmt4.c1 = bmt1.c1) + -> Seq Scan on t4 bmt4 + -> Hash + -> Nested Loop + -> Nested Loop + -> 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 + -> Index Scan using t2_pkey on t2 bmt2 + Index Cond: (c1 = bmt1.c1) + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt1.c1) +(53 rows) -/*+NoSeqScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; -LOG: pg_hint_plan: -used hint: -NoSeqScan(t1) +---- +---- No. S-2-2 the number of the tables per quiry block +---- +-- No. S-2-2-1 +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = 1 +) +SELECT max(bmt1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1 +) + FROM s1.t1 bmt1, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = 1 +AND bmt1.c1 <> ( +SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1 +) +; + QUERY PLAN +----------------------------------------------------------------- + Aggregate + InitPlan 2 (returns $1) + -> Result + 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) + -> Result + InitPlan 3 (returns $2) + -> 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 <> $3) AND (c1 = 1)) + -> Result +(20 rows) + +/*+SeqScan(bmt1) +TidScan(b1t1) +BitmapScan(b2t1 t1_pkey) +IndexScan(b3t1 t1_pkey) +*/ +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = 1 +) +SELECT max(bmt1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1 +) + FROM s1.t1 bmt1, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = 1 +AND bmt1.c1 <> ( +SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1 +) +; +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: pg_hint_plan: +used hint: +TidScan(b1t1) +BitmapScan(b2t1 t1_pkey) +IndexScan(b3t1 t1_pkey) +SeqScan(bmt1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------------------- + Aggregate + InitPlan 2 (returns $1) + -> Result + 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 4 (returns $3) + -> Result + 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 <> $3) AND (ctid = '(1,1)'::tid) AND (c1 = 1)) + -> Result +(21 rows) + +-- No. S-2-2-2 +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' +) +SELECT max(bmt1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' +) + FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' +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 +----------------------------------------------------------- + Aggregate + InitPlan 1 (returns $0) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) + InitPlan 2 (returns $1) + -> Aggregate + -> Nested Loop + Join Filter: (b3t1.c1 = b3t2.c1) + -> Tid Scan on t1 b3t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b3t2 + Filter: (ctid = '(1,1)'::tid) + -> Nested Loop + -> Nested Loop + Join Filter: (bmt1.c1 = bmt2.c1) + -> Tid Scan on t1 bmt1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 <> $1) + -> Seq Scan on t2 bmt2 + Filter: (ctid = '(1,1)'::tid) + -> 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) +BitmapScan(b2t1 t1_pkey)TidScan(b2t2) +IndexScan(b3t1 t1_pkey)BitmapScan(b3t2 t2_pkey) +*/ +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' +) +SELECT max(bmt1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' +) + FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' +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)' +) +; +LOG: available indexes for BitmapScan(b2t1): t1_pkey +LOG: available indexes for IndexScan(b3t1): t1_pkey +LOG: available indexes for BitmapScan(b3t2): t2_pkey +LOG: available indexes for IndexScan(bmt2): t2_pkey +LOG: pg_hint_plan: +used hint: +TidScan(b1t1) +SeqScan(b1t2) +BitmapScan(b2t1 t1_pkey) +TidScan(b2t2) +IndexScan(b3t1 t1_pkey) +BitmapScan(b3t2 t2_pkey) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + InitPlan 1 (returns $1) + -> Aggregate + -> Nested Loop + -> Tid Scan on t2 b2t2 + TID Cond: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t1 b2t1 + Recheck Cond: (c1 = b2t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = b2t2.c1) + InitPlan 2 (returns $3) + -> Aggregate + -> Nested Loop + -> Index Scan using t1_pkey on t1 b3t1 + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t2 b3t2 + Recheck Cond: (c1 = b3t1.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t2_pkey + Index Cond: (c1 = b3t1.c1) + -> Nested Loop + -> Nested Loop + Join Filter: (bmt1.c1 = bmt2.c1) + -> Seq Scan on t1 bmt1 + Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid)) + -> Index Scan using t2_pkey on t2 bmt2 + Filter: (ctid = '(1,1)'::tid) + -> 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) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)' +) +SELECT max(bmt1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)' +) + FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1 +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 +----------------------------------------------------------------------- + Aggregate + InitPlan 1 (returns $0) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t3.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) + InitPlan 2 (returns $1) + -> Aggregate + -> Nested Loop + Join Filter: (b3t1.c1 = b3t4.c1) + -> Nested Loop + Join Filter: (b3t1.c1 = b3t3.c1) + -> Nested Loop + Join Filter: (b3t1.c1 = b3t2.c1) + -> Tid Scan on t1 b3t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b3t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b3t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b3t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Nested Loop + Join Filter: (bmt1.c1 = (max(b1t1.c1))) + -> Nested Loop + Join Filter: (bmt1.c1 = bmt4.c1) + -> Nested Loop + Join Filter: (bmt1.c1 = bmt3.c1) + -> Nested Loop + Join Filter: (bmt1.c1 = bmt2.c1) + -> Tid Scan on t1 bmt1 + TID Cond: (ctid = '(1,1)'::tid) + 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) + -> 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) +BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey) +IndexScan(b3t1 t1_pkey)BitmapScan(b3t2 t2_pkey)TidScan(b3t3)SeqScan(b3t4) +*/ +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)' +) +SELECT max(bmt1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)' +) + FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1 +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 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: +used hint: +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey) +TidScan(b2t2) +SeqScan(b2t3) +IndexScan(b2t4 t4_pkey) +IndexScan(b3t1 t1_pkey) +BitmapScan(b3t2 t2_pkey) +TidScan(b3t3) +SeqScan(b3t4) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +BitmapScan(bmt3 t3_pkey) +TidScan(bmt4) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------------------------------------------------- + Aggregate + InitPlan 1 (returns $2) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) + -> Nested Loop + 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) + -> Seq Scan on t3 b2t3 + Filter: (ctid = '(1,1)'::tid) + -> 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 = b2t2.c1) + Filter: (ctid = '(1,1)'::tid) + InitPlan 2 (returns $5) + -> Aggregate + -> Nested Loop + 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) + -> Seq Scan on t4 b3t4 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t1_pkey on t1 b3t1 + Index Cond: (c1 = b3t4.c1) + Filter: (ctid = '(1,1)'::tid) + -> 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 = (max(b1t1.c1))) + -> Nested Loop + 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)) + -> 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 t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) +(80 rows) + +-- No. S-2-2-4 +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)' +) +SELECT max(bmt1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1 +) + FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1 +AND bmt1.c1 <> ( +SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' +) +; + QUERY PLAN +----------------------------------------------------------------------- + Aggregate + InitPlan 2 (returns $1) + -> Result + 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 3 (returns $3) + -> Aggregate + -> Tid Scan on t1 b3t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Nested Loop + Join Filter: (bmt1.c1 = (max(b1t1.c1))) + -> Nested Loop + Join Filter: (bmt1.c1 = bmt4.c1) + -> Nested Loop + Join Filter: (bmt1.c1 = bmt3.c1) + -> Nested Loop + Join Filter: (bmt1.c1 = bmt2.c1) + -> Tid Scan on t1 bmt1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 <> $3) + -> 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) + -> 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) +BitmapScan(b2t1 t1_pkey) +IndexScan(b3t1 t1_pkey) +*/ +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)' +) +SELECT max(bmt1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1 +) + FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1 +AND bmt1.c1 <> ( +SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' +) +; +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: +used hint: +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey) +IndexScan(b3t1 t1_pkey) +SeqScan(bmt1) +IndexScan(bmt2 t2_pkey) +BitmapScan(bmt3 t3_pkey) +TidScan(bmt4) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------------------------------------------------- + Aggregate + InitPlan 2 (returns $1) + -> Result + 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 4 (returns $3) + -> Result + 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 = (max(b1t1.c1))) + -> Nested Loop + 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 <> $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 t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) +(56 rows) + +---- +---- No. S-2-3 RULE or VIEW +---- +-- No. S-2-3-1 +EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; + QUERY PLAN +----------------------------------------------------------------- + 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 + -> Tid Scan on r1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 + TID Cond: (ctid = '(1,1)'::tid) +(19 rows) + +/*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey) +SeqScan(r1)*/ +EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; +LOG: available indexes for IndexScan(t3): t3_pkey +LOG: available indexes for BitmapScan(t4): t4_pkey +LOG: pg_hint_plan: +used hint: +SeqScan(r1) +TidScan(t1) +SeqScan(t2) +IndexScan(t3 t3_pkey) +BitmapScan(t4 t4_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + 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 + -> Seq Scan on r1 + Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t3_pkey on t3 + Index Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t4 + Recheck Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + 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 +----------------------------------------------------------------- + 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 + -> Tid Scan on r1_ + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> 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) +(19 rows) + +/*+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) +SeqScan(r1_)*/ +EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; +LOG: available indexes for IndexScan(b1t3): t3_pkey +LOG: available indexes for BitmapScan(b1t4): t4_pkey +LOG: pg_hint_plan: +used hint: +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +SeqScan(r1_) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + 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 + -> Seq Scan on r1_ + Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) + -> 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 t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + 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)'; + QUERY PLAN +----------------------------------------------------------------- + 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 + -> Tid Scan on r2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 + TID Cond: (ctid = '(1,1)'::tid) + + 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 + -> Tid Scan on r2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 + TID Cond: (ctid = '(1,1)'::tid) +(39 rows) + +/*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey) +SeqScan(r2)*/ +EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; +LOG: available indexes for IndexScan(t3): t3_pkey +LOG: available indexes for BitmapScan(t4): t4_pkey +LOG: pg_hint_plan: +used hint: +SeqScan(r2) +TidScan(t1) +SeqScan(t2) +IndexScan(t3 t3_pkey) +BitmapScan(t4 t4_pkey) +not used hint: +duplication hint: +error hint: + +LOG: available indexes for IndexScan(t3): t3_pkey +LOG: available indexes for BitmapScan(t4): t4_pkey +LOG: pg_hint_plan: +used hint: +SeqScan(r2) +TidScan(t1) +SeqScan(t2) +IndexScan(t3 t3_pkey) +BitmapScan(t4 t4_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + 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 + -> Seq Scan on r2 + Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t3_pkey on t3 + Index Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t4 + Recheck Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + 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 + -> Seq Scan on r2 + Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t3_pkey on t3 + Index Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t4 + Recheck Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + 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 +----------------------------------------------------------------- + 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 + -> Tid Scan on r2_ + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> 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) + + Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t3.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Nested Loop + -> Tid Scan on r2_ + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) +(39 rows) + +/*+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey) +SeqScan(r2_)*/ +EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; +LOG: available indexes for IndexScan(b1t3): t3_pkey +LOG: available indexes for BitmapScan(b1t4): t4_pkey +LOG: pg_hint_plan: +used hint: +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +SeqScan(r2_) +not used hint: +BitmapScan(b2t1 t1_pkey) +TidScan(b2t2) +SeqScan(b2t3) +IndexScan(b2t4 t4_pkey) +duplication hint: +error hint: + +LOG: available indexes for BitmapScan(b2t1): t1_pkey +LOG: available indexes for IndexScan(b2t4): t4_pkey +LOG: pg_hint_plan: +used hint: +BitmapScan(b2t1 t1_pkey) +TidScan(b2t2) +SeqScan(b2t3) +IndexScan(b2t4 t4_pkey) +SeqScan(r2_) +not used hint: +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + 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 + -> Seq Scan on r2_ + Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) + -> 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 t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) + + Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) + -> Nested Loop + 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) + -> Seq Scan on t3 b2t3 + Filter: (ctid = '(1,1)'::tid) + -> 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 = b2t2.c1) + Filter: (ctid = '(1,1)'::tid) +(45 rows) + +-- No. S-2-3-3 +EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; + QUERY PLAN +----------------------------------------------------------------- + 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 + -> Tid Scan on r3 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 + TID Cond: (ctid = '(1,1)'::tid) + + 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 + -> Tid Scan on r3 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 + TID Cond: (ctid = '(1,1)'::tid) + + 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 + -> Tid Scan on r3 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 + TID Cond: (ctid = '(1,1)'::tid) +(59 rows) + +/*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey) +SeqScan(r3)*/ +EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; +LOG: available indexes for IndexScan(t3): t3_pkey +LOG: available indexes for BitmapScan(t4): t4_pkey +LOG: pg_hint_plan: +used hint: +SeqScan(r3) +TidScan(t1) +SeqScan(t2) +IndexScan(t3 t3_pkey) +BitmapScan(t4 t4_pkey) +not used hint: +duplication hint: +error hint: + +LOG: available indexes for IndexScan(t3): t3_pkey +LOG: available indexes for BitmapScan(t4): t4_pkey +LOG: pg_hint_plan: +used hint: +SeqScan(r3) +TidScan(t1) +SeqScan(t2) +IndexScan(t3 t3_pkey) +BitmapScan(t4 t4_pkey) +not used hint: +duplication hint: +error hint: + +LOG: available indexes for IndexScan(t3): t3_pkey +LOG: available indexes for BitmapScan(t4): t4_pkey +LOG: pg_hint_plan: +used hint: +SeqScan(r3) +TidScan(t1) +SeqScan(t2) +IndexScan(t3 t3_pkey) +BitmapScan(t4 t4_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + 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 + -> Seq Scan on r3 + Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t3_pkey on t3 + Index Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t4 + Recheck Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + 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 + -> Seq Scan on r3 + Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t3_pkey on t3 + Index Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t4 + Recheck Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + 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 + -> Seq Scan on r3 + Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t3_pkey on t3 + Index Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t4 + Recheck Cond: (c1 = t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + 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 +----------------------------------------------------------------- + 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 + -> Tid Scan on r3_ + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> 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) + + Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t3.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Nested Loop + -> Tid Scan on r3_ + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) + + Aggregate + -> Nested Loop + Join Filter: (b3t1.c1 = b3t4.c1) + -> Nested Loop + Join Filter: (b3t1.c1 = b3t3.c1) + -> Nested Loop + Join Filter: (b3t1.c1 = b3t2.c1) + -> Nested Loop + -> Tid Scan on r3_ + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) + -> Tid Scan on t1 b3t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b3t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b3t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b3t4 + TID Cond: (ctid = '(1,1)'::tid) +(59 rows) + +/*+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey) +IndexScan(b3t1 t1_pkey)BitmapScan(b3t2 t2_pkey)TidScan(b3t3)SeqScan(b3t4) +SeqScan(r3_)*/ +EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; +LOG: available indexes for IndexScan(b1t3): t3_pkey +LOG: available indexes for BitmapScan(b1t4): t4_pkey +LOG: pg_hint_plan: +used hint: +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +SeqScan(r3_) +not used hint: +BitmapScan(b2t1 t1_pkey) +TidScan(b2t2) +SeqScan(b2t3) +IndexScan(b2t4 t4_pkey) +IndexScan(b3t1 t1_pkey) +BitmapScan(b3t2 t2_pkey) +TidScan(b3t3) +SeqScan(b3t4) +duplication hint: +error hint: + +LOG: available indexes for BitmapScan(b2t1): t1_pkey +LOG: available indexes for IndexScan(b2t4): t4_pkey +LOG: pg_hint_plan: +used hint: +BitmapScan(b2t1 t1_pkey) +TidScan(b2t2) +SeqScan(b2t3) +IndexScan(b2t4 t4_pkey) +SeqScan(r3_) +not used hint: +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +IndexScan(b3t1 t1_pkey) +BitmapScan(b3t2 t2_pkey) +TidScan(b3t3) +SeqScan(b3t4) +duplication hint: +error hint: + +LOG: available indexes for IndexScan(b3t1): t1_pkey +LOG: available indexes for BitmapScan(b3t2): t2_pkey +LOG: pg_hint_plan: +used hint: +IndexScan(b3t1 t1_pkey) +BitmapScan(b3t2 t2_pkey) +TidScan(b3t3) +SeqScan(b3t4) +SeqScan(r3_) +not used hint: +TidScan(b1t1) +SeqScan(b1t2) +IndexScan(b1t3 t3_pkey) +BitmapScan(b1t4 t4_pkey) +BitmapScan(b2t1 t1_pkey) +TidScan(b2t2) +SeqScan(b2t3) +IndexScan(b2t4 t4_pkey) +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + 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 + -> Seq Scan on r3_ + Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) + -> 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 t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) + + Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) + -> Nested Loop + 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) + -> Seq Scan on t3 b2t3 + Filter: (ctid = '(1,1)'::tid) + -> 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 = b2t2.c1) + Filter: (ctid = '(1,1)'::tid) + + Aggregate + -> Nested Loop + 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) + -> Seq Scan on t4 b3t4 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t1_pkey on t1 b3t1 + Index Cond: (c1 = b3t4.c1) + Filter: (ctid = '(1,1)'::tid) + -> 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) +(68 rows) + +-- No. S-2-3-4 +EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1; + QUERY PLAN +------------------------------------ + Hash Join + Hash Cond: (v1t1.c1 = v1t1_1.c1) + -> Seq Scan on t1 v1t1 + -> Hash + -> Seq Scan on t1 v1t1_1 +(5 rows) + +/*+BitmapScan(v1t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1; +LOG: pg_hint_plan: +used hint: +BitmapScan(v1t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------ + Nested Loop + -> Index Scan using t1_i1 on t1 v1t1 + -> Bitmap Heap Scan on t1 v1t1_1 + Recheck Cond: (c1 = v1t1.c1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 = v1t1.c1) +(6 rows) + +-- No. S-2-3-5 +EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1; + QUERY PLAN +----------------------------------- + Hash Join + Hash Cond: (v1t1.c1 = v1t1_.c1) + -> Seq Scan on t1 v1t1 + -> Hash + -> Seq Scan on t1 v1t1_ +(5 rows) + +/*+SeqScan(v1t1)BitmapScan(v1t1_)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1; +LOG: pg_hint_plan: +used hint: +SeqScan(v1t1) +BitmapScan(v1t1_) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------ + Nested Loop + -> Seq Scan on t1 v1t1 + -> Bitmap Heap Scan on t1 v1t1_ + Recheck Cond: (c1 = v1t1.c1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 = v1t1.c1) +(6 rows) + +-- No. S-2-3-6 +EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1; + QUERY PLAN +------------------------------------ + Hash Join + Hash Cond: (r4t1.c1 = r4t1_1.c1) + -> Seq Scan on t1 r4t1 + -> Hash + -> Seq Scan on t1 r4t1_1 +(5 rows) + +/*+BitmapScan(r4t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1; +LOG: pg_hint_plan: +used hint: +BitmapScan(r4t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------ + Nested Loop + -> Index Scan using t1_i1 on t1 r4t1 + -> Bitmap Heap Scan on t1 r4t1_1 + Recheck Cond: (c1 = r4t1.c1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 = r4t1.c1) +(6 rows) + +-- No. S-2-3-7 +EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1; + QUERY PLAN +---------------------------------- + Hash Join + Hash Cond: (r4t1.c1 = r5t1.c1) + -> Seq Scan on t1 r4t1 + -> Hash + -> Seq Scan on t1 r5t1 +(5 rows) + +/*+SeqScan(r4t1)BitmapScan(r5t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1; +LOG: pg_hint_plan: +used hint: +SeqScan(r4t1) +BitmapScan(r5t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------ + Nested Loop + -> Seq Scan on t1 r4t1 + -> Bitmap Heap Scan on t1 r5t1 + Recheck Cond: (c1 = r4t1.c1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 = r4t1.c1) +(6 rows) + +---- +---- No. S-2-4 VALUES clause +---- +-- No. S-2-4-1 +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1; + QUERY PLAN +------------ + Result +(1 row) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(t1) +duplication hint: +error hint: + + QUERY PLAN +------------ + Result +(1 row) + +/*+SeqScan(*VALUES*)*/ +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +not used hint: +SeqScan(*VALUES*) +duplication hint: +error hint: + + QUERY PLAN +------------ + Result +(1 row) + +-- No. S-2-4-2 +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*" + -> Hash + -> Values Scan on "*VALUES*_1" +(5 rows) + +/*+SeqScan(t1 t2)*/ +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: +not used hint: +duplication hint: +error hint: +SeqScan(t1 t2) + + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1) + -> Values Scan on "*VALUES*" + -> Hash + -> Values Scan on "*VALUES*_1" +(5 rows) + +/*+SeqScan(*VALUES*)*/ +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: +SeqScan(*VALUES*) +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1) + -> Values Scan on "*VALUES*" + -> Hash + -> Values Scan on "*VALUES*_1" +(5 rows) + +---- +---- No. S-3-1 scan method hint +---- +-- No. S-3-1-1 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +-- No. S-3-1-2 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on t1 + Filter: (c1 = 1) +(2 rows) + +-- No. S-3-1-3 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+IndexScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-1-4 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +/*+IndexScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +LOG: pg_hint_plan: +used hint: +IndexScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 >= 1) +(2 rows) + +-- No. S-3-1-5 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; + QUERY PLAN +--------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c3 < 10) + -> Bitmap Index Scan on t1_i + Index Cond: (c3 < 10) +(4 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c3 < 10) + -> Bitmap Index Scan on t1_i + Index Cond: (c3 < 10) +(4 rows) + +-- No. S-3-1-6 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+BitmapScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +BitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 = 1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 = 1) +(4 rows) + +-- No. S-3-1-7 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; + QUERY PLAN +----------------------------------- + Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +/*+TidScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; +LOG: pg_hint_plan: +used hint: +TidScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- No. S-3-1-8 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)'); + QUERY PLAN +------------------------------------------------------------- + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) + Filter: (ctid = ANY ('{"(1,1)","(2,2)","(3,3)"}'::tid[])) +(3 rows) + +/*+TidScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)'); +LOG: pg_hint_plan: +used hint: +TidScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------- + Tid Scan on t1 + TID Cond: (ctid = ANY ('{"(1,1)","(2,2)","(3,3)"}'::tid[])) + Filter: (c1 = 1) +(3 rows) + +-- No. S-3-1-9 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +/*+NoSeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +LOG: pg_hint_plan: +used hint: +NoSeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 >= 1) +(2 rows) + +-- No. S-3-1-10 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+NoSeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +NoSeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-1-11 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+NoIndexScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +NoIndexScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c1 = 1) + -> Bitmap Index Scan on t1_i1 + Index Cond: (c1 = 1) +(4 rows) + +-- No. S-3-1-12 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +/*+NoIndexScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +LOG: pg_hint_plan: +used hint: +NoIndexScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +-- No. S-3-1-13 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; + QUERY PLAN +--------------------------------- + Bitmap Heap Scan on t1 + Recheck Cond: (c3 < 10) + -> Bitmap Index Scan on t1_i + Index Cond: (c3 < 10) +(4 rows) + +/*+NoBitmapScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; +LOG: pg_hint_plan: +used hint: +NoBitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c3 < 10) +(2 rows) + +-- No. S-3-1-14 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+NoBitmapScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +NoBitmapScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-1-15 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; + QUERY PLAN +----------------------------------- + Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +/*+NoTidScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; +LOG: pg_hint_plan: +used hint: +NoTidScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------- + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) +(3 rows) + +-- No. S-3-1-16 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+NoTidScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +NoTidScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-1-17 +EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +----------------------------------- + Index Only Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+IndexOnlyScan(t1)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Index Only Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-1-18 +EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +/*+IndexOnlyScan(t1)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1; +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Index Only Scan using t1_i1 on t1 + Index Cond: (c1 >= 1) +(2 rows) + +-- No. S-3-1-19 +EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +----------------------------------- + Index Only Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +/*+NoIndexOnlyScan(t1)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1; +LOG: pg_hint_plan: +used hint: +NoIndexOnlyScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-1-20 +EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +/*+NoIndexOnlyScan(t1)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1; +LOG: pg_hint_plan: +used hint: +NoIndexOnlyScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------- + Seq Scan on t1 + Filter: (c1 >= 1) +(2 rows) + +---- +---- No. S-3-3 index name specified +---- +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)'; + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c2 = 1) +(3 rows) + +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_i4 on ti1 + Index Cond: (c2 = 1) + Filter: (ctid = '(1,1)'::tid) +(3 rows) + +SET enable_indexscan TO off; +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)'; + QUERY PLAN +----------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c2 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_i4 + Index Cond: (c2 = 1) +(5 rows) + +RESET enable_tidscan; +RESET enable_indexscan; +EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1; + QUERY PLAN +--------------------- + Seq Scan on ti1 + Filter: (c2 >= 1) +(2 rows) + +-- No. S-3-3-1 +/*+IndexScan(ti1 ti1_i3)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)'; +LOG: available indexes for IndexScan(ti1): ti1_i3 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_i3) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------- + Index Scan using ti1_i3 on ti1 + Index Cond: (c2 = 1) + Filter: (ctid = '(1,1)'::tid) +(3 rows) + +-- No. S-3-3-2 +/*+IndexScan(ti1 ti1_i3 ti1_i2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)'; +LOG: available indexes for IndexScan(ti1): ti1_i3 ti1_i2 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_i3 ti1_i2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------- + Index Scan using ti1_i3 on ti1 + Index Cond: (c2 = 1) + Filter: (ctid = '(1,1)'::tid) +(3 rows) + +-- No. S-3-3-3 +/*+IndexScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)'; +LOG: available indexes for IndexScan(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------- + Index Scan using ti1_i4 on ti1 + Index Cond: (c2 = 1) + Filter: (ctid = '(1,1)'::tid) +(3 rows) + +-- No. S-3-3-4 +/*+BitmapScan(ti1 ti1_i3)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)'; +LOG: available indexes for BitmapScan(ti1): ti1_i3 +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_i3) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c2 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_i3 + Index Cond: (c2 = 1) +(5 rows) + +-- No. S-3-3-5 +/*+BitmapScan(ti1 ti1_i3 ti1_i2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)'; +LOG: available indexes for BitmapScan(ti1): ti1_i3 ti1_i2 +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_i3 ti1_i2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c2 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_i3 + Index Cond: (c2 = 1) +(5 rows) + +-- No. S-3-3-6 +/*+BitmapScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)'; +LOG: available indexes for BitmapScan(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1 +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c2 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_i4 + Index Cond: (c2 = 1) +(5 rows) + +-- No. S-3-3-7 +/*+IndexOnlyScan(ti1 ti1_i3)*/ +EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_i3 +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_i3) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------- + Index Only Scan using ti1_i3 on ti1 + Index Cond: (c2 >= 1) +(2 rows) + +-- No. S-3-3-8 +/*+IndexOnlyScan(ti1 ti1_i3 ti1_i2)*/ +EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_i3 ti1_i2 +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_i3 ti1_i2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------- + Index Only Scan using ti1_i2 on ti1 + Index Cond: (c2 >= 1) +(2 rows) + +-- No. S-3-3-9 +/*+IndexOnlyScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/ +EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1 +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------- + Index Only Scan using ti1_i1 on ti1 + Index Cond: (c2 >= 1) +(2 rows) + +---- +---- No. S-3-4 index type +---- +\d s1.ti1 + 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_btree" btree (c1) + "ti1_c2_key" UNIQUE CONSTRAINT, btree (c2) + "ti1_expr" btree ((c1 < 100)) + "ti1_gin" gin (c1) + "ti1_gist" gist (c1) + "ti1_hash" hash (c1) + "ti1_i1" btree (c2) + "ti1_i2" btree (c2, c4) + "ti1_i3" btree (c2, c4, c4) + "ti1_i4" btree (c2, c4, c4, c4) + "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 (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) +(3 rows) + +-- No. S-3-4-1 +/*+IndexScan(ti1 ti1_btree)*/ +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 IndexScan(ti1): ti1_btree +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_btree) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Index Scan using ti1_btree on ti1 + Index Cond: (c1 < 100) + 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 +/*+IndexScan(ti1 ti1_hash)*/ +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 IndexScan(ti1): ti1_hash +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_hash) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Index Scan using ti1_hash on ti1 + Index Cond: (c1 = 100) + 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 +/*+IndexScan(ti1 ti1_gist)*/ +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 IndexScan(ti1): ti1_gist +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_gist) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Index Scan using ti1_gist on ti1 + Index Cond: (c1 < 100) + 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 +/*+IndexScan(ti1 ti1_gin)*/ +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 IndexScan(ti1): ti1_gin +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_gin) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + 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 +/*+IndexScan(ti1 ti1_expr)*/ +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 IndexScan(ti1): ti1_expr +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_expr) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Index Scan using ti1_expr on ti1 + Index Cond: ((c1 < 100) = true) + 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 +/*+IndexScan(ti1 ti1_pred)*/ +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 IndexScan(ti1): ti1_pred +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_pred) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- + Index Scan using ti1_pred on ti1 + Index Cond: (lower(c4) = '1'::text) + Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) +(3 rows) + +-- No. S-3-4-7 +/*+IndexScan(ti1 ti1_uniq)*/ +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 IndexScan(ti1): ti1_uniq +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_uniq) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Index Scan using ti1_uniq on ti1 + Index Cond: (c1 < 100) + 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 +/*+IndexScan(ti1 ti1_multi)*/ +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 IndexScan(ti1): ti1_multi +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_multi) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- + Index Scan using ti1_multi on ti1 + Index Cond: ((c1 < 100) AND (c2 = 1)) + 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 +/*+IndexScan(ti1 ti1_ts)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)'; +LOG: available indexes for IndexScan(ti1): ti1_ts +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_ts) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + 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 +/*+IndexScan(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 IndexScan(ti1): ti1_pkey +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Index Scan using ti1_pkey on ti1 + Index Cond: (c1 < 100) + 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 +/*+IndexScan(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 IndexScan(ti1): ti1_c2_key +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_c2_key) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------ + Index Scan using ti1_c2_key on ti1 + Index Cond: (c2 = 1) + 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 +/*+BitmapScan(ti1 ti1_btree)*/ +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_btree +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_btree) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 < 100) + 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) + +-- No. S-3-4-13 +/*+BitmapScan(ti1 ti1_hash)*/ +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_hash +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_hash) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 = 100) + 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) + +-- No. S-3-4-14 +/*+BitmapScan(ti1 ti1_gist)*/ +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_gist +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_gist) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 < 100) + 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) + +-- No. S-3-4-15 +/*+BitmapScan(ti1 ti1_gin)*/ +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_gin +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_gin) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 < 100) + 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) + +-- No. S-3-4-16 +/*+BitmapScan(ti1 ti1_expr)*/ +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_expr +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_expr) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + 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) + +-- No. S-3-4-17 +/*+BitmapScan(ti1 ti1_pred)*/ +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_pred +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_pred) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (lower(c4) = '1'::text) + Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) + -> Bitmap Index Scan on ti1_pred + Index Cond: (lower(c4) = '1'::text) +(5 rows) + +-- No. S-3-4-18 +/*+BitmapScan(ti1 ti1_uniq)*/ +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_uniq +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_uniq) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 < 100) + 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) + +-- No. S-3-4-19 +/*+BitmapScan(ti1 ti1_multi)*/ +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_multi +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_multi) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: ((c1 < 100) AND (c2 = 1)) + 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) + +-- No. S-3-4-20 +/*+BitmapScan(ti1 ti1_ts)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)'; +LOG: available indexes for BitmapScan(ti1): ti1_ts +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_ts) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) + Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text)) + -> Bitmap Index Scan on ti1_ts + Index Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) +(5 rows) + +-- 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 +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 < 100) + 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-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 +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_c2_key) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------ + Bitmap Heap Scan on ti1 + Recheck Cond: (c2 = 1) + 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) + +-- No. S-3-4-23 +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on ti1 + Filter: (c1 >= 1) +(2 rows) + +/*+IndexOnlyScan(ti1 ti1_btree)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_btree +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_btree) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------- + Index Only Scan using ti1_btree on ti1 + Index Cond: (c1 >= 1) +(2 rows) + +-- No. S-3-4-24 +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 + Index Cond: (c1 = 1) +(2 rows) + +/*+IndexOnlyScan(ti1 ti1_hash)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_hash +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_hash) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-4-25 +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 1; + QUERY PLAN +--------------------------------------- + Index Only Scan using ti1_uniq on ti1 + Index Cond: (c1 < 1) +(2 rows) + +/*+IndexOnlyScan(ti1 ti1_gist)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_gist +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_gist) +not used hint: +duplication hint: +error hint: + + 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 Scan using ti1_hash on ti1 + Index Cond: (c1 = 1) +(2 rows) + +/*+IndexOnlyScan(ti1 ti1_gin)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_gin +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_gin) +not used hint: +duplication hint: +error hint: + + 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; + QUERY PLAN +--------------------------------------- + Index Only Scan using ti1_uniq on ti1 + Index Cond: (c1 < 100) +(2 rows) + +/*+IndexOnlyScan(ti1 ti1_expr)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 100; +LOG: available indexes for IndexOnlyScan(ti1): ti1_expr +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_expr) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Index Scan using ti1_expr on ti1 + Index Cond: ((c1 < 100) = true) +(2 rows) + +-- No. S-3-4-28 +EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1'; + QUERY PLAN +------------------------------------ + Seq Scan on ti1 + Filter: (lower(c4) >= '1'::text) +(2 rows) + +/*+IndexOnlyScan(ti1 ti1_pred)*/ +EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1'; +LOG: available indexes for IndexOnlyScan(ti1): ti1_pred +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_pred) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------- + Index Scan using ti1_pred on ti1 + Index Cond: (lower(c4) >= '1'::text) +(2 rows) + +-- No. S-3-4-29 +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on ti1 + Filter: (c1 >= 1) +(2 rows) + +/*+IndexOnlyScan(ti1 ti1_uniq)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_uniq +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_uniq) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------- + Index Only Scan using ti1_uniq on ti1 + Index Cond: (c1 >= 1) +(2 rows) + +-- No. S-3-4-30 +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; + QUERY PLAN +--------------------- + Seq Scan on ti1 + Filter: (c1 >= 1) +(2 rows) + +/*+IndexOnlyScan(ti1 ti1_multi)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_multi +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_multi) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------- + Index Only Scan using ti1_multi on ti1 + Index Cond: (c1 >= 1) +(2 rows) + +-- No. S-3-4-31 +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE to_tsvector('english', c4) @@ 'a & b'; + 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) + +/*+IndexOnlyScan(ti1 ti1_ts)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE to_tsvector('english', c4) @@ 'a & b'; +LOG: available indexes for IndexOnlyScan(ti1): ti1_ts +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_ts) +not used hint: +duplication hint: +error hint: + + 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; + QUERY PLAN +--------------------- + Seq Scan on ti1 + Filter: (c1 >= 1) +(2 rows) + +/*+IndexOnlyScan(ti1 ti1_pkey)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_pkey +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------- + Index Only Scan using ti1_pkey on ti1 + Index Cond: (c1 >= 1) +(2 rows) + +-- No. S-3-4-33 +EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 >= 1; + QUERY PLAN +--------------------- + Seq Scan on ti1 + Filter: (c2 >= 1) +(2 rows) + +/*+IndexOnlyScan(ti1 ti1_c2_key)*/ +EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 >= 1; +LOG: available indexes for IndexOnlyScan(ti1): ti1_c2_key +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_c2_key) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------------- + Index Only Scan using ti1_c2_key on ti1 + Index Cond: (c2 >= 1) +(2 rows) + +---- +---- No. S-3-5 not used index +---- +-- No. S-3-5-1 +\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: +IndexScan(ti1 ti1_pred) +not used hint: +duplication hint: +error hint: + +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +---------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c1 = 100) + +-- No. S-3-5-2 +\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: +BitmapScan(ti1 ti1_pred) +not used hint: +duplication hint: +error hint: + +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +---------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c1 = 100) + +-- No. S-3-5-3 +\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: +IndexOnlyScan(ti1 ti1_pred) +not used hint: +duplication hint: +error hint: + +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +---------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c1 = 100) + +-- No. S-3-5-4 +\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: +IndexScan(ti1 not_exist) +not used hint: +duplication hint: +error hint: + +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +---------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c1 = 100) + +-- No. S-3-5-5 +\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: +BitmapScan(ti1 not_exist) +not used hint: +duplication hint: +error hint: + +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +---------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c1 = 100) + +-- No. S-3-5-6 +\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: +IndexOnlyScan(ti1 not_exist) +not used hint: +duplication hint: +error hint: + +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +---------------- + Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c1 = 100) + +-- No. S-3-5-7 +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +------------------------------ + Index Scan using t1_i1 on t1 + Index Cond: (c1 = 1) +(2 rows) + +\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) +not used hint: +duplication hint: +error hint: + +\o +\! sql/maskout.sh results/ut-S.tmpout + QUERY PLAN +---------------- + Seq Scan on t1 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c1 = 1) + +---- +---- No. S-3-6 query structure +---- +EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)'; + QUERY PLAN +----------------------------------------------- + Hash Join + Hash Cond: (t2.c1 = t1.c1) + -> Seq Scan on t2 + -> Hash + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) +(6 rows) + +-- No. S-3-6-1 +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE c1 = 100; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------- + Seq Scan on t1 + Filter: (c1 = 100) +(2 rows) + +-- No. S-3-6-2 +/*+SeqScan(t1)BitmapScan(t2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)'; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +BitmapScan(t2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------- + Nested Loop + -> Seq Scan on t1 + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t2 + Recheck Cond: (c1 = t1.c1) + -> Bitmap Index Scan on t2_i1 + Index Cond: (c1 = t1.c1) +(7 rows) + +-- No. S-3-6-3 +/*+SeqScan(t1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)'; +LOG: pg_hint_plan: +used hint: +SeqScan(t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------- + Hash Join + Hash Cond: (t2.c1 = t1.c1) + -> Seq Scan on t2 + -> Hash + -> Seq Scan on t1 + Filter: (ctid = '(1,1)'::tid) +(6 rows) + +---- +---- No. S-3-7 number of tables in a query block +---- +-- No. S-3-7-1 +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.c1 = 1) +SELECT max(b3t1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1 + ) FROM s1.t1 b3t1 WHERE b3t1.c1 = ( +SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1); + QUERY PLAN +--------------------------------------------------------------------- + Aggregate + InitPlan 2 (returns $1) + -> Result + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan using t1_i1 on t1 b2t1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) + InitPlan 4 (returns $3) + -> Result + InitPlan 3 (returns $2) + -> Limit + -> Index Only Scan using t1_i1 on t1 b4t1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) + -> 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) +*/ +EXPLAIN (COSTS false) +WITH c1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.c1 = 1) +SELECT max(b3t1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1 + ) FROM s1.t1 b3t1 WHERE b3t1.c1 = ( +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: pg_hint_plan: +used hint: +IndexScan(b2t1 t1_pkey) +BitmapScan(b3t1 t1_pkey) +TidScan(b4t1) +not used hint: +SeqScan(b1t1) +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------------- + Aggregate + InitPlan 2 (returns $1) + -> Result + InitPlan 1 (returns $0) + -> Limit + -> Index Scan using t1_pkey on t1 b2t1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) + InitPlan 4 (returns $3) + -> Result + InitPlan 3 (returns $2) + -> Limit + -> Seq Scan on t1 b4t1 + Filter: ((c1 IS NOT NULL) AND (c1 = 1)) + -> 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) +WITH cte1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1) +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 +---------------------------------------------------------------- + Aggregate + 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 2 (returns $1) + -> Aggregate + -> Nested Loop + -> Index Only Scan using t1_i1 on t1 b4t1 + Index Cond: (c1 = 1) + -> Seq Scan on t2 b4t2 + Filter: (c1 = 1) + -> Nested Loop + -> Nested Loop + -> Index Only Scan using t1_i1 on t1 b3t1 + Index Cond: (c1 = $1) + -> Seq Scan on t2 b3t2 + 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) +*/ +EXPLAIN (COSTS false) +WITH cte1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1) +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); +LOG: available indexes for IndexScan(b2t1): t1_pkey +LOG: available indexes for BitmapScan(b4t2): t2_pkey +LOG: available indexes for BitmapScan(b3t1): t1_pkey +LOG: available indexes for IndexScan(b3t2): t2_pkey +LOG: pg_hint_plan: +used hint: +SeqScan(b1t1) +TidScan(b1t2) +IndexScan(b2t1 t1_pkey) +SeqScan(b2t2) +BitmapScan(b3t1 t1_pkey) +IndexScan(b3t2 t2_pkey) +TidScan(b4t1) +BitmapScan(b4t2 t2_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------- + Aggregate + 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 2 (returns $1) + -> Aggregate + -> Nested Loop + -> Seq Scan on t1 b4t1 + Filter: (c1 = 1) + -> Bitmap Heap Scan on t2 b4t2 + Recheck Cond: (c1 = 1) + -> Bitmap Index Scan on t2_pkey + Index Cond: (c1 = 1) + -> Nested Loop + -> Nested Loop + -> Bitmap Heap Scan on t1 b3t1 + Recheck Cond: (c1 = $1) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = $1) + -> Index Scan using t2_pkey on t2 b3t2 + 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) +WITH cte1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1) +SELECT max(b3t1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1 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 WHERE b4t1.c1 = 1); + QUERY PLAN +--------------------------------------------------------------------- + Aggregate + InitPlan 2 (returns $1) + -> Result + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan using t1_i1 on t1 b2t1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) + InitPlan 4 (returns $3) + -> Result + 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 = $3) + -> Seq Scan on t2 b3t2 + 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) +*/ +EXPLAIN (COSTS false) +WITH cte1 (c1) AS ( +SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1) +SELECT max(b3t1.c1), ( +SELECT max(b2t1.c1) FROM s1.t1 b2t1 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 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 IndexScan(b3t2): t2_pkey +LOG: pg_hint_plan: +used hint: +SeqScan(b1t1) +TidScan(b1t2) +IndexScan(b2t1 t1_pkey) +BitmapScan(b3t1 t1_pkey) +IndexScan(b3t2 t2_pkey) +TidScan(b4t1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------------------------- + Aggregate + InitPlan 2 (returns $1) + -> Result + InitPlan 1 (returns $0) + -> Limit + -> Index Scan using t1_pkey on t1 b2t1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) + InitPlan 4 (returns $3) + -> Result + 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 = $3) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = $3) + -> Index Scan using t2_pkey on t2 b3t2 + 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/update type +---- +-- No. S-3-8-1 +EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1; + QUERY PLAN +-------------------- + Seq Scan on p1 + Filter: (c1 = 1) +(2 rows) + +/*+IndexScan(p1)*/ +EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(p1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------- + Index Scan using p1_i on p1 + Index Cond: (c1 = 1) +(2 rows) + +-- No. S-3-8-2 +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; + QUERY PLAN +----------------------------- + Append + -> Seq Scan on p1 p1_1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1_2 + Filter: (c1 = 1) +(5 rows) + +/*+IndexScan(p1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(p1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------------- + Append + -> Index Scan using p1_i on p1 p1_1 + Index Cond: (c1 = 1) + -> 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 p1_1 + Update on p1c1 p1_2 + -> Append + -> Seq Scan on p1 p1_1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1_2 + Filter: (c1 = 1) +(8 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 p1_1 + Update on p1c1 p1_2 + -> Append + -> Index Scan using p1_i on p1 p1_1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1_2 + Index Cond: (c1 = 1) +(8 rows) + +/*+IndexScan(p1 p1_pkey)*/ +EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; +LOG: available indexes for IndexScan(p1): p1_pkey +LOG: available indexes for IndexScan(p1c1): p1c1_pkey +LOG: pg_hint_plan: +used hint: +IndexScan(p1 p1_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------------------------- + Update on p1 + Update on p1 p1_1 + Update on p1c1 p1_2 + -> Append + -> Index Scan using p1_pkey on p1 p1_1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_pkey on p1c1 p1_2 + Index Cond: (c1 = 1) +(8 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 +----------------------------- + Append + -> Seq Scan on p1 p1_1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1_2 + Filter: (c1 = 1) +(5 rows) + +/*+IndexScan(p1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(p1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------------- + Append + -> Index Scan using p1_i on p1 p1_1 + Index Cond: (c1 = 1) + -> 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 +------------------------------- + Append + -> Seq Scan on p2 p2_1 + Filter: (c1 = 1) + -> Seq Scan on p2c1 p2_2 + Filter: (c1 = 1) + -> Seq Scan on p2c1c1 p2_3 + Filter: (c1 = 1) +(7 rows) + +/*+IndexScan(p2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(p2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------ + Append + -> Index Scan using p2_i on p2 p2_1 + Index Cond: (c1 = 1) + -> Index Scan using p2c1_i on p2c1 p2_2 + Index Cond: (c1 = 1) + -> Index Scan using p2c1c1_i on p2c1c1 p2_3 + Index Cond: (c1 = 1) +(7 rows) + +---- +---- No. S-3-10 inheritance table specified table +---- +EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1; + QUERY PLAN +------------------------------- + Append + -> Seq Scan on p2 p2_1 + Filter: (c1 = 1) + -> Seq Scan on p2c1 p2_2 + Filter: (c1 = 1) + -> Seq Scan on p2c1c1 p2_3 + Filter: (c1 = 1) +(7 rows) + +-- No. S-3-10-1 +/*+IndexScan(p2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(p2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------ + Append + -> Index Scan using p2_i on p2 p2_1 + Index Cond: (c1 = 1) + -> Index Scan using p2c1_i on p2c1 p2_2 + Index Cond: (c1 = 1) + -> Index Scan using p2c1c1_i on p2c1c1 p2_3 + Index Cond: (c1 = 1) +(7 rows) + +-- No. S-3-10-2 +/*+IndexScan(p2c1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1; +LOG: pg_hint_plan: +used hint: +IndexScan(p2c1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------------- + Append + -> Seq Scan on p2 p2_1 + Filter: (c1 = 1) + -> 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; +\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 p1_2 (cost=xxx..xxx rows=3 width=xxx) + Filter: ((c1 < 10) AND ((c2 * 2) < 100)) + +\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: pg_hint_plan: +used hint: +IndexScan(p1 p1_parent) +not used hint: +duplication hint: +error hint: + +\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 p1_2 (cost=xxx..xxx rows=3 width=xxx) + Filter: ((c2 * 2) < 100) + +-- No. S-3-10-4 +\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): +LOG: available indexes for IndexScan(p1c3): +LOG: pg_hint_plan: +used hint: +IndexScan(p1 p1_i2) +not used hint: +duplication hint: +error hint: + +\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 p1_2 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c2 = 1) + -> Seq Scan on p1c2 p1_3 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c2 = 1) + -> Seq Scan on p1c3 p1_4 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c2 = 1) + +-- No. S-3-10-5 +\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(p2c1c1): +LOG: pg_hint_plan: +used hint: +IndexScan(p2 p2c1_pkey) +not used hint: +duplication hint: +error hint: + +\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 p2_2 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = 1) + -> Seq Scan on p2c1c1 p2_3 (cost={inf}..{inf} rows=1 width=xxx) + Filter: (c1 = 1) + +---- +---- No. S-3-12 specified same table +---- +-- No. S-3-12-1 +/*+IndexScan(ti1) BitmapScan(ti1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; +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: +BitmapScan(ti1) +not used hint: +duplication hint: +IndexScan(ti1) +error hint: + + QUERY PLAN +------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_hash + Index Cond: (c1 = 1) +(5 rows) + +-- 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: 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: +used hint: +BitmapScan(ti1 ti1_btree) +not used hint: +duplication hint: +IndexScan(ti1 ti1_pkey) +error hint: + + QUERY PLAN +-------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_btree + Index Cond: (c1 = 1) +(5 rows) + +-- 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: pg_hint_plan: hint syntax error at or near "BitmapScan(ti1) IndexScan(ti1) BitmapScan(ti1)" +DETAIL: Conflict scan method hint. +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: +BitmapScan(ti1) +not used hint: +duplication hint: +BitmapScan(ti1) +IndexScan(ti1) +error hint: + + QUERY PLAN +------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_hash + Index Cond: (c1 = 1) +(5 rows) + +-- 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: 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: 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: +used hint: +BitmapScan(ti1 ti1_btree) +not used hint: +duplication hint: +BitmapScan(ti1 ti1_hash) +IndexScan(ti1 ti1_pkey) +error hint: + + QUERY PLAN +-------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_btree + Index Cond: (c1 = 1) +(5 rows) + +---- +---- No. S-3-13 message output of hint +---- +-- 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: +used hint: +SeqScan(ti1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------ + Seq Scan on ti1 + Filter: ((c1 = 1) AND (ctid = '(1,1)'::tid)) +(2 rows) + +-- No. S-3-13-2 +/*+SeqScan(ti1 ti1_pkey)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; +INFO: pg_hint_plan: hint syntax error at or near "" +DETAIL: SeqScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +SeqScan(ti1 ti1_pkey) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: pg_hint_plan: hint syntax error at or near "" +DETAIL: SeqScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +SeqScan(ti1 ti1_pkey ti1_btree) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: +used hint: +IndexScan(ti1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 + Index Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) +(3 rows) + +-- 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 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Index Scan using ti1_pkey on ti1 + Index Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) +(3 rows) + +-- 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 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_pkey ti1_btree) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Index Scan using ti1_btree on ti1 + Index Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) +(3 rows) + +-- 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: +used hint: +BitmapScan(ti1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_hash + Index Cond: (c1 = 1) +(5 rows) + +-- 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 +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_pkey + Index Cond: (c1 = 1) +(5 rows) + +-- 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 +LOG: pg_hint_plan: +used hint: +BitmapScan(ti1 ti1_pkey ti1_btree) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------- + Bitmap Heap Scan on ti1 + Recheck Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on ti1_btree + Index Cond: (c1 = 1) +(5 rows) + +-- 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: +used hint: +TidScan(ti1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- No. S-3-13-11 +/*+TidScan(ti1 ti1_pkey)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; +INFO: pg_hint_plan: hint syntax error at or near "" +DETAIL: TidScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +TidScan(ti1 ti1_pkey) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: pg_hint_plan: hint syntax error at or near "" +DETAIL: TidScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +TidScan(ti1 ti1_pkey ti1_btree) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: +used hint: +NoSeqScan(ti1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- No. S-3-13-14 +/*+NoSeqScan(ti1 ti1_pkey)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; +INFO: pg_hint_plan: hint syntax error at or near "" +DETAIL: NoSeqScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +NoSeqScan(ti1 ti1_pkey) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: pg_hint_plan: hint syntax error at or near "" +DETAIL: NoSeqScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +NoSeqScan(ti1 ti1_pkey ti1_btree) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: +used hint: +NoIndexScan(ti1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- No. S-3-13-17 +/*+NoIndexScan(ti1 ti1_pkey)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; +INFO: pg_hint_plan: hint syntax error at or near "" +DETAIL: NoIndexScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +NoIndexScan(ti1 ti1_pkey) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: pg_hint_plan: hint syntax error at or near "" +DETAIL: NoIndexScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +NoIndexScan(ti1 ti1_pkey ti1_btree) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: +used hint: +NoBitmapScan(ti1) not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- No. S-3-13-20 +/*+NoBitmapScan(ti1 ti1_pkey)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; +INFO: pg_hint_plan: hint syntax error at or near "" +DETAIL: NoBitmapScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +NoBitmapScan(ti1 ti1_pkey) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: pg_hint_plan: hint syntax error at or near "" +DETAIL: NoBitmapScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +NoBitmapScan(ti1 ti1_pkey ti1_btree) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: +used hint: +NoTidScan(ti1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 + Index Cond: (c1 = 1) + Filter: (ctid = '(1,1)'::tid) +(3 rows) + +-- No. S-3-13-23 +/*+NoTidScan(ti1 ti1_pkey)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)'; +INFO: pg_hint_plan: hint syntax error at or near "" +DETAIL: NoTidScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +NoTidScan(ti1 ti1_pkey) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- 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: pg_hint_plan: hint syntax error at or near "" +DETAIL: NoTidScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +NoTidScan(ti1 ti1_pkey ti1_btree) + + QUERY PLAN +----------------------------------- + Tid Scan on ti1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: (c1 = 1) +(3 rows) + +-- No. S-3-13-25 +/*+IndexOnlyScan(ti1)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1; +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------- + Index Only Scan using ti1_uniq on ti1 Index Cond: (c1 >= 1) (2 rows) --- No. S-3-1-10 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 +-- 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 +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------- + Index Only Scan using ti1_pkey on ti1 + Index Cond: (c1 >= 1) +(2 rows) + +-- 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 +LOG: pg_hint_plan: +used hint: +IndexOnlyScan(ti1 ti1_pkey ti1_btree) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------------- + Index Only Scan using ti1_btree on ti1 + Index Cond: (c1 >= 1) +(2 rows) + +-- No. S-3-13-28 +/*+NoIndexOnlyScan(ti1)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; +LOG: pg_hint_plan: +used hint: +NoIndexOnlyScan(ti1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 Index Cond: (c1 = 1) (2 rows) -/*+NoSeqScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +-- No. S-3-13-29 +/*+NoIndexOnlyScan(ti1 ti1_pkey)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; +INFO: pg_hint_plan: hint syntax error at or near "" +DETAIL: NoIndexOnlyScan hint accepts only one relation. LOG: pg_hint_plan: used hint: -NoSeqScan(t1) not used hint: duplication hint: error hint: +NoIndexOnlyScan(ti1 ti1_pkey) - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 Index Cond: (c1 = 1) (2 rows) --- No. S-3-1-11 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +-- No. S-3-13-30 +/*+NoIndexOnlyScan(ti1 ti1_pkey ti1_btree)*/ +EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; +INFO: pg_hint_plan: hint syntax error at or near "" +DETAIL: NoIndexOnlyScan hint accepts only one relation. +LOG: pg_hint_plan: +used hint: +not used hint: +duplication hint: +error hint: +NoIndexOnlyScan(ti1 ti1_pkey ti1_btree) + + QUERY PLAN +---------------------------------- + Index Scan using ti1_hash on ti1 + Index Cond: (c1 = 1) +(2 rows) + +---- +---- No. S-3-14 regular expression +---- +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; QUERY PLAN -------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) + Index Scan using ti1_i4 on ti1 + Index Cond: (c2 = 1) (2 rows) -/*+NoIndexScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 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 LOG: pg_hint_plan: used hint: -NoIndexScan(t1) +IndexScanRegexp(ti1 ti1_.*_key) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------ - Bitmap Heap Scan on t1 - Recheck Cond: (c1 = 1) - -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = 1) -(4 rows) + Index Scan using ti1_c2_key on ti1 + Index Cond: (c2 = 1) +(2 rows) --- No. S-3-1-12 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 >= 1) +-- 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 +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(ti1 ti1_i.) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using ti1_i4 on ti1 + Index Cond: (c2 = 1) (2 rows) -/*+NoIndexScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; +-- 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): LOG: pg_hint_plan: used hint: -NoIndexScan(t1) +IndexScanRegexp(ti1 no.*_exist) not used hint: duplication hint: error hint: - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c1 >= 1) + QUERY PLAN +-------------------- + Seq Scan on ti1 + Filter: (c2 = 1) (2 rows) --- No. S-3-1-13 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; - QUERY PLAN ---------------------------------- - Bitmap Heap Scan on t1 - Recheck Cond: (c3 < 10) - -> Bitmap Index Scan on t1_i - Index Cond: (c3 < 10) -(4 rows) +-- 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: pg_hint_plan: +used hint: +IndexScanRegexp(p1 .*pkey) +not used hint: +duplication hint: +error hint: -/*+NoBitmapScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10; + QUERY PLAN +----------------------------------------------- + Append + -> Index Scan using p1_pkey on p1 p1_1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_pkey on p1c1 p1_2 + Index Cond: (c1 = 1) +(5 rows) + +-- 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: pg_hint_plan: used hint: -NoBitmapScan(t1) +IndexScanRegexp(p1 p1.*i) not used hint: duplication hint: error hint: - QUERY PLAN ---------------------- - Seq Scan on t1 - Filter: (c3 < 10) -(2 rows) + QUERY PLAN +-------------------------------------------- + Append + -> Index Scan using p1_i on p1 p1_1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1_2 + Index Cond: (c1 = 1) +(5 rows) --- No. S-3-1-14 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; - QUERY PLAN --------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) -(2 rows) +-- 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: pg_hint_plan: +used hint: +IndexScanRegexp(p1 no.*_exist) +not used hint: +duplication hint: +error hint: -/*+NoBitmapScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; + QUERY PLAN +----------------------------- + Append + -> Seq Scan on p1 p1_1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1_2 + Filter: (c1 = 1) +(5 rows) + +---- +---- No. S-3-15 message output of index candidate +---- +-- 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 LOG: pg_hint_plan: used hint: -NoBitmapScan(t1) +IndexScan(ti1 ti1_i1) not used hint: duplication hint: error hint: QUERY PLAN -------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) + Index Scan using ti1_i1 on ti1 + Index Cond: (c2 = 1) (2 rows) --- No. S-3-1-15 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; - QUERY PLAN ------------------------------------ - Tid Scan on t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 = 1) -(3 rows) - -/*+NoTidScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)'; +-- 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): LOG: pg_hint_plan: used hint: -NoTidScan(t1) +IndexScan(ti1 not_exist) not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) - Filter: (ctid = '(1,1)'::tid) -(3 rows) + QUERY PLAN +-------------------- + Seq Scan on ti1 + Filter: (c2 = 1) +(2 rows) + +-- 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 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_i1 ti1_i2) +not used hint: +duplication hint: +error hint: --- No. S-3-1-16 -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; QUERY PLAN -------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) + Index Scan using ti1_i2 on ti1 + Index Cond: (c2 = 1) (2 rows) -/*+NoTidScan(t1)*/ -EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; +-- 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 LOG: pg_hint_plan: used hint: -NoTidScan(t1) +IndexScan(ti1 ti1_i1 not_exist) not used hint: duplication hint: error hint: QUERY PLAN -------------------------------- - Index Scan using t1_pkey on t1 - Index Cond: (c1 = 1) + Index Scan using ti1_i1 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- 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): +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 not_exist1 not_exist2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on ti1 + 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