SET search_path TO public;
+SET client_min_messages TO log;
+\set SHOW_CONTEXT always
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
QUERY PLAN
--------------------------------------
LOAD 'pg_hint_plan';
SET pg_hint_plan.debug_print TO on;
-SET client_min_messages TO LOG;
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
QUERY PLAN
--------------------------------------
/*+ Test (t1 t2) */
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
-INFO: hint syntax error at or near "Test (t1 t2) "
+INFO: pg_hint_plan: hint syntax error at or near "Test (t1 t2) "
DETAIL: Unrecognized hint keyword "Test".
QUERY PLAN
--------------------------------------
/*+Set(enable_indexscan off) /* nest comment */ */
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
-INFO: hint syntax error at or near "/* nest comment */ */
+INFO: pg_hint_plan: hint syntax error at or near "/* nest comment */ */
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;"
DETAIL: Nested block comments are not supported.
QUERY PLAN
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------
- Nested Loop
- -> Index Scan using t2_pkey on t2
- -> Bitmap Heap Scan on t1
- Recheck Cond: (id = t2.id)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (id = t2.id)
-(6 rows)
+ QUERY PLAN
+--------------------------------------------
+ Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ -> Hash
+ -> Index Scan using t2_pkey on t2
+(5 rows)
/*+Set(work_mem "1M")*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
INFO: invalid value for parameter "work_mem": "1M"
-HINT: Valid units for this parameter are "kB", "MB", and "GB".
+HINT: Valid units for this parameter are "kB", "MB", "GB", and "TB".
LOG: pg_hint_plan:
used hint:
not used hint:
/*+Set(work_mem TO "1MB")*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
-INFO: hint syntax error at or near "Set(work_mem TO "1MB")"
+INFO: pg_hint_plan: hint syntax error at or near "Set(work_mem TO "1MB")"
DETAIL: Set hint requires name and value of GUC parameter.
LOG: pg_hint_plan:
used hint:
/*+SeqScan(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
-INFO: hint syntax error at or near ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: SeqScan hint accepts only one relation.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
--------------------------------------------------
Merge Join
- Merge Cond: (t1.id = t4.id)
+ Merge Cond: (t1.id = t3.id)
-> Merge Join
- Merge Cond: (t1.id = t3.id)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ -> Index Scan using t2_pkey on t2
+ -> Materialize
-> Merge Join
- Merge Cond: (t1.id = t2.id)
- -> Index Scan using t1_pkey on t1
- -> Index Scan using t2_pkey on t2
- -> Index Scan using t3_pkey on t3
- -> Sort
- Sort Key: t4.id
- -> Seq Scan on t4
-(12 rows)
+ Merge Cond: (t3.id = t4.id)
+ -> Index Scan using t3_pkey on t3
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+(13 rows)
/*+HashJoin(t3 t4 t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
QUERY PLAN
--------------------------------------------------
Merge Join
- Merge Cond: (t1.id = t4.id)
+ Merge Cond: (t1.id = t3.id)
-> Merge Join
- Merge Cond: (t1.id = t3.id)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ -> Index Scan using t2_pkey on t2
+ -> Materialize
-> Merge Join
- Merge Cond: (t1.id = t2.id)
- -> Index Scan using t1_pkey on t1
- -> Index Scan using t2_pkey on t2
- -> Index Scan using t3_pkey on t3
- -> Sort
- Sort Key: t4.id
- -> Seq Scan on t4
-(12 rows)
+ Merge Cond: (t3.id = t4.id)
+ -> Index Scan using t3_pkey on t3
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+(13 rows)
/*+Leading( */
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
-INFO: hint syntax error at or near ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: Closing parenthesis is necessary.
QUERY PLAN
--------------------------------------------------
/*+Leading( )*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
-INFO: hint syntax error at or near "Leading( )"
+INFO: pg_hint_plan: hint syntax error at or near "Leading( )"
DETAIL: Leading hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
/*+Leading( t3 )*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
-INFO: hint syntax error at or near "Leading( t3 )"
+INFO: pg_hint_plan: hint syntax error at or near "Leading( t3 )"
DETAIL: Leading hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
/*+Leading(t3 t4 t1 t2 t1)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
-INFO: hint syntax error at or near "Leading(t3 t4 t1 t2 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t3 t4 t1 t2 t1)"
DETAIL: Relation name "t1" is duplicated.
LOG: pg_hint_plan:
used hint:
/*+Leading(t3 t4 t4)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
-INFO: hint syntax error at or near "Leading(t3 t4 t4)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t3 t4 t4)"
DETAIL: Relation name "t4" is duplicated.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
------------------------------------------------------------------------
Index Only Scan using t1_pkey on t1
- Index Cond: (id = $2)
- InitPlan 1 (returns $0)
+ Index Cond: (id = $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Bitmap Heap Scan on t1 v_1
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
- InitPlan 3 (returns $2)
+ InitPlan 3 (returns $3)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 2 (returns $2)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_2
Index Cond: ((id IS NOT NULL) AND (id < 10))
QUERY PLAN
------------------------------------------------------------------------
Index Only Scan using t1_pkey on t1
- Index Cond: (id = $2)
+ Index Cond: (id = $3)
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_1
Index Cond: ((id IS NOT NULL) AND (id < 10))
- InitPlan 3 (returns $2)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Bitmap Heap Scan on t1 v_2
Recheck Cond: (id < 10)
QUERY PLAN
--------------------------------------------------
Index Only Scan using t1_pkey on t1
- Index Cond: (id = $1)
- InitPlan 1 (returns $0)
+ Index Cond: (id = $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Bitmap Heap Scan on t1 v_1
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
- InitPlan 2 (returns $1)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Bitmap Heap Scan on t1 v_2
Recheck Cond: (id < 10)
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on t1
- Recheck Cond: (id = $2)
- InitPlan 1 (returns $0)
+ Recheck Cond: (id = $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Bitmap Heap Scan on t1 v_1
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
- InitPlan 3 (returns $2)
+ InitPlan 3 (returns $3)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 2 (returns $2)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_2
Index Cond: ((id IS NOT NULL) AND (id < 10))
-> Bitmap Index Scan on t1_pkey
- Index Cond: (id = $2)
+ Index Cond: (id = $3)
(16 rows)
/*+BitmapScan(v_2)BitmapScan(t1)*/
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on t1
- Recheck Cond: (id = $2)
+ Recheck Cond: (id = $3)
InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan Backward using t1_pkey on t1 v_1
Index Cond: ((id IS NOT NULL) AND (id < 10))
- InitPlan 3 (returns $2)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Bitmap Heap Scan on t1 v_2
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
- Index Cond: (id = $2)
+ Index Cond: (id = $3)
(16 rows)
/*+BitmapScan(v_1)BitmapScan(v_2)BitmapScan(t1)*/
QUERY PLAN
--------------------------------------------------
Bitmap Heap Scan on t1
- Recheck Cond: (id = $1)
- InitPlan 1 (returns $0)
+ Recheck Cond: (id = $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Bitmap Heap Scan on t1 v_1
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
- InitPlan 2 (returns $1)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Bitmap Heap Scan on t1 v_2
Recheck Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
Index Cond: (id < 10)
-> Bitmap Index Scan on t1_pkey
- Index Cond: (id = $1)
+ Index Cond: (id = $3)
(16 rows)
-- full scan hint pattern test
(3 rows)
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
-----------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
- -> Seq Scan on t1
- Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t1
+ Filter: (ctid = '(1,1)'::tid)
(6 rows)
/*+SeqScan(t2)*/
-----------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
- -> Seq Scan on t1
- Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t1
+ Filter: (ctid = '(1,1)'::tid)
(6 rows)
/*+SeqScan(t1) NoIndexScan(t2)*/
-----------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
- -> Seq Scan on t1
- Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t1
+ Filter: (ctid = '(1,1)'::tid)
(6 rows)
/*+SeqScan(t1) NoBitmapScan(t2)*/
-----------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
- -> Seq Scan on t1
- Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t1
+ Filter: (ctid = '(1,1)'::tid)
(6 rows)
/*+SeqScan(t1) NoTidScan(t2)*/
QUERY PLAN
---------------------------------------
Nested Loop
- -> Seq Scan on t1
+ Join Filter: (t1.id = t2.id)
+ -> Seq Scan on t2
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using t2_pkey on t2
- Index Cond: (id = t1.id)
+ -> Seq Scan on t1
Filter: (ctid = '(1,1)'::tid)
(6 rows)
-----------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
(6 rows)
/*+TidScan(t2)*/
-----------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
(6 rows)
/*+TidScan(t1) NoIndexScan(t2)*/
-----------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
(6 rows)
/*+TidScan(t1) NoBitmapScan(t2)*/
-----------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t2
TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
(6 rows)
/*+TidScan(t1) NoTidScan(t2)*/
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoSeqScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoSeqScan(t1) SeqScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoSeqScan(t1) NoIndexScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoSeqScan(t1) NoBitmapScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoSeqScan(t1) NoTidScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoIndexScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoIndexScan(t1) SeqScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoIndexScan(t1) NoIndexScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoIndexScan(t1) NoBitmapScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoIndexScan(t1) NoTidScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoBitmapScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoBitmapScan(t1) SeqScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoBitmapScan(t1) NoIndexScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoBitmapScan(t1) NoBitmapScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+(7 rows)
/*+NoBitmapScan(t1) NoTidScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
-- additional test
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)' AND t1.id < 10 AND t2.id < 10;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (t1.id = t2.id)
-> Tid Scan on t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: (id < 10)
- -> Tid Scan on t2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: (id < 10)
-(8 rows)
+ -> Materialize
+ -> Tid Scan on t2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (id < 10)
+(9 rows)
/*+BitmapScan(t1) BitmapScan(t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)' AND t1.id < 10 AND t2.id < 10;
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------
- Merge Full Join
- Merge Cond: (t1.id = t2.id)
- -> Index Scan using t1_pkey on t1
- -> Index Scan using t2_pkey on t2
-(4 rows)
+ QUERY PLAN
+------------------------------
+ Hash Full Join
+ Hash Cond: (t2.id = t1.id)
+ -> Seq Scan on t2
+ -> Hash
+ -> Seq Scan on t1
+(5 rows)
--- inherite table test
+-- inheritance tables test
SET constraint_exclusion TO off;
EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
QUERY PLAN
QUERY PLAN
-----------------------------------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
+ Merge Cond: (p1.id = t1.id)
-> Sort
- Sort Key: public.p1.id
+ Sort Key: p1.id
-> Append
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
QUERY PLAN
-----------------------------------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
+ Merge Cond: (p1.id = t1.id)
-> Sort
- Sort Key: public.p1.id
+ Sort Key: p1.id
-> Append
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
QUERY PLAN
-----------------------------------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
+ Merge Cond: (p1.id = t1.id)
-> Sort
- Sort Key: public.p1.id
+ Sort Key: p1.id
-> Append
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
- -> Sort
- Sort Key: public.p1.id
- -> Append
- -> Seq Scan on p1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c4
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ Merge Cond: (p1.id = t1.id)
+ -> Merge Append
+ Sort Key: p1.id
+ -> Index Scan using p1_pkey on p1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c1_pkey on p1_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c2_pkey on p1_c2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c3_pkey on p1_c3
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c4_pkey on p1_c4
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c3_c1_pkey on p1_c3_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c3_c2_pkey on p1_c3_c2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-(25 rows)
+(33 rows)
/*+BitmapScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
+ Merge Cond: (p1.id = t1.id)
-> Sort
- Sort Key: public.p1.id
+ Sort Key: p1.id
-> Append
- -> Seq Scan on p1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c4
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Bitmap Heap Scan on p1
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c1
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c1_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c2
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c2_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c3
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c3_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c4
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c4_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c1_c1
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c1_c1_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c1_c2
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c1_c2_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c3_c1
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c3_c1_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c3_c2
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c3_c2_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-(25 rows)
+(52 rows)
/*+TidScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
+ Merge Cond: (p1.id = t1.id)
-> Sort
- Sort Key: public.p1.id
+ Sort Key: p1.id
+ -> Append
+ -> Tid Scan on p1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c3
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c4
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c1_c1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c1_c2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c3_c1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c3_c2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+(34 rows)
+
+/*+NestLoop(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+NestLoop(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (p1.id = t1.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Materialize
-> Append
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p1_c3_c2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Index Scan using t1_pkey on t1
- Index Cond: (id < 10)
-(25 rows)
-
-/*+NestLoop(p1 t1)*/
-EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
-LOG: pg_hint_plan:
-used hint:
-NestLoop(p1 t1)
-not used hint:
-duplication hint:
-error hint:
-
- QUERY PLAN
------------------------------------------------------------------------------
- Nested Loop
- Join Filter: (public.p1.id = t1.id)
- -> Append
- -> Seq Scan on p1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c4
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Materialize
- -> Index Scan using t1_pkey on t1
- Index Cond: (id < 10)
(24 rows)
/*+MergeJoin(p1 t1)*/
QUERY PLAN
-----------------------------------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
+ Merge Cond: (p1.id = t1.id)
-> Sort
- Sort Key: public.p1.id
+ Sort Key: p1.id
-> Append
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
QUERY PLAN
-----------------------------------------------------------------------------
Hash Join
- Hash Cond: (public.p1.id = t1.id)
+ Hash Cond: (p1.id = t1.id)
-> Append
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
QUERY PLAN
-----------------------------------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
+ Merge Cond: (p1.id = t1.id)
-> Sort
- Sort Key: public.p1.id
+ Sort Key: p1.id
-> Append
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
- -> Sort
- Sort Key: public.p1.id
- -> Append
- -> Seq Scan on p1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ Merge Cond: (p1.id = t1.id)
+ -> Merge Append
+ Sort Key: p1.id
+ -> Index Scan using p1_pkey on p1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c1_pkey on p1_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-(15 rows)
+(18 rows)
/*+BitmapScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
+ Merge Cond: (p1.id = t1.id)
-> Sort
- Sort Key: public.p1.id
+ Sort Key: p1.id
-> Append
- -> Seq Scan on p1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Bitmap Heap Scan on p1
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c1
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c1_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c1_c1
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c1_c1_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+ -> Bitmap Heap Scan on p1_c1_c2
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_c1_c2_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-(15 rows)
+(27 rows)
/*+TidScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
+ Merge Cond: (p1.id = t1.id)
-> Sort
- Sort Key: public.p1.id
+ Sort Key: p1.id
-> Append
- -> Seq Scan on p1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Tid Scan on p1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c1_c1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+ -> Tid Scan on p1_c1_c2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-(15 rows)
+(19 rows)
/*+NestLoop(p1 t1)*/
EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
QUERY PLAN
-----------------------------------------------------------------------------------
Nested Loop
- Join Filter: (public.p1.id = t1.id)
+ Join Filter: (p1.id = t1.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Materialize
QUERY PLAN
-----------------------------------------------------------------------------------
Merge Join
- Merge Cond: (public.p1.id = t1.id)
+ Merge Cond: (p1.id = t1.id)
-> Sort
- Sort Key: public.p1.id
+ Sort Key: p1.id
-> Append
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
QUERY PLAN
-----------------------------------------------------------------------------------
Hash Join
- Hash Cond: (t1.id = public.p1.id)
+ Hash Cond: (t1.id = p1.id)
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Hash
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop
+ Join Filter: (p1.id = t1.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(5 rows)
+(6 rows)
/*+MergeJoin(p1 t1)*/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop
+ Join Filter: (p1.id = t1.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(5 rows)
+(6 rows)
/*+MergeJoin(p1 t1)*/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop
+ Join Filter: (p1.id = t1.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(5 rows)
+(6 rows)
SET constraint_exclusion TO on;
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop
+ Join Filter: (p1.id = t1.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(5 rows)
+(6 rows)
SET constraint_exclusion TO off;
/*+SeqScan(p1)*/
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop
+ Join Filter: (p1.id = t1.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(5 rows)
+(6 rows)
/*+IndexScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
- Nested Loop
+ QUERY PLAN
+-------------------------------------------------
+ Merge Join
+ Merge Cond: (p1.id = t1.id)
-> Index Scan using p1_pkey on p1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(6 rows)
+ Index Cond: (id < 10)
+(7 rows)
/*+BitmapScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Nested Loop
- -> Bitmap Heap Scan on p1
- Recheck Cond: ((id >= 50) AND (id <= 51))
- Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on p1_pkey
- Index Cond: ((id >= 50) AND (id <= 51))
+ Join Filter: (p1.id = t1.id)
-> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(8 rows)
+ Index Cond: (id < 10)
+ -> Materialize
+ -> Bitmap Heap Scan on p1
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+(10 rows)
/*+TidScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Nested Loop
- -> Tid Scan on p1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
+ Join Filter: (p1.id = t1.id)
-> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(6 rows)
+ Index Cond: (id < 10)
+ -> Materialize
+ -> Tid Scan on p1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+(8 rows)
SET constraint_exclusion TO on;
/*+SeqScan(p1)*/
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop
+ Join Filter: (p1.id = t1.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
-> Seq Scan on p1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(5 rows)
+(6 rows)
/*+IndexScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
- Nested Loop
+ QUERY PLAN
+-------------------------------------------------
+ Merge Join
+ Merge Cond: (p1.id = t1.id)
-> Index Scan using p1_pkey on p1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(6 rows)
+ Index Cond: (id < 10)
+(7 rows)
/*+BitmapScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Nested Loop
- -> Bitmap Heap Scan on p1
- Recheck Cond: ((id >= 50) AND (id <= 51))
- Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on p1_pkey
- Index Cond: ((id >= 50) AND (id <= 51))
+ Join Filter: (p1.id = t1.id)
-> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(8 rows)
+ Index Cond: (id < 10)
+ -> Materialize
+ -> Bitmap Heap Scan on p1
+ Recheck Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on p1_pkey
+ Index Cond: ((id >= 50) AND (id <= 51))
+(10 rows)
/*+TidScan(p1)*/
EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Nested Loop
- -> Tid Scan on p1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
+ Join Filter: (p1.id = t1.id)
-> Index Scan using t1_pkey on t1
- Index Cond: ((id = p1.id) AND (id < 10))
-(6 rows)
+ Index Cond: (id < 10)
+ -> Materialize
+ -> Tid Scan on p1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((id >= 50) AND (id <= 51))
+(8 rows)
-- quote test
/*+SeqScan("""t1 ) ")IndexScan("t 2 """)HashJoin("""t1 ) "T3"t 2 """)Leading("""t1 ) "T3"t 2 """)Set(application_name"a a a"" a A")*/
-- duplicate hint test
/*+SeqScan(t1)SeqScan(t2)IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
-INFO: hint syntax error at or near "SeqScan(t1)SeqScan(t2)IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
+INFO: pg_hint_plan: hint syntax error at or near "SeqScan(t1)SeqScan(t2)IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
+INFO: pg_hint_plan: hint syntax error at or near "IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
+INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "SeqScan(t2)IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
+INFO: pg_hint_plan: hint syntax error at or near "SeqScan(t2)IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
+INFO: pg_hint_plan: hint syntax error at or near "IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
+INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict join method hint.
-INFO: hint syntax error at or near "NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
+INFO: pg_hint_plan: hint syntax error at or near "NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict join method hint.
-INFO: hint syntax error at or near "Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
+INFO: pg_hint_plan: hint syntax error at or near "Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict set hint.
-INFO: hint syntax error at or near "Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)"
DETAIL: Conflict leading hint.
LOG: pg_hint_plan:
used hint:
-- ambigous error
EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Merge Join
- Merge Cond: (public.t1.id = t2.id)
+ Merge Cond: (t1.id = t2.id)
-> Merge Join
- Merge Cond: (public.t1.id = s0.t1.id)
- -> Index Scan using t1_pkey on t1
+ Merge Cond: (t1.id = t1_1.id)
-> Index Scan using t1_pkey on t1
+ -> Index Scan using t1_pkey on t1 t1_1
-> Index Scan using t2_pkey on t2
(7 rows)
/*+NestLoop(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
-INFO: hint syntax error at or near "NestLoop(t1 t2)"
+INFO: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t2)"
DETAIL: Relation name "t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
error hint:
NestLoop(t1 t2)
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Merge Join
- Merge Cond: (public.t1.id = t2.id)
+ Merge Cond: (t1.id = t2.id)
-> Merge Join
- Merge Cond: (public.t1.id = s0.t1.id)
- -> Index Scan using t1_pkey on t1
+ Merge Cond: (t1.id = t1_1.id)
-> Index Scan using t1_pkey on t1
+ -> Index Scan using t1_pkey on t1 t1_1
-> Index Scan using t2_pkey on t2
(7 rows)
/*+Leading(t1 t2 t1)*/
EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
-INFO: hint syntax error at or near "Leading(t1 t2 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t1 t2 t1)"
DETAIL: Relation name "t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
error hint:
Leading(t1 t2 t1)
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Merge Join
- Merge Cond: (public.t1.id = t2.id)
+ Merge Cond: (t1.id = t2.id)
-> Merge Join
- Merge Cond: (public.t1.id = s0.t1.id)
- -> Index Scan using t1_pkey on t1
+ Merge Cond: (t1.id = t1_1.id)
-> Index Scan using t1_pkey on t1
+ -> Index Scan using t1_pkey on t1 t1_1
-> Index Scan using t2_pkey on t2
(7 rows)
/*+Leading((t1))*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
-INFO: hint syntax error at or near "Leading((t1))"
+INFO: pg_hint_plan: hint syntax error at or near "Leading((t1))"
DETAIL: Leading hint requires two sets of relations when parentheses nests.
LOG: pg_hint_plan:
used hint:
/*+Leading((t1 t2 t3))*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
-INFO: hint syntax error at or near "Leading((t1 t2 t3))"
+INFO: pg_hint_plan: hint syntax error at or near "Leading((t1 t2 t3))"
DETAIL: Leading hint requires two sets of relations when parentheses nests.
LOG: pg_hint_plan:
used hint:
/*+Leading(((t1 t2) t3)) Leading(((t3 t1) t2))*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t1.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
-INFO: hint syntax error at or near "Leading(((t1 t2) t3)) Leading(((t3 t1) t2))"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(((t1 t2) t3)) Leading(((t3 t1) t2))"
DETAIL: Conflict leading hint.
LOG: pg_hint_plan:
used hint:
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
- -> Index Scan using t3_pkey on t3
+ -> Sort
+ Sort Key: t3.id
+ -> Seq Scan on t3
-> Sort
Sort Key: t4.id
-> Seq Scan on t4
-> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
Index Cond: (id = t1.id)
-(15 rows)
+(17 rows)
/*+Leading(((((t5 t4)t3)t2)t1))*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
-> Materialize
-> Merge Join
Merge Cond: (t3.id = t1.id)
- -> Index Scan using t3_pkey on t3
+ -> Sort
+ Sort Key: t3.id
+ -> Seq Scan on t3
-> Materialize
-> Merge Join
Merge Cond: (t2.id = t1.id)
-> Index Scan using t2_pkey on t2
-> Index Scan using t1_pkey on t1
-(17 rows)
+(19 rows)
/*+Leading((((t1 t2)t3)(t4 t5)))*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
Merge Cond: (t1.id = t2.id)
-> Index Scan using t1_pkey on t1
-> Index Scan using t2_pkey on t2
- -> Index Scan using t3_pkey on t3
+ -> Sort
+ Sort Key: t3.id
+ -> Seq Scan on t3
-> Materialize
-> Merge Join
Merge Cond: (t4.id = t5.id)
-> Index Scan using t4_pkey on t4
-> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
-(14 rows)
+(16 rows)
/*+Leading((((t5 t4)t3)(t2 t1)))*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Tid Scan on p2_c1_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p2_c1_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
-(11 rows)
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(9 rows)
/*+IndexScan(p2 p2_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_pkey
LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_pkey)
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_id_val_idx
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
-LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
-LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
-LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_id_val_idx)
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_val_id_idx
LOG: available indexes for IndexScan(p2_c1): p2_c1_val_id_idx
-LOG: available indexes for IndexScan(p2_c2): p2_c2_val_id_idx
-LOG: available indexes for IndexScan(p2_c3): p2_c3_val_id_idx
-LOG: available indexes for IndexScan(p2_c4):
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_id_idx
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_id_idx
-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_id_idx
-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_id_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_val_id_idx)
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c3
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Tid Scan on p2_c4
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c1_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c1_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c3_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c3_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
-(24 rows)
+ -> Seq Scan on p2_c4
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+(19 rows)
-/*+IndexScan(p2 p2_val)*/
+-- Inhibit parallel exection to avoid interfaring the hint
+set max_parallel_workers_per_gather to 0;
+/*+ IndexScan(p2 p2_val)*/
EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2):
LOG: available indexes for IndexScan(p2_c1):
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c3
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Tid Scan on p2_c4
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c1_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c1_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c3_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c3_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
-(24 rows)
+ -> Seq Scan on p2_c4
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+(19 rows)
/*+IndexScan(p2 p2_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_pkey
LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_pkey)
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_id2_val
LOG: available indexes for IndexScan(p2_c1): p2_c1_id2_val
-LOG: available indexes for IndexScan(p2_c2): p2_c2_id2_val
-LOG: available indexes for IndexScan(p2_c3):
-LOG: available indexes for IndexScan(p2_c4):
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
-LOG: available indexes for IndexScan(p2_c3_c1):
-LOG: available indexes for IndexScan(p2_c3_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_id2_val)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Append
-> Index Scan using p2_id2_val on p2
Index Cond: ((id >= 50) AND (id <= 51))
-> Index Scan using p2_c1_id2_val on p2_c1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on p2_c1_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p2_c1_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
-(13 rows)
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(11 rows)
/*+IndexScan(p2 p2_val2_id)*/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_val2_id
LOG: available indexes for IndexScan(p2_c1):
-LOG: available indexes for IndexScan(p2_c2):
-LOG: available indexes for IndexScan(p2_c3):
-LOG: available indexes for IndexScan(p2_c4):
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
-LOG: available indexes for IndexScan(p2_c3_c1):
-LOG: available indexes for IndexScan(p2_c3_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_val2_id)
Filter: (ctid = '(1,1)'::tid)
-> Seq Scan on p2_c1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Tid Scan on p2_c1_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p2_c1_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
-(12 rows)
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(10 rows)
/*+IndexScan(p2 p2_pkey)*/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_pkey
LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_pkey)
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2):
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
-LOG: available indexes for IndexScan(p2_c2):
-LOG: available indexes for IndexScan(p2_c3):
-LOG: available indexes for IndexScan(p2_c4):
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
-LOG: available indexes for IndexScan(p2_c3_c1):
-LOG: available indexes for IndexScan(p2_c3_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_c1_id_val_idx)
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using p2_c1_id_val_idx on p2_c1
Index Cond: ((id >= 50) AND (id <= 51))
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on p2_c1_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p2_c1_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
-(12 rows)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(10 rows)
/*+IndexScan(p2 no_exist)*/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2):
LOG: available indexes for IndexScan(p2_c1):
-LOG: available indexes for IndexScan(p2_c2):
-LOG: available indexes for IndexScan(p2_c3):
-LOG: available indexes for IndexScan(p2_c4):
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
-LOG: available indexes for IndexScan(p2_c3_c1):
-LOG: available indexes for IndexScan(p2_c3_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 no_exist)
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Tid Scan on p2_c1_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p2_c1_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
-(11 rows)
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(9 rows)
/*+IndexScan(p2 p2_pkey p2_c1_id_val_idx)*/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_pkey
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx p2_c1_pkey
-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_pkey p2_c1_id_val_idx)
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_pkey
LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_pkey no_exist)
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2):
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
-LOG: available indexes for IndexScan(p2_c2):
-LOG: available indexes for IndexScan(p2_c3):
-LOG: available indexes for IndexScan(p2_c4):
LOG: available indexes for IndexScan(p2_c1_c1):
LOG: available indexes for IndexScan(p2_c1_c2):
-LOG: available indexes for IndexScan(p2_c3_c1):
-LOG: available indexes for IndexScan(p2_c3_c2):
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_c1_id_val_idx no_exist)
-> Index Scan using p2_c1_id_val_idx on p2_c1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on p2_c1_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p2_c1_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((id >= 50) AND (id <= 51))
-(12 rows)
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(10 rows)
/*+IndexScan(p2 p2_pkey p2_c1_id_val_idx no_exist)*/
EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
LOG: available indexes for IndexScan(p2): p2_pkey
LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx p2_c1_pkey
-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_pkey p2_c1_id_val_idx no_exist)
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c3
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Tid Scan on p2_c4
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c1_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c1_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c3_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c3_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
-(24 rows)
+ -> Seq Scan on p2_c4
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+(19 rows)
/*+IndexScan(p2 p2_val_idx6)*/
EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
-> Seq Scan on p2_c3
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Tid Scan on p2_c4
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c1_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c1_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c3_c1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
- -> Tid Scan on p2_c3_c2
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((val >= '50'::text) AND (val <= '51'::text))
-(24 rows)
+ -> Seq Scan on p2_c4
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+(19 rows)
/*+IndexScan(p2 p2_val_idx p2_val_idx6)*/
EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
Index Cond: (id = 1)
(2 rows)
-DO LANGUAGE plpgsql $$
+-- static function
+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
+DECLARE
+ ret record;
+BEGIN
+ SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
+ RETURN ret;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 LIMIT 1"
+PL/pgSQL function testfunc() line 5 at SQL statement
+ testfunc
+----------
+ (1,1)
+(1 row)
+
+-- dynamic function
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+ EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "/*+ SeqScan(t1) */ SELECT * FROM t1"
+PL/pgSQL function testfunc() line 3 at EXECUTE
+ testfunc
+----------
+
+(1 row)
+
+-- This should not use SeqScan(t1)
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ id | val
+----+-----
+ 1 | 1
+(1 row)
+
+-- Perform
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+ PERFORM 1, /*+ SeqScan(t1) */ * from t1;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1"
+PL/pgSQL function testfunc() line 3 at PERFORM
+ testfunc
+----------
+
+(1 row)
+
+-- FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ sum int;
+ v int;
+BEGIN
+ sum := 0;
+ FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
+ sum := sum + v;
+ END LOOP;
+ RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 7 at FOR over SELECT rows
+ testfunc
+----------
+
+(1 row)
+
+-- Dynamic FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ sum int;
+ v int;
+ i int;
+BEGIN
+ sum := 0;
+ FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
+ sum := sum + v;
+ END LOOP;
+ RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 8 at FOR over EXECUTE statement
+ testfunc
+----------
+ 0
+(1 row)
+
+-- Cursor FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
DECLARE
- id integer;
+ ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+ rec record;
+ sum int := 0;
+BEGIN
+ FOR rec IN ref LOOP
+ sum := sum + rec.val;
+ END LOOP;
+ RETURN sum;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 7 at FOR over cursor
+ testfunc
+----------
+ 495000
+(1 row)
+
+-- RETURN QUERY
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
BEGIN
- SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
- RETURN;
+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
END;
-$$;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
duplication hint:
error hint:
-CONTEXT: SQL statement "SELECT /*+SeqScan(t1)*/ t1.id FROM t1 WHERE t1.id = 1"
-PL/pgSQL function inline_code_block line 5 at SQL statement
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 3 at RETURN QUERY
+ id | val
+----+-----
+ 1 | 1
+(1 row)
+
+-- Test for error exit from inner SQL statement.
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+ERROR: relation "ttx" does not exist
+LINE 1: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
+ ^
+QUERY: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
+CONTEXT: PL/pgSQL function testfunc() line 3 at RETURN QUERY
+-- this should not use SeqScan(t1) hint.
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ id | val
+----+-----
+ 1 | 1
+(1 row)
+
+DROP FUNCTION testfunc();
DROP EXTENSION pg_hint_plan;
+--
+-- Rows hint tests
+--
+-- Explain result includes "Planning time" if COSTS is enabled, but
+-- this test needs it enabled for get rows count. So do tests via psql
+-- and grep -v the mutable line.
+-- value types
+\o results/pg_hint_plan.tmpout
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 #99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 #99)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=99 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 +99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 +99)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1099 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 -99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 -99)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=901 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 *99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 *99)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=99000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 *0.01) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 *0.01)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=10 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 #aa) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- ERROR
+INFO: pg_hint_plan: hint syntax error at or near "aa"
+DETAIL: Rows hint requires valid number as rows estimation.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+Rows(t1 t2 #aa)
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 /99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- ERROR
+INFO: pg_hint_plan: hint syntax error at or near "/99"
+DETAIL: Unrecognized rows value type notation.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+Rows(t1 t2 /99)
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+-- round up to 1
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 -99999) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+WARNING: Force estimate to be at least one row, to avoid possible divide-by-zero when interpolating costs : Rows(t1 t2 -99999)
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 -99999)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+-- complex join tree
+\o results/pg_hint_plan.tmpout
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=10 width=xxx)
+ Merge Cond: (t1.id = t3.id)
+ -> Merge Join (cost=xxx rows=1000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx rows=100 width=xxx)
+ Sort Key: t3.id
+ -> Seq Scan on t3 (cost=xxx rows=100 width=xxx)
+(9 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 #22) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 #22)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1 width=xxx)
+ Merge Cond: (t1.id = t3.id)
+ -> Merge Join (cost=xxx rows=22 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx rows=100 width=xxx)
+ Sort Key: t3.id
+ -> Seq Scan on t3 (cost=xxx rows=100 width=xxx)
+(9 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t3 *10) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t3 *10)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+set max_parallel_workers_per_gather to DEFAULT;
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=100 width=xxx)
+ Merge Cond: (t1.id = t3.id)
+ -> Merge Join (cost=xxx rows=1000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx rows=100 width=xxx)
+ Sort Key: t3.id
+ -> Seq Scan on t3 (cost=xxx rows=100 width=xxx)
+(9 rows)
+
+\! rm results/pg_hint_plan.tmpout
OSDN Git Service
