LOAD 'pg_hint_plan';
-SET pg_hint_plan.enable TO on;
+-- We cannot do ALTER USER current_user SET ...
+DELETE FROM pg_db_role_setting WHERE setrole = (SELECT oid FROM pg_roles WHERE rolname = current_user);
+INSERT INTO pg_db_role_setting (SELECT 0, (SELECT oid FROM pg_roles WHERE rolname = current_user), '{client_min_messages=log,pg_hint_plan.debug_print=on}');
+ALTER SYSTEM SET session_preload_libraries TO 'pg_hint_plan';
+SELECT pg_reload_conf();
+ pg_reload_conf
+----------------
+ t
+(1 row)
+
+SET pg_hint_plan.enable_hint TO on;
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
SET search_path TO public;
+SET max_parallel_workers_per_gather TO 0;
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
QUERY PLAN
---------------------
(2 rows)
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
(2 rows)
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------
- Index Scan using t1_pkey on t1 t_1
+ QUERY PLAN
+----------------------------------
+ Index Scan using t1_i1 on t1 t_1
Index Cond: (c1 = 1)
(2 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
(2 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
(2 rows)
----
-- No. S-1-4-1
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = 1 AND t1.c1 = t2.c1;
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+------------------------------------
Nested Loop
- -> Index Scan using t1_pkey on t1
+ -> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
-> Seq Scan on t2
Filter: (c1 = 1)
-- No. S-1-4-2
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2.t1.c1;
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+------------------------------------
Nested Loop
- -> Index Scan using t1_pkey on t1
+ -> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
- -> Seq Scan on t1
+ -> Seq Scan on t1 t1_1
Filter: (c1 = 1)
(5 rows)
-/*+IndexScan(t1)*/
+/*+BitmapScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2.t1.c1;
LOG: pg_hint_plan:
used hint:
-IndexScan(t1)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+------------------------------------------
Nested Loop
- -> Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
- -> Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
-(5 rows)
+ -> Bitmap Heap Scan on t1
+ Recheck Cond: (c1 = 1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = 1)
+ -> Bitmap Heap Scan on t1 t1_1
+ Recheck Cond: (c1 = 1)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = 1)
+(9 rows)
-/*+SeqScan(t1)*/
+/*+BitmapScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2t1.c1;
LOG: pg_hint_plan:
used hint:
-SeqScan(t1)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
----------------------------
+ QUERY PLAN
+----------------------------------------
Nested Loop
- -> Seq Scan on t1
- Filter: (c1 = 1)
+ -> Bitmap Heap Scan on t1
+ Recheck Cond: (c1 = 1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = 1)
-> Seq Scan on t1 s2t1
Filter: (c1 = 1)
-(5 rows)
+(7 rows)
/*+BitmapScan(s2t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2t1.c1;
QUERY PLAN
------------------------------------------
Nested Loop
- -> Index Scan using t1_pkey on t1
+ -> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
-> Bitmap Heap Scan on t1 s2t1
Recheck Cond: (c1 = 1)
EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 WHERE s1.t1.c1 = 1) FROM s1.t1 WHERE s1.t1.c1 = 1;
QUERY PLAN
---------------------------------------------------------------------
- Index Scan using t1_pkey on t1
+ Index Only Scan using t1_i1 on t1
Index Cond: (c1 = 1)
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan using t1_pkey on t1
+ -> Index Only Scan using t1_i1 on t1 t1_1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
(8 rows)
-/*+SeqScan(t1)*/
+/*+BitmapScan(t1)*/
EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 WHERE s1.t1.c1 = 1) FROM s1.t1 WHERE s1.t1.c1 = 1;
LOG: pg_hint_plan:
used hint:
-SeqScan(t1)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------
- Seq Scan on t1
- Filter: (c1 = 1)
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 = 1)
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Seq Scan on t1
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-(8 rows)
+ -> Bitmap Heap Scan on t1 t1_1
+ Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = 1)
+(12 rows)
-/*+SeqScan(t11)*/
+/*+BitmapScan(t11)*/
EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 t11 WHERE t11.c1 = 1) FROM s1.t1 t12 WHERE t12.c1 = 1;
LOG: pg_hint_plan:
used hint:
-SeqScan(t11)
+BitmapScan(t11)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------
- Index Scan using t1_pkey on t1 t12
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Index Only Scan using t1_i1 on t1 t12
Index Cond: (c1 = 1)
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Seq Scan on t1 t11
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-(8 rows)
+ -> Bitmap Heap Scan on t1 t11
+ Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+(10 rows)
-/*+SeqScan(t12)*/
+/*+BitmapScan(t12)*/
EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 t11 WHERE t11.c1 = 1) FROM s1.t1 t12 WHERE t12.c1 = 1;
LOG: pg_hint_plan:
used hint:
-SeqScan(t12)
+BitmapScan(t12)
not used hint:
duplication hint:
error hint:
QUERY PLAN
---------------------------------------------------------------------
- Seq Scan on t1 t12
- Filter: (c1 = 1)
+ Bitmap Heap Scan on t1 t12
+ Recheck Cond: (c1 = 1)
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan using t1_pkey on t1 t11
+ -> Index Only Scan using t1_i1 on t1 t11
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
-(8 rows)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = 1)
+(10 rows)
----
---- No. S-1-5 object type for the hint
-- No. S-1-5-2
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1;
- QUERY PLAN
----------------------------------
- Result
- -> Append
- -> Seq Scan on p1
- Filter: (c1 = 1)
- -> Seq Scan on p1c1 p1
- Filter: (c1 = 1)
-(6 rows)
+ QUERY PLAN
+-----------------------------
+ Append
+ -> Seq Scan on p1 p1_1
+ Filter: (c1 = 1)
+ -> Seq Scan on p1c1 p1_2
+ Filter: (c1 = 1)
+(5 rows)
/*+IndexScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1;
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------
- Result
- -> Append
- -> Index Scan using p1_pkey on p1
- Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1 p1
- Index Cond: (c1 = 1)
-(6 rows)
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Index Scan using p1_i on p1 p1_1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_i on p1c1 p1_2
+ Index Cond: (c1 = 1)
+(5 rows)
-- No. S-1-5-3
EXPLAIN (COSTS false) SELECT * FROM s1.ul1 WHERE ul1.c1 = 1;
-- No. S-1-5-4
CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tm1_pkey" for table "tm1"
EXPLAIN (COSTS false) SELECT * FROM tm1 WHERE tm1.c1 = 1;
- QUERY PLAN
-----------------------------------
- Index Scan using tm1_pkey on tm1
+ QUERY PLAN
+------------------------------------
+ Index Scan using tm1_c1_idx on tm1
Index Cond: (c1 = 1)
(2 rows)
QUERY PLAN
-------------------------------------------------
Index Scan using pg_class_oid_index on pg_class
- Index Cond: (oid = 1::oid)
+ Index Cond: (oid = '1'::oid)
(2 rows)
/*+SeqScan(pg_class)*/
duplication hint:
error hint:
- QUERY PLAN
---------------------------
+ QUERY PLAN
+----------------------------
Seq Scan on pg_class
- Filter: (oid = 1::oid)
+ Filter: (oid = '1'::oid)
(2 rows)
-- No. S-1-5-6
--- refer fdw.sql
+-- refer ut-fdw.sql
-- No. S-1-5-7
EXPLAIN (COSTS false) SELECT * FROM s1.f1() AS ft1 WHERE ft1.c1 = 1;
QUERY PLAN
-- No. S-1-5-9
EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1)
SELECT * FROM s1.t1, c1 WHERE t1.c1 = 1 AND t1.c1 = c1.c1;
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Nested Loop
- CTE c1
- -> Result
- InitPlan 1 (returns $0)
- -> Limit
- -> Index Scan using t1_pkey on t1
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- -> Index Scan using t1_pkey on t1
+ -> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(11 rows)
+ -> Result
+ One-Time Filter: ($0 = 1)
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Index Only Scan using t1_i1 on t1 t1_1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+(9 rows)
/*+SeqScan(c1)*/
EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Nested Loop
- CTE c1
- -> Result
- InitPlan 1 (returns $0)
- -> Limit
- -> Index Scan using t1_pkey on t1
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- -> Index Scan using t1_pkey on t1
+ -> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(11 rows)
+ -> Result
+ One-Time Filter: ($0 = 1)
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Index Only Scan using t1_i1 on t1 t1_1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+(9 rows)
-- No. S-1-5-10
EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1;
- QUERY PLAN
--------------------------------------
- Index Scan using t1_pkey on t1 v1t1
+ QUERY PLAN
+-----------------------------------
+ Index Scan using t1_i1 on t1 v1t1
Index Cond: (c1 = 1)
(2 rows)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------
- Index Scan using t1_pkey on t1 v1t1
+ QUERY PLAN
+-----------------------------------
+ Index Scan using t1_i1 on t1 v1t1
Index Cond: (c1 = 1)
(2 rows)
-- No. S-1-5-11
EXPLAIN (COSTS false) SELECT * FROM (SELECT * FROM s1.t1 WHERE t1.c1 = 1) AS s1 WHERE s1.c1 = 1;
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
(2 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
(2 rows)
----
----- No. S-3-1 scan method hint
+---- No. S-2-1 some complexity query blocks
----
--- No. S-3-1-1
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
- QUERY PLAN
----------------------
- Seq Scan on t1
- Filter: (c1 >= 1)
-(2 rows)
-
-/*+SeqScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+-- 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:
-SeqScan(t1)
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+SeqScan(bmt1)
+IndexScan(bmt2 t2_pkey)
+BitmapScan(bmt3 t3_pkey)
+TidScan(bmt4)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
----------------------
- Seq Scan on t1
- Filter: (c1 >= 1)
-(2 rows)
-
--- No. S-3-1-2
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
-(2 rows)
-
-/*+SeqScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ 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:
-SeqScan(t1)
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+BitmapScan(b2t1 t1_pkey)
+TidScan(b2t2)
+SeqScan(b2t3)
+IndexScan(b2t4 t4_pkey)
+SeqScan(bmt1)
+IndexScan(bmt2 t2_pkey)
+BitmapScan(bmt3 t3_pkey)
+TidScan(bmt4)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------
- Seq Scan on t1
- Filter: (c1 = 1)
-(2 rows)
-
--- No. S-3-1-3
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
-(2 rows)
-
-/*+IndexScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ 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:
-IndexScan(t1)
+SeqScan(bmt1)
+IndexScan(bmt2 t2_pkey)
+BitmapScan(bmt3 t3_pkey)
+TidScan(bmt4)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
-(2 rows)
-
--- 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;
+ 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:
-IndexScan(t1)
+SeqScan(bmt1)
+IndexScan(bmt2 t2_pkey)
+BitmapScan(bmt3 t3_pkey)
+TidScan(bmt4)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
- Index Cond: (c1 >= 1)
-(2 rows)
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Aggregate
+ -> Hash Join
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4
+ -> Hash
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ -> Hash
+ -> Index Scan using t2_pkey on t2 bmt2
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
+(15 rows)
+
+-- No. S-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)
--- 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)
+/*+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:
-/*+BitmapScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
+ 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:
-BitmapScan(t1)
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+BitmapScan(b2t1 t1_pkey)
+TidScan(b2t2)
+SeqScan(b2t3)
+IndexScan(b2t4 t4_pkey)
+SeqScan(bmt1)
+IndexScan(bmt2 t2_pkey)
+BitmapScan(bmt3 t3_pkey)
+TidScan(bmt4)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
----------------------------------
- Bitmap Heap Scan on t1
- Recheck Cond: (c3 < 10)
- -> Bitmap Index Scan on t1_i
- Index Cond: (c3 < 10)
-(4 rows)
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $2)
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
+ -> Hash
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
+ 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
+ -> Nested Loop
+ -> Nested Loop
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt2.c1 = (max(b1t1.c1)))
+ -> Seq Scan on t2 bmt2
+ -> Hash
+ -> Aggregate
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ -> Index Only Scan using t3_i1 on t3 b1t3
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t1.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1
+ Index Cond: (c1 = bmt2.c1)
+ -> Index Only Scan using t3_i1 on t3 bmt3
+ Index Cond: (c1 = bmt1.c1)
+ -> Index Only Scan using t4_i1 on t4 bmt4
+ Index Cond: (c1 = bmt1.c1)
+(27 rows)
+
+/*+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
+ -> Hash Join
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4
+ -> Hash
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: (bmt1.c1 = (max(b1t1.c1)))
+ -> Sort
+ Sort Key: bmt1.c1
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ -> Hash
+ -> Index Scan using t2_pkey on t2 bmt2
+ -> Sort
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
+ -> Hash
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
+(36 rows)
+
+-- No. S-2-1-8
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
+)
+, c2 (c1) AS (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
+)
+SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
+, c1, c2
+ WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 = c2.c1
+;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
+ -> Nested Loop
+ -> Nested Loop
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt2.c1 = (max(b1t1.c1)))
+ -> Seq Scan on t2 bmt2
+ -> Hash
+ -> Aggregate
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ -> Index Only Scan using t3_i1 on t3 b1t3
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t1.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1
+ Index Cond: (c1 = bmt2.c1)
+ -> Index Only Scan using t3_i1 on t3 bmt3
+ Index Cond: (c1 = bmt1.c1)
+ -> Index Only Scan using t4_i1 on t4 bmt4
+ Index Cond: (c1 = bmt1.c1)
+ -> Aggregate
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t3.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1
+ -> Index Only Scan using t3_i1 on t3 b2t3
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ -> Index Only Scan using t4_i1 on t4 b2t4
+ Index Cond: (c1 = b2t1.c1)
+(42 rows)
+
+/*+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
+ -> Hash Join
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4
+ -> Hash
+ -> Nested Loop
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
+ -> Merge Join
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
+ -> Sort
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
+ -> Hash
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
+ -> Sort
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Seq Scan on t3 b2t3
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ -> Index Scan using t4_pkey on t4 b2t4
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t4.c1)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ -> Seq Scan on t1 bmt1
+ -> Index Scan using t2_pkey on t2 bmt2
+ Index Cond: (c1 = bmt1.c1)
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
+(53 rows)
+
+----
+---- No. S-2-2 the number of the tables per quiry block
+----
+-- No. S-2-2-1
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = 1
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = 1
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
+)
+;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+ InitPlan 4 (returns $3)
+ -> Result
+ InitPlan 3 (returns $2)
+ -> Limit
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+ -> Nested Loop
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+(20 rows)
+
+/*+SeqScan(bmt1)
+TidScan(b1t1)
+BitmapScan(b2t1 t1_pkey)
+IndexScan(b3t1 t1_pkey)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = 1
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = 1
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
+)
+;
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: pg_hint_plan:
+used hint:
+TidScan(b1t1)
+BitmapScan(b2t1 t1_pkey)
+IndexScan(b3t1 t1_pkey)
+SeqScan(bmt1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ InitPlan 4 (returns $3)
+ -> Result
+ InitPlan 3 (returns $2)
+ -> Limit
+ -> Index Scan using t1_pkey on t1 b3t1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop
+ -> Seq Scan on t1 bmt1
+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Result
+(21 rows)
+
+-- No. S-2-2-2
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)'
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)'
+)
+;
+ QUERY PLAN
+-----------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ InitPlan 2 (returns $1)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t2.c1)
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 <> $1)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(32 rows)
+
+/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)
+TidScan(b1t1)SeqScan(b1t2)
+BitmapScan(b2t1 t1_pkey)TidScan(b2t2)
+IndexScan(b3t1 t1_pkey)BitmapScan(b3t2 t2_pkey)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)'
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)'
+)
+;
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t2): t2_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: pg_hint_plan:
+used hint:
+TidScan(b1t1)
+SeqScan(b1t2)
+BitmapScan(b2t1 t1_pkey)
+TidScan(b2t2)
+IndexScan(b3t1 t1_pkey)
+BitmapScan(b3t2 t2_pkey)
+SeqScan(bmt1)
+IndexScan(bmt2 t2_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $1)
+ -> Aggregate
+ -> Nested Loop
+ -> Tid Scan on t2 b2t2
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t2.c1)
+ InitPlan 2 (returns $3)
+ -> Aggregate
+ -> Nested Loop
+ -> Index Scan using t1_pkey on t1 b3t1
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t2 b3t2
+ Recheck Cond: (c1 = b3t1.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t2_pkey
+ Index Cond: (c1 = b3t1.c1)
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid))
+ -> Index Scan using t2_pkey on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(35 rows)
+
+-- No. S-2-2-3
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)'
+)
+;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ InitPlan 2 (returns $1)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t4.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t2.c1)
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 <> $1)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(65 rows)
+
+/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
+BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey)
+IndexScan(b3t1 t1_pkey)BitmapScan(b3t2 t2_pkey)TidScan(b3t3)SeqScan(b3t4)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)'
+)
+;
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t4): t4_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t2): t2_pkey
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
+LOG: pg_hint_plan:
+used hint:
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+BitmapScan(b2t1 t1_pkey)
+TidScan(b2t2)
+SeqScan(b2t3)
+IndexScan(b2t4 t4_pkey)
+IndexScan(b3t1 t1_pkey)
+BitmapScan(b3t2 t2_pkey)
+TidScan(b3t3)
+SeqScan(b3t4)
+SeqScan(bmt1)
+IndexScan(bmt2 t2_pkey)
+BitmapScan(bmt3 t3_pkey)
+TidScan(bmt4)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $2)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t2.c1 = b2t1.c1)
+ -> Nested Loop
+ Join Filter: (b2t2.c1 = b2t3.c1)
+ -> Tid Scan on t2 b2t2
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t3 b2t3
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t3.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t3.c1)
+ -> Index Scan using t4_pkey on t4 b2t4
+ Index Cond: (c1 = b2t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ InitPlan 2 (returns $5)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t2.c1)
+ -> Nested Loop
+ Join Filter: (b3t3.c1 = b3t1.c1)
+ -> Nested Loop
+ Join Filter: (b3t3.c1 = b3t4.c1)
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t4 b3t4
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t1_pkey on t1 b3t1
+ Index Cond: (c1 = b3t4.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t2 b3t2
+ Recheck Cond: (c1 = b3t3.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t2_pkey
+ Index Cond: (c1 = b3t3.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ -> Seq Scan on t1 bmt1
+ Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid))
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Index Scan using t2_pkey on t2 bmt2
+ Index Cond: (c1 = bmt4.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt2.c1)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+(80 rows)
+
+-- No. S-2-2-4
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
+)
+;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+ InitPlan 3 (returns $3)
+ -> Aggregate
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 <> $3)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(44 rows)
+
+/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
+BitmapScan(b2t1 t1_pkey)
+IndexScan(b3t1 t1_pkey)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
+)
+;
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: available indexes for IndexScan(bmt2): t2_pkey
+LOG: available indexes for BitmapScan(bmt3): t3_pkey
+LOG: pg_hint_plan:
+used hint:
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+BitmapScan(b2t1 t1_pkey)
+IndexScan(b3t1 t1_pkey)
+SeqScan(bmt1)
+IndexScan(bmt2 t2_pkey)
+BitmapScan(bmt3 t3_pkey)
+TidScan(bmt4)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ InitPlan 4 (returns $3)
+ -> Result
+ InitPlan 3 (returns $2)
+ -> Limit
+ -> Index Scan Backward using t1_pkey on t1 b3t1
+ Index Cond: (c1 IS NOT NULL)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ -> Seq Scan on t1 bmt1
+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid))
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Index Scan using t2_pkey on t2 bmt2
+ Index Cond: (c1 = bmt4.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt2.c1)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+(56 rows)
+
+----
+---- No. S-2-3 RULE or VIEW
+----
+-- No. S-2-3-1
+EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(19 rows)
+
+/*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey)
+SeqScan(r1)*/
+EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+SeqScan(r1)
+TidScan(t1)
+SeqScan(t2)
+IndexScan(t3 t3_pkey)
+BitmapScan(t4 t4_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Seq Scan on r1
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3
+ Index Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4
+ Recheck Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = t2.c1)
+(22 rows)
+
+EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r1_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(19 rows)
+
+/*+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
+SeqScan(r1_)*/
+EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+SeqScan(r1_)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ -> Seq Scan on r1_
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+(22 rows)
+
+-- No. S-2-3-2
+EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(39 rows)
+
+/*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey)
+SeqScan(r2)*/
+EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+SeqScan(r2)
+TidScan(t1)
+SeqScan(t2)
+IndexScan(t3 t3_pkey)
+BitmapScan(t4 t4_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+SeqScan(r2)
+TidScan(t1)
+SeqScan(t2)
+IndexScan(t3 t3_pkey)
+BitmapScan(t4 t4_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Seq Scan on r2
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3
+ Index Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4
+ Recheck Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = t2.c1)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Seq Scan on r2
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3
+ Index Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4
+ Recheck Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = t2.c1)
+(45 rows)
+
+EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r2_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r2_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(39 rows)
+
+/*+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
+BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey)
+SeqScan(r2_)*/
+EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+SeqScan(r2_)
+not used hint:
+BitmapScan(b2t1 t1_pkey)
+TidScan(b2t2)
+SeqScan(b2t3)
+IndexScan(b2t4 t4_pkey)
+duplication hint:
+error hint:
+
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(b2t1 t1_pkey)
+TidScan(b2t2)
+SeqScan(b2t3)
+IndexScan(b2t4 t4_pkey)
+SeqScan(r2_)
+not used hint:
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ -> Seq Scan on r2_
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t2.c1 = b2t1.c1)
+ -> Nested Loop
+ Join Filter: (b2t2.c1 = b2t3.c1)
+ -> Nested Loop
+ -> Seq Scan on r2_
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t2 b2t2
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t3 b2t3
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t3.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t3.c1)
+ -> Index Scan using t4_pkey on t4 b2t4
+ Index Cond: (c1 = b2t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+(45 rows)
+
+-- No. S-2-3-3
+EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r3
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r3
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r3
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(59 rows)
+
+/*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey)
+SeqScan(r3)*/
+EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+SeqScan(r3)
+TidScan(t1)
+SeqScan(t2)
+IndexScan(t3 t3_pkey)
+BitmapScan(t4 t4_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+SeqScan(r3)
+TidScan(t1)
+SeqScan(t2)
+IndexScan(t3 t3_pkey)
+BitmapScan(t4 t4_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: available indexes for IndexScan(t3): t3_pkey
+LOG: available indexes for BitmapScan(t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+SeqScan(r3)
+TidScan(t1)
+SeqScan(t2)
+IndexScan(t3 t3_pkey)
+BitmapScan(t4 t4_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Seq Scan on r3
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3
+ Index Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4
+ Recheck Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = t2.c1)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Seq Scan on r3
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3
+ Index Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4
+ Recheck Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = t2.c1)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Seq Scan on r3
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3
+ Index Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4
+ Recheck Cond: (c1 = t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = t2.c1)
+(68 rows)
+
+EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r3_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r3_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t4.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r3_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(59 rows)
+
+/*+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
+BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey)
+IndexScan(b3t1 t1_pkey)BitmapScan(b3t2 t2_pkey)TidScan(b3t3)SeqScan(b3t4)
+SeqScan(r3_)*/
+EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+SeqScan(r3_)
+not used hint:
+BitmapScan(b2t1 t1_pkey)
+TidScan(b2t2)
+SeqScan(b2t3)
+IndexScan(b2t4 t4_pkey)
+IndexScan(b3t1 t1_pkey)
+BitmapScan(b3t2 t2_pkey)
+TidScan(b3t3)
+SeqScan(b3t4)
+duplication hint:
+error hint:
+
+LOG: available indexes for BitmapScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t4): t4_pkey
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(b2t1 t1_pkey)
+TidScan(b2t2)
+SeqScan(b2t3)
+IndexScan(b2t4 t4_pkey)
+SeqScan(r3_)
+not used hint:
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+IndexScan(b3t1 t1_pkey)
+BitmapScan(b3t2 t2_pkey)
+TidScan(b3t3)
+SeqScan(b3t4)
+duplication hint:
+error hint:
+
+LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t2): t2_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(b3t1 t1_pkey)
+BitmapScan(b3t2 t2_pkey)
+TidScan(b3t3)
+SeqScan(b3t4)
+SeqScan(r3_)
+not used hint:
+TidScan(b1t1)
+SeqScan(b1t2)
+IndexScan(b1t3 t3_pkey)
+BitmapScan(b1t4 t4_pkey)
+BitmapScan(b2t1 t1_pkey)
+TidScan(b2t2)
+SeqScan(b2t3)
+IndexScan(b2t4 t4_pkey)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ -> Seq Scan on r3_
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t2.c1 = b2t1.c1)
+ -> Nested Loop
+ Join Filter: (b2t2.c1 = b2t3.c1)
+ -> Nested Loop
+ -> Seq Scan on r3_
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t2 b2t2
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t3 b2t3
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t3.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t3.c1)
+ -> Index Scan using t4_pkey on t4 b2t4
+ Index Cond: (c1 = b2t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t2.c1)
+ -> Nested Loop
+ Join Filter: (b3t3.c1 = b3t1.c1)
+ -> Nested Loop
+ Join Filter: (b3t3.c1 = b3t4.c1)
+ -> Nested Loop
+ -> Seq Scan on r3_
+ Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t4 b3t4
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t1_pkey on t1 b3t1
+ Index Cond: (c1 = b3t4.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t2 b3t2
+ Recheck Cond: (c1 = b3t3.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t2_pkey
+ Index Cond: (c1 = b3t3.c1)
+(68 rows)
+
+-- No. S-2-3-4
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
+ QUERY PLAN
+------------------------------------
+ Hash Join
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
+ -> Seq Scan on t1 v1t1
+ -> Hash
+ -> Seq Scan on t1 v1t1_1
+(5 rows)
+
+/*+BitmapScan(v1t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(v1t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Index Scan using t1_i1 on t1 v1t1
+ -> Bitmap Heap Scan on t1 v1t1_1
+ Recheck Cond: (c1 = v1t1.c1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = v1t1.c1)
+(6 rows)
+
+-- No. S-2-3-5
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+ QUERY PLAN
+-----------------------------------
+ Hash Join
+ Hash Cond: (v1t1.c1 = v1t1_.c1)
+ -> Seq Scan on t1 v1t1
+ -> Hash
+ -> Seq Scan on t1 v1t1_
+(5 rows)
+
+/*+SeqScan(v1t1)BitmapScan(v1t1_)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(v1t1)
+BitmapScan(v1t1_)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Seq Scan on t1 v1t1
+ -> Bitmap Heap Scan on t1 v1t1_
+ Recheck Cond: (c1 = v1t1.c1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = v1t1.c1)
+(6 rows)
+
+-- No. S-2-3-6
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------------
+ Hash Join
+ Hash Cond: (r4t1.c1 = r4t1_1.c1)
+ -> Seq Scan on t1 r4t1
+ -> Hash
+ -> Seq Scan on t1 r4t1_1
+(5 rows)
+
+/*+BitmapScan(r4t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(r4t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Index Scan using t1_i1 on t1 r4t1
+ -> Bitmap Heap Scan on t1 r4t1_1
+ Recheck Cond: (c1 = r4t1.c1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = r4t1.c1)
+(6 rows)
+
+-- No. S-2-3-7
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+----------------------------------
+ Hash Join
+ Hash Cond: (r4t1.c1 = r5t1.c1)
+ -> Seq Scan on t1 r4t1
+ -> Hash
+ -> Seq Scan on t1 r5t1
+(5 rows)
+
+/*+SeqScan(r4t1)BitmapScan(r5t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(r4t1)
+BitmapScan(r5t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Seq Scan on t1 r4t1
+ -> Bitmap Heap Scan on t1 r5t1
+ Recheck Cond: (c1 = r4t1.c1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = r4t1.c1)
+(6 rows)
+
+----
+---- No. S-2-4 VALUES clause
+----
+-- No. S-2-4-1
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+SeqScan(t1)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+/*+SeqScan(*VALUES*)*/
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+SeqScan(*VALUES*)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+-- No. S-2-4-2
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+----------------------------------------------------------
+ Hash Join
+ Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1)
+ -> Values Scan on "*VALUES*"
+ -> Hash
+ -> Values Scan on "*VALUES*_1"
+(5 rows)
+
+/*+SeqScan(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: SeqScan hint accepts only one relation.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+SeqScan(t1 t2)
+
+ QUERY PLAN
+----------------------------------------------------------
+ Hash Join
+ Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1)
+ -> Values Scan on "*VALUES*"
+ -> Hash
+ -> Values Scan on "*VALUES*_1"
+(5 rows)
+
+/*+SeqScan(*VALUES*)*/
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+SeqScan(*VALUES*)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------
+ Hash Join
+ Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1)
+ -> Values Scan on "*VALUES*"
+ -> Hash
+ -> Values Scan on "*VALUES*_1"
+(5 rows)
+
+----
+---- No. S-3-1 scan method hint
+----
+-- No. S-3-1-1
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 >= 1)
+(2 rows)
+
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 >= 1)
+(2 rows)
+
+-- No. S-3-1-2
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
+
+-- No. S-3-1-3
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+IndexScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+-- No. S-3-1-4
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 >= 1)
+(2 rows)
+
+/*+IndexScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 >= 1)
+(2 rows)
+
+-- No. S-3-1-5
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
+ QUERY PLAN
+---------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c3 < 10)
+ -> Bitmap Index Scan on t1_i
+ Index Cond: (c3 < 10)
+(4 rows)
+
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c3 < 10)
+ -> Bitmap Index Scan on t1_i
+ Index Cond: (c3 < 10)
+(4 rows)
+
+-- No. S-3-1-6
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 = 1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = 1)
+(4 rows)
+
+-- No. S-3-1-7
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 rows)
+
+/*+TidScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+TidScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 rows)
+
+-- No. S-3-1-8
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)');
+ QUERY PLAN
+-------------------------------------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+ Filter: (ctid = ANY ('{"(1,1)","(2,2)","(3,3)"}'::tid[]))
+(3 rows)
+
+/*+TidScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)');
+LOG: pg_hint_plan:
+used hint:
+TidScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------------------
+ Tid Scan on t1
+ TID Cond: (ctid = ANY ('{"(1,1)","(2,2)","(3,3)"}'::tid[]))
+ Filter: (c1 = 1)
+(3 rows)
+
+-- No. S-3-1-9
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 >= 1)
+(2 rows)
+
+/*+NoSeqScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+LOG: pg_hint_plan:
+used hint:
+NoSeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 >= 1)
+(2 rows)
+
+-- No. S-3-1-10
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+NoSeqScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+NoSeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+-- No. S-3-1-11
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+NoIndexScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+NoIndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 = 1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = 1)
+(4 rows)
+
+-- No. S-3-1-12
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 >= 1)
+(2 rows)
+
+/*+NoIndexScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+LOG: pg_hint_plan:
+used hint:
+NoIndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 >= 1)
+(2 rows)
+
+-- No. S-3-1-13
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
+ QUERY PLAN
+---------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c3 < 10)
+ -> Bitmap Index Scan on t1_i
+ Index Cond: (c3 < 10)
+(4 rows)
+
+/*+NoBitmapScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
+LOG: pg_hint_plan:
+used hint:
+NoBitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c3 < 10)
+(2 rows)
+
+-- No. S-3-1-14
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+NoBitmapScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+NoBitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+-- No. S-3-1-15
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 rows)
+
+/*+NoTidScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+NoTidScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+ Filter: (ctid = '(1,1)'::tid)
+(3 rows)
+
+-- No. S-3-1-16
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+NoTidScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+NoTidScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+-- No. S-3-1-17
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+-----------------------------------
+ Index Only Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+IndexOnlyScan(t1)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------
+ Index Only Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+-- No. S-3-1-18
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 >= 1)
+(2 rows)
+
+/*+IndexOnlyScan(t1)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1;
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------
+ Index Only Scan using t1_i1 on t1
+ Index Cond: (c1 >= 1)
+(2 rows)
+
+-- No. S-3-1-19
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+-----------------------------------
+ Index Only Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+NoIndexOnlyScan(t1)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+NoIndexOnlyScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+-- No. S-3-1-20
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 >= 1)
+(2 rows)
+
+/*+NoIndexOnlyScan(t1)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1;
+LOG: pg_hint_plan:
+used hint:
+NoIndexOnlyScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 >= 1)
+(2 rows)
+
+----
+---- No. S-3-3 index name specified
+----
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c2 = 1)
+(3 rows)
+
+SET enable_tidscan TO off;
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+---------------------------------
+ Index Scan using ti1_i4 on ti1
+ Index Cond: (c2 = 1)
+ Filter: (ctid = '(1,1)'::tid)
+(3 rows)
+
+SET enable_indexscan TO off;
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c2 = 1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on ti1_i4
+ Index Cond: (c2 = 1)
+(5 rows)
+
+RESET enable_tidscan;
+RESET enable_indexscan;
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on ti1
+ Filter: (c2 >= 1)
+(2 rows)
+
+-- No. S-3-3-1
+/*+IndexScan(ti1 ti1_i3)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_i3
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_i3)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------
+ Index Scan using ti1_i3 on ti1
+ Index Cond: (c2 = 1)
+ Filter: (ctid = '(1,1)'::tid)
+(3 rows)
+
+-- No. S-3-3-2
+/*+IndexScan(ti1 ti1_i3 ti1_i2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_i3 ti1_i2
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_i3 ti1_i2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------
+ Index Scan using ti1_i3 on ti1
+ Index Cond: (c2 = 1)
+ Filter: (ctid = '(1,1)'::tid)
+(3 rows)
+
+-- No. S-3-3-3
+/*+IndexScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------
+ Index Scan using ti1_i4 on ti1
+ Index Cond: (c2 = 1)
+ Filter: (ctid = '(1,1)'::tid)
+(3 rows)
+
+-- No. S-3-3-4
+/*+BitmapScan(ti1 ti1_i3)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_i3
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_i3)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c2 = 1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on ti1_i3
+ Index Cond: (c2 = 1)
+(5 rows)
+
+-- No. S-3-3-5
+/*+BitmapScan(ti1 ti1_i3 ti1_i2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_i3 ti1_i2
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_i3 ti1_i2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c2 = 1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on ti1_i3
+ Index Cond: (c2 = 1)
+(5 rows)
+
+-- No. S-3-3-6
+/*+BitmapScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c2 = 1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on ti1_i4
+ Index Cond: (c2 = 1)
+(5 rows)
+
+-- No. S-3-3-7
+/*+IndexOnlyScan(ti1 ti1_i3)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_i3
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_i3)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------
+ Index Only Scan using ti1_i3 on ti1
+ Index Cond: (c2 >= 1)
+(2 rows)
+
+-- No. S-3-3-8
+/*+IndexOnlyScan(ti1 ti1_i3 ti1_i2)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_i3 ti1_i2
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_i3 ti1_i2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------
+ Index Only Scan using ti1_i2 on ti1
+ Index Cond: (c2 >= 1)
+(2 rows)
+
+-- No. S-3-3-9
+/*+IndexOnlyScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------
+ Index Only Scan using ti1_i1 on ti1
+ Index Cond: (c2 >= 1)
+(2 rows)
+
+----
+---- No. S-3-4 index type
+----
+\d s1.ti1
+ Table "s1.ti1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ c1 | integer | | not null |
+ c2 | integer | | |
+ c3 | integer | | |
+ c4 | text | | |
+Indexes:
+ "ti1_pkey" PRIMARY KEY, btree (c1)
+ "ti1_btree" btree (c1)
+ "ti1_c2_key" UNIQUE CONSTRAINT, btree (c2)
+ "ti1_expr" btree ((c1 < 100))
+ "ti1_gin" gin (c1)
+ "ti1_gist" gist (c1)
+ "ti1_hash" hash (c1)
+ "ti1_i1" btree (c2)
+ "ti1_i2" btree (c2, c4)
+ "ti1_i3" btree (c2, c4, c4)
+ "ti1_i4" btree (c2, c4, c4, c4)
+ "ti1_multi" btree (c1, c2, c3, c4)
+ "ti1_pred" btree (lower(c4))
+ "ti1_ts" gin (to_tsvector('english'::regconfig, c4))
+ "ti1_uniq" UNIQUE, btree (c1)
+
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-1
+/*+IndexScan(ti1 ti1_btree)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_btree
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_btree)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Index Scan using ti1_btree on ti1
+ Index Cond: (c1 < 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-2
+/*+IndexScan(ti1 ti1_hash)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_hash
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_hash)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Index Scan using ti1_hash on ti1
+ Index Cond: (c1 = 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-3
+/*+IndexScan(ti1 ti1_gist)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_gist
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_gist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Index Scan using ti1_gist on ti1
+ Index Cond: (c1 < 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-4
+/*+IndexScan(ti1 ti1_gin)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_gin
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_gin)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-5
+/*+IndexScan(ti1 ti1_expr)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_expr
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_expr)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Index Scan using ti1_expr on ti1
+ Index Cond: ((c1 < 100) = true)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-6
+/*+IndexScan(ti1 ti1_pred)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_pred
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_pred)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------
+ Index Scan using ti1_pred on ti1
+ Index Cond: (lower(c4) = '1'::text)
+ Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-7
+/*+IndexScan(ti1 ti1_uniq)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_uniq
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_uniq)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Index Scan using ti1_uniq on ti1
+ Index Cond: (c1 < 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-8
+/*+IndexScan(ti1 ti1_multi)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_multi
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_multi)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------
+ Index Scan using ti1_multi on ti1
+ Index Cond: ((c1 < 100) AND (c2 = 1))
+ Filter: ((ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-9
+/*+IndexScan(ti1 ti1_ts)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_ts
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_ts)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-10
+/*+IndexScan(ti1 ti1_pkey)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Index Scan using ti1_pkey on ti1
+ Index Cond: (c1 < 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-11
+/*+IndexScan(ti1 ti1_c2_key)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for IndexScan(ti1): ti1_c2_key
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_c2_key)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ Index Scan using ti1_c2_key on ti1
+ Index Cond: (c2 = 1)
+ Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+(3 rows)
+
+-- No. S-3-4-12
+/*+BitmapScan(ti1 ti1_btree)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_btree
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_btree)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c1 < 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ -> Bitmap Index Scan on ti1_btree
+ Index Cond: (c1 < 100)
+(5 rows)
+
+-- No. S-3-4-13
+/*+BitmapScan(ti1 ti1_hash)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_hash
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_hash)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c1 = 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ -> Bitmap Index Scan on ti1_hash
+ Index Cond: (c1 = 100)
+(5 rows)
+
+-- No. S-3-4-14
+/*+BitmapScan(ti1 ti1_gist)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_gist
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_gist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c1 < 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ -> Bitmap Index Scan on ti1_gist
+ Index Cond: (c1 < 100)
+(5 rows)
+
+-- No. S-3-4-15
+/*+BitmapScan(ti1 ti1_gin)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_gin
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_gin)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c1 < 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ -> Bitmap Index Scan on ti1_gin
+ Index Cond: (c1 < 100)
+(5 rows)
+
+-- No. S-3-4-16
+/*+BitmapScan(ti1 ti1_expr)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_expr
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_expr)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ -> Bitmap Index Scan on ti1_expr
+ Index Cond: ((c1 < 100) = true)
+(4 rows)
+
+-- No. S-3-4-17
+/*+BitmapScan(ti1 ti1_pred)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_pred
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_pred)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (lower(c4) = '1'::text)
+ Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ -> Bitmap Index Scan on ti1_pred
+ Index Cond: (lower(c4) = '1'::text)
+(5 rows)
+
+-- No. S-3-4-18
+/*+BitmapScan(ti1 ti1_uniq)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_uniq
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_uniq)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c1 < 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ -> Bitmap Index Scan on ti1_uniq
+ Index Cond: (c1 < 100)
+(5 rows)
+
+-- No. S-3-4-19
+/*+BitmapScan(ti1 ti1_multi)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_multi
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_multi)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: ((c1 < 100) AND (c2 = 1))
+ Filter: ((ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ -> Bitmap Index Scan on ti1_multi
+ Index Cond: ((c1 < 100) AND (c2 = 1))
+(5 rows)
+
+-- No. S-3-4-20
+/*+BitmapScan(ti1 ti1_ts)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_ts
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_ts)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)
+ Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text))
+ -> Bitmap Index Scan on ti1_ts
+ Index Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)
+(5 rows)
+
+-- No. S-3-4-21
+/*+BitmapScan(ti1 ti1_pkey)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_pkey
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c1 < 100)
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ -> Bitmap Index Scan on ti1_pkey
+ Index Cond: (c1 < 100)
+(5 rows)
+
+-- No. S-3-4-22
+/*+BitmapScan(ti1 ti1_c2_key)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
+LOG: available indexes for BitmapScan(ti1): ti1_c2_key
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_c2_key)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c2 = 1)
+ Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ -> Bitmap Index Scan on ti1_c2_key
+ Index Cond: (c2 = 1)
+(5 rows)
+
+-- No. S-3-4-23
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on ti1
+ Filter: (c1 >= 1)
+(2 rows)
+
+/*+IndexOnlyScan(ti1 ti1_btree)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_btree
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_btree)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------
+ Index Only Scan using ti1_btree on ti1
+ Index Cond: (c1 >= 1)
+(2 rows)
+
+-- No. S-3-4-24
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+IndexOnlyScan(ti1 ti1_hash)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_hash
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_hash)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+-- No. S-3-4-25
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 1;
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using ti1_uniq on ti1
+ Index Cond: (c1 < 1)
+(2 rows)
+
+/*+IndexOnlyScan(ti1 ti1_gist)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_gist
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_gist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using ti1_gist on ti1
+ Index Cond: (c1 < 1)
+(2 rows)
+
+-- No. S-3-4-26
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+/*+IndexOnlyScan(ti1 ti1_gin)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_gin
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_gin)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c1 = 1)
+ -> Bitmap Index Scan on ti1_gin
+ Index Cond: (c1 = 1)
+(4 rows)
+
+-- No. S-3-4-27
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 100;
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using ti1_uniq on ti1
+ Index Cond: (c1 < 100)
+(2 rows)
+
+/*+IndexOnlyScan(ti1 ti1_expr)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 100;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_expr
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_expr)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------
+ Index Scan using ti1_expr on ti1
+ Index Cond: ((c1 < 100) = true)
+(2 rows)
+
+-- No. S-3-4-28
+EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1';
+ QUERY PLAN
+------------------------------------
+ Seq Scan on ti1
+ Filter: (lower(c4) >= '1'::text)
+(2 rows)
+
+/*+IndexOnlyScan(ti1 ti1_pred)*/
+EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1';
+LOG: available indexes for IndexOnlyScan(ti1): ti1_pred
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_pred)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------
+ Index Scan using ti1_pred on ti1
+ Index Cond: (lower(c4) >= '1'::text)
+(2 rows)
+
+-- No. S-3-4-29
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on ti1
+ Filter: (c1 >= 1)
+(2 rows)
+
+/*+IndexOnlyScan(ti1 ti1_uniq)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_uniq
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_uniq)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using ti1_uniq on ti1
+ Index Cond: (c1 >= 1)
+(2 rows)
+
+-- No. S-3-4-30
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on ti1
+ Filter: (c1 >= 1)
+(2 rows)
+
+/*+IndexOnlyScan(ti1 ti1_multi)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_multi
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_multi)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------
+ Index Only Scan using ti1_multi on ti1
+ Index Cond: (c1 >= 1)
+(2 rows)
+
+-- No. S-3-4-31
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE to_tsvector('english', c4) @@ 'a & b';
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)
+ -> Bitmap Index Scan on ti1_ts
+ Index Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)
+(4 rows)
+
+/*+IndexOnlyScan(ti1 ti1_ts)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE to_tsvector('english', c4) @@ 'a & b';
+LOG: available indexes for IndexOnlyScan(ti1): ti1_ts
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_ts)
+not used hint:
+duplication hint:
+error hint:
--- No. S-3-1-6
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)
+ -> Bitmap Index Scan on ti1_ts
+ Index Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)
+(4 rows)
+
+-- No. S-3-4-32
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on ti1
+ Filter: (c1 >= 1)
+(2 rows)
+
+/*+IndexOnlyScan(ti1 ti1_pkey)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using ti1_pkey on ti1
+ Index Cond: (c1 >= 1)
+(2 rows)
+
+-- No. S-3-4-33
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 >= 1;
+ QUERY PLAN
+---------------------
+ Seq Scan on ti1
+ Filter: (c2 >= 1)
+(2 rows)
+
+/*+IndexOnlyScan(ti1 ti1_c2_key)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 >= 1;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_c2_key
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_c2_key)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------
+ Index Only Scan using ti1_c2_key on ti1
+ Index Cond: (c2 >= 1)
+(2 rows)
+
+----
+---- No. S-3-5 not used index
+----
+-- No. S-3-5-1
+\o results/ut-S.tmpout
+/*+IndexScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for IndexScan(ti1): ti1_pred
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 ti1_pred)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c1 = 100)
+
+-- No. S-3-5-2
+\o results/ut-S.tmpout
+/*+BitmapScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for BitmapScan(ti1): ti1_pred
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 ti1_pred)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c1 = 100)
+
+-- No. S-3-5-3
+\o results/ut-S.tmpout
+/*+IndexOnlyScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for IndexOnlyScan(ti1): ti1_pred
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 ti1_pred)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c1 = 100)
+
+-- No. S-3-5-4
+\o results/ut-S.tmpout
+/*+IndexScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for IndexScan(ti1):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(ti1 not_exist)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c1 = 100)
+
+-- No. S-3-5-5
+\o results/ut-S.tmpout
+/*+BitmapScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for BitmapScan(ti1):
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(ti1 not_exist)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c1 = 100)
+
+-- No. S-3-5-6
+\o results/ut-S.tmpout
+/*+IndexOnlyScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100;
+LOG: available indexes for IndexOnlyScan(ti1):
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1 not_exist)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c1 = 100)
+
+-- No. S-3-5-7
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
+ 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;
+\o results/ut-S.tmpout
+/*+TidScan(t1)*/ EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
used hint:
-BitmapScan(t1)
+TidScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------
- Bitmap Heap Scan on t1
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = 1)
-(4 rows)
-
--- 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)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Seq Scan on t1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 1)
-(3 rows)
-/*+TidScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
+----
+---- No. S-3-6 query structure
+----
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------
+ Hash Join
+ Hash Cond: (t2.c1 = t1.c1)
+ -> Seq Scan on t2
+ -> Hash
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+(6 rows)
+
+-- No. S-3-6-1
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE c1 = 100;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------
+ Seq Scan on t1
+ Filter: (c1 = 100)
+(2 rows)
+
+-- No. S-3-6-2
+/*+SeqScan(t1)BitmapScan(t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+BitmapScan(t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------
+ Nested Loop
+ -> Seq Scan on t1
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t2
+ Recheck Cond: (c1 = t1.c1)
+ -> Bitmap Index Scan on t2_i1
+ Index Cond: (c1 = t1.c1)
+(7 rows)
+
+-- No. S-3-6-3
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------
+ Hash Join
+ Hash Cond: (t2.c1 = t1.c1)
+ -> Seq Scan on t2
+ -> Hash
+ -> Seq Scan on t1
+ Filter: (ctid = '(1,1)'::tid)
+(6 rows)
+
+----
+---- No. S-3-7 number of tables in a query block
+----
+-- No. S-3-7-1
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.c1 = 1)
+SELECT max(b3t1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+ ) FROM s1.t1 b3t1 WHERE b3t1.c1 = (
+SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
+ QUERY PLAN
+---------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Index Only Scan using t1_i1 on t1 b2t1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ InitPlan 4 (returns $3)
+ -> Result
+ InitPlan 3 (returns $2)
+ -> Limit
+ -> Index Only Scan using t1_i1 on t1 b4t1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ -> Index Only Scan using t1_i1 on t1 b3t1
+ Index Cond: (c1 = $3)
+(15 rows)
+
+/*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.c1 = 1)
+SELECT max(b3t1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+ ) FROM s1.t1 b3t1 WHERE b3t1.c1 = (
+SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
+LOG: available indexes for IndexScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t1): t1_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(b2t1 t1_pkey)
+BitmapScan(b3t1 t1_pkey)
+TidScan(b4t1)
+not used hint:
+SeqScan(b1t1)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Index Scan using t1_pkey on t1 b2t1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ InitPlan 4 (returns $3)
+ -> Result
+ InitPlan 3 (returns $2)
+ -> Limit
+ -> Seq Scan on t1 b4t1
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+ -> Bitmap Heap Scan on t1 b3t1
+ Recheck Cond: (c1 = $3)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = $3)
+(17 rows)
+
+-- No. S-3-7-2
+EXPLAIN (COSTS false)
+WITH cte1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1)
+SELECT max(b3t1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 JOIN s1.t2 b2t2 ON(b2t1.c1 = b2t2.c1) WHERE b2t1.c1 = 1
+ ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
+SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE b4t1.c1 = 1);
+ QUERY PLAN
+----------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Aggregate
+ -> Nested Loop
+ -> Index Only Scan using t1_i1 on t1 b2t1
+ Index Cond: (c1 = 1)
+ -> Seq Scan on t2 b2t2
+ Filter: (c1 = 1)
+ InitPlan 2 (returns $1)
+ -> Aggregate
+ -> Nested Loop
+ -> Index Only Scan using t1_i1 on t1 b4t1
+ Index Cond: (c1 = 1)
+ -> Seq Scan on t2 b4t2
+ Filter: (c1 = 1)
+ -> Nested Loop
+ -> Nested Loop
+ -> Index Only Scan using t1_i1 on t1 b3t1
+ Index Cond: (c1 = $1)
+ -> Seq Scan on t2 b3t2
+ Filter: (c1 = $1)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $1)
+ -> Nested Loop
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ Index Cond: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(28 rows)
+
+/*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
+TidScan(b1t2)SeqScan(b2t2)IndexScan(b3t2 t2_pkey)BitmapScan(b4t2 t2_pkey)
+*/
+EXPLAIN (COSTS false)
+WITH cte1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1)
+SELECT max(b3t1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 JOIN s1.t2 b2t2 ON(b2t1.c1 = b2t2.c1) WHERE b2t1.c1 = 1
+ ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
+SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE b4t1.c1 = 1);
+LOG: available indexes for IndexScan(b2t1): t1_pkey
+LOG: available indexes for BitmapScan(b4t2): t2_pkey
+LOG: available indexes for BitmapScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(b3t2): t2_pkey
LOG: pg_hint_plan:
used hint:
-TidScan(t1)
+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
------------------------------------
- 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_pkey on t1
- Index Cond: (c1 = 1)
- Filter: (ctid = ANY ('{"(1,1)","(2,2)","(3,3)"}'::tid[]))
-(3 rows)
+ QUERY PLAN
+---------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Aggregate
+ -> Nested Loop
+ -> Index Scan using t1_pkey on t1 b2t1
+ Index Cond: (c1 = 1)
+ -> Seq Scan on t2 b2t2
+ Filter: (c1 = 1)
+ InitPlan 2 (returns $1)
+ -> Aggregate
+ -> Nested Loop
+ -> Seq Scan on t1 b4t1
+ Filter: (c1 = 1)
+ -> Bitmap Heap Scan on t2 b4t2
+ Recheck Cond: (c1 = 1)
+ -> Bitmap Index Scan on t2_pkey
+ Index Cond: (c1 = 1)
+ -> Nested Loop
+ -> Nested Loop
+ -> Bitmap Heap Scan on t1 b3t1
+ Recheck Cond: (c1 = $1)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = $1)
+ -> Index Scan using t2_pkey on t2 b3t2
+ Index Cond: (c1 = $1)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $1)
+ -> Nested Loop
+ -> Seq Scan on t1 b1t1
+ Filter: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(32 rows)
-/*+TidScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)');
+-- No. S-3-7-3
+EXPLAIN (COSTS false)
+WITH cte1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1)
+SELECT max(b3t1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+ ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
+SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
+ QUERY PLAN
+---------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Index Only Scan using t1_i1 on t1 b2t1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ InitPlan 4 (returns $3)
+ -> Result
+ InitPlan 3 (returns $2)
+ -> Limit
+ -> Index Only Scan using t1_i1 on t1 b4t1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ -> Nested Loop
+ -> Nested Loop
+ -> Index Only Scan using t1_i1 on t1 b3t1
+ Index Cond: (c1 = $3)
+ -> Seq Scan on t2 b3t2
+ Filter: (c1 = $3)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $3)
+ -> Nested Loop
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ Index Cond: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(26 rows)
+
+/*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
+TidScan(b1t2)IndexScan(b3t2 t2_pkey)
+*/
+EXPLAIN (COSTS false)
+WITH cte1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1)
+SELECT max(b3t1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+ ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
+SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
+LOG: available indexes for IndexScan(b2t1): t1_pkey
+LOG: available indexes for IndexScan(b2t1): t1_pkey
+LOG: available indexes for BitmapScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(b3t2): t2_pkey
LOG: pg_hint_plan:
used hint:
-TidScan(t1)
+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
----------------------------------------------------------------
- Tid Scan on t1
- TID Cond: (ctid = ANY ('{"(1,1)","(2,2)","(3,3)"}'::tid[]))
- Filter: (c1 = 1)
-(3 rows)
+ QUERY PLAN
+---------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Index Scan using t1_pkey on t1 b2t1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+ InitPlan 4 (returns $3)
+ -> Result
+ InitPlan 3 (returns $2)
+ -> Limit
+ -> Seq Scan on t1 b4t1
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+ -> Nested Loop
+ -> Nested Loop
+ -> Bitmap Heap Scan on t1 b3t1
+ Recheck Cond: (c1 = $3)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = $3)
+ -> Index Scan using t2_pkey on t2 b3t2
+ Index Cond: (c1 = $3)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $3)
+ -> Nested Loop
+ -> Seq Scan on t1 b1t1
+ Filter: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(28 rows)
--- No. S-3-1-9
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
- QUERY PLAN
----------------------
- Seq Scan on t1
- Filter: (c1 >= 1)
+----
+---- 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)
-/*+NoSeqScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+/*+IndexScan(p1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1;
LOG: pg_hint_plan:
used hint:
-NoSeqScan(t1)
+IndexScan(p1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey 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_pkey on t1
+ QUERY PLAN
+-----------------------------
+ Index Scan using p1_i on p1
Index Cond: (c1 = 1)
(2 rows)
-/*+NoSeqScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+-- No. S-3-8-2
+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
+ QUERY PLAN
+-----------------------------
+ Append
+ -> Seq Scan on p1 p1_1
+ Filter: (c1 = 1)
+ -> Seq Scan on p1c1 p1_2
+ Filter: (c1 = 1)
+(5 rows)
+
+/*+IndexScan(p1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
LOG: pg_hint_plan:
used hint:
-NoSeqScan(t1)
+IndexScan(p1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
-(2 rows)
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Index Scan using p1_i on p1 p1_1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_i on p1c1 p1_2
+ Index Cond: (c1 = 1)
+(5 rows)
--- No. S-3-1-11
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
-(2 rows)
+-- No. S-3-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)
-/*+NoIndexScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+/*+IndexScan(p1)*/
+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
LOG: pg_hint_plan:
used hint:
-NoIndexScan(t1)
+IndexScan(p1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------
- Bitmap Heap Scan on t1
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t1_pkey
+ QUERY PLAN
+-----------------------------------
+ Update on p1
+ -> Index Scan using p1_i on p1
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)
+(3 rows)
-/*+NoIndexScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
+/*+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:
-NoIndexScan(t1)
+IndexScan(p1 p1_pkey)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
----------------------
- Seq Scan on t1
- Filter: (c1 >= 1)
-(2 rows)
+ QUERY PLAN
+--------------------------------------
+ Update on p1
+ -> Index Scan using p1_pkey on p1
+ Index Cond: (c1 = 1)
+(3 rows)
--- No. S-3-1-13
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
- QUERY PLAN
----------------------------------
- Bitmap Heap Scan on t1
- Recheck Cond: (c3 < 10)
- -> Bitmap Index Scan on t1_i
- Index Cond: (c3 < 10)
-(4 rows)
+-- No. S-3-8-4
+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
+ QUERY PLAN
+-----------------------------
+ Update on p1
+ Update on p1
+ Update on p1c1 p1_1
+ -> Seq Scan on p1
+ Filter: (c1 = 1)
+ -> Seq Scan on p1c1 p1_1
+ Filter: (c1 = 1)
+(7 rows)
-/*+NoBitmapScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
+/*+IndexScan(p1)*/
+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
LOG: pg_hint_plan:
used hint:
-NoBitmapScan(t1)
+IndexScan(p1)
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_pkey on t1
- Index Cond: (c1 = 1)
-(2 rows)
+ QUERY PLAN
+--------------------------------------------
+ Update on p1
+ Update on p1
+ Update on p1c1 p1_1
+ -> Index Scan using p1_i on p1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_i on p1c1 p1_1
+ Index Cond: (c1 = 1)
+(7 rows)
-/*+NoBitmapScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+/*+IndexScan(p1 p1_pkey)*/
+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
+LOG: available indexes for IndexScan(p1): p1_pkey
+LOG: available indexes for IndexScan(p1c1): p1c1_pkey
+LOG: available indexes for IndexScan(p1): p1_pkey
+LOG: available indexes for IndexScan(p1c1): p1c1_pkey
LOG: pg_hint_plan:
used hint:
-NoBitmapScan(t1)
+IndexScan(p1 p1_pkey)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
-(2 rows)
+ QUERY PLAN
+-----------------------------------------------
+ Update on p1
+ Update on p1
+ Update on p1c1 p1_1
+ -> Index Scan using p1_pkey on p1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_pkey on p1c1 p1_1
+ Index Cond: (c1 = 1)
+(7 rows)
--- No. S-3-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)
+----
+---- No. S-3-9 inheritance table number
+----
+-- No. S-3-9-1
+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
+ QUERY PLAN
+-----------------------------
+ Append
+ -> Seq Scan on p1 p1_1
+ Filter: (c1 = 1)
+ -> Seq Scan on p1c1 p1_2
+ Filter: (c1 = 1)
+(5 rows)
-/*+NoTidScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
+/*+IndexScan(p1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
LOG: pg_hint_plan:
used hint:
-NoTidScan(t1)
+IndexScan(p1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
----------------------------------
- Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
- Filter: (ctid = '(1,1)'::tid)
-(3 rows)
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Index Scan using p1_i on p1 p1_1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_i on p1c1 p1_2
+ Index Cond: (c1 = 1)
+(5 rows)
--- No. S-3-1-16
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
-(2 rows)
+-- No. S-3-9-2
+EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on p2 p2_1
+ Filter: (c1 = 1)
+ -> Seq Scan on p2c1 p2_2
+ Filter: (c1 = 1)
+ -> Seq Scan on p2c1c1 p2_3
+ Filter: (c1 = 1)
+(7 rows)
-/*+NoTidScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+/*+IndexScan(p2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
LOG: pg_hint_plan:
used hint:
-NoTidScan(t1)
+IndexScan(p2)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------------------
- Index Scan using t1_pkey on t1
- Index Cond: (c1 = 1)
-(2 rows)
+ QUERY PLAN
+------------------------------------------------
+ Append
+ -> Index Scan using p2_i on p2 p2_1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p2c1_i on p2c1 p2_2
+ Index Cond: (c1 = 1)
+ -> Index Scan using p2c1c1_i on p2c1c1 p2_3
+ Index Cond: (c1 = 1)
+(7 rows)
----
----- No. S-3-3 index name specified
+---- No. S-3-10 inheritance table specified table
----
-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)
+EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on p2 p2_1
+ Filter: (c1 = 1)
+ -> Seq Scan on p2c1 p2_2
+ Filter: (c1 = 1)
+ -> Seq Scan on p2c1c1 p2_3
+ Filter: (c1 = 1)
+(7 rows)
-SET enable_tidscan TO off;
-EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
- QUERY PLAN
----------------------------------
- Index Scan using ti1_i2 on ti1
- Index Cond: (c2 = 1)
- Filter: (ctid = '(1,1)'::tid)
-(3 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:
-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_i2
- Index Cond: (c2 = 1)
-(5 rows)
+ QUERY PLAN
+------------------------------------------------
+ Append
+ -> Index Scan using p2_i on p2 p2_1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p2c1_i on p2c1 p2_2
+ Index Cond: (c1 = 1)
+ -> Index Scan using p2c1c1_i on p2c1c1 p2_3
+ Index Cond: (c1 = 1)
+(7 rows)
-RESET enable_tidscan;
-RESET enable_indexscan;
--- No. S-3-3-1
-/*+IndexScan(ti1 ti1_i3)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+-- No. S-3-10-2
+/*+IndexScan(p2c1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
LOG: pg_hint_plan:
used hint:
-IndexScan(ti1 ti1_i3)
+IndexScan(p2c1)
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)
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Seq Scan on p2 p2_1
+ Filter: (c1 = 1)
+ -> Index Scan using p2c1_i on p2c1 p2_2
+ Index Cond: (c1 = 1)
+ -> Seq Scan on p2c1c1 p2_3
+ Filter: (c1 = 1)
+(7 rows)
--- No. S-3-3-2
-/*+IndexScan(ti1 ti1_i3 ti1_i2)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+-- No. S-3-10-3
+\o results/ut-S.tmpout
+EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Append (cost=xxx..xxx rows=4 width=xxx)
+ -> Seq Scan on p1 p1_1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: ((c1 < 10) AND ((c2 * 2) < 100))
+ -> Seq Scan on p1c1 p1_2 (cost=xxx..xxx rows=3 width=xxx)
+ Filter: ((c1 < 10) AND ((c2 * 2) < 100))
+
+\o results/ut-S.tmpout
+/*+IndexScan(p1 p1_parent)*/ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
+LOG: available indexes for IndexScan(p1): p1_parent
+LOG: available indexes for IndexScan(p1c1): p1c1_c4_expr_idx
LOG: pg_hint_plan:
used hint:
-IndexScan(ti1 ti1_i3 ti1_i2)
+IndexScan(p1 p1_parent)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
----------------------------------
- Index Scan using ti1_i2 on ti1
- Index Cond: (c2 = 1)
- Filter: (ctid = '(1,1)'::tid)
-(3 rows)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Append (cost=xxx..xxx rows=4 width=xxx)
+ -> Index Scan using p1_parent on p1 p1_1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: ((c2 * 2) < 100)
+ -> Index Scan using p1c1_c4_expr_idx on p1c1 p1_2 (cost=xxx..xxx rows=3 width=xxx)
+ Filter: ((c2 * 2) < 100)
+
+-- No. S-3-10-4
+\o results/ut-S.tmpout
+/*+IndexScan(p1 p1_i2)*/ EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1;
+LOG: available indexes for IndexScan(p1): p1_i2
+LOG: available indexes for IndexScan(p1c1):
+LOG: available indexes for IndexScan(p1c2):
+LOG: available indexes for IndexScan(p1c3):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p1 p1_i2)
+not used hint:
+duplication hint:
+error hint:
--- 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)';
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Append (cost=xxx..xxx rows=4 width=xxx)
+ -> Index Scan using p1_i2 on p1 p1_1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c2 = 1)
+ -> Seq Scan on p1c1 p1_2 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c2 = 1)
+ -> Seq Scan on p1c2 p1_3 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c2 = 1)
+ -> Seq Scan on p1c3 p1_4 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c2 = 1)
+
+-- No. S-3-10-5
+\o results/ut-S.tmpout
+/*+IndexScan(p2 p2c1_pkey)*/ EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1;
+LOG: available indexes for IndexScan(p2):
+LOG: available indexes for IndexScan(p2c1): p2c1_pkey
+LOG: available indexes for IndexScan(p2c1c1):
LOG: pg_hint_plan:
used hint:
-IndexScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)
+IndexScan(p2 p2c1_pkey)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
----------------------------------
- Index Scan using ti1_i2 on ti1
- Index Cond: (c2 = 1)
- Filter: (ctid = '(1,1)'::tid)
-(3 rows)
+\o
+\! sql/maskout.sh results/ut-S.tmpout
+ QUERY PLAN
+----------------
+ Append (cost={inf}..{inf} rows=3 width=xxx)
+ -> Seq Scan on p2 p2_1 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c1 = 1)
+ -> Index Scan using p2c1_pkey on p2c1 p2_2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+ -> Seq Scan on p2c1c1 p2_3 (cost={inf}..{inf} rows=1 width=xxx)
+ Filter: (c1 = 1)
--- No. S-3-3-4
-/*+BitmapScan(ti1 ti1_i3)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
+----
+---- No. S-3-12 specified same table
+----
+-- No. S-3-12-1
+/*+IndexScan(ti1) BitmapScan(ti1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
+INFO: pg_hint_plan: hint syntax error at or near "IndexScan(ti1) BitmapScan(ti1)"
+DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
-BitmapScan(ti1 ti1_i3)
+BitmapScan(ti1)
not used hint:
duplication hint:
+IndexScan(ti1)
error hint:
- QUERY PLAN
------------------------------------
+ QUERY PLAN
+-------------------------------------
Bitmap Heap Scan on ti1
- Recheck Cond: (c2 = 1)
+ Recheck Cond: (c1 = 1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on ti1_i3
- Index Cond: (c2 = 1)
+ -> Bitmap Index Scan on ti1_hash
+ Index Cond: (c1 = 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)';
+-- 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_i3 ti1_i2)
+BitmapScan(ti1 ti1_btree)
not used hint:
duplication hint:
+IndexScan(ti1 ti1_pkey)
error hint:
- QUERY PLAN
------------------------------------
+ QUERY PLAN
+--------------------------------------
Bitmap Heap Scan on ti1
- Recheck Cond: (c2 = 1)
+ Recheck Cond: (c1 = 1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on ti1_i2
- Index Cond: (c2 = 1)
+ -> Bitmap Index Scan on ti1_btree
+ Index Cond: (c1 = 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)';
+-- 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 ti1_i4 ti1_i3 ti1_i2 ti1_i1)
+BitmapScan(ti1)
not used hint:
duplication hint:
+BitmapScan(ti1)
+IndexScan(ti1)
error hint:
- QUERY PLAN
------------------------------------
+ QUERY PLAN
+-------------------------------------
Bitmap Heap Scan on ti1
- Recheck Cond: (c2 = 1)
+ Recheck Cond: (c1 = 1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on ti1_i2
- Index Cond: (c2 = 1)
+ -> Bitmap Index Scan on ti1_hash
+ Index Cond: (c1 = 1)
(5 rows)
-----
----- No. S-3-4 index type
-----
-\d s1.ti1
- Table "s1.ti1"
- Column | Type | Modifiers
---------+---------+-----------
- c1 | integer | not null
- c2 | integer |
- c3 | integer |
- c4 | text |
-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 (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
-(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)';
+-- 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:
-IndexScan(ti1 ti1_btree)
+BitmapScan(ti1 ti1_btree)
not used hint:
duplication hint:
+BitmapScan(ti1 ti1_hash)
+IndexScan(ti1 ti1_pkey)
error hint:
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
- Index Scan using ti1_btree on ti1
- Index Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
-(3 rows)
+ 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-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)';
+----
+---- 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:
-IndexScan(ti1 ti1_hash)
+SeqScan(ti1)
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 (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
-(3 rows)
+ QUERY PLAN
+------------------------------------------------
+ Seq Scan on ti1
+ Filter: ((c1 = 1) AND (ctid = '(1,1)'::tid))
+(2 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)';
+-- 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:
-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 (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+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-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)';
+-- 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:
-IndexScan(ti1 ti1_gin)
not used hint:
duplication hint:
error hint:
+SeqScan(ti1 ti1_pkey ti1_btree)
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------
Tid Scan on ti1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 < 100) AND (c2 = 1) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ Filter: (c1 = 1)
(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)';
+-- 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 ti1_expr)
+IndexScan(ti1)
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 (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
+ Index Cond: (c1 = 1)
+ Filter: (ctid = '(1,1)'::tid)
(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)';
+-- 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_pred)
+IndexScan(ti1 ti1_pkey)
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))
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_pkey on ti1
+ Index Cond: (c1 = 1)
+ Filter: (ctid = '(1,1)'::tid)
(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)';
+-- 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_uniq)
+IndexScan(ti1 ti1_pkey ti1_btree)
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 (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ QUERY PLAN
+-----------------------------------
+ Index Scan using ti1_btree on ti1
+ Index Cond: (c1 = 1)
+ Filter: (ctid = '(1,1)'::tid)
(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)';
+-- 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:
-IndexScan(ti1 ti1_multi)
+BitmapScan(ti1)
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 (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
-(3 rows)
+ 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-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)';
+-- 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:
-IndexScan(ti1 ti1_ts)
+BitmapScan(ti1 ti1_pkey)
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 (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
-(3 rows)
+ 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-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)';
+-- 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:
-IndexScan(ti1 ti1_pkey)
+BitmapScan(ti1 ti1_pkey ti1_btree)
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 (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
-(3 rows)
+ 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-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)';
+-- 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:
-IndexScan(ti1 ti1_c2_key)
+TidScan(ti1)
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 (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
(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)';
+-- 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:
-BitmapScan(ti1 ti1_btree)
not used hint:
duplication hint:
error hint:
+TidScan(ti1 ti1_pkey)
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on ti1
- Recheck Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
- -> Bitmap Index Scan on ti1_btree
- Index Cond: (c1 < 100)
-(5 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 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)';
+-- 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:
-BitmapScan(ti1 ti1_hash)
not used hint:
duplication hint:
error hint:
+TidScan(ti1 ti1_pkey ti1_btree)
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on ti1
- Recheck Cond: (c1 = 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
- -> Bitmap Index Scan on ti1_hash
- Index Cond: (c1 = 100)
-(5 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 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)';
+-- 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:
-BitmapScan(ti1 ti1_gist)
+NoSeqScan(ti1)
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 (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
- -> Bitmap Index Scan on ti1_gist
- Index Cond: (c1 < 100)
-(5 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 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)';
+-- 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:
-BitmapScan(ti1 ti1_gin)
not used hint:
duplication hint:
error hint:
+NoSeqScan(ti1 ti1_pkey)
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on ti1
- Recheck Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
- -> Bitmap Index Scan on ti1_gin
- Index Cond: (c1 < 100)
-(5 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 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)';
+-- 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:
-BitmapScan(ti1 ti1_expr)
not used hint:
duplication hint:
error hint:
+NoSeqScan(ti1 ti1_pkey ti1_btree)
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on ti1
- Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
- -> Bitmap Index Scan on ti1_expr
- Index Cond: ((c1 < 100) = true)
-(4 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 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)';
+-- 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:
-BitmapScan(ti1 ti1_pred)
+NoIndexScan(ti1)
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)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 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)';
+-- 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:
-BitmapScan(ti1 ti1_uniq)
not used hint:
duplication hint:
error hint:
+NoIndexScan(ti1 ti1_pkey)
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on ti1
- Recheck Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
- -> Bitmap Index Scan on ti1_uniq
- Index Cond: (c1 < 100)
-(5 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 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)';
+-- 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:
-BitmapScan(ti1 ti1_multi)
not used hint:
duplication hint:
error hint:
+NoIndexScan(ti1 ti1_pkey ti1_btree)
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on ti1
- Recheck Cond: ((c1 < 100) AND (c2 = 1))
- Filter: ((ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
- -> Bitmap Index Scan on ti1_multi
- Index Cond: ((c1 < 100) AND (c2 = 1))
-(5 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 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)';
+-- 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:
-BitmapScan(ti1 ti1_ts)
+NoBitmapScan(ti1)
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)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 rows)
--- No. S-3-4-10
-/*+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)';
+-- 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:
-BitmapScan(ti1 ti1_pkey)
not used hint:
duplication hint:
error hint:
+NoBitmapScan(ti1 ti1_pkey)
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on ti1
- Recheck Cond: (c1 < 100)
- Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
- -> Bitmap Index Scan on ti1_pkey
- Index Cond: (c1 < 100)
-(5 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 rows)
--- No. S-3-4-11
-/*+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)';
+-- 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:
-BitmapScan(ti1 ti1_c2_key)
not used hint:
duplication hint:
error hint:
+NoBitmapScan(ti1 ti1_pkey ti1_btree)
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on ti1
- Recheck Cond: (c2 = 1)
- Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text))
- -> Bitmap Index Scan on ti1_c2_key
- Index Cond: (c2 = 1)
-(5 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 rows)
-----
----- No. S-3-5 not used index
-----
--- No. S-3-5-1
-/*+IndexScan(ti1 ti1_pred)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+-- 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:
-IndexScan(ti1 ti1_pred)
+NoTidScan(ti1)
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)
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
+ Index Cond: (c1 = 1)
+ Filter: (ctid = '(1,1)'::tid)
+(3 rows)
--- No. S-3-5-2
-/*+BitmapScan(ti1 ti1_pred)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+-- 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:
-BitmapScan(ti1 ti1_pred)
not used hint:
duplication hint:
error hint:
+NoTidScan(ti1 ti1_pkey)
- QUERY PLAN
-------------------------------------------------------------------------
- Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
- Filter: (c1 = 100)
-(2 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 rows)
-----
----- No. S-3-6 not exist index
-----
--- No. S-3-6-1
-/*+IndexScan(ti1 not_exist)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 100;
+-- 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:
-IndexScan(ti1 not_exist)
not used hint:
duplication hint:
error hint:
+NoTidScan(ti1 ti1_pkey ti1_btree)
- QUERY PLAN
-----------------------
- Seq Scan on ti1
- Filter: (c1 = 100)
-(2 rows)
+ QUERY PLAN
+-----------------------------------
+ Tid Scan on ti1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(3 rows)
--- No. S-3-6-2
-/*+BitmapScan(ti1 not_exist)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 100;
+-- No. S-3-13-25
+/*+IndexOnlyScan(ti1)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
LOG: pg_hint_plan:
used hint:
-BitmapScan(ti1 not_exist)
+IndexOnlyScan(ti1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-----------------------
- Seq Scan on ti1
- Filter: (c1 = 100)
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using ti1_uniq on ti1
+ Index Cond: (c1 >= 1)
(2 rows)
-----
----- No. S-3-7 query structure
-----
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
- QUERY PLAN
------------------------------------------------
- Hash Join
- Hash Cond: (t2.c1 = t1.c1)
- -> Seq Scan on t2
- -> Hash
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
-
--- No. S-3-7-1
-/*+SeqScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE c1 = 100;
+-- 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:
-SeqScan(t1)
+IndexOnlyScan(ti1 ti1_pkey)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-----------------------
- Seq Scan on t1
- Filter: (c1 = 100)
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using ti1_pkey on ti1
+ Index Cond: (c1 >= 1)
(2 rows)
--- No. S-3-7-2
-/*+SeqScan(t1)BitmapScan(t2)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
+-- 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:
-SeqScan(t1)
-BitmapScan(t2)
+IndexOnlyScan(ti1 ti1_pkey ti1_btree)
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_pkey
- Index Cond: (c1 = t1.c1)
-(7 rows)
+ QUERY PLAN
+----------------------------------------
+ Index Only Scan using ti1_btree on ti1
+ Index Cond: (c1 >= 1)
+(2 rows)
--- No. S-3-7-3
-/*+SeqScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
+-- No. S-3-13-28
+/*+NoIndexOnlyScan(ti1)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
LOG: pg_hint_plan:
used hint:
-SeqScan(t1)
+NoIndexOnlyScan(ti1)
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-8 query structure
-----
--- No. S-3-8-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
----------------------------------------------------------------------
- Result
- 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
- -> Index Scan using t1_pkey on t1 b4t1
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $4)
- -> Limit
- -> Index Scan using t1_pkey on t1 b3t1
- Index Cond: ((c1 IS NOT NULL) AND (c1 = $3))
-(17 rows)
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
+ Index Cond: (c1 = 1)
+(2 rows)
-/*+BitmapScan(b1t1)BitmapScan(b2t1)BitmapScan(b3t1)BitmapScan(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);
+-- 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:
-BitmapScan(b2t1)
-BitmapScan(b3t1)
-BitmapScan(b4t1)
not used hint:
-BitmapScan(b1t1)
duplication hint:
error hint:
+NoIndexOnlyScan(ti1 ti1_pkey)
- QUERY PLAN
----------------------------------------------------------------------------
- Result
- InitPlan 2 (returns $1)
- -> Result
- InitPlan 1 (returns $0)
- -> Limit
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- -> Bitmap Index Scan on t1_pkey
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $3)
- -> Result
- InitPlan 3 (returns $2)
- -> Limit
- -> Bitmap Heap Scan on t1 b4t1
- Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- -> Bitmap Index Scan on t1_pkey
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $4)
- -> Limit
- -> Bitmap Heap Scan on t1 b3t1
- Recheck Cond: ((c1 IS NOT NULL) AND (c1 = $3))
- -> Bitmap Index Scan on t1_pkey
- Index Cond: ((c1 IS NOT NULL) AND (c1 = $3))
-(23 rows)
-
--- No. S-3-8-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 Scan using t1_pkey on t1 b1t1
- Index Cond: (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
- -> Index Scan using t1_pkey on t1 b4t1
- Index Cond: (c1 = 1)
- -> Seq Scan on t2 b4t2
- Filter: (c1 = 1)
- -> Nested Loop
- -> Nested Loop
- -> Index Scan using t1_pkey on t1 b3t1
- Index Cond: (c1 = $2)
- -> Seq Scan on t2 b3t2
- Filter: (c1 = $2)
- -> CTE Scan on cte1
- Filter: (c1 = $2)
-(30 rows)
-
-/*+BitmapScan(b1t1)BitmapScan(b2t1)BitmapScan(b3t1)BitmapScan(b4t1)BitmapScan(b1t2)BitmapScan(b2t2)BitmapScan(b3t2)BitmapScan(b4t2)*/
-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
+----------------------------------
+ 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:
-BitmapScan(b1t1)
-BitmapScan(b1t2)
-BitmapScan(b2t1)
-BitmapScan(b2t2)
-BitmapScan(b3t1)
-BitmapScan(b3t2)
-BitmapScan(b4t1)
-BitmapScan(b4t2)
not used hint:
duplication hint:
error hint:
+NoIndexOnlyScan(ti1 ti1_pkey ti1_btree)
- QUERY PLAN
---------------------------------------------------------
- Aggregate
- CTE cte1
- -> Aggregate
- -> Nested Loop
- -> Bitmap Heap Scan on t1 b1t1
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = 1)
- -> Bitmap Heap Scan on t2 b1t2
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t2_pkey
- Index Cond: (c1 = 1)
- InitPlan 2 (returns $1)
- -> Aggregate
- -> Nested Loop
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = 1)
- -> Bitmap Heap Scan on t2 b2t2
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t2_pkey
- Index Cond: (c1 = 1)
- InitPlan 3 (returns $2)
- -> Aggregate
- -> Nested Loop
- -> Bitmap Heap Scan on t1 b4t1
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (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)
- -> Bitmap Heap Scan on t2 b3t2
- Recheck Cond: (c1 = $2)
- -> Bitmap Index Scan on t2_pkey
- Index Cond: (c1 = $2)
- -> CTE Scan on cte1
- Filter: (c1 = $2)
-(46 rows)
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
+ Index Cond: (c1 = 1)
+(2 rows)
--- No. S-3-8-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 Scan using t1_pkey 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 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
- -> Index Scan using t1_pkey on t1 b4t1
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- -> Nested Loop
- -> Nested Loop
- -> Index Scan using t1_pkey on t1 b3t1
- Index Cond: (c1 = $4)
- -> Seq Scan on t2 b3t2
- Filter: (c1 = $4)
- -> CTE Scan on cte1
- Filter: (c1 = $4)
-(28 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)
-/*+BitmapScan(b1t1)BitmapScan(b2t1)BitmapScan(b3t1)BitmapScan(b4t1)BitmapScan(b1t2)BitmapScan(b3t2)*/
-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);
+-- 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:
-BitmapScan(b1t1)
-BitmapScan(b1t2)
-BitmapScan(b2t1)
-BitmapScan(b3t1)
-BitmapScan(b3t2)
-BitmapScan(b4t1)
+IndexScanRegexp(ti1 ti1_.*_key)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------------------
- Aggregate
- CTE cte1
- -> Aggregate
- -> Nested Loop
- -> Bitmap Heap Scan on t1 b1t1
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = 1)
- -> Bitmap Heap Scan on t2 b1t2
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t2_pkey
- Index Cond: (c1 = 1)
- InitPlan 3 (returns $2)
- -> Result
- InitPlan 2 (returns $1)
- -> Limit
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- -> Bitmap Index Scan on t1_pkey
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $4)
- -> Result
- InitPlan 4 (returns $3)
- -> Limit
- -> Bitmap Heap Scan on t1 b4t1
- Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- -> Bitmap Index Scan on t1_pkey
- Index Cond: ((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)
- -> Bitmap Heap Scan on t2 b3t2
- Recheck Cond: (c1 = $4)
- -> Bitmap Index Scan on t2_pkey
- Index Cond: (c1 = $4)
- -> CTE Scan on cte1
- Filter: (c1 = $4)
-(40 rows)
-
-----
----- No. S-3-9 inheritance table select type
-----
--- No. S-3-9-1
-EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1;
- QUERY PLAN
---------------------
- Seq Scan on p1
- Filter: (c1 = 1)
+ QUERY PLAN
+------------------------------------
+ Index Scan using ti1_c2_key on ti1
+ Index Cond: (c2 = 1)
(2 rows)
-/*+IndexScan(p1)*/
-EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1;
+-- No. S-3-14-2
+/*+IndexScanRegexp(ti1 ti1_i.)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScanRegexp(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
LOG: pg_hint_plan:
used hint:
-IndexScan(p1)
+IndexScanRegexp(ti1 ti1_i.)
not used hint:
duplication hint:
error hint:
QUERY PLAN
--------------------------------
- Index Scan using p1_pkey on p1
- Index Cond: (c1 = 1)
+ Index Scan using ti1_i4 on ti1
+ Index Cond: (c2 = 1)
(2 rows)
--- No. S-3-9-2
-EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
- QUERY PLAN
----------------------------------
- Result
- -> Append
- -> Seq Scan on p1
- Filter: (c1 = 1)
- -> Seq Scan on p1c1 p1
- Filter: (c1 = 1)
-(6 rows)
-
-/*+IndexScan(p1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
+-- No. S-3-14-3
+/*+IndexScanRegexp(ti1 no.*_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScanRegexp(ti1):
LOG: pg_hint_plan:
used hint:
-IndexScan(p1)
+IndexScanRegexp(ti1 no.*_exist)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------
- Result
- -> Append
- -> Index Scan using p1_pkey on p1
- Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1 p1
- Index Cond: (c1 = 1)
-(6 rows)
+ QUERY PLAN
+--------------------
+ Seq Scan on ti1
+ Filter: (c2 = 1)
+(2 rows)
-----
----- No. S-3-10 inheritance table number
-----
--- No. S-3-10-1
+-- No. S-3-14-4
+/*+IndexScanRegexp(p1 .*pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
- QUERY PLAN
----------------------------------
- Result
- -> Append
- -> Seq Scan on p1
- Filter: (c1 = 1)
- -> Seq Scan on p1c1 p1
- Filter: (c1 = 1)
-(6 rows)
+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:
-/*+IndexScan(p1)*/
+ QUERY PLAN
+-----------------------------------------------
+ Append
+ -> Index Scan using p1_pkey on p1 p1_1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_pkey on p1c1 p1_2
+ Index Cond: (c1 = 1)
+(5 rows)
+
+-- No. S-3-14-5
+/*+IndexScanRegexp(p1 p1.*i)*/
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
+LOG: available indexes for IndexScanRegexp(p1): p1_i2 p1_i
+LOG: available indexes for IndexScanRegexp(p1c1): p1c1_i p1c1_c4_expr_idx
LOG: pg_hint_plan:
used hint:
-IndexScan(p1)
+IndexScanRegexp(p1 p1.*i)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------
- Result
- -> Append
- -> Index Scan using p1_pkey on p1
- Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1 p1
- Index Cond: (c1 = 1)
-(6 rows)
-
--- No. S-3-10-2
-EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
- QUERY PLAN
------------------------------------
- Result
- -> Append
- -> Seq Scan on p2
- Filter: (c1 = 1)
- -> Seq Scan on p2c1 p2
- Filter: (c1 = 1)
- -> Seq Scan on p2c1c1 p2
- Filter: (c1 = 1)
-(8 rows)
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Index Scan using p1_i on p1 p1_1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_i on p1c1 p1_2
+ Index Cond: (c1 = 1)
+(5 rows)
-/*+IndexScan(p2)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
+-- 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:
-IndexScan(p2)
+IndexScanRegexp(p1 no.*_exist)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-----------------------------------------------------
- Result
- -> Append
- -> Index Scan using p2_pkey on p2
- Index Cond: (c1 = 1)
- -> Index Scan using p2c1_i on p2c1 p2
- Index Cond: (c1 = 1)
- -> Index Scan using p2c1c1_i on p2c1c1 p2
- Index Cond: (c1 = 1)
-(8 rows)
+ QUERY PLAN
+-----------------------------
+ Append
+ -> Seq Scan on p1 p1_1
+ Filter: (c1 = 1)
+ -> Seq Scan on p1c1 p1_2
+ Filter: (c1 = 1)
+(5 rows)
----
----- No. S-3-11 inheritance table specified table
+---- No. S-3-15 message output of index candidate
----
-EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
- QUERY PLAN
------------------------------------
- Result
- -> Append
- -> Seq Scan on p2
- Filter: (c1 = 1)
- -> Seq Scan on p2c1 p2
- Filter: (c1 = 1)
- -> Seq Scan on p2c1c1 p2
- Filter: (c1 = 1)
-(8 rows)
+-- 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:
--- No. S-3-11-1
-/*+IndexScan(p2)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
+ 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(p2)
+IndexScan(ti1 not_exist)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-----------------------------------------------------
- Result
- -> Append
- -> Index Scan using p2_pkey on p2
- Index Cond: (c1 = 1)
- -> Index Scan using p2c1_i on p2c1 p2
- Index Cond: (c1 = 1)
- -> Index Scan using p2c1c1_i on p2c1c1 p2
- Index Cond: (c1 = 1)
-(8 rows)
+ QUERY PLAN
+--------------------
+ Seq Scan on ti1
+ Filter: (c2 = 1)
+(2 rows)
--- No. S-3-11-2
-/*+IndexScan(p2c1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
+-- 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:
-IndexScan(p2c1)
duplication hint:
error hint:
- QUERY PLAN
------------------------------------
- Result
- -> Append
- -> Seq Scan on p2
- Filter: (c1 = 1)
- -> Seq Scan on p2c1 p2
- Filter: (c1 = 1)
- -> Seq Scan on p2c1c1 p2
- Filter: (c1 = 1)
-(8 rows)
+ QUERY PLAN
+--------------------------------
+ Index Scan using ti1_i2 on ti1
+ Index Cond: (c2 = 1)
+(2 rows)
--- No. S-3-11-3
-/*+IndexScan(p2 p2_pkey p2c1_pkey p2c1c1_pkey)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
+-- No. S-3-15-4
+/*+IndexScan(ti1 ti1_i1 not_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
+LOG: available indexes for IndexScan(ti1): ti1_i1
LOG: pg_hint_plan:
used hint:
-IndexScan(p2 p2_pkey p2c1_pkey p2c1c1_pkey)
+IndexScan(ti1 ti1_i1 not_exist)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------
- Result
- -> Append
- -> Index Scan using p2_pkey on p2
- Index Cond: (c1 = 1)
- -> Index Scan using p2c1_pkey on p2c1 p2
- Index Cond: (c1 = 1)
- -> Index Scan using p2c1c1_pkey on p2c1c1 p2
- Index Cond: (c1 = 1)
-(8 rows)
+ QUERY PLAN
+--------------------------------
+ Index Scan using ti1_i1 on ti1
+ Index Cond: (c2 = 1)
+(2 rows)
--- No. S-3-11-4
-/*+IndexScan(p2 p2c1_pkey)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1;
+-- 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(p2 p2c1_pkey)
+IndexScan(ti1 not_exist1 not_exist2)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------------------------
- Result (cost=10000000000.00..20000000009.89 rows=3 width=34)
- -> Append (cost=10000000000.00..20000000009.89 rows=3 width=34)
- -> Seq Scan on p2 (cost=10000000000.00..10000000000.00 rows=1 width=44)
- Filter: (c1 = 1)
- -> Index Scan using p2c1_pkey on p2c1 p2 (cost=0.00..8.27 rows=1 width=44)
- Index Cond: (c1 = 1)
- -> Seq Scan on p2c1c1 p2 (cost=10000000000.00..10000000001.62 rows=1 width=14)
- Filter: (c1 = 1)
-(8 rows)
+ 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
OSDN Git Service
