LOAD 'pg_hint_plan'; -- We cannot do ALTER USER current_user SET ... DELETE FROM pg_db_role_setting WHERE setrole = (SELECT oid FROM pg_roles WHERE rolname = current_user); INSERT INTO pg_db_role_setting (SELECT 0, (SELECT oid FROM pg_roles WHERE rolname = current_user), '{client_min_messages=log,pg_hint_plan.debug_print=on}'); ALTER SYSTEM SET session_preload_libraries TO 'pg_hint_plan'; SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) SET pg_hint_plan.enable_hint TO on; SET pg_hint_plan.debug_print TO on; SET client_min_messages TO LOG; SET search_path TO public; SET max_parallel_workers_per_gather TO 0; EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1; QUERY PLAN --------------------- Seq Scan on t1 Filter: (c1 >= 1) (2 rows) 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) 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) 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-1-1 specified pattern of the object name ---- -- No. S-1-1-1 /*+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-1-1-2 /*+SeqScan(t1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1 t_1 WHERE t_1.c1 = 1; LOG: pg_hint_plan: used hint: not used hint: SeqScan(t1) duplication hint: error hint: QUERY PLAN ---------------------------------- Index Scan using t1_i1 on t1 t_1 Index Cond: (c1 = 1) (2 rows) -- No. S-1-1-3 /*+SeqScan(t_1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1 t_1 WHERE t_1.c1 = 1; LOG: pg_hint_plan: used hint: SeqScan(t_1) not used hint: duplication hint: error hint: QUERY PLAN -------------------- Seq Scan on t1 t_1 Filter: (c1 = 1) (2 rows) ---- ---- No. S-1-2 specified schema name in the hint option ---- -- No. S-1-2-1 /*+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-1-2-2 /*+SeqScan(s1.t1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; LOG: pg_hint_plan: used hint: not used hint: SeqScan(s1.t1) duplication hint: error hint: QUERY PLAN ------------------------------ Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) (2 rows) ---- ---- No. S-1-3 table doesn't exist in the hint option ---- -- No. S-1-3-1 /*+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-1-3-2 /*+SeqScan(t2)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1; LOG: pg_hint_plan: used hint: not used hint: SeqScan(t2) duplication hint: error hint: QUERY PLAN ------------------------------ Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) (2 rows) ---- ---- No. S-1-4 conflict table name ---- -- 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 ------------------------------------ Nested Loop -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) -> Seq Scan on t2 Filter: (c1 = 1) (5 rows) /*+SeqScan(t1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = 1 AND t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: SeqScan(t1) not used hint: duplication hint: error hint: QUERY PLAN -------------------------- Nested Loop -> Seq Scan on t1 Filter: (c1 = 1) -> Seq Scan on t2 Filter: (c1 = 1) (5 rows) -- 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 ------------------------------------ Nested Loop -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) -> Seq Scan on t1 t1_1 Filter: (c1 = 1) (5 rows) /*+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: BitmapScan(t1) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------ Nested Loop -> 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) /*+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: BitmapScan(t1) not used hint: duplication hint: error hint: QUERY PLAN ---------------------------------------- Nested Loop -> 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) (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; LOG: pg_hint_plan: used hint: BitmapScan(s2t1) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------ Nested Loop -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) -> Bitmap Heap Scan on t1 s2t1 Recheck Cond: (c1 = 1) -> Bitmap Index Scan on t1_pkey Index Cond: (c1 = 1) (7 rows) -- No. S-1-4-3 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 Only Scan using t1_i1 on t1 Index Cond: (c1 = 1) InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit -> Index Only Scan using t1_i1 on t1 t1_1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) (8 rows) /*+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: BitmapScan(t1) not used hint: duplication hint: error hint: QUERY PLAN --------------------------------------------------------------------------- Bitmap Heap Scan on t1 Recheck Cond: (c1 = 1) InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit -> 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) /*+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: BitmapScan(t11) not used hint: duplication hint: error hint: 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 -> 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) /*+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: BitmapScan(t12) not used hint: duplication hint: error hint: QUERY PLAN --------------------------------------------------------------------- Bitmap Heap Scan on t1 t12 Recheck Cond: (c1 = 1) InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit -> Index Only Scan using t1_i1 on t1 t11 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -> Bitmap Index Scan on t1_i1 Index Cond: (c1 = 1) (10 rows) ---- ---- No. S-1-5 object type for the hint ---- -- No. S-1-5-1 /*+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-1-5-2 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1; QUERY PLAN -------------------------- Append -> Seq Scan on p1 Filter: (c1 = 1) -> Seq Scan on p1c1 Filter: (c1 = 1) (5 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.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 Index Cond: (c1 = 1) -> Index Scan using p1c1_i on p1c1 Index Cond: (c1 = 1) (5 rows) -- No. S-1-5-3 EXPLAIN (COSTS false) SELECT * FROM s1.ul1 WHERE ul1.c1 = 1; QUERY PLAN ---------------------------------- Index Scan using ul1_pkey on ul1 Index Cond: (c1 = 1) (2 rows) /*+SeqScan(ul1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.ul1 WHERE ul1.c1 = 1; LOG: pg_hint_plan: used hint: SeqScan(ul1) not used hint: duplication hint: error hint: QUERY PLAN -------------------- Seq Scan on ul1 Filter: (c1 = 1) (2 rows) -- No. S-1-5-4 CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL); EXPLAIN (COSTS false) SELECT * FROM tm1 WHERE tm1.c1 = 1; QUERY PLAN ---------------------------------- Index Scan using tm1_pkey on tm1 Index Cond: (c1 = 1) (2 rows) /*+SeqScan(tm1)*/ EXPLAIN (COSTS false) SELECT * FROM tm1 WHERE tm1.c1 = 1; LOG: pg_hint_plan: used hint: SeqScan(tm1) not used hint: duplication hint: error hint: QUERY PLAN -------------------- Seq Scan on tm1 Filter: (c1 = 1) (2 rows) -- No. S-1-5-5 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) (2 rows) /*+SeqScan(pg_class)*/ EXPLAIN (COSTS false) SELECT * FROM pg_catalog.pg_class WHERE oid = 1; LOG: pg_hint_plan: used hint: SeqScan(pg_class) not used hint: duplication hint: error hint: QUERY PLAN ---------------------------- Seq Scan on pg_class Filter: (oid = '1'::oid) (2 rows) -- No. S-1-5-6 -- refer ut-fdw.sql -- No. S-1-5-7 EXPLAIN (COSTS false) SELECT * FROM s1.f1() AS ft1 WHERE ft1.c1 = 1; QUERY PLAN ------------------------- Function Scan on f1 ft1 Filter: (c1 = 1) (2 rows) /*+SeqScan(ft1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.f1() AS ft1 WHERE ft1.c1 = 1; LOG: pg_hint_plan: used hint: not used hint: SeqScan(ft1) duplication hint: error hint: QUERY PLAN ------------------------- Function Scan on f1 ft1 Filter: (c1 = 1) (2 rows) -- No. S-1-5-8 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS val1 (c1, c2, c3, c4) WHERE val1.c1 = 1; QUERY PLAN --------------------------- Values Scan on "*VALUES*" Filter: (column1 = 1) (2 rows) /*+SeqScan(val1)*/ EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS val1 (c1, c2, c3, c4) WHERE val1.c1 = 1; LOG: pg_hint_plan: used hint: not used hint: SeqScan(val1) duplication hint: error hint: QUERY PLAN --------------------------- Values Scan on "*VALUES*" Filter: (column1 = 1) (2 rows) /*+SeqScan(*VALUES*)*/ EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS val1 (c1, c2, c3, c4) WHERE val1.c1 = 1; LOG: pg_hint_plan: used hint: not used hint: SeqScan(*VALUES*) duplication hint: error hint: QUERY PLAN --------------------------- Values Scan on "*VALUES*" Filter: (column1 = 1) (2 rows) -- 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 --------------------------------------------------------------------- Nested Loop CTE c1 -> Result InitPlan 1 (returns $0) -> Limit -> Index Only Scan using t1_i1 on t1 t1_1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) -> CTE Scan on c1 Filter: (c1 = 1) (11 rows) /*+SeqScan(c1)*/ 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; LOG: pg_hint_plan: used hint: not used hint: SeqScan(c1) duplication hint: error hint: QUERY PLAN --------------------------------------------------------------------- Nested Loop CTE c1 -> Result InitPlan 1 (returns $0) -> Limit -> Index Only Scan using t1_i1 on t1 t1_1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) -> CTE Scan on c1 Filter: (c1 = 1) (11 rows) -- No. S-1-5-10 EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1; QUERY PLAN ----------------------------------- Index Scan using t1_i1 on t1 v1t1 Index Cond: (c1 = 1) (2 rows) /*+SeqScan(v1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1; LOG: pg_hint_plan: used hint: not used hint: SeqScan(v1) duplication hint: error hint: 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_i1 on t1 Index Cond: (c1 = 1) (2 rows) /*+SeqScan(s1)*/ EXPLAIN (COSTS false) SELECT * FROM (SELECT * FROM s1.t1 WHERE t1.c1 = 1) AS s1 WHERE s1.c1 = 1; LOG: pg_hint_plan: used hint: not used hint: SeqScan(s1) duplication hint: error hint: QUERY PLAN ------------------------------ Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) (2 rows) ---- ---- No. S-2-1 some complexity query blocks ---- -- 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(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: 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 -------------------------------------------------------------------------- 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-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(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: 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 --------------------------------------------------------------------------- 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-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) /*+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: SeqScan(bmt1) IndexScan(bmt2 t2_pkey) BitmapScan(bmt3 t3_pkey) TidScan(bmt4) not used hint: duplication hint: error hint: 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-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) /*+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: SeqScan(bmt1) IndexScan(bmt2 t2_pkey) BitmapScan(bmt3 t3_pkey) TidScan(bmt4) not used hint: duplication hint: error hint: 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-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) /*+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: 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 -------------------------------------------------------------------------- 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-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) /*+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: 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 --------------------------------------------------------------------------- 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-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 CTE c1 -> 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 -> Nested Loop -> Nested Loop -> Hash Join Hash Cond: (bmt2.c1 = c1.c1) -> Seq Scan on t2 bmt2 -> Hash -> CTE Scan on 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) (29 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) */ 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(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 -------------------------------------------------------------------------- Aggregate CTE c1 -> Aggregate -> Hash Join Hash Cond: (b1t1.c1 = b1t2.c1) -> Seq Scan on t1 b1t1 -> Hash -> Nested Loop Join Filter: (b1t2.c1 = b1t3.c1) -> Nested Loop -> Seq Scan on t2 b1t2 -> Bitmap Heap Scan on t4 b1t4 Recheck Cond: (c1 = b1t2.c1) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = b1t2.c1) -> Index Scan using t3_pkey on t3 b1t3 Index Cond: (c1 = b1t4.c1) -> Hash Join Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 -> Hash -> Nested Loop -> Nested Loop -> Hash Join Hash Cond: (bmt1.c1 = c1.c1) -> Seq Scan on t1 bmt1 -> Hash -> CTE Scan on c1 -> Index Scan using t2_pkey on t2 bmt2 Index Cond: (c1 = bmt1.c1) -> Bitmap Heap Scan on t3 bmt3 Recheck Cond: (c1 = bmt1.c1) -> Bitmap Index Scan on t3_pkey Index Cond: (c1 = bmt1.c1) (34 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 CTE c1 -> 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) CTE c2 -> 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 Join Filter: (bmt1.c1 = c2.c1) -> Nested Loop -> Nested Loop -> Nested Loop -> Hash Join Hash Cond: (bmt2.c1 = c1.c1) -> Seq Scan on t2 bmt2 -> Hash -> CTE Scan on 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) -> CTE Scan on c2 (46 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) 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(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 --------------------------------------------------------------------------- Aggregate CTE c1 -> Aggregate -> Hash Join Hash Cond: (b1t1.c1 = b1t2.c1) -> Seq Scan on t1 b1t1 -> Hash -> Nested Loop Join Filter: (b1t2.c1 = b1t3.c1) -> Nested Loop -> Seq Scan on t2 b1t2 -> Bitmap Heap Scan on t4 b1t4 Recheck Cond: (c1 = b1t2.c1) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = b1t2.c1) -> Index Scan using t3_pkey on t3 b1t3 Index Cond: (c1 = b1t4.c1) CTE c2 -> Aggregate -> Hash Join Hash Cond: (b2t3.c1 = b2t1.c1) -> Seq Scan on t3 b2t3 -> Hash -> Merge Join Merge Cond: (b2t1.c1 = b2t2.c1) -> Nested Loop -> Index Scan using t4_pkey on t4 b2t4 -> Bitmap Heap Scan on t1 b2t1 Recheck Cond: (c1 = b2t4.c1) -> Bitmap Index Scan on t1_pkey Index Cond: (c1 = b2t4.c1) -> Sort Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 -> Hash Join Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 -> Hash -> Nested Loop Join Filter: (bmt1.c1 = c2.c1) -> Nested Loop -> Nested Loop -> Hash Join Hash Cond: (bmt1.c1 = c1.c1) -> Seq Scan on t1 bmt1 -> Hash -> CTE Scan on c1 -> Index Scan using t2_pkey on t2 bmt2 Index Cond: (c1 = bmt1.c1) -> Bitmap Heap Scan on t3 bmt3 Recheck Cond: (c1 = bmt1.c1) -> Bitmap Index Scan on t3_pkey Index Cond: (c1 = bmt1.c1) -> CTE Scan on c2 (54 rows) ---- ---- 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 CTE c1 -> Result InitPlan 1 (returns $0) -> Limit -> Tid Scan on t1 b1t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 4 (returns $3) -> Result InitPlan 3 (returns $2) -> Limit -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 6 (returns $5) -> Result InitPlan 5 (returns $4) -> Limit -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 <> $5) AND (c1 = 1)) -> CTE Scan on c1 (27 rows) /*+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 CTE c1 -> Result InitPlan 1 (returns $0) -> Limit -> Tid Scan on t1 b1t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 4 (returns $3) -> Result InitPlan 3 (returns $2) -> Limit -> Bitmap Heap Scan on t1 b2t1 Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1)) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t1_pkey Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 6 (returns $5) -> Result InitPlan 5 (returns $4) -> Limit -> Index Scan using t1_pkey on t1 b3t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) Filter: (ctid = '(1,1)'::tid) -> Nested Loop -> Seq Scan on t1 bmt1 Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid) AND (c1 = 1)) -> CTE Scan on c1 (28 rows) -- 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 CTE c1 -> Aggregate -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Tid Scan on t1 b1t1 TID Cond: (ctid = '(1,1)'::tid) -> Materialize -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t2.c1) -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) -> Materialize -> Seq Scan on t2 b2t2 Filter: (ctid = '(1,1)'::tid) InitPlan 3 (returns $2) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t2.c1) -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) -> Materialize -> Seq Scan on t2 b3t2 Filter: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Nested Loop -> CTE Scan on c1 -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) Filter: (c1 <> $2) (37 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 CTE c1 -> Aggregate -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Tid Scan on t1 b1t1 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) InitPlan 2 (returns $2) -> 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 3 (returns $4) -> 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) -> Index Scan using t2_pkey on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Seq Scan on t1 bmt1 Filter: ((c1 <> $4) AND (ctid = '(1,1)'::tid)) -> CTE Scan on c1 (37 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 CTE c1 -> Aggregate -> Nested Loop Join Filter: (b1t1.c1 = b1t4.c1) -> Tid Scan on t4 b1t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b1t1.c1 = b1t3.c1) -> Tid Scan on t3 b1t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Tid Scan on t1 b1t1 TID Cond: (ctid = '(1,1)'::tid) -> Materialize -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t4.c1) -> Tid Scan on t4 b2t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b2t1.c1 = b2t3.c1) -> Tid Scan on t3 b2t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b2t1.c1 = b2t2.c1) -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) -> Materialize -> Seq Scan on t2 b2t2 Filter: (ctid = '(1,1)'::tid) InitPlan 3 (returns $2) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t4.c1) -> Tid Scan on t4 b3t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b3t1.c1 = b3t3.c1) -> Tid Scan on t3 b3t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b3t1.c1 = b3t2.c1) -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) -> Materialize -> Seq Scan on t2 b3t2 Filter: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = bmt3.c1) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = c1.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) Filter: (c1 <> $2) -> CTE Scan on c1 (70 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 IndexScan(b1t3): t3_pkey LOG: available indexes for BitmapScan(b1t4): t4_pkey LOG: available indexes for BitmapScan(b2t1): t1_pkey LOG: available indexes for IndexScan(b2t4): t4_pkey LOG: available indexes for IndexScan(b3t1): t1_pkey LOG: available indexes for BitmapScan(b3t2): t2_pkey LOG: available indexes for IndexScan(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 CTE c1 -> Aggregate -> Nested Loop -> Nested Loop -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Tid Scan on t1 b1t1 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 b1t3 Index Cond: (c1 = b1t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 b1t4 Recheck Cond: (c1 = b1t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = b1t1.c1) InitPlan 2 (returns $4) -> Aggregate -> Nested Loop -> Nested Loop Join Filter: (b2t1.c1 = b2t3.c1) -> 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) -> Seq Scan on t3 b2t3 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t4_pkey on t4 b2t4 Index Cond: (c1 = b2t1.c1) Filter: (ctid = '(1,1)'::tid) InitPlan 3 (returns $7) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t4.c1) -> Nested Loop -> Nested Loop -> Tid Scan on t3 b3t3 TID Cond: (ctid = '(1,1)'::tid) -> Index Scan using t1_pkey on t1 b3t1 Index Cond: (c1 = b3t3.c1) 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) -> Seq Scan on t4 b3t4 Filter: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = c1.c1) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Index Scan using t2_pkey on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Seq Scan on t1 bmt1 Filter: ((c1 <> $7) AND (ctid = '(1,1)'::tid)) -> Bitmap Heap Scan on t3 bmt3 Recheck Cond: (c1 = bmt1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t3_pkey Index Cond: (c1 = bmt1.c1) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) -> CTE Scan on c1 (74 rows) -- 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 CTE c1 -> Aggregate -> Nested Loop Join Filter: (b1t1.c1 = b1t4.c1) -> Tid Scan on t4 b1t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b1t1.c1 = b1t3.c1) -> Tid Scan on t3 b1t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Tid Scan on t1 b1t1 TID Cond: (ctid = '(1,1)'::tid) -> Materialize -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) InitPlan 3 (returns $2) -> Result InitPlan 2 (returns $1) -> Limit -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 4 (returns $4) -> Aggregate -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = bmt3.c1) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = c1.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) Filter: (c1 <> $4) -> CTE Scan on c1 (47 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 IndexScan(b1t3): t3_pkey LOG: available indexes for BitmapScan(b1t4): t4_pkey LOG: available indexes for BitmapScan(b2t1): t1_pkey LOG: available indexes for BitmapScan(b2t1): t1_pkey LOG: available indexes for IndexScan(b3t1): t1_pkey LOG: available indexes for IndexScan(b3t1): t1_pkey LOG: available indexes for IndexScan(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 CTE c1 -> Aggregate -> Nested Loop -> Nested Loop -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Tid Scan on t1 b1t1 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t3_pkey on t3 b1t3 Index Cond: (c1 = b1t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 b1t4 Recheck Cond: (c1 = b1t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = b1t1.c1) InitPlan 3 (returns $3) -> Result InitPlan 2 (returns $2) -> Limit -> Bitmap Heap Scan on t1 b2t1 Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1)) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t1_pkey Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 5 (returns $5) -> Result InitPlan 4 (returns $4) -> Limit -> Index Scan Backward using t1_pkey on t1 b3t1 Index Cond: (c1 IS NOT NULL) Filter: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = c1.c1) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Index Scan using t2_pkey on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Seq Scan on t1 bmt1 Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid)) -> Bitmap Heap Scan on t3 bmt3 Recheck Cond: (c1 = bmt1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t3_pkey Index Cond: (c1 = bmt1.c1) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) -> CTE Scan on c1 (54 rows) ---- ---- 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) -> Tid Scan on t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> 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) (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 -> Nested Loop -> 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 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 Recheck Cond: (c1 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = t1.c1) (20 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) -> Tid Scan on t4 b1t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b1t1.c1 = b1t3.c1) -> Tid Scan on t3 b1t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) -> 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) (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 -> Nested Loop -> 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 = b1t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 b1t4 Recheck Cond: (c1 = b1t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = b1t1.c1) (20 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) -> Tid Scan on t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> 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) Aggregate -> Nested Loop Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> 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) (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 -> Nested Loop -> 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 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 Recheck Cond: (c1 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = t1.c1) Aggregate -> Nested Loop -> Nested Loop -> 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 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 Recheck Cond: (c1 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = t1.c1) (41 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) -> Tid Scan on t4 b1t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b1t1.c1 = b1t3.c1) -> Tid Scan on t3 b1t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) -> 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) Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t4.c1) -> Tid Scan on t4 b2t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b2t1.c1 = b2t3.c1) -> Tid Scan on t3 b2t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b2t1.c1 = b2t2.c1) -> Seq Scan on t2 b2t2 Filter: (ctid = '(1,1)'::tid) -> 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) (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 -> Nested Loop -> 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 = b1t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 b1t4 Recheck Cond: (c1 = b1t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = b1t1.c1) Aggregate -> Nested Loop -> Nested Loop Join Filter: (b2t1.c1 = b2t3.c1) -> Nested Loop -> Nested Loop -> Seq Scan on r2_ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) -> Tid Scan on t2 b2t2 TID Cond: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t1 b2t1 Recheck Cond: (c1 = b2t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t1_pkey Index Cond: (c1 = b2t2.c1) -> Seq Scan on t3 b2t3 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t4_pkey on t4 b2t4 Index Cond: (c1 = b2t1.c1) Filter: (ctid = '(1,1)'::tid) (41 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) -> Tid Scan on t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> 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) Aggregate -> Nested Loop Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> 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) Aggregate -> Nested Loop Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 Filter: (ctid = '(1,1)'::tid) -> 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) (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 -> Nested Loop -> 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 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 Recheck Cond: (c1 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = t1.c1) Aggregate -> Nested Loop -> Nested Loop -> 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 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 Recheck Cond: (c1 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = t1.c1) Aggregate -> Nested Loop -> Nested Loop -> 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 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 Recheck Cond: (c1 = t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = t1.c1) (62 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) -> Tid Scan on t4 b1t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b1t1.c1 = b1t3.c1) -> Tid Scan on t3 b1t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b1t1.c1 = b1t2.c1) -> Seq Scan on t2 b1t2 Filter: (ctid = '(1,1)'::tid) -> 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) Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t4.c1) -> Tid Scan on t4 b2t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b2t1.c1 = b2t3.c1) -> Tid Scan on t3 b2t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b2t1.c1 = b2t2.c1) -> Seq Scan on t2 b2t2 Filter: (ctid = '(1,1)'::tid) -> 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) Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t4.c1) -> Tid Scan on t4 b3t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b3t1.c1 = b3t3.c1) -> Tid Scan on t3 b3t3 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (b3t1.c1 = b3t2.c1) -> Seq Scan on t2 b3t2 Filter: (ctid = '(1,1)'::tid) -> 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) (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 -> Nested Loop -> 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 = b1t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t4 b1t4 Recheck Cond: (c1 = b1t1.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t4_pkey Index Cond: (c1 = b1t1.c1) Aggregate -> Nested Loop -> Nested Loop Join Filter: (b2t1.c1 = b2t3.c1) -> Nested Loop -> Nested Loop -> Seq Scan on r3_ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) -> Tid Scan on t2 b2t2 TID Cond: (ctid = '(1,1)'::tid) -> Bitmap Heap Scan on t1 b2t1 Recheck Cond: (c1 = b2t2.c1) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t1_pkey Index Cond: (c1 = b2t2.c1) -> Seq Scan on t3 b2t3 Filter: (ctid = '(1,1)'::tid) -> Index Scan using t4_pkey on t4 b2t4 Index Cond: (c1 = b2t1.c1) Filter: (ctid = '(1,1)'::tid) Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t4.c1) -> Nested Loop -> Nested Loop -> Nested Loop -> Seq Scan on r3_ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1)) -> Tid Scan on t3 b3t3 TID Cond: (ctid = '(1,1)'::tid) -> Index Scan using t1_pkey on t1 b3t1 Index Cond: (c1 = b3t3.c1) 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) -> Seq Scan on t4 b3t4 Filter: (ctid = '(1,1)'::tid) (62 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_c2_key" UNIQUE CONSTRAINT, btree (c2) "ti1_uniq" UNIQUE, btree (c1) "ti1_btree" btree (c1) "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)) 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: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-6 /*+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) Filter: (c1 < 100) (3 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 \! psql contrib_regression -c "/*+IndexScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" 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 ------------------------------------------------------------------------ Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15) Filter: (c1 = 100) (2 rows) -- No. S-3-5-2 \! psql contrib_regression -c "/*+BitmapScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" 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 ------------------------------------------------------------------------ Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15) Filter: (c1 = 100) (2 rows) -- No. S-3-5-3 \! psql contrib_regression -c "/*+IndexOnlyScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" 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 ----------------------------------------------------------------------- Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=4) Filter: (c1 = 100) (2 rows) -- No. S-3-5-4 \! psql contrib_regression -c "/*+IndexScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" LOG: available indexes for IndexScan(ti1): LOG: pg_hint_plan: used hint: IndexScan(ti1 not_exist) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------------------------------ Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15) Filter: (c1 = 100) (2 rows) -- No. S-3-5-5 \! psql contrib_regression -c "/*+BitmapScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" LOG: available indexes for BitmapScan(ti1): LOG: pg_hint_plan: used hint: BitmapScan(ti1 not_exist) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------------------------------ Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15) Filter: (c1 = 100) (2 rows) -- No. S-3-5-6 \! psql contrib_regression -c "/*+IndexOnlyScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:" LOG: available indexes for IndexOnlyScan(ti1): LOG: pg_hint_plan: used hint: IndexOnlyScan(ti1 not_exist) not used hint: duplication hint: error hint: QUERY PLAN ----------------------------------------------------------------------- Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=4) Filter: (c1 = 100) (2 rows) -- 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) \! psql contrib_regression -c "/*+TidScan(t1)*/ EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1" | grep -v "Planning time:" LOG: pg_hint_plan: used hint: TidScan(t1) not used hint: duplication hint: error hint: QUERY PLAN ----------------------------------------------------------------------- Seq Scan on t1 (cost=10000000000.00..10000000018.50 rows=1 width=15) Filter: (c1 = 1) (2 rows) ---- ---- 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: (t1.c1 = t2.c1) -> Tid Scan on t1 TID Cond: (ctid = '(1,1)'::tid) -> Hash -> Seq Scan on t2 (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 CTE cte1 -> Aggregate -> Nested Loop -> Index Only Scan using t1_i1 on t1 b1t1 Index Cond: (c1 = 1) -> Seq Scan on t2 b1t2 Filter: (c1 = 1) InitPlan 2 (returns $1) -> Aggregate -> Nested Loop -> Index Only Scan using t1_i1 on t1 b2t1 Index Cond: (c1 = 1) -> Seq Scan on t2 b2t2 Filter: (c1 = 1) InitPlan 3 (returns $2) -> 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 = $2) -> Seq Scan on t2 b3t2 Filter: (c1 = $2) -> CTE Scan on cte1 Filter: (c1 = $2) (30 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 CTE cte1 -> Aggregate -> Nested Loop -> Seq Scan on t1 b1t1 Filter: (c1 = 1) -> Seq Scan on t2 b1t2 Filter: (c1 = 1) InitPlan 2 (returns $1) -> Aggregate -> Nested Loop -> Index Scan using t1_pkey on t1 b2t1 Index Cond: (c1 = 1) -> Seq Scan on t2 b2t2 Filter: (c1 = 1) InitPlan 3 (returns $2) -> 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 = $2) -> Bitmap Index Scan on t1_pkey Index Cond: (c1 = $2) -> Index Scan using t2_pkey on t2 b3t2 Index Cond: (c1 = $2) -> CTE Scan on cte1 Filter: (c1 = $2) (34 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 CTE cte1 -> Aggregate -> Nested Loop -> Index Only Scan using t1_i1 on t1 b1t1 Index Cond: (c1 = 1) -> Seq Scan on t2 b1t2 Filter: (c1 = 1) InitPlan 3 (returns $2) -> Result InitPlan 2 (returns $1) -> Limit -> Index Only Scan using t1_i1 on t1 b2t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 5 (returns $4) -> Result InitPlan 4 (returns $3) -> Limit -> Index Only Scan using t1_i1 on t1 b4t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Nested Loop -> Index Only Scan using t1_i1 on t1 b3t1 Index Cond: (c1 = $4) -> Seq Scan on t2 b3t2 Filter: (c1 = $4) -> CTE Scan on cte1 Filter: (c1 = $4) (28 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 CTE cte1 -> Aggregate -> Nested Loop -> Seq Scan on t1 b1t1 Filter: (c1 = 1) -> Seq Scan on t2 b1t2 Filter: (c1 = 1) InitPlan 3 (returns $2) -> Result InitPlan 2 (returns $1) -> Limit -> Index Scan using t1_pkey on t1 b2t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 5 (returns $4) -> Result InitPlan 4 (returns $3) -> Limit -> Seq Scan on t1 b4t1 Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Nested Loop -> Bitmap Heap Scan on t1 b3t1 Recheck Cond: (c1 = $4) -> Bitmap Index Scan on t1_pkey Index Cond: (c1 = $4) -> Index Scan using t2_pkey on t2 b3t2 Index Cond: (c1 = $4) -> CTE Scan on cte1 Filter: (c1 = $4) (30 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 Filter: (c1 = 1) -> Seq Scan on p1c1 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 Index Cond: (c1 = 1) -> Index Scan using p1c1_i on p1c1 Index Cond: (c1 = 1) (5 rows) -- No. S-3-8-3 EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1; QUERY PLAN -------------------------- Update on p1 -> Seq Scan on p1 Filter: (c1 = 1) (3 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1; LOG: pg_hint_plan: used hint: IndexScan(p1) not used hint: duplication hint: error hint: QUERY PLAN ----------------------------------- Update on p1 -> Index Scan using p1_i on p1 Index Cond: (c1 = 1) (3 rows) /*+IndexScan(p1 p1_pkey)*/ EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1; LOG: available indexes for IndexScan(p1): p1_pkey LOG: pg_hint_plan: used hint: IndexScan(p1 p1_pkey) not used hint: duplication hint: error hint: QUERY PLAN -------------------------------------- Update on p1 -> Index Scan using p1_pkey on p1 Index Cond: (c1 = 1) (3 rows) -- No. S-3-8-4 EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; QUERY PLAN -------------------------- Update on p1 Update on p1 Update on p1c1 -> Seq Scan on p1 Filter: (c1 = 1) -> Seq Scan on p1c1 Filter: (c1 = 1) (7 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; LOG: pg_hint_plan: used hint: IndexScan(p1) not used hint: duplication hint: error hint: QUERY PLAN --------------------------------------- Update on p1 Update on p1 Update on p1c1 -> Index Scan using p1_i on p1 Index Cond: (c1 = 1) -> Index Scan using p1c1_i on p1c1 Index Cond: (c1 = 1) (7 rows) /*+IndexScan(p1 p1_pkey)*/ EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; LOG: available indexes for IndexScan(p1): p1_pkey LOG: available indexes for IndexScan(p1c1): p1c1_pkey LOG: pg_hint_plan: used hint: IndexScan(p1 p1_pkey) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------ Update on p1 Update on p1 Update on p1c1 -> Index Scan using p1_pkey on p1 Index Cond: (c1 = 1) -> Index Scan using p1c1_pkey on p1c1 Index Cond: (c1 = 1) (7 rows) ---- ---- No. S-3-9 inheritance table number ---- -- No. S-3-9-1 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; QUERY PLAN -------------------------- Append -> Seq Scan on p1 Filter: (c1 = 1) -> Seq Scan on p1c1 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 Index Cond: (c1 = 1) -> Index Scan using p1c1_i on p1c1 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 Filter: (c1 = 1) -> Seq Scan on p2c1 Filter: (c1 = 1) -> Seq Scan on p2c1c1 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 Index Cond: (c1 = 1) -> Index Scan using p2c1_i on p2c1 Index Cond: (c1 = 1) -> Index Scan using p2c1c1_i on p2c1c1 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 Filter: (c1 = 1) -> Seq Scan on p2c1 Filter: (c1 = 1) -> Seq Scan on p2c1c1 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 Index Cond: (c1 = 1) -> Index Scan using p2c1_i on p2c1 Index Cond: (c1 = 1) -> Index Scan using p2c1c1_i on p2c1c1 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 Filter: (c1 = 1) -> Index Scan using p2c1_i on p2c1 Index Cond: (c1 = 1) -> Seq Scan on p2c1c1 Filter: (c1 = 1) (7 rows) -- No. S-3-10-3 \o results/ut-S.tmpout EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10; \o \! sql/maskout.sh results/ut-S.tmpout QUERY PLAN ---------------------------------------------------------- Append (cost=xxx rows=4 width=xxx) -> Seq Scan on p1 (cost=xxx rows=1 width=xxx) Filter: ((c1 < 10) AND ((c2 * 2) < 100)) -> Seq Scan on p1c1 (cost=xxx rows=3 width=xxx) Filter: ((c1 < 10) AND ((c2 * 2) < 100)) (5 rows) \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 rows=4 width=xxx) -> Index Scan using p1_parent on p1 (cost=xxx rows=1 width=xxx) Filter: ((c2 * 2) < 100) -> Index Scan using p1c1_c4_expr_idx on p1c1 (cost=xxx rows=3 width=xxx) Filter: ((c2 * 2) < 100) (5 rows) -- 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 rows=4 width=xxx) -> Index Scan using p1_i2 on p1 (cost=xxx rows=1 width=xxx) Index Cond: (c2 = 1) -> Seq Scan on p1c1 (cost=xxx rows=1 width=xxx) Filter: (c2 = 1) -> Seq Scan on p1c2 (cost=xxx rows=1 width=xxx) Filter: (c2 = 1) -> Seq Scan on p1c3 (cost=xxx rows=1 width=xxx) Filter: (c2 = 1) (9 rows) -- 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=xxx rows=3 width=xxx) -> Seq Scan on p2 (cost=xxx rows=1 width=xxx) Filter: (c1 = 1) -> Index Scan using p2c1_pkey on p2c1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) -> Seq Scan on p2c1c1 (cost=xxx rows=1 width=xxx) Filter: (c1 = 1) (7 rows) ---- ---- 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 ----------------------------------- 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-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) -- 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: not used hint: duplication hint: error hint: NoIndexOnlyScan(ti1 ti1_pkey) QUERY PLAN ---------------------------------- Index Scan using ti1_hash on ti1 Index Cond: (c1 = 1) (2 rows) -- 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 ti1_i4 on ti1 Index Cond: (c2 = 1) (2 rows) -- 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: IndexScanRegexp(ti1 ti1_.*_key) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------ Index Scan using ti1_c2_key on ti1 Index Cond: (c2 = 1) (2 rows) -- 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) -- 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: IndexScanRegexp(ti1 no.*_exist) not used hint: duplication hint: error hint: QUERY PLAN -------------------- Seq Scan on ti1 Filter: (c2 = 1) (2 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: QUERY PLAN ------------------------------------------ Append -> Index Scan using p1_pkey on p1 Index Cond: (c1 = 1) -> Index Scan using p1c1_pkey on p1c1 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: IndexScanRegexp(p1 p1.*i) not used hint: duplication hint: error hint: QUERY PLAN --------------------------------------- Append -> Index Scan using p1_i on p1 Index Cond: (c1 = 1) -> Index Scan using p1c1_i on p1c1 Index Cond: (c1 = 1) (5 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: QUERY PLAN -------------------------- Append -> Seq Scan on p1 Filter: (c1 = 1) -> Seq Scan on p1c1 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: IndexScan(ti1 ti1_i1) not used hint: duplication hint: error hint: QUERY PLAN -------------------------------- Index Scan using ti1_i1 on ti1 Index Cond: (c2 = 1) (2 rows) -- 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: IndexScan(ti1 not_exist) not used hint: duplication hint: error hint: 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: QUERY PLAN -------------------------------- Index Scan using ti1_i2 on ti1 Index Cond: (c2 = 1) (2 rows) -- 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: IndexScan(ti1 ti1_i1 not_exist) not used hint: duplication hint: error hint: QUERY PLAN -------------------------------- 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