LOAD 'pg_hint_plan';
+-- We cannot do ALTER USER current_user SET ...
+DELETE FROM pg_db_role_setting WHERE setrole = (SELECT oid FROM pg_roles WHERE rolname = current_user);
+INSERT INTO pg_db_role_setting (SELECT 0, (SELECT oid FROM pg_roles WHERE rolname = current_user), '{client_min_messages=log,pg_hint_plan.debug_print=on}');
+ALTER SYSTEM SET session_preload_libraries TO 'pg_hint_plan';
+\! pg_ctl reload
+server signaled
SET pg_hint_plan.enable_hint TO on;
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
Nested Loop
-> 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)
Recheck Cond: (c1 = 1)
-> Bitmap Index Scan on t1_i1
Index Cond: (c1 = 1)
- -> Bitmap Heap Scan on t1
+ -> Bitmap Heap Scan on t1 t1_1
Recheck Cond: (c1 = 1)
-> Bitmap Index Scan on t1_pkey
Index Cond: (c1 = 1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Only Scan using t1_i1 on t1
+ -> Index Only Scan using t1_i1 on t1 t1_1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
(8 rows)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Bitmap Heap Scan on t1
+ -> 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))
Append
-> Seq Scan on p1
Filter: (c1 = 1)
- -> Seq Scan on p1c1 p1
+ -> Seq Scan on p1c1
Filter: (c1 = 1)
(5 rows)
Append
-> Index Scan using p1_i on p1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1 p1
+ -> Index Scan using p1c1_i on p1c1
Index Cond: (c1 = 1)
(5 rows)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Only Scan using t1_i1 on t1
+ -> Index Only Scan using t1_i1 on t1 t1_1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
-> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Only Scan using t1_i1 on t1
+ -> Index Only Scan using t1_i1 on t1 t1_1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
-> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
QUERY PLAN
------------------------------------------------------------------------
Aggregate
- InitPlan 1 (returns $0)
+ InitPlan 1 (returns $1)
-> Aggregate
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
-> Merge Join
- Merge Cond: (b1t1.c1 = b1t4.c1)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
-> Merge Join
Merge Cond: (b1t1.c1 = b1t3.c1)
-> Index Only Scan using t1_i1 on t1 b1t1
-> Index Only Scan using t3_i1 on t3 b1t3
- -> Index Only Scan using t4_i1 on t4 b1t4
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- -> Merge Join
- Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
- Merge Cond: (bmt1.c1 = bmt4.c1)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt3.c1)
-> Index Only Scan using t1_i1 on t1 bmt1
-> Index Only Scan using t3_i1 on t3 bmt3
- -> Index Only Scan using t4_i1 on t4 bmt4
- -> Sort
- Sort Key: bmt2.c1
- -> Seq Scan on t2 bmt2
-(27 rows)
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ -> Index Only Scan using t4_i1 on t4 bmt4
+ Index Cond: (c1 = bmt3.c1)
+(29 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Aggregate
InitPlan 1 (returns $2)
-> Aggregate
-> Hash Join
- Hash Cond: (b1t2.c1 = b1t1.c1)
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Nested Loop
- -> Seq Scan on t2 b1t2
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t4.c1)
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
-> Hash
- -> Seq Scan on t1 b1t1
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
-> Hash Join
- Hash Cond: (bmt1.c1 = bmt4.c1)
- -> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t1 bmt1
- -> Hash
- -> Index Scan using t2_pkey on t2 bmt2
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4
-> Hash
- -> Seq Scan on t4 bmt4
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ -> Hash
+ -> Index Scan using t2_pkey on t2 bmt2
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
(31 rows)
-- No. S-2-1-2
QUERY PLAN
------------------------------------------------------------------------
Aggregate
- InitPlan 1 (returns $0)
+ InitPlan 1 (returns $1)
-> Aggregate
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
-> Merge Join
- Merge Cond: (b1t1.c1 = b1t4.c1)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
-> Merge Join
Merge Cond: (b1t1.c1 = b1t3.c1)
-> Index Only Scan using t1_i1 on t1 b1t1
-> Index Only Scan using t3_i1 on t3 b1t3
- -> Index Only Scan using t4_i1 on t4 b1t4
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- InitPlan 2 (returns $1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t3.c1)
+ InitPlan 2 (returns $3)
-> Aggregate
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
-> Merge Join
- Merge Cond: (b2t1.c1 = b2t4.c1)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
-> Merge Join
Merge Cond: (b2t1.c1 = b2t3.c1)
-> Index Only Scan using t1_i1 on t1 b2t1
-> Index Only Scan using t3_i1 on t3 b2t3
- -> Index Only Scan using t4_i1 on t4 b2t4
- -> Sort
- Sort Key: b2t2.c1
- -> Seq Scan on t2 b2t2
- -> Merge Join
- Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ -> Index Only Scan using t4_i1 on t4 b2t4
+ Index Cond: (c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
- Merge Cond: (bmt1.c1 = bmt4.c1)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt3.c1)
-> Index Only Scan using t1_i1 on t1 bmt1
-> Index Only Scan using t3_i1 on t3 bmt3
- -> Index Only Scan using t4_i1 on t4 bmt4
- -> Sort
- Sort Key: bmt2.c1
- -> Seq Scan on t2 bmt2
-(41 rows)
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ -> Index Only Scan using t4_i1 on t4 bmt4
+ Index Cond: (c1 = bmt3.c1)
+(44 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Aggregate
InitPlan 1 (returns $2)
-> Aggregate
-> Hash Join
- Hash Cond: (b1t2.c1 = b1t1.c1)
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Nested Loop
- -> Seq Scan on t2 b1t2
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t4.c1)
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
-> Hash
- -> Seq Scan on t1 b1t1
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
InitPlan 2 (returns $4)
-> Aggregate
-> Hash Join
- Hash Cond: (b2t1.c1 = b2t3.c1)
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t2.c1)
- -> Nested Loop
- -> Index Scan using t4_pkey on t4 b2t4
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: (c1 = b2t4.c1)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = b2t4.c1)
- -> Sort
- Sort Key: b2t2.c1
- -> Seq Scan on t2 b2t2
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Seq Scan on t3 b2t3
-> Hash
- -> Seq Scan on t3 b2t3
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ -> Index Scan using t4_pkey on t4 b2t4
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t4.c1)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
-> Hash Join
- Hash Cond: (bmt1.c1 = bmt4.c1)
- -> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t1 bmt1
- -> Hash
- -> Index Scan using t2_pkey on t2 bmt2
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4
-> Hash
- -> Seq Scan on t4 bmt4
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ -> Hash
+ -> Index Scan using t2_pkey on t2 bmt2
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
(48 rows)
-- No. S-2-1-3
QUERY PLAN
----------------------------------------------------------------
Aggregate
- -> Merge Join
- Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
- Merge Cond: (bmt1.c1 = bmt4.c1)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt3.c1)
-> Index Only Scan using t1_i1 on t1 bmt1
-> Index Only Scan using t3_i1 on t3 bmt3
- -> Index Only Scan using t4_i1 on t4 bmt4
- -> Sort
- Sort Key: bmt2.c1
- -> Seq Scan on t2 bmt2
-(13 rows)
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ -> Index Only Scan using t4_i1 on t4 bmt4
+ Index Cond: (c1 = bmt3.c1)
+(14 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
*/
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Aggregate
-> Hash Join
- Hash Cond: (bmt1.c1 = bmt4.c1)
- -> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t1 bmt1
- -> Hash
- -> Index Scan using t2_pkey on t2 bmt2
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4
-> Hash
- -> Seq Scan on t4 bmt4
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ -> Hash
+ -> Index Scan using t2_pkey on t2 bmt2
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
(15 rows)
-- No. S-2-1-4
QUERY PLAN
----------------------------------------------------------------
Aggregate
- -> Merge Join
- Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
- Merge Cond: (bmt1.c1 = bmt4.c1)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt3.c1)
-> Index Only Scan using t1_i1 on t1 bmt1
-> Index Only Scan using t3_i1 on t3 bmt3
- -> Index Only Scan using t4_i1 on t4 bmt4
- -> Sort
- Sort Key: bmt2.c1
- -> Seq Scan on t2 bmt2
-(13 rows)
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ -> Index Only Scan using t4_i1 on t4 bmt4
+ Index Cond: (c1 = bmt3.c1)
+(14 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
*/
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Aggregate
-> Hash Join
- Hash Cond: (bmt1.c1 = bmt4.c1)
- -> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t1 bmt1
- -> Hash
- -> Index Scan using t2_pkey on t2 bmt2
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4
-> Hash
- -> Seq Scan on t4 bmt4
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ -> Hash
+ -> Index Scan using t2_pkey on t2 bmt2
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
(15 rows)
-- No. S-2-1-5
QUERY PLAN
------------------------------------------------------------------------
Aggregate
- InitPlan 1 (returns $0)
+ InitPlan 1 (returns $1)
-> Aggregate
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
-> Merge Join
- Merge Cond: (b1t1.c1 = b1t4.c1)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
-> Merge Join
Merge Cond: (b1t1.c1 = b1t3.c1)
-> Index Only Scan using t1_i1 on t1 b1t1
-> Index Only Scan using t3_i1 on t3 b1t3
- -> Index Only Scan using t4_i1 on t4 b1t4
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- -> Merge Join
- Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
- Merge Cond: (bmt1.c1 = bmt4.c1)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt3.c1)
-> Index Only Scan using t1_i1 on t1 bmt1
- Filter: (c1 <> $0)
+ Filter: (c1 <> $1)
-> Index Only Scan using t3_i1 on t3 bmt3
- -> Index Only Scan using t4_i1 on t4 bmt4
- -> Sort
- Sort Key: bmt2.c1
- -> Seq Scan on t2 bmt2
-(28 rows)
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ -> Index Only Scan using t4_i1 on t4 bmt4
+ Index Cond: (c1 = bmt3.c1)
+(30 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Aggregate
InitPlan 1 (returns $2)
-> Aggregate
-> Hash Join
- Hash Cond: (b1t2.c1 = b1t1.c1)
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Nested Loop
- -> Seq Scan on t2 b1t2
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t4.c1)
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
-> Hash
- -> Seq Scan on t1 b1t1
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
-> Hash Join
- Hash Cond: (bmt1.c1 = bmt4.c1)
- -> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t1 bmt1
- Filter: (c1 <> $2)
- -> Hash
- -> Index Scan using t2_pkey on t2 bmt2
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4
-> Hash
- -> Seq Scan on t4 bmt4
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ Filter: (c1 <> $2)
+ -> Hash
+ -> Index Scan using t2_pkey on t2 bmt2
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
(32 rows)
-- No. S-2-1-6
QUERY PLAN
------------------------------------------------------------------------
Aggregate
- InitPlan 1 (returns $0)
+ InitPlan 1 (returns $1)
-> Aggregate
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
-> Merge Join
- Merge Cond: (b1t1.c1 = b1t4.c1)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
-> Merge Join
Merge Cond: (b1t1.c1 = b1t3.c1)
-> Index Only Scan using t1_i1 on t1 b1t1
-> Index Only Scan using t3_i1 on t3 b1t3
- -> Index Only Scan using t4_i1 on t4 b1t4
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- InitPlan 2 (returns $1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t3.c1)
+ InitPlan 2 (returns $3)
-> Aggregate
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
-> Merge Join
- Merge Cond: (b2t1.c1 = b2t4.c1)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
-> Merge Join
Merge Cond: (b2t1.c1 = b2t3.c1)
-> Index Only Scan using t1_i1 on t1 b2t1
-> Index Only Scan using t3_i1 on t3 b2t3
- -> Index Only Scan using t4_i1 on t4 b2t4
- -> Sort
- Sort Key: b2t2.c1
- -> Seq Scan on t2 b2t2
- -> Merge Join
- Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ -> Index Only Scan using t4_i1 on t4 b2t4
+ Index Cond: (c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Merge Join
- Merge Cond: (bmt1.c1 = bmt4.c1)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
-> Merge Join
Merge Cond: (bmt1.c1 = bmt3.c1)
-> Index Only Scan using t1_i1 on t1 bmt1
- Filter: ((c1 <> $0) AND (c1 <> $1))
+ Filter: ((c1 <> $1) AND (c1 <> $3))
-> Index Only Scan using t3_i1 on t3 bmt3
- -> Index Only Scan using t4_i1 on t4 bmt4
- -> Sort
- Sort Key: bmt2.c1
- -> Seq Scan on t2 bmt2
-(42 rows)
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ -> Index Only Scan using t4_i1 on t4 bmt4
+ Index Cond: (c1 = bmt3.c1)
+(45 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Aggregate
InitPlan 1 (returns $2)
-> Aggregate
-> Hash Join
- Hash Cond: (b1t2.c1 = b1t1.c1)
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Nested Loop
- -> Seq Scan on t2 b1t2
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t4.c1)
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
-> Hash
- -> Seq Scan on t1 b1t1
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
InitPlan 2 (returns $4)
-> Aggregate
-> Hash Join
- Hash Cond: (b2t1.c1 = b2t3.c1)
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t2.c1)
- -> Nested Loop
- -> Index Scan using t4_pkey on t4 b2t4
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: (c1 = b2t4.c1)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = b2t4.c1)
- -> Sort
- Sort Key: b2t2.c1
- -> Seq Scan on t2 b2t2
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Seq Scan on t3 b2t3
-> Hash
- -> Seq Scan on t3 b2t3
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ -> Index Scan using t4_pkey on t4 b2t4
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t4.c1)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
-> Hash Join
- Hash Cond: (bmt1.c1 = bmt4.c1)
- -> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t1 bmt1
- Filter: ((c1 <> $2) AND (c1 <> $4))
- -> Hash
- -> Index Scan using t2_pkey on t2 bmt2
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4
-> Hash
- -> Seq Scan on t4 bmt4
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ Filter: ((c1 <> $2) AND (c1 <> $4))
+ -> Hash
+ -> Index Scan using t2_pkey on t2 bmt2
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
(49 rows)
-- No. S-2-1-7
Aggregate
CTE c1
-> Aggregate
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
-> Merge Join
- Merge Cond: (b1t1.c1 = b1t4.c1)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
-> Merge Join
Merge Cond: (b1t1.c1 = b1t3.c1)
-> Index Only Scan using t1_i1 on t1 b1t1
-> Index Only Scan using t3_i1 on t3 b1t3
- -> Index Only Scan using t4_i1 on t4 b1t4
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t3.c1)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
Index Cond: (c1 = bmt3.c1)
-> Index Only Scan using t4_i1 on t4 bmt4
Index Cond: (c1 = bmt3.c1)
-(31 rows)
+(32 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)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Aggregate
CTE c1
-> Aggregate
-> Hash Join
- Hash Cond: (b1t2.c1 = b1t1.c1)
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Nested Loop
- -> Seq Scan on t2 b1t2
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t4.c1)
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
-> Hash
- -> Seq Scan on t1 b1t1
- -> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
- -> Nested Loop
- -> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = c1.c1)
- -> Seq Scan on t1 bmt1
- -> Hash
- -> CTE Scan on c1
- -> Index Scan using t2_pkey on t2 bmt2
- Index Cond: (c1 = bmt1.c1)
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
+ -> 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
-(33 rows)
+ -> Hash
+ -> Nested Loop
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = c1.c1)
+ -> Seq Scan on t1 bmt1
+ -> Hash
+ -> CTE Scan on c1
+ -> Index Scan using t2_pkey on t2 bmt2
+ Index Cond: (c1 = bmt1.c1)
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
+(34 rows)
-- No. S-2-1-8
EXPLAIN (COSTS false)
Aggregate
CTE c1
-> Aggregate
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
-> Merge Join
- Merge Cond: (b1t1.c1 = b1t4.c1)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
-> Merge Join
Merge Cond: (b1t1.c1 = b1t3.c1)
-> Index Only Scan using t1_i1 on t1 b1t1
-> Index Only Scan using t3_i1 on t3 b1t3
- -> Index Only Scan using t4_i1 on t4 b1t4
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t3.c1)
CTE c2
-> Aggregate
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
-> Merge Join
- Merge Cond: (b2t1.c1 = b2t4.c1)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
-> Merge Join
Merge Cond: (b2t1.c1 = b2t3.c1)
-> Index Only Scan using t1_i1 on t1 b2t1
-> Index Only Scan using t3_i1 on t3 b2t3
- -> Index Only Scan using t4_i1 on t4 b2t4
- -> Sort
- Sort Key: b2t2.c1
- -> Seq Scan on t2 b2t2
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ -> Index Only Scan using t4_i1 on t4 b2t4
+ Index Cond: (c1 = b2t3.c1)
-> Nested Loop
Join Filter: (bmt1.c1 = c2.c1)
-> Nested Loop
-> Index Only Scan using t4_i1 on t4 bmt4
Index Cond: (c1 = bmt3.c1)
-> CTE Scan on c2
-(48 rows)
+(50 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)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Aggregate
CTE c1
-> Aggregate
-> Hash Join
- Hash Cond: (b1t2.c1 = b1t1.c1)
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Nested Loop
- -> Seq Scan on t2 b1t2
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t4.c1)
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
-> Hash
- -> Seq Scan on t1 b1t1
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
CTE c2
-> Aggregate
-> Hash Join
- Hash Cond: (b2t1.c1 = b2t3.c1)
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t2.c1)
- -> Nested Loop
- -> Index Scan using t4_pkey on t4 b2t4
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: (c1 = b2t4.c1)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = b2t4.c1)
- -> Sort
- Sort Key: b2t2.c1
- -> Seq Scan on t2 b2t2
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Seq Scan on t3 b2t3
-> Hash
- -> Seq Scan on t3 b2t3
- -> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
- -> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ -> Index Scan using t4_pkey on t4 b2t4
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t4.c1)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ -> Hash Join
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4
+ -> Hash
-> Nested Loop
+ Join Filter: (bmt1.c1 = c2.c1)
-> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = c1.c1)
- -> Seq Scan on t1 bmt1
- -> Hash
- -> CTE Scan on c1
- -> Index Scan using t2_pkey on t2 bmt2
- Index Cond: (c1 = bmt1.c1)
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
- -> CTE Scan on c2
- -> Seq Scan on t4 bmt4
-(53 rows)
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = c1.c1)
+ -> Seq Scan on t1 bmt1
+ -> Hash
+ -> CTE Scan on c1
+ -> Index Scan using t2_pkey on t2 bmt2
+ Index Cond: (c1 = bmt1.c1)
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
+ -> CTE Scan on c2
+(54 rows)
----
---- No. S-2-2 the number of the tables per quiry block
-- No. S-2-3-4
EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
-> Seq Scan on t1 v1t1
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_1
(5 rows)
/*+BitmapScan(v1t1)*/
------------------------------------------
Nested Loop
-> Index Scan using t1_i1 on t1 v1t1
- -> Bitmap Heap Scan 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)
-- No. S-2-3-6
EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (r4t1.c1 = r4t1.c1)
+ Hash Cond: (r4t1.c1 = r4t1_1.c1)
-> Seq Scan on t1 r4t1
-> Hash
- -> Seq Scan on t1 r4t1
+ -> Seq Scan on t1 r4t1_1
(5 rows)
/*+BitmapScan(r4t1)*/
------------------------------------------
Nested Loop
-> Index Scan using t1_i1 on t1 r4t1
- -> Bitmap Heap Scan 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)
-- No. S-2-4-2
EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Nested Loop
- Join Filter: ("*VALUES*".column1 = "*VALUES*".column1)
- -> Values Scan on "*VALUES*"
+ Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1)
-> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
(4 rows)
/*+SeqScan(t1 t2)*/
error hint:
SeqScan(t1 t2)
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Nested Loop
- Join Filter: ("*VALUES*".column1 = "*VALUES*".column1)
- -> Values Scan on "*VALUES*"
+ Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1)
-> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
(4 rows)
/*+SeqScan(*VALUES*)*/
duplication hint:
error hint:
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Nested Loop
- Join Filter: ("*VALUES*".column1 = "*VALUES*".column1)
- -> Values Scan on "*VALUES*"
+ Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1)
-> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
(4 rows)
----
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
QUERY PLAN
---------------------------------
- Index Scan using ti1_i2 on ti1
+ Index Scan using ti1_i4 on ti1
Index Cond: (c2 = 1)
Filter: (ctid = '(1,1)'::tid)
(3 rows)
Bitmap Heap Scan on ti1
Recheck Cond: (c2 = 1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on ti1_i2
+ -> Bitmap Index Scan on ti1_i4
Index Cond: (c2 = 1)
(5 rows)
QUERY PLAN
---------------------------------
- Index Scan using ti1_i2 on ti1
+ Index Scan using ti1_i3 on ti1
Index Cond: (c2 = 1)
Filter: (ctid = '(1,1)'::tid)
(3 rows)
QUERY PLAN
---------------------------------
- Index Scan using ti1_i2 on ti1
+ Index Scan using ti1_i4 on ti1
Index Cond: (c2 = 1)
Filter: (ctid = '(1,1)'::tid)
(3 rows)
Bitmap Heap Scan on ti1
Recheck Cond: (c2 = 1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on ti1_i2
+ -> Bitmap Index Scan on ti1_i3
Index Cond: (c2 = 1)
(5 rows)
Bitmap Heap Scan on ti1
Recheck Cond: (c2 = 1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on ti1_i2
+ -> Bitmap Index Scan on ti1_i4
Index Cond: (c2 = 1)
(5 rows)
Index Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)
(5 rows)
--- No. S-3-4-10
+-- No. S-3-4-21
/*+BitmapScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
LOG: available indexes for BitmapScan(ti1): ti1_pkey
Index Cond: (c1 < 100)
(5 rows)
--- No. S-3-4-11
+-- No. S-3-4-22
/*+BitmapScan(ti1 ti1_c2_key)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
LOG: available indexes for BitmapScan(ti1): ti1_c2_key
-- No. S-3-4-24
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
- QUERY PLAN
----------------------------------------
- Index Only Scan using ti1_uniq on ti1
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
Index Cond: (c1 = 1)
(2 rows)
-- No. S-3-4-26
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
- QUERY PLAN
----------------------------------------
- Index Only Scan using ti1_uniq on ti1
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
Index Cond: (c1 = 1)
(2 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------
- Seq Scan on ti1
- Filter: (c1 = 1)
-(2 rows)
+ QUERY PLAN
+------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (c1 = 1)
+ -> Bitmap Index Scan on ti1_gin
+ Index Cond: (c1 = 1)
+(4 rows)
-- No. S-3-4-27
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 100;
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------------------
- Seq Scan on ti1
- Filter: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)
-(2 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Bitmap Heap Scan on ti1
+ Recheck Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)
+ -> Bitmap Index Scan on ti1_ts
+ Index Cond: (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)
+(4 rows)
-- No. S-3-4-32
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
---- 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;
+\! psql contrib_regression -c "/*+IndexScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
LOG: available indexes for IndexScan(ti1): ti1_pred
LOG: pg_hint_plan:
used hint:
------------------------------------------------------------------------
Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
Filter: (c1 = 100)
-(2 rows)
+(3 rows)
-- No. S-3-5-2
-/*+BitmapScan(ti1 ti1_pred)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+BitmapScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
LOG: available indexes for BitmapScan(ti1): ti1_pred
LOG: pg_hint_plan:
used hint:
------------------------------------------------------------------------
Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
Filter: (c1 = 100)
-(2 rows)
+(3 rows)
-- No. S-3-5-3
-/*+IndexOnlyScan(ti1 ti1_pred)*/
-EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+IndexOnlyScan(ti1 ti1_pred)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
LOG: available indexes for IndexOnlyScan(ti1): ti1_pred
LOG: pg_hint_plan:
used hint:
-----------------------------------------------------------------------
Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=4)
Filter: (c1 = 100)
-(2 rows)
+(3 rows)
-- No. S-3-5-4
-/*+IndexScan(ti1 not_exist)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+IndexScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
LOG: available indexes for IndexScan(ti1):
LOG: pg_hint_plan:
used hint:
------------------------------------------------------------------------
Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
Filter: (c1 = 100)
-(2 rows)
+(3 rows)
-- No. S-3-5-5
-/*+BitmapScan(ti1 not_exist)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+BitmapScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
LOG: available indexes for BitmapScan(ti1):
LOG: pg_hint_plan:
used hint:
------------------------------------------------------------------------
Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
Filter: (c1 = 100)
-(2 rows)
+(3 rows)
-- No. S-3-5-6
-/*+IndexOnlyScan(ti1 not_exist)*/
-EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100;
+\! psql contrib_regression -c "/*+IndexOnlyScan(ti1 not_exist)*/ EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100" | grep -v "Planning time:"
LOG: available indexes for IndexOnlyScan(ti1):
LOG: pg_hint_plan:
used hint:
-----------------------------------------------------------------------
Seq Scan on ti1 (cost=10000000000.00..10000000018.50 rows=1 width=4)
Filter: (c1 = 100)
-(2 rows)
+(3 rows)
-- No. S-3-5-7
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
Index Cond: (c1 = 1)
(2 rows)
-/*+TidScan(t1)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+\! psql contrib_regression -c "/*+TidScan(t1)*/ EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1" | grep -v "Planning time:"
LOG: pg_hint_plan:
used hint:
TidScan(t1)
-----------------------------------------------------------------------
Seq Scan on t1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
Filter: (c1 = 1)
-(2 rows)
+(3 rows)
----
---- No. S-3-6 query structure
(6 rows)
----
----- No. S-3-7 query structure
+---- No. S-3-7 number of tables in a query block
----
-- No. S-3-7-1
EXPLAIN (COSTS false)
(30 rows)
----
----- No. S-3-8 inheritance table select type
+---- No. S-3-8 inheritance table select/update type
----
-- No. S-3-8-1
EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1;
Append
-> Seq Scan on p1
Filter: (c1 = 1)
- -> Seq Scan on p1c1 p1
+ -> Seq Scan on p1c1
Filter: (c1 = 1)
(5 rows)
Append
-> Index Scan using p1_i on p1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1 p1
+ -> Index Scan using p1c1_i on p1c1
+ Index Cond: (c1 = 1)
+(5 rows)
+
+-- No. S-3-8-3
+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
+ QUERY PLAN
+--------------------------
+ Update on p1
+ -> Seq Scan on p1
+ Filter: (c1 = 1)
+(3 rows)
+
+/*+IndexScan(p1)*/
+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------
+ Update on p1
+ -> Index Scan using p1_i on p1
+ Index Cond: (c1 = 1)
+(3 rows)
+
+/*+IndexScan(p1 p1_pkey)*/
+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
+LOG: available indexes for IndexScan(p1): p1_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p1 p1_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------
+ Update on p1
+ -> Index Scan using p1_pkey on p1
+ Index Cond: (c1 = 1)
+(3 rows)
+
+-- No. S-3-8-4
+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
+ QUERY PLAN
+--------------------------
+ Update on p1
+ -> Seq Scan on p1
+ Filter: (c1 = 1)
+ -> Seq Scan on p1c1
+ Filter: (c1 = 1)
+(5 rows)
+
+/*+IndexScan(p1)*/
+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------
+ Update on p1
+ -> Index Scan using p1_i on p1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_i on p1c1
+ Index Cond: (c1 = 1)
+(5 rows)
+
+/*+IndexScan(p1 p1_pkey)*/
+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
+LOG: available indexes for IndexScan(p1): p1_pkey
+LOG: available indexes for IndexScan(p1c1): p1c1_pkey
+LOG: available indexes for IndexScan(p1c2): p1c2_pkey
+LOG: available indexes for IndexScan(p1c3): p1c3_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p1 p1_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------
+ Update on p1
+ -> Index Scan using p1_pkey on p1
+ Index Cond: (c1 = 1)
+ -> Index Scan using p1c1_pkey on p1c1
Index Cond: (c1 = 1)
(5 rows)
Append
-> Seq Scan on p1
Filter: (c1 = 1)
- -> Seq Scan on p1c1 p1
+ -> Seq Scan on p1c1
Filter: (c1 = 1)
(5 rows)
Append
-> Index Scan using p1_i on p1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1 p1
+ -> Index Scan using p1c1_i on p1c1
Index Cond: (c1 = 1)
(5 rows)
Append
-> Seq Scan on p2
Filter: (c1 = 1)
- -> Seq Scan on p2c1 p2
+ -> Seq Scan on p2c1
Filter: (c1 = 1)
- -> Seq Scan on p2c1c1 p2
+ -> Seq Scan on p2c1c1
Filter: (c1 = 1)
(7 rows)
Append
-> Index Scan using p2_i on p2
Index Cond: (c1 = 1)
- -> Index Scan using p2c1_i on p2c1 p2
+ -> Index Scan using p2c1_i on p2c1
Index Cond: (c1 = 1)
- -> Index Scan using p2c1c1_i on p2c1c1 p2
+ -> Index Scan using p2c1c1_i on p2c1c1
Index Cond: (c1 = 1)
(7 rows)
Append
-> Seq Scan on p2
Filter: (c1 = 1)
- -> Seq Scan on p2c1 p2
+ -> Seq Scan on p2c1
Filter: (c1 = 1)
- -> Seq Scan on p2c1c1 p2
+ -> Seq Scan on p2c1c1
Filter: (c1 = 1)
(7 rows)
Append
-> Index Scan using p2_i on p2
Index Cond: (c1 = 1)
- -> Index Scan using p2c1_i on p2c1 p2
+ -> Index Scan using p2c1_i on p2c1
Index Cond: (c1 = 1)
- -> Index Scan using p2c1c1_i on p2c1c1 p2
+ -> Index Scan using p2c1c1_i on p2c1c1
Index Cond: (c1 = 1)
(7 rows)
Append
-> Seq Scan on p2
Filter: (c1 = 1)
- -> Seq Scan on p2c1 p2
+ -> Seq Scan on p2c1
Filter: (c1 = 1)
- -> Seq Scan on p2c1c1 p2
+ -> Seq Scan on p2c1c1
Filter: (c1 = 1)
(7 rows)
-- No. S-3-10-3
-EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
+\! psql contrib_regression -c "EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10" | grep -v "Planning time:"
QUERY PLAN
----------------------------------------------------------
Append (cost=0.00..2.75 rows=4 width=10)
-> Seq Scan on p1 (cost=0.00..0.00 rows=1 width=32)
Filter: ((c1 < 10) AND ((c2 * 2) < 100))
- -> Seq Scan on p1c1 p1 (cost=0.00..2.75 rows=3 width=2)
+ -> Seq Scan on p1c1 (cost=0.00..2.75 rows=3 width=2)
Filter: ((c1 < 10) AND ((c2 * 2) < 100))
-(5 rows)
+(6 rows)
-/*+IndexScan(p1 p1_parent)*/
-EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
+\! psql contrib_regression -c "/*+IndexScan(p1 p1_parent)*/ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10" | grep -v "Planning time:"
LOG: available indexes for IndexScan(p1): p1_parent
LOG: available indexes for IndexScan(p1c1): p1c1_c4_expr_idx
LOG: available indexes for IndexScan(p1c2): p1c2_c4_expr_idx
Append (cost=0.12..20.48 rows=4 width=10)
-> Index Scan using p1_parent on p1 (cost=0.12..8.15 rows=1 width=32)
Filter: ((c2 * 2) < 100)
- -> Index Scan using p1c1_c4_expr_idx on p1c1 p1 (cost=0.14..12.34 rows=3 width=2)
+ -> Index Scan using p1c1_c4_expr_idx on p1c1 (cost=0.14..12.34 rows=3 width=2)
Filter: ((c2 * 2) < 100)
-(5 rows)
+(6 rows)
-- No. S-3-10-4
-/*+IndexScan(p1 p1_i2)*/
-EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1;
+\! psql contrib_regression -c "/*+IndexScan(p1 p1_i2)*/ EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1" | grep -v "Planning time:"
LOG: available indexes for IndexScan(p1): p1_i2
LOG: available indexes for IndexScan(p1c1):
LOG: available indexes for IndexScan(p1c2):
Index Cond: (c2 = 1)
-> Seq Scan on p1c1 (cost=10000000000.00..10000000002.25 rows=1 width=4)
Filter: (c2 = 1)
- -> Seq Scan on p1c2 p1 (cost=10000000000.00..10000000002.25 rows=1 width=4)
+ -> Seq Scan on p1c2 (cost=10000000000.00..10000000002.25 rows=1 width=4)
Filter: (c2 = 1)
- -> Seq Scan on p1c3 p1 (cost=10000000000.00..10000000002.25 rows=1 width=4)
+ -> Seq Scan on p1c3 (cost=10000000000.00..10000000002.25 rows=1 width=4)
Filter: (c2 = 1)
-(9 rows)
+(10 rows)
-- No. S-3-10-5
-/*+IndexScan(p2 p2c1_pkey)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1;
+\! psql contrib_regression -c "/*+IndexScan(p2 p2c1_pkey)*/ EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1" | grep -v "Planning time:"
LOG: available indexes for IndexScan(p2):
LOG: available indexes for IndexScan(p2c1): p2c1_pkey
LOG: available indexes for IndexScan(p2c2):
Append (cost=10000000000.00..20000000009.77 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.12..8.14 rows=1 width=44)
+ -> Index Scan using p2c1_pkey on p2c1 (cost=0.12..8.14 rows=1 width=44)
Index Cond: (c1 = 1)
- -> Seq Scan on p2c1c1 p2 (cost=10000000000.00..10000000001.62 rows=1 width=14)
+ -> Seq Scan on p2c1c1 (cost=10000000000.00..10000000001.62 rows=1 width=14)
Filter: (c1 = 1)
-(7 rows)
+(8 rows)
----
----- No. S-3-11 specified same table
+---- No. S-3-12 specified same table
----
--- No. S-3-11-1
+-- No. S-3-12-1
/*+IndexScan(ti1) BitmapScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near "IndexScan(ti1) BitmapScan(ti1)"
Bitmap Heap Scan on ti1
Recheck Cond: (c1 = 1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on ti1_uniq
+ -> Bitmap Index Scan on ti1_hash
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-11-2
+-- No. S-3-12-2
/*+IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near "IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)"
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-11-3
+-- No. S-3-12-3
/*+BitmapScan(ti1) IndexScan(ti1) BitmapScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near "BitmapScan(ti1) IndexScan(ti1) BitmapScan(ti1)"
Bitmap Heap Scan on ti1
Recheck Cond: (c1 = 1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on ti1_uniq
+ -> Bitmap Index Scan on ti1_hash
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-11-4
+-- No. S-3-12-4
/*+BitmapScan(ti1 ti1_hash) IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near "BitmapScan(ti1 ti1_hash) IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)"
(5 rows)
----
----- No. S-3-12 message output
+---- No. S-3-13 message output of hint
----
--- No. S-3-12-1
+-- No. S-3-13-1
/*+SeqScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: ((c1 = 1) AND (ctid = '(1,1)'::tid))
(2 rows)
--- No. S-3-12-2
+-- No. S-3-13-2
/*+SeqScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-3
+-- No. S-3-13-3
/*+SeqScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-4
+-- No. S-3-13-4
/*+IndexScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
QUERY PLAN
----------------------------------
- Index Scan using ti1_uniq on ti1
+ Index Scan using ti1_hash on ti1
Index Cond: (c1 = 1)
Filter: (ctid = '(1,1)'::tid)
(3 rows)
--- No. S-3-12-5
+-- No. S-3-13-5
/*+IndexScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: available indexes for IndexScan(ti1): ti1_pkey
Filter: (ctid = '(1,1)'::tid)
(3 rows)
--- No. S-3-12-6
+-- No. S-3-13-6
/*+IndexScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: available indexes for IndexScan(ti1): ti1_btree ti1_pkey
Filter: (ctid = '(1,1)'::tid)
(3 rows)
--- No. S-3-12-7
+-- No. S-3-13-7
/*+BitmapScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Bitmap Heap Scan on ti1
Recheck Cond: (c1 = 1)
Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on ti1_uniq
+ -> Bitmap Index Scan on ti1_hash
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-12-8
+-- No. S-3-13-8
/*+BitmapScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: available indexes for BitmapScan(ti1): ti1_pkey
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-12-9
+-- No. S-3-13-9
/*+BitmapScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: available indexes for BitmapScan(ti1): ti1_btree ti1_pkey
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-12-10
+-- No. S-3-13-10
/*+TidScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-11
+-- No. S-3-13-11
/*+TidScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-12
+-- No. S-3-13-12
/*+TidScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-13
+-- No. S-3-13-13
/*+NoSeqScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-14
+-- No. S-3-13-14
/*+NoSeqScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-15
+-- No. S-3-13-15
/*+NoSeqScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-16
+-- No. S-3-13-16
/*+NoIndexScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-17
+-- No. S-3-13-17
/*+NoIndexScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-18
+-- No. S-3-13-18
/*+NoIndexScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-19
+-- No. S-3-13-19
/*+NoBitmapScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-20
+-- No. S-3-13-20
/*+NoBitmapScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-21
+-- No. S-3-13-21
/*+NoBitmapScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-22
+-- No. S-3-13-22
/*+NoTidScan(ti1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
LOG: pg_hint_plan:
QUERY PLAN
----------------------------------
- Index Scan using ti1_uniq on ti1
+ Index Scan using ti1_hash on ti1
Index Cond: (c1 = 1)
Filter: (ctid = '(1,1)'::tid)
(3 rows)
--- No. S-3-12-23
+-- No. S-3-13-23
/*+NoTidScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-24
+-- No. S-3-13-24
/*+NoTidScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
INFO: hint syntax error at or near ""
Filter: (c1 = 1)
(3 rows)
--- No. S-3-12-25
+-- No. S-3-13-25
/*+IndexOnlyScan(ti1)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
LOG: pg_hint_plan:
Index Cond: (c1 >= 1)
(2 rows)
--- No. S-3-12-26
+-- No. S-3-13-26
/*+IndexOnlyScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
LOG: available indexes for IndexOnlyScan(ti1): ti1_pkey
Index Cond: (c1 >= 1)
(2 rows)
--- No. S-3-12-27
+-- No. S-3-13-27
/*+IndexOnlyScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
LOG: available indexes for IndexOnlyScan(ti1): ti1_btree ti1_pkey
Index Cond: (c1 >= 1)
(2 rows)
--- No. S-3-12-28
+-- No. S-3-13-28
/*+NoIndexOnlyScan(ti1)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
LOG: pg_hint_plan:
QUERY PLAN
----------------------------------
- Index Scan using ti1_uniq on ti1
+ Index Scan using ti1_hash on ti1
Index Cond: (c1 = 1)
(2 rows)
--- No. S-3-12-29
+-- No. S-3-13-29
/*+NoIndexOnlyScan(ti1 ti1_pkey)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
INFO: hint syntax error at or near ""
error hint:
NoIndexOnlyScan(ti1 ti1_pkey)
- QUERY PLAN
----------------------------------------
- Index Only Scan using ti1_uniq on ti1
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
Index Cond: (c1 = 1)
(2 rows)
--- No. S-3-12-30
+-- No. S-3-13-30
/*+NoIndexOnlyScan(ti1 ti1_pkey ti1_btree)*/
EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
INFO: hint syntax error at or near ""
error hint:
NoIndexOnlyScan(ti1 ti1_pkey ti1_btree)
- QUERY PLAN
----------------------------------------
- Index Only Scan using ti1_uniq on ti1
+ QUERY PLAN
+----------------------------------
+ Index Scan using ti1_hash on ti1
Index Cond: (c1 = 1)
(2 rows)
----
----- No. S-3-13 message output
+---- No. S-3-14 regular expression
----
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
QUERY PLAN
--------------------------------
- Index Scan using ti1_i2 on ti1
+ Index Scan using ti1_i4 on ti1
Index Cond: (c2 = 1)
(2 rows)
--- No. S-3-13-1
+-- No. S-3-14-1
/*+IndexScanRegexp(ti1 ti1_.*_key)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
LOG: available indexes for IndexScanRegexp(ti1): ti1_c2_key
Index Cond: (c2 = 1)
(2 rows)
--- No. S-3-13-2
+-- No. S-3-14-2
/*+IndexScanRegexp(ti1 ti1_i.)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
LOG: available indexes for IndexScanRegexp(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
QUERY PLAN
--------------------------------
- Index Scan using ti1_i2 on ti1
+ Index Scan using ti1_i4 on ti1
Index Cond: (c2 = 1)
(2 rows)
--- No. S-3-13-3
+-- No. S-3-14-3
/*+IndexScanRegexp(ti1 no.*_exist)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
LOG: available indexes for IndexScanRegexp(ti1):
Filter: (c2 = 1)
(2 rows)
--- No. S-3-13-4
+-- No. S-3-14-4
/*+IndexScanRegexp(p1 .*pkey)*/
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
LOG: available indexes for IndexScanRegexp(p1): p1_pkey
Append
-> Index Scan using p1_pkey on p1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_pkey on p1c1 p1
+ -> Index Scan using p1c1_pkey on p1c1
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-13-5
+-- No. S-3-14-5
/*+IndexScanRegexp(p1 p1.*i)*/
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
LOG: available indexes for IndexScanRegexp(p1): p1_i2 p1_i
Append
-> Index Scan using p1_i on p1
Index Cond: (c1 = 1)
- -> Index Scan using p1c1_i on p1c1 p1
+ -> Index Scan using p1c1_i on p1c1
Index Cond: (c1 = 1)
(5 rows)
--- No. S-3-13-6
+-- No. S-3-14-6
/*+IndexScanRegexp(p1 no.*_exist)*/
EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
LOG: available indexes for IndexScanRegexp(p1):
Append
-> Seq Scan on p1
Filter: (c1 = 1)
- -> Seq Scan on p1c1 p1
+ -> Seq Scan on p1c1
Filter: (c1 = 1)
(5 rows)
----
----- No. S-3-14 message output
+---- No. S-3-15 message output of index candidate
----
--- No. S-3-14-1
+-- No. S-3-15-1
/*+IndexScan(ti1 ti1_i1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
LOG: available indexes for IndexScan(ti1): ti1_i1
Index Cond: (c2 = 1)
(2 rows)
--- No. S-3-14-2
+-- No. S-3-15-2
/*+IndexScan(ti1 not_exist)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
LOG: available indexes for IndexScan(ti1):
Filter: (c2 = 1)
(2 rows)
--- No. S-3-14-3
+-- No. S-3-15-3
/*+IndexScan(ti1 ti1_i1 ti1_i2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
LOG: available indexes for IndexScan(ti1): ti1_i2 ti1_i1
Index Cond: (c2 = 1)
(2 rows)
--- No. S-3-14-4
+-- No. S-3-15-4
/*+IndexScan(ti1 ti1_i1 not_exist)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
LOG: available indexes for IndexScan(ti1): ti1_i1
Index Cond: (c2 = 1)
(2 rows)
--- No. S-3-14-5
+-- No. S-3-15-5
/*+IndexScan(ti1 not_exist1 not_exist2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
LOG: available indexes for IndexScan(ti1):
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;
+\! pg_ctl reload
+server signaled