EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
QUERY PLAN
-------------------------------------------
- Merge Join
- Merge Cond: (t2.val = t1.val)
- -> Index Scan using t2_val on t2
- -> Materialize
+ Nested Loop
+ -> Seq Scan on t2
+ -> Memoize
+ Cache Key: t2.val
-> Index Scan using t1_val on t1
-(5 rows)
+ Index Cond: (val = t2.val)
+(6 rows)
LOAD 'pg_hint_plan';
SET pg_hint_plan.debug_print TO on;
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
QUERY PLAN
-------------------------------------------
- Merge Join
- Merge Cond: (t2.val = t1.val)
- -> Index Scan using t2_val on t2
- -> Materialize
+ Nested Loop
+ -> Seq Scan on t2
+ -> Memoize
+ Cache Key: t2.val
-> Index Scan using t1_val on t1
-(5 rows)
+ Index Cond: (val = t2.val)
+(6 rows)
/*+ Test (t1 t2) */
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
/*+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", "GB", and "TB".
+HINT: Valid units for this parameter are "B", "kB", "MB", "GB", and "TB".
LOG: pg_hint_plan:
used hint:
not used hint:
-> Index Scan using t2_pkey on t2
(4 rows)
+/*+SeqScan() */ SELECT 1;
+INFO: pg_hint_plan: hint syntax error at or near " "
+DETAIL: SeqScan hint requires a relation.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+SeqScan()
+
+ ?column?
+----------
+ 1
+(1 row)
+
/*+SeqScan(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
INFO: pg_hint_plan: hint syntax error at or near ""
Index Cond: (id = t1.id)
(5 rows)
+/*+ NestLoop() */ SELECT 1;
+INFO: pg_hint_plan: hint syntax error at or near " "
+DETAIL: NestLoop hint requires at least two relations.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+NestLoop()
+
+ ?column?
+----------
+ 1
+(1 row)
+
+/*+ NestLoop(x) */ SELECT 1;
+INFO: pg_hint_plan: hint syntax error at or near " "
+DETAIL: NestLoop hint requires at least two relations.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+NestLoop(x)
+
+ ?column?
+----------
+ 1
+(1 row)
+
/*+HashJoin(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
LOG: pg_hint_plan:
-> Index Scan using t2_pkey on t2
(4 rows)
+-- Cannot work
/*+NestLoop(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN t2 ON (t1.id = t2.id);
LOG: pg_hint_plan:
QUERY PLAN
------------------------------
Hash Full Join
- Hash Cond: (t2.id = t1.id)
- -> Seq Scan on t2
+ Hash Cond: (t1.id = t2.id)
+ -> Seq Scan on t1
-> Hash
- -> Seq Scan on t1
+ -> Seq Scan on t2
(5 rows)
-- inheritance tables test
QUERY PLAN
-----------------------------------------------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(19 rows)
QUERY PLAN
-----------------------------------------------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(9 rows)
QUERY PLAN
-----------------------------------------------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Append
- -> Index Scan using p1_pkey on p1
+ -> Index Scan using p1_pkey on p1 p1_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_pkey on p1_c1
+ -> Index Scan using p1_c1_pkey on p1_c1 p1_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c2_pkey on p1_c2
+ -> Index Scan using p1_c2_pkey on p1_c2 p1_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c3_pkey on p1_c3
+ -> Index Scan using p1_c3_pkey on p1_c3 p1_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c4_pkey on p1_c4
+ -> Index Scan using p1_c4_pkey on p1_c4 p1_5
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1_6
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1_7
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c3_c1_pkey on p1_c3_c1
+ -> Index Scan using p1_c3_c1_pkey on p1_c3_c1 p1_8
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c3_c2_pkey on p1_c3_c2
+ -> Index Scan using p1_c3_c2_pkey on p1_c3_c2 p1_9
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(28 rows)
QUERY PLAN
-------------------------------------------------------
Append
- -> Bitmap Heap Scan on p1
+ -> Bitmap Heap Scan on p1 p1_1
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
+ -> Bitmap Heap Scan on p1_c1 p1_2
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
+ -> Bitmap Heap Scan on p1_c2 p1_3
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
+ -> Bitmap Heap Scan on p1_c3 p1_4
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
+ -> Bitmap Heap Scan on p1_c4 p1_5
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
+ -> Bitmap Heap Scan on p1_c1_c1 p1_6
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
+ -> Bitmap Heap Scan on p1_c1_c2 p1_7
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
+ -> Bitmap Heap Scan on p1_c3_c1 p1_8
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
+ -> Bitmap Heap Scan on p1_c3_c2 p1_9
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c3_c2_pkey
QUERY PLAN
---------------------------------------------
Append
- -> Tid Scan on p1
+ -> Tid Scan on p1 p1_1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1
+ -> Tid Scan on p1_c1 p1_2
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c2
+ -> Tid Scan on p1_c2 p1_3
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c3
+ -> Tid Scan on p1_c3 p1_4
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c4
+ -> Tid Scan on p1_c4 p1_5
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1_c1
+ -> Tid Scan on p1_c1_c1 p1_6
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1_c2
+ -> Tid Scan on p1_c1_c2 p1_7
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c3_c1
+ -> Tid Scan on p1_c3_c1 p1_8
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c3_c2
+ -> Tid Scan on p1_c3_c2 p1_9
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
(28 rows)
QUERY PLAN
-----------------------------------------------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(9 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Append
- -> Index Scan using p1_pkey on p1
+ -> Index Scan using p1_pkey on p1 p1_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_pkey on p1_c1
+ -> Index Scan using p1_c1_pkey on p1_c1 p1_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)
QUERY PLAN
-------------------------------------------------------
Append
- -> Bitmap Heap Scan on p1
+ -> Bitmap Heap Scan on p1 p1_1
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
+ -> Bitmap Heap Scan on p1_c1 p1_2
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
+ -> Bitmap Heap Scan on p1_c1_c1 p1_3
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
+ -> Bitmap Heap Scan on p1_c1_c2 p1_4
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c1_c2_pkey
QUERY PLAN
---------------------------------------------
Append
- -> Tid Scan on p1
+ -> Tid Scan on p1 p1_1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1
+ -> Tid Scan on p1_c1 p1_2
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1_c1
+ -> Tid Scan on p1_c1_c1 p1_3
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1_c2
+ -> Tid Scan on p1_c1_c2 p1_4
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
(13 rows)
-> Sort
Sort Key: p1.id
-> Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Sort
Sort Key: p1.id
-> Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Sort
Sort Key: p1.id
-> Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Merge Join
Merge Cond: (p1.id = t1.id)
-> Merge Append
Sort Key: p1.id
- -> Index Scan using p1_pkey on p1
+ -> Index Scan using p1_pkey on p1 p1_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_pkey on p1_c1
+ -> Index Scan using p1_c1_pkey on p1_c1 p1_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c2_pkey on p1_c2
+ -> Index Scan using p1_c2_pkey on p1_c2 p1_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c3_pkey on p1_c3
+ -> Index Scan using p1_c3_pkey on p1_c3 p1_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c4_pkey on p1_c4
+ -> Index Scan using p1_c4_pkey on p1_c4 p1_5
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1_6
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1_7
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c3_c1_pkey on p1_c3_c1
+ -> Index Scan using p1_c3_c1_pkey on p1_c3_c1 p1_8
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c3_c2_pkey on p1_c3_c2
+ -> Index Scan using p1_c3_c2_pkey on p1_c3_c2 p1_9
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
-> Sort
Sort Key: p1.id
-> Append
- -> Bitmap Heap Scan on p1
+ -> Bitmap Heap Scan on p1 p1_1
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
+ -> Bitmap Heap Scan on p1_c1 p1_2
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
+ -> Bitmap Heap Scan on p1_c2 p1_3
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
+ -> Bitmap Heap Scan on p1_c3 p1_4
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
+ -> Bitmap Heap Scan on p1_c4 p1_5
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
+ -> Bitmap Heap Scan on p1_c1_c1 p1_6
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
+ -> Bitmap Heap Scan on p1_c1_c2 p1_7
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
+ -> Bitmap Heap Scan on p1_c3_c1 p1_8
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
+ -> Bitmap Heap Scan on p1_c3_c2 p1_9
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c3_c2_pkey
-> Sort
Sort Key: p1.id
-> Append
- -> Tid Scan on p1
+ -> Tid Scan on p1 p1_1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1
+ -> Tid Scan on p1_c1 p1_2
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c2
+ -> Tid Scan on p1_c2 p1_3
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c3
+ -> Tid Scan on p1_c3 p1_4
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c4
+ -> Tid Scan on p1_c4 p1_5
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1_c1
+ -> Tid Scan on p1_c1_c1 p1_6
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1_c2
+ -> Tid Scan on p1_c1_c2 p1_7
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c3_c1
+ -> Tid Scan on p1_c3_c1 p1_8
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c3_c2
+ -> Tid Scan on p1_c3_c2 p1_9
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Index Scan using t1_pkey on t1
Nested Loop
Join Filter: (p1.id = t1.id)
-> Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Materialize
-> Index Scan using t1_pkey on t1
-> Sort
Sort Key: p1.id
-> Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
Hash Join
Hash Cond: (p1.id = t1.id)
-> Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Hash
-> Index Scan using t1_pkey on t1
-> Sort
Sort Key: p1.id
-> Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Merge Join
Merge Cond: (p1.id = t1.id)
-> Merge Append
Sort Key: p1.id
- -> Index Scan using p1_pkey on p1
+ -> Index Scan using p1_pkey on p1 p1_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_pkey on p1_c1
+ -> Index Scan using p1_c1_pkey on p1_c1 p1_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Index Scan using t1_pkey on t1
-> Sort
Sort Key: p1.id
-> Append
- -> Bitmap Heap Scan on p1
+ -> Bitmap Heap Scan on p1 p1_1
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
+ -> Bitmap Heap Scan on p1_c1 p1_2
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
+ -> Bitmap Heap Scan on p1_c1_c1 p1_3
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
+ -> Bitmap Heap Scan on p1_c1_c2 p1_4
Recheck Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on p1_c1_c2_pkey
-> Sort
Sort Key: p1.id
-> Append
- -> Tid Scan on p1
+ -> Tid Scan on p1 p1_1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1
+ -> Tid Scan on p1_c1 p1_2
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1_c1
+ -> Tid Scan on p1_c1_c1 p1_3
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
- -> Tid Scan on p1_c1_c2
+ -> Tid Scan on p1_c1_c2 p1_4
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((id >= 50) AND (id <= 51))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
-> Materialize
-> Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(14 rows)
-> Sort
Sort Key: p1.id
-> Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
-> Index Scan using t1_pkey on t1
Index Cond: (id < 10)
Index Cond: (id < 10)
-> Hash
-> Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(14 rows)
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Merge Join
- Merge Cond: (t1_5.id = t2_5.id)
- -> Index Only Scan using t1_pkey on t1 t1_5
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_1
- Filter: (id = $2)
+ Filter: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_3
- Filter: (id = $2)
- -> CTE Scan on c1_1
- Filter: (id = $2)
-(53 rows)
+ Filter: (id = $1)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $1)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t2_5.id)
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)*/
EXPLAIN (COSTS false)
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t1_1 t1_2 t1_4 t1_5)*/
EXPLAIN (COSTS false)
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t3_2 t3_5 t2_2 c1_1 t3_4 t3_3 t2_3 t2_4 t1_3 t2_5 t1_2 t3_1 t1_4 t2_1 t1_5 t1_1)*/
EXPLAIN (COSTS false)
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(t3_5 t2_5 t1_5)Leading(t3_2 t2_2 t1_2)Leading(t3_4 t2_4 t1_4)Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)*/
EXPLAIN (COSTS false)
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t2_5.id = t1_5.id)
- -> Merge Join
- Merge Cond: (t2_5.id = t3_5.id)
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t2_2.id = t1_2.id)
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
-> Index Only Scan using t1_pkey on t1 t1_2
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t2_4.id = t1_4.id)
-> Nested Loop
-> Nested Loop
-> Seq Scan on t3 t3_3
- Filter: (id = $2)
- -> CTE Scan on c1_1
- Filter: (id = $2)
+ Filter: (id = $1)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $1)
+ -> Merge Join
+ Merge Cond: (t2_5.id = t1_5.id)
+ -> Merge Join
+ Merge Cond: (t2_5.id = t3_5.id)
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_1
- Filter: (id = $2)
+ Filter: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $2)
-(53 rows)
+ Index Cond: (id = $1)
+(51 rows)
SET from_collapse_limit TO 1;
EXPLAIN (COSTS false)
QUERY PLAN
--------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Merge Join
- Merge Cond: (t1_5.id = t2_5.id)
- -> Index Only Scan using t1_pkey on t1 t1_5
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_1
- Filter: (id = $2)
+ Filter: (id = $1)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_3
- Filter: (id = $2)
- -> CTE Scan on c1_1
- Filter: (id = $2)
-(53 rows)
+ Filter: (id = $1)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $1)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t2_5.id)
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)*/
EXPLAIN (COSTS false)
QUERY PLAN
--------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t1_1 t1_2 t1_4 t1_5)*/
EXPLAIN (COSTS false)
QUERY PLAN
--------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t3_2 t3_5 t2_2 c1_1 t3_4 t3_3 t2_3 t2_4 t1_3 t2_5 t1_2 t3_1 t1_4 t2_1 t1_5 t1_1)*/
EXPLAIN (COSTS false)
QUERY PLAN
--------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(t3_5 t2_5 t1_5)Leading(t3_2 t2_2 t1_2)Leading(t3_4 t2_4 t1_4)Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)*/
EXPLAIN (COSTS false)
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t2_5.id = t1_5.id)
- -> Merge Join
- Merge Cond: (t2_5.id = t3_5.id)
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t2_2.id = t1_2.id)
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
-> Index Only Scan using t1_pkey on t1 t1_2
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t2_4.id = t1_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_3
- Filter: (id = $2)
- -> CTE Scan on c1_1
- Filter: (id = $2)
+ Filter: (id = $1)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $1)
+ -> Merge Join
+ Merge Cond: (t2_5.id = t1_5.id)
+ -> Merge Join
+ Merge Cond: (t2_5.id = t3_5.id)
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
-> Seq Scan on t3 t3_1
- Filter: (id = $2)
+ Filter: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $2)
-(53 rows)
+ Index Cond: (id = $1)
+(51 rows)
--- ambigous error
+-- ambiguous 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
--------------------------------------------------
- Merge Join
- Merge Cond: (t1.id = t2.id)
- -> Merge Join
- Merge Cond: (t1.id = t1_1.id)
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ -> Nested Loop
+ -> Seq Scan on t1 t1_1
-> Index Scan using t1_pkey on t1
- -> Index Scan using t1_pkey on t1 t1_1
+ Index Cond: (id = t1_1.id)
-> Index Scan using t2_pkey on t2
+ Index Cond: (id = t1.id)
(7 rows)
-/*+NestLoop(t1 t2)*/
+/*+MergeJoin(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: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t2)"
+INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t1 t2)"
DETAIL: Relation name "t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
-NestLoop(t1 t2)
+MergeJoin(t1 t2)
- QUERY PLAN
--------------------------------------------------
- Merge Join
- Merge Cond: (t1.id = t2.id)
- -> Merge Join
- Merge Cond: (t1.id = t1_1.id)
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ -> Nested Loop
+ -> Seq Scan on t1 t1_1
-> Index Scan using t1_pkey on t1
- -> Index Scan using t1_pkey on t1 t1_1
+ Index Cond: (id = t1_1.id)
-> Index Scan using t2_pkey on t2
+ Index Cond: (id = t1.id)
(7 rows)
/*+Leading(t1 t2 t1)*/
error hint:
Leading(t1 t2 t1)
- QUERY PLAN
--------------------------------------------------
- Merge Join
- Merge Cond: (t1.id = t2.id)
- -> Merge Join
- Merge Cond: (t1.id = t1_1.id)
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ -> Nested Loop
+ -> Seq Scan on t1 t1_1
-> Index Scan using t1_pkey on t1
- -> Index Scan using t1_pkey on t1 t1_1
+ Index Cond: (id = t1_1.id)
-> Index Scan using t2_pkey on t2
+ Index Cond: (id = t1.id)
(7 rows)
-- identifier length test
/*+ Set(enable_seqscan 100)Set(seq_page_cost on)*/
EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
INFO: parameter "enable_seqscan" requires a Boolean value
-INFO: parameter "seq_page_cost" requires a numeric value
+INFO: invalid value for parameter "seq_page_cost": "on"
LOG: pg_hint_plan:
used hint:
not used hint:
QUERY PLAN
-----------------------------------------------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1
+ -> Seq Scan on p2_c1 p2_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c1
+ -> Seq Scan on p2_c1_c1 p2_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c2
+ -> Seq Scan on p2_c1_c2 p2_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(9 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Append
- -> Index Scan using p2_pkey on p2
+ -> Index Scan using p2_pkey on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_pkey on p2_c1
+ -> Index Scan using p2_c1_pkey on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Append
- -> Index Scan using p2_id_val_idx on p2
+ -> Index Scan using p2_id_val_idx on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_id_val_idx on p2_c1
+ -> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Append
- -> Index Scan using p2_val_id_idx on p2
+ -> Index Scan using p2_val_id_idx on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_val_id_idx on p2_c1
+ -> Index Scan using p2_c1_val_id_idx on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c1_val_id_idx on p2_c1_c1
+ -> Index Scan using p2_c1_c1_val_id_idx on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c2_val_id_idx on p2_c1_c2
+ -> Index Scan using p2_c1_c2_val_id_idx on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1
+ -> Seq Scan on p2_c1 p2_2
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c2
+ -> Seq Scan on p2_c2 p2_3
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3
+ -> Seq Scan on p2_c3 p2_4
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c4
+ -> Seq Scan on p2_c4 p2_5
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c1
+ -> Seq Scan on p2_c1_c1 p2_6
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c2
+ -> Seq Scan on p2_c1_c2 p2_7
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3_c1
+ -> Seq Scan on p2_c3_c1 p2_8
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3_c2
+ -> Seq Scan on p2_c3_c2 p2_9
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
(19 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1
+ -> Seq Scan on p2_c1 p2_2
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c2
+ -> Seq Scan on p2_c2 p2_3
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3
+ -> Seq Scan on p2_c3 p2_4
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c4
+ -> Seq Scan on p2_c4 p2_5
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c1
+ -> Seq Scan on p2_c1_c1 p2_6
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c2
+ -> Seq Scan on p2_c1_c2 p2_7
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3_c1
+ -> Seq Scan on p2_c3_c1 p2_8
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3_c2
+ -> Seq Scan on p2_c3_c2 p2_9
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Append
- -> Index Scan using p2_pkey on p2
+ -> Index Scan using p2_pkey on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_pkey on p2_c1
+ -> Index Scan using p2_c1_pkey on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------
Append
- -> Index Scan using p2_id2_val on p2
+ -> Index Scan using p2_id2_val on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_id2_val on p2_c1
+ -> Index Scan using p2_c1_id2_val on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Seq Scan on p2_c1_c1
+ -> Seq Scan on p2_c1_c1 p2_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c2
+ -> Seq Scan on p2_c1_c2 p2_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(11 rows)
QUERY PLAN
-----------------------------------------------------------------------
Append
- -> Index Scan using p2_val2_id on p2
+ -> Index Scan using p2_val2_id on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Seq Scan on p2_c1
+ -> Seq Scan on p2_c1 p2_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c1
+ -> Seq Scan on p2_c1_c1 p2_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c2
+ -> Seq Scan on p2_c1_c2 p2_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(10 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Append
- -> Index Scan using p2_pkey on p2
+ -> Index Scan using p2_pkey on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_pkey on p2_c1
+ -> Index Scan using p2_c1_pkey on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Index Scan using p2_c1_id_val_idx on p2_c1
+ -> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Seq Scan on p2_c1_c1
+ -> Seq Scan on p2_c1_c1 p2_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c2
+ -> Seq Scan on p2_c1_c2 p2_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(10 rows)
QUERY PLAN
-----------------------------------------------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1
+ -> Seq Scan on p2_c1 p2_2
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c1
+ -> Seq Scan on p2_c1_c1 p2_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c2
+ -> Seq Scan on p2_c1_c2 p2_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(9 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Append
- -> Index Scan using p2_pkey on p2
+ -> Index Scan using p2_pkey on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_id_val_idx on p2_c1
+ -> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Append
- -> Index Scan using p2_pkey on p2
+ -> Index Scan using p2_pkey on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_pkey on p2_c1
+ -> Index Scan using p2_c1_pkey on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Index Scan using p2_c1_id_val_idx on p2_c1
+ -> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Seq Scan on p2_c1_c1
+ -> Seq Scan on p2_c1_c1 p2_3
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c2
+ -> Seq Scan on p2_c1_c2 p2_4
Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
(10 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Append
- -> Index Scan using p2_pkey on p2
+ -> Index Scan using p2_pkey on p2 p2_1
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_id_val_idx on p2_c1
+ -> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2_3
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2_4
Index Cond: ((id >= 50) AND (id <= 51))
Filter: (ctid = '(1,1)'::tid)
(13 rows)
QUERY PLAN
-------------------------------------------------------------------
Append
- -> Index Scan using p2_val_idx on p2
+ -> Index Scan using p2_val_idx on p2 p2_1
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_val_idx on p2_c1
+ -> Index Scan using p2_c1_val_idx on p2_c1 p2_2
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c2_val_idx on p2_c2
+ -> Index Scan using p2_c2_val_idx on p2_c2 p2_3
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c3_val_idx on p2_c3
+ -> Index Scan using p2_c3_val_idx on p2_c3 p2_4
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c4_val_idx on p2_c4
+ -> Index Scan using p2_c4_val_idx on p2_c4 p2_5
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c1_val_idx on p2_c1_c1
+ -> Index Scan using p2_c1_c1_val_idx on p2_c1_c1 p2_6
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c2_val_idx on p2_c1_c2
+ -> Index Scan using p2_c1_c2_val_idx on p2_c1_c2 p2_7
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c3_c1_val_idx on p2_c3_c1
+ -> Index Scan using p2_c3_c1_val_idx on p2_c3_c1 p2_8
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c3_c2_val_idx on p2_c3_c2
+ -> Index Scan using p2_c3_c2_val_idx on p2_c3_c2 p2_9
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
(28 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1
+ -> Seq Scan on p2_c1 p2_2
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c2
+ -> Seq Scan on p2_c2 p2_3
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3
+ -> Seq Scan on p2_c3 p2_4
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c4
+ -> Seq Scan on p2_c4 p2_5
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c1
+ -> Seq Scan on p2_c1_c1 p2_6
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c2
+ -> Seq Scan on p2_c1_c2 p2_7
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3_c1
+ -> Seq Scan on p2_c3_c1 p2_8
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3_c2
+ -> Seq Scan on p2_c3_c2 p2_9
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)';
LOG: available indexes for IndexScan(p2): p2_val_idx6
-LOG: available indexes for IndexScan(p2_c1): p2_c1_val_idx6
-LOG: available indexes for IndexScan(p2_c2): p2_c2_val_idx6
-LOG: available indexes for IndexScan(p2_c3): p2_c3_val_idx6
-LOG: available indexes for IndexScan(p2_c4): p2_c4_val_idx6
-LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx6
-LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx6
-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx6
-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx6
+LOG: available indexes for IndexScan(p2_c1): p2_c1_val_idx7
+LOG: available indexes for IndexScan(p2_c2): p2_c2_val_idx7
+LOG: available indexes for IndexScan(p2_c3): p2_c3_val_idx7
+LOG: available indexes for IndexScan(p2_c4): p2_c4_val_idx7
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx7
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx7
+LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx7
+LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx7
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_val_idx6)
QUERY PLAN
-----------------------------------------------------------------------------------------
Append
- -> Seq Scan on p2
+ -> Seq Scan on p2 p2_1
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1
+ -> Seq Scan on p2_c1 p2_2
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c2
+ -> Seq Scan on p2_c2 p2_3
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3
+ -> Seq Scan on p2_c3 p2_4
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c4
+ -> Seq Scan on p2_c4 p2_5
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c1
+ -> Seq Scan on p2_c1_c1 p2_6
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c1_c2
+ -> Seq Scan on p2_c1_c2 p2_7
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3_c1
+ -> Seq Scan on p2_c3_c1 p2_8
Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
- -> Seq Scan on p2_c3_c2
+ -> Seq Scan on p2_c3_c2 p2_9
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)';
LOG: available indexes for IndexScan(p2): p2_val_idx6 p2_val_idx
-LOG: available indexes for IndexScan(p2_c1): p2_c1_val_idx6 p2_c1_val_idx
-LOG: available indexes for IndexScan(p2_c2): p2_c2_val_idx6 p2_c2_val_idx
-LOG: available indexes for IndexScan(p2_c3): p2_c3_val_idx6 p2_c3_val_idx
-LOG: available indexes for IndexScan(p2_c4): p2_c4_val_idx6 p2_c4_val_idx
-LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx6 p2_c1_c1_val_idx
-LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx6 p2_c1_c2_val_idx
-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx6 p2_c3_c1_val_idx
-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx6 p2_c3_c2_val_idx
+LOG: available indexes for IndexScan(p2_c1): p2_c1_val_idx7 p2_c1_val_idx
+LOG: available indexes for IndexScan(p2_c2): p2_c2_val_idx7 p2_c2_val_idx
+LOG: available indexes for IndexScan(p2_c3): p2_c3_val_idx7 p2_c3_val_idx
+LOG: available indexes for IndexScan(p2_c4): p2_c4_val_idx7 p2_c4_val_idx
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx7 p2_c1_c1_val_idx
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx7 p2_c1_c2_val_idx
+LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx7 p2_c3_c1_val_idx
+LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx7 p2_c3_c2_val_idx
LOG: pg_hint_plan:
used hint:
IndexScan(p2 p2_val_idx p2_val_idx6)
QUERY PLAN
-------------------------------------------------------------------
Append
- -> Index Scan using p2_val_idx on p2
+ -> Index Scan using p2_val_idx on p2 p2_1
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_val_idx on p2_c1
+ -> Index Scan using p2_c1_val_idx on p2_c1 p2_2
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c2_val_idx on p2_c2
+ -> Index Scan using p2_c2_val_idx on p2_c2 p2_3
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c3_val_idx on p2_c3
+ -> Index Scan using p2_c3_val_idx on p2_c3 p2_4
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c4_val_idx on p2_c4
+ -> Index Scan using p2_c4_val_idx on p2_c4 p2_5
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c1_val_idx on p2_c1_c1
+ -> Index Scan using p2_c1_c1_val_idx on p2_c1_c1 p2_6
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c1_c2_val_idx on p2_c1_c2
+ -> Index Scan using p2_c1_c2_val_idx on p2_c1_c2 p2_7
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c3_c1_val_idx on p2_c3_c1
+ -> Index Scan using p2_c3_c1_val_idx on p2_c3_c1 p2_8
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using p2_c3_c2_val_idx on p2_c3_c2
+ -> Index Scan using p2_c3_c2_val_idx on p2_c3_c2 p2_9
Index Cond: ((val >= '50'::text) AND (val <= '51'::text))
Filter: (ctid = '(1,1)'::tid)
(28 rows)
-- Inheritance
EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
- QUERY PLAN
-----------------------------
+ QUERY PLAN
+---------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (val = 1)
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
-----------------------------
+ QUERY PLAN
+---------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (val = 1)
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Append
- -> Index Scan using p1_val2 on p1
+ -> Index Scan using p1_val2 on p1 p1_1
Index Cond: (val = 1)
- -> Index Scan using p1_c1_val2 on p1_c1
+ -> Index Scan using p1_c1_val2 on p1_c1 p1_2
Index Cond: (val = 1)
- -> Index Scan using p1_c2_val2 on p1_c2
+ -> Index Scan using p1_c2_val2 on p1_c2 p1_3
Index Cond: (val = 1)
- -> Index Scan using p1_c3_val2 on p1_c3
+ -> Index Scan using p1_c3_val2 on p1_c3 p1_4
Index Cond: (val = 1)
- -> Index Scan using p1_c4_val2 on p1_c4
+ -> Index Scan using p1_c4_val2 on p1_c4 p1_5
Index Cond: (val = 1)
- -> Index Scan using p1_c1_c1_val2 on p1_c1_c1
+ -> Index Scan using p1_c1_c1_val2 on p1_c1_c1 p1_6
Index Cond: (val = 1)
- -> Index Scan using p1_c1_c2_val2 on p1_c1_c2
+ -> Index Scan using p1_c1_c2_val2 on p1_c1_c2 p1_7
Index Cond: (val = 1)
- -> Index Scan using p1_c3_c1_val2 on p1_c3_c1
+ -> Index Scan using p1_c3_c1_val2 on p1_c3_c1 p1_8
Index Cond: (val = 1)
- -> Index Scan using p1_c3_c2_val2 on p1_c3_c2
+ -> Index Scan using p1_c3_c2_val2 on p1_c3_c2 p1_9
Index Cond: (val = 1)
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
-----------------------------
+ QUERY PLAN
+---------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (val = 1)
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
-----------------------------
+ QUERY PLAN
+---------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (val = 1)
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
-----------------------------
+ QUERY PLAN
+---------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (val = 1)
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Append
- -> Index Only Scan using p1_val2 on p1
+ -> Index Only Scan using p1_val2 on p1 p1_1
Index Cond: (val = 1)
- -> Index Only Scan using p1_c1_val2 on p1_c1
+ -> Index Only Scan using p1_c1_val2 on p1_c1 p1_2
Index Cond: (val = 1)
- -> Index Only Scan using p1_c2_val2 on p1_c2
+ -> Index Only Scan using p1_c2_val2 on p1_c2 p1_3
Index Cond: (val = 1)
- -> Index Only Scan using p1_c3_val2 on p1_c3
+ -> Index Only Scan using p1_c3_val2 on p1_c3 p1_4
Index Cond: (val = 1)
- -> Index Only Scan using p1_c4_val2 on p1_c4
+ -> Index Only Scan using p1_c4_val2 on p1_c4 p1_5
Index Cond: (val = 1)
- -> Index Only Scan using p1_c1_c1_val2 on p1_c1_c1
+ -> Index Only Scan using p1_c1_c1_val2 on p1_c1_c1 p1_6
Index Cond: (val = 1)
- -> Index Only Scan using p1_c1_c2_val2 on p1_c1_c2
+ -> Index Only Scan using p1_c1_c2_val2 on p1_c1_c2 p1_7
Index Cond: (val = 1)
- -> Index Only Scan using p1_c3_c1_val2 on p1_c3_c1
+ -> Index Only Scan using p1_c3_c1_val2 on p1_c3_c1 p1_8
Index Cond: (val = 1)
- -> Index Only Scan using p1_c3_c2_val2 on p1_c3_c2
+ -> Index Only Scan using p1_c3_c2_val2 on p1_c3_c2 p1_9
Index Cond: (val = 1)
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
-----------------------------
+ QUERY PLAN
+---------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (val = 1)
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
-----------------------------
+ QUERY PLAN
+---------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (val = 1)
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
-----------------------------
+ QUERY PLAN
+---------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (val = 1)
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)
QUERY PLAN
------------------------------------------------
Append
- -> Bitmap Heap Scan on p1
+ -> Bitmap Heap Scan on p1 p1_1
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_val2
Index Cond: (val = 1)
- -> Bitmap Heap Scan on p1_c1
+ -> Bitmap Heap Scan on p1_c1 p1_2
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c1_val2
Index Cond: (val = 1)
- -> Bitmap Heap Scan on p1_c2
+ -> Bitmap Heap Scan on p1_c2 p1_3
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c2_val2
Index Cond: (val = 1)
- -> Bitmap Heap Scan on p1_c3
+ -> Bitmap Heap Scan on p1_c3 p1_4
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c3_val2
Index Cond: (val = 1)
- -> Bitmap Heap Scan on p1_c4
+ -> Bitmap Heap Scan on p1_c4 p1_5
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c4_val2
Index Cond: (val = 1)
- -> Bitmap Heap Scan on p1_c1_c1
+ -> Bitmap Heap Scan on p1_c1_c1 p1_6
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c1_c1_val2
Index Cond: (val = 1)
- -> Bitmap Heap Scan on p1_c1_c2
+ -> Bitmap Heap Scan on p1_c1_c2 p1_7
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c1_c2_val2
Index Cond: (val = 1)
- -> Bitmap Heap Scan on p1_c3_c1
+ -> Bitmap Heap Scan on p1_c3_c1 p1_8
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c3_c1_val2
Index Cond: (val = 1)
- -> Bitmap Heap Scan on p1_c3_c2
+ -> Bitmap Heap Scan on p1_c3_c2 p1_9
Recheck Cond: (val = 1)
-> Bitmap Index Scan on p1_c3_c2_val2
Index Cond: (val = 1)
duplication hint:
error hint:
- QUERY PLAN
-----------------------------
+ QUERY PLAN
+---------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (val = 1)
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)
duplication hint:
error hint:
- QUERY PLAN
-----------------------------
+ QUERY PLAN
+---------------------------------
Append
- -> Seq Scan on p1
+ -> Seq Scan on p1 p1_1
Filter: (val = 1)
- -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c1 p1_2
Filter: (val = 1)
- -> Seq Scan on p1_c2
+ -> Seq Scan on p1_c2 p1_3
Filter: (val = 1)
- -> Seq Scan on p1_c3
+ -> Seq Scan on p1_c3 p1_4
Filter: (val = 1)
- -> Seq Scan on p1_c4
+ -> Seq Scan on p1_c4 p1_5
Filter: (val = 1)
- -> Seq Scan on p1_c1_c1
+ -> Seq Scan on p1_c1_c1 p1_6
Filter: (val = 1)
- -> Seq Scan on p1_c1_c2
+ -> Seq Scan on p1_c1_c2 p1_7
Filter: (val = 1)
- -> Seq Scan on p1_c3_c1
+ -> Seq Scan on p1_c3_c1 p1_8
Filter: (val = 1)
- -> Seq Scan on p1_c3_c2
+ -> Seq Scan on p1_c3_c2 p1_9
Filter: (val = 1)
(19 rows)
duplication hint:
error hint:
-CONTEXT: SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1"
+CONTEXT: SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1"
PL/pgSQL function testfunc() line 3 at PERFORM
testfunc
----------
DROP FUNCTION testfunc();
DROP EXTENSION pg_hint_plan;
+CREATE FUNCTION reset_stats_and_wait() RETURNS void AS $$
+DECLARE
+ rows int;
+BEGIN
+ rows = 1;
+ while rows > 0 LOOP
+ PERFORM pg_stat_reset();
+ PERFORM pg_sleep(0.5);
+ SELECT sum(seq_scan + idx_scan) from pg_stat_user_tables into rows;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+-- Dynamic query in pl/pgsql
+CREATE OR REPLACE FUNCTION dynsql1(x int) RETURNS int AS $$
+DECLARE c int;
+BEGIN
+ EXECUTE '/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1'
+ INTO c USING x;
+ RETURN c;
+END;
+$$ VOLATILE LANGUAGE plpgsql;
+vacuum analyze t1;
+SET pg_hint_plan.enable_hint = false;
+SELECT pg_sleep(1);
+ pg_sleep
+----------
+
+(1 row)
+
+SELECT reset_stats_and_wait();
+ reset_stats_and_wait
+----------------------
+
+(1 row)
+
+SELECT dynsql1(9000);
+ dynsql1
+---------
+ 8999
+(1 row)
+
+SELECT pg_sleep(1);
+ pg_sleep
+----------
+
+(1 row)
+
+SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
+ relname | seq_scan | idx_scan
+---------+----------+----------
+ t1 | t | f
+(1 row)
+
+SET pg_hint_plan.enable_hint = true;
+SELECT reset_stats_and_wait();
+ reset_stats_and_wait
+----------------------
+
+(1 row)
+
+SELECT dynsql1(9000);
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1"
+PL/pgSQL function dynsql1(integer) line 4 at EXECUTE
+ dynsql1
+---------
+ 8999
+(1 row)
+
+SELECT pg_sleep(1);
+ pg_sleep
+----------
+
+(1 row)
+
+SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
+ relname | seq_scan | idx_scan
+---------+----------+----------
+ t1 | f | t
+(1 row)
+
+-- Looped dynamic query in pl/pgsql
+CREATE OR REPLACE FUNCTION dynsql2(x int, OUT r int) AS $$
+DECLARE
+ c text;
+ s int;
+BEGIN
+ r := 0;
+ FOR c IN SELECT f.f FROM (VALUES ('p1_c1'), ('p1_c2')) f(f) LOOP
+ FOR s IN EXECUTE '/*+ IndexScan(' || c || ' ' || c || '_pkey) */ SELECT sum(val) FROM ' || c || ' WHERE id < ' || x LOOP
+ r := r + s;
+ END LOOP;
+ END LOOP;
+END;
+$$ VOLATILE LANGUAGE plpgsql;
+SET pg_hint_plan.enable_hint = false;
+SELECT reset_stats_and_wait();
+ reset_stats_and_wait
+----------------------
+
+(1 row)
+
+SELECT dynsql2(9000);
+ dynsql2
+---------
+ 9900
+(1 row)
+
+SELECT pg_sleep(1);
+ pg_sleep
+----------
+
+(1 row)
+
+-- one of the index scans happened while planning.
+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
+ relname | seq_scan | idx_scan
+---------+----------+----------
+ p1_c1 | 1 | 0
+ p1_c2 | 1 | 1
+(2 rows)
+
+SET pg_hint_plan.enable_hint = true;
+SELECT reset_stats_and_wait();
+ reset_stats_and_wait
+----------------------
+
+(1 row)
+
+SELECT dynsql2(9000);
+LOG: available indexes for IndexScan(p1_c1): p1_c1_pkey
+CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_pkey
+CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_pkey
+CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p1_c1 p1_c1_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+LOG: available indexes for IndexScan(p1_c2): p1_c2_pkey
+CONTEXT: SQL statement "/*+ IndexScan(p1_c2 p1_c2_pkey) */ SELECT sum(val) FROM p1_c2 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p1_c2 p1_c2_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "/*+ IndexScan(p1_c2 p1_c2_pkey) */ SELECT sum(val) FROM p1_c2 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+ dynsql2
+---------
+ 9900
+(1 row)
+
+SELECT pg_sleep(1);
+ pg_sleep
+----------
+
+(1 row)
+
+-- the index scan happened while planning.
+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
+ relname | seq_scan | idx_scan
+---------+----------+----------
+ p1_c1 | 0 | 1
+ p1_c2 | 0 | 2
+(2 rows)
+
+-- Subqueries on inheritance tables under UNION
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+ QUERY PLAN
+---------------------------------------------
+ Append
+ -> Append
+ -> Seq Scan on p1 p1_1
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c1 p1_2
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c2 p1_3
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c3 p1_4
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c4 p1_5
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c1_c1 p1_6
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c1_c2 p1_7
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c3_c1 p1_8
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c3_c2 p1_9
+ Filter: (val < 1000)
+ -> Result
+ -> Append
+ -> Seq Scan on p2 p2_1
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c1 p2_2
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c2 p2_3
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c3 p2_4
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c4 p2_5
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c1_c1 p2_6
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c1_c2 p2_7
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c3_c1 p2_8
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c3_c2 p2_9
+ Filter: (id < 1000)
+(40 rows)
+
+/*+ IndexScan(p1 p1_val2) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
+LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
+LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
+LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
+LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
+LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
+LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p1 p1_val2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------------
+ Append
+ -> Append
+ -> Index Scan using p1_val3 on p1 p1_1
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c1_val3 on p1_c1 p1_2
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c2_val3 on p1_c2 p1_3
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c3_val3 on p1_c3 p1_4
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c4_val3 on p1_c4 p1_5
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c1_c1_val3 on p1_c1_c1 p1_6
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c1_c2_val3 on p1_c1_c2 p1_7
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c3_c1_val3 on p1_c3_c1 p1_8
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c3_c2_val3 on p1_c3_c2 p1_9
+ Index Cond: (val < 1000)
+ -> Result
+ -> Append
+ -> Seq Scan on p2 p2_1
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c1 p2_2
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c2 p2_3
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c3 p2_4
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c4 p2_5
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c1_c1 p2_6
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c1_c2 p2_7
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c3_c1 p2_8
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c3_c2 p2_9
+ Filter: (id < 1000)
+(40 rows)
+
+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
+LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
+LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
+LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
+LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
+LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
+LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
+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(p1 p1_val2)
+IndexScan(p2 p2_id_val_idx)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Append
+ -> Append
+ -> Index Scan using p1_val3 on p1 p1_1
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c1_val3 on p1_c1 p1_2
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c2_val3 on p1_c2 p1_3
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c3_val3 on p1_c3 p1_4
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c4_val3 on p1_c4 p1_5
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c1_c1_val3 on p1_c1_c1 p1_6
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c1_c2_val3 on p1_c1_c2 p1_7
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c3_c1_val3 on p1_c3_c1 p1_8
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c3_c2_val3 on p1_c3_c2 p1_9
+ Index Cond: (val < 1000)
+ -> Result
+ -> Append
+ -> Index Scan using p2_id_val_idx on p2 p2_1
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c2_id_val_idx on p2_c2 p2_3
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c3_id_val_idx on p2_c3 p2_4
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c4_id_val_idx on p2_c4 p2_5
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2_6
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2_7
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 p2_8
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 p2_9
+ Index Cond: (id < 1000)
+(40 rows)
+
+-- union all case
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+ QUERY PLAN
+---------------------------------------------------
+ HashAggregate
+ Group Key: p1.val
+ -> Append
+ -> Append
+ -> Seq Scan on p1 p1_1
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c1 p1_2
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c2 p1_3
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c3 p1_4
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c4 p1_5
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c1_c1 p1_6
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c1_c2 p1_7
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c3_c1 p1_8
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c3_c2 p1_9
+ Filter: (val < 1000)
+ -> Result
+ -> Append
+ -> Seq Scan on p2 p2_1
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c1 p2_2
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c2 p2_3
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c3 p2_4
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c4 p2_5
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c1_c1 p2_6
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c1_c2 p2_7
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c3_c1 p2_8
+ Filter: (id < 1000)
+ -> Seq Scan on p2_c3_c2 p2_9
+ Filter: (id < 1000)
+(42 rows)
+
+/*+ IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+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)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ HashAggregate
+ Group Key: p1.val
+ -> Append
+ -> Append
+ -> Seq Scan on p1 p1_1
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c1 p1_2
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c2 p1_3
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c3 p1_4
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c4 p1_5
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c1_c1 p1_6
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c1_c2 p1_7
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c3_c1 p1_8
+ Filter: (val < 1000)
+ -> Seq Scan on p1_c3_c2 p1_9
+ Filter: (val < 1000)
+ -> Result
+ -> Append
+ -> Index Scan using p2_id_val_idx on p2 p2_1
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c2_id_val_idx on p2_c2 p2_3
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c3_id_val_idx on p2_c3 p2_4
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c4_id_val_idx on p2_c4 p2_5
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2_6
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2_7
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 p2_8
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 p2_9
+ Index Cond: (id < 1000)
+(42 rows)
+
+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
+LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
+LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
+LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
+LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
+LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
+LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
+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(p1 p1_val2)
+IndexScan(p2 p2_id_val_idx)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ HashAggregate
+ Group Key: p1.val
+ -> Append
+ -> Append
+ -> Index Scan using p1_val3 on p1 p1_1
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c1_val3 on p1_c1 p1_2
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c2_val3 on p1_c2 p1_3
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c3_val3 on p1_c3 p1_4
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c4_val3 on p1_c4 p1_5
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c1_c1_val3 on p1_c1_c1 p1_6
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c1_c2_val3 on p1_c1_c2 p1_7
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c3_c1_val3 on p1_c3_c1 p1_8
+ Index Cond: (val < 1000)
+ -> Index Scan using p1_c3_c2_val3 on p1_c3_c2 p1_9
+ Index Cond: (val < 1000)
+ -> Result
+ -> Append
+ -> Index Scan using p2_id_val_idx on p2 p2_1
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c1_id_val_idx on p2_c1 p2_2
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c2_id_val_idx on p2_c2 p2_3
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c3_id_val_idx on p2_c3 p2_4
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c4_id_val_idx on p2_c4 p2_5
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2_6
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2_7
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 p2_8
+ Index Cond: (id < 1000)
+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 p2_9
+ Index Cond: (id < 1000)
+(42 rows)
+
--
-- 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.
+-- Parse error check
+/*+ Rows() */ SELECT 1;
+INFO: pg_hint_plan: hint syntax error at or near " "
+DETAIL: Rows hint needs at least one relation followed by one correction term.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+Rows()
+
+ ?column?
+----------
+ 1
+(1 row)
+
+/*+ Rows(x) */ SELECT 1;
+INFO: pg_hint_plan: hint syntax error at or near " "
+DETAIL: Rows hint needs at least one relation followed by one correction term.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+Rows()
+
+ ?column?
+----------
+ 1
+(1 row)
+
-- 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)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
\o results/pg_hint_plan.tmpout
/*+ Rows(t1 t2 #99) */
\o
\! sql/maskout.sh results/pg_hint_plan.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------
- Merge Join (cost=xxx rows=99 width=xxx)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
\o results/pg_hint_plan.tmpout
/*+ Rows(t1 t2 +99) */
\o
\! sql/maskout.sh results/pg_hint_plan.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------
- Merge Join (cost=xxx rows=1099 width=xxx)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
\o results/pg_hint_plan.tmpout
/*+ Rows(t1 t2 -99) */
\o
\! sql/maskout.sh results/pg_hint_plan.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------
- Merge Join (cost=xxx rows=901 width=xxx)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
\o results/pg_hint_plan.tmpout
/*+ Rows(t1 t2 *99) */
\o
\! sql/maskout.sh results/pg_hint_plan.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------
- Merge Join (cost=xxx rows=99000 width=xxx)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
\o results/pg_hint_plan.tmpout
/*+ Rows(t1 t2 *0.01) */
\o
\! sql/maskout.sh results/pg_hint_plan.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------
- Merge Join (cost=xxx rows=10 width=xxx)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
\o results/pg_hint_plan.tmpout
/*+ Rows(t1 t2 #aa) */
\o
\! sql/maskout.sh results/pg_hint_plan.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------
- Merge Join (cost=xxx rows=1000 width=xxx)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
\o results/pg_hint_plan.tmpout
/*+ Rows(t1 t2 /99) */
\o
\! sql/maskout.sh results/pg_hint_plan.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------
- Merge Join (cost=xxx rows=1000 width=xxx)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
-- round up to 1
\o results/pg_hint_plan.tmpout
\o
\! sql/maskout.sh results/pg_hint_plan.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------
- Merge Join (cost=xxx rows=1 width=xxx)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
-- 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)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=10 width=xxx)
Merge Cond: (t1.id = t3.id)
- -> Merge Join (cost=xxx rows=1000 width=xxx)
+ -> Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: t3.id
- -> Seq Scan on t3 (cost=xxx rows=100 width=xxx)
-(9 rows)
+ -> Seq Scan on t3 (cost=xxx..xxx rows=100 width=xxx)
\o results/pg_hint_plan.tmpout
/*+ Rows(t1 t2 #22) */
\o
\! sql/maskout.sh results/pg_hint_plan.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------------
- Merge Join (cost=xxx rows=1 width=xxx)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
Merge Cond: (t1.id = t3.id)
- -> Merge Join (cost=xxx rows=22 width=xxx)
+ -> Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: t3.id
- -> Seq Scan on t3 (cost=xxx rows=100 width=xxx)
-(9 rows)
+ -> Seq Scan on t3 (cost=xxx..xxx rows=100 width=xxx)
\o results/pg_hint_plan.tmpout
/*+ Rows(t1 t3 *10) */
\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)
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
Merge Cond: (t1.id = t3.id)
- -> Merge Join (cost=xxx rows=1000 width=xxx)
+ -> Merge Join (cost=xxx..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)
+ -> Index Scan using t1_pkey on t1 (cost=xxx..xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: t3.id
- -> Seq Scan on t3 (cost=xxx rows=100 width=xxx)
-(9 rows)
+ -> Seq Scan on t3 (cost=xxx..xxx rows=100 width=xxx)
\! rm results/pg_hint_plan.tmpout
+-- hint error level
+set client_min_messages to 'DEBUG1';
+set pg_hint_plan.debug_level to 'verbose';
+/*+ SeqScan( */ SELECT 1;
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Closing parenthesis is necessary.
+ ?column?
+----------
+ 1
+(1 row)
+
+/*+ SeqScan(t1) */ SELECT * FROM t1 LIMIT 0;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ id | val
+----+-----
+(0 rows)
+
+set pg_hint_plan.message_level to 'DEBUG1';
+set pg_hint_plan.parse_messages to 'NOTICE';
+/*+ SeqScan( */ SELECT 1;
+NOTICE: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Closing parenthesis is necessary.
+ ?column?
+----------
+ 1
+(1 row)
+
+/*+ SeqScan(t1) */ SELECT * FROM t1 LIMIT 0;
+DEBUG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ id | val
+----+-----
+(0 rows)
+
+-- all hint types together
+/*+ SeqScan(t1) MergeJoin(t1 t2) Leading(t1 t2) Rows(t1 t2 +10) Parallel(t1 8 hard) Set(random_page_cost 2.0)*/
+EXPLAIN (costs off) SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
+DEBUG: adjusted rows 1000 to 1010
+DEBUG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+MergeJoin(t1 t2)
+Leading(t1 t2)
+Set(random_page_cost 2.0)
+Rows(t1 t2 +10)
+Parallel(t1 8 hard)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------
+ Nested Loop
+ -> Merge Join
+ Merge Cond: (t2.id = t1.id)
+ -> Index Scan using t2_pkey on t2
+ -> Sort
+ Sort Key: t1.id
+ -> Gather
+ Workers Planned: 8
+ -> Parallel Seq Scan on t1
+ -> Index Scan using t3_pkey on t3
+ Index Cond: (id = t1.id)
+(11 rows)
+
OSDN Git Service
