+-- identifier length test
+EXPLAIN (COSTS false) SELECT * FROM t1 "123456789012345678901234567890123456789012345678901234567890123" JOIN t2 ON ("123456789012345678901234567890123456789012345678901234567890123".id = t2.id) JOIN t3 ON (t2.id = t3.id);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------
+ Merge Join
+ Merge Cond: ("123456789012345678901234567890123456789012345678901234567890123".id = t3.id)
+ -> Merge Join
+ Merge Cond: ("123456789012345678901234567890123456789012345678901234567890123".id = t2.id)
+ -> Index Scan using t1_pkey on t1 "123456789012345678901234567890123456789012345678901234567890123"
+ -> Index Scan using t2_pkey on t2
+ -> Sort
+ Sort Key: t3.id
+ -> Seq Scan on t3
+(9 rows)
+
+/*+
+Leading(123456789012345678901234567890123456789012345678901234567890123 t2 t3)
+SeqScan(123456789012345678901234567890123456789012345678901234567890123)
+MergeJoin(123456789012345678901234567890123456789012345678901234567890123 t2)
+Set(123456789012345678901234567890123456789012345678901234567890123 1)
+*/
+EXPLAIN (COSTS false) SELECT * FROM t1 "123456789012345678901234567890123456789012345678901234567890123" JOIN t2 ON ("123456789012345678901234567890123456789012345678901234567890123".id = t2.id) JOIN t3 ON (t2.id = t3.id);
+INFO: unrecognized configuration parameter "123456789012345678901234567890123456789012345678901234567890123"
+LOG: pg_hint_plan:
+used hint:
+SeqScan(123456789012345678901234567890123456789012345678901234567890123)
+MergeJoin(123456789012345678901234567890123456789012345678901234567890123 t2)
+Leading(123456789012345678901234567890123456789012345678901234567890123 t2 t3)
+not used hint:
+duplication hint:
+error hint:
+Set(123456789012345678901234567890123456789012345678901234567890123 1)
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Merge Join
+ Merge Cond: ("123456789012345678901234567890123456789012345678901234567890123".id = t3.id)
+ -> Merge Join
+ Merge Cond: (t2.id = "123456789012345678901234567890123456789012345678901234567890123".id)
+ -> Index Scan using t2_pkey on t2
+ -> Sort
+ Sort Key: "123456789012345678901234567890123456789012345678901234567890123".id
+ -> Seq Scan on t1 "123456789012345678901234567890123456789012345678901234567890123"
+ -> Sort
+ Sort Key: t3.id
+ -> Seq Scan on t3
+(11 rows)
+
+/*+
+Leading(1234567890123456789012345678901234567890123456789012345678901234 t2 t3)
+SeqScan(1234567890123456789012345678901234567890123456789012345678901234)
+MergeJoin(1234567890123456789012345678901234567890123456789012345678901234 t2)
+Set(1234567890123456789012345678901234567890123456789012345678901234 1)
+Set(cursor_tuple_fraction 0.1234567890123456789012345678901234567890123456789012345678901234)
+*/
+EXPLAIN (COSTS false) SELECT * FROM t1 "1234567890123456789012345678901234567890123456789012345678901234" JOIN t2 ON ("1234567890123456789012345678901234567890123456789012345678901234".id = t2.id) JOIN t3 ON (t2.id = t3.id);
+NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
+NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
+NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
+NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
+NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
+NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
+INFO: unrecognized configuration parameter "123456789012345678901234567890123456789012345678901234567890123"
+LOG: pg_hint_plan:
+used hint:
+SeqScan(123456789012345678901234567890123456789012345678901234567890123)
+MergeJoin(123456789012345678901234567890123456789012345678901234567890123 t2)
+Leading(123456789012345678901234567890123456789012345678901234567890123 t2 t3)
+Set(cursor_tuple_fraction 0.1234567890123456789012345678901234567890123456789012345678901234)
+not used hint:
+duplication hint:
+error hint:
+Set(123456789012345678901234567890123456789012345678901234567890123 1)
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Merge Join
+ Merge Cond: ("123456789012345678901234567890123456789012345678901234567890123".id = t3.id)
+ -> Merge Join
+ Merge Cond: (t2.id = "123456789012345678901234567890123456789012345678901234567890123".id)
+ -> Index Scan using t2_pkey on t2
+ -> Sort
+ Sort Key: "123456789012345678901234567890123456789012345678901234567890123".id
+ -> Seq Scan on t1 "123456789012345678901234567890123456789012345678901234567890123"
+ -> Sort
+ Sort Key: t3.id
+ -> Seq Scan on t3
+(11 rows)
+
+SET "123456789012345678901234567890123456789012345678901234567890123" TO 1;
+ERROR: unrecognized configuration parameter "123456789012345678901234567890123456789012345678901234567890123"
+SET "1234567890123456789012345678901234567890123456789012345678901234" TO 1;
+NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
+ERROR: unrecognized configuration parameter "123456789012345678901234567890123456789012345678901234567890123"
+SET cursor_tuple_fraction TO 1234567890123456789012345678901234567890123456789012345678901234;
+ERROR: 1.23457e+63 is outside the valid range for parameter "cursor_tuple_fraction" (0 .. 1)
+-- multi error
+/*+ 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
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+Set(enable_seqscan 100)
+Set(seq_page_cost on)
+
+ QUERY PLAN
+--------------------------------------
+ Merge Join
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ -> Index Scan using t2_pkey on t2
+(4 rows)
+
+-- debug log of candidate index to use IndexScan
+EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
+ Index Cond: (id = 1)
+(2 rows)
+
+/*+IndexScan(t5 t5_id2)*/
+EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
+LOG: available indexes for IndexScan(t5): t5_id2
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t5 t5_id2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------
+ Index Scan using t5_id2 on t5
+ Index Cond: (id = 1)
+(2 rows)
+
+/*+IndexScan(t5 no_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
+LOG: available indexes for IndexScan(t5):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t5 no_exist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+/*+IndexScan(t5 t5_id1 t5_id2)*/
+EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
+LOG: available indexes for IndexScan(t5): t5_id2 t5_id1
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t5 t5_id1 t5_id2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------
+ Index Scan using t5_id2 on t5
+ Index Cond: (id = 1)
+(2 rows)
+
+/*+IndexScan(t5 no_exist t5_id2)*/
+EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
+LOG: available indexes for IndexScan(t5): t5_id2
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t5 no_exist t5_id2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------
+ Index Scan using t5_id2 on t5
+ Index Cond: (id = 1)
+(2 rows)
+
+/*+IndexScan(t5 no_exist5 no_exist2)*/
+EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
+LOG: available indexes for IndexScan(t5):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t5 no_exist5 no_exist2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+-- outer inner
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+ QUERY PLAN
+--------------------------------------------------------
+ Hash Join
+ Hash Cond: (t3.val = t2.val)
+ -> Seq Scan on t3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.id = t1.id)
+ -> Seq Scan on t2
+ -> Hash
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+(10 rows)
+
+/*+Leading((t1))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+INFO: pg_hint_plan: hint syntax error at or near "Leading((t1))"
+DETAIL: Leading hint requires two sets of relations when parentheses nests.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+Leading((t1))
+
+ QUERY PLAN
+--------------------------------------------------------
+ Hash Join
+ Hash Cond: (t3.val = t2.val)
+ -> Seq Scan on t3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.id = t1.id)
+ -> Seq Scan on t2
+ -> Hash
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+(10 rows)
+
+/*+Leading((t1 t2))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading((t1 t2))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ Join Filter: (t2.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Seq Scan on t3
+(9 rows)
+
+/*+Leading((t1 t2 t3))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+INFO: pg_hint_plan: hint syntax error at or near "Leading((t1 t2 t3))"
+DETAIL: Leading hint requires two sets of relations when parentheses nests.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+Leading((t1 t2 t3))
+
+ QUERY PLAN
+--------------------------------------------------------
+ Hash Join
+ Hash Cond: (t3.val = t2.val)
+ -> Seq Scan on t3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.id = t1.id)
+ -> Seq Scan on t2
+ -> Hash
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+(10 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.id < 10;
+ QUERY PLAN
+--------------------------------------------
+ Hash Join
+ Hash Cond: (t2.id = t1.id)
+ -> Seq Scan on t2
+ -> Hash
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+(6 rows)
+
+/*+Leading((t1 t2))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading((t1 t2))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------
+ Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+(6 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+ QUERY PLAN
+--------------------------------------------------------
+ Hash Join
+ Hash Cond: (t3.val = t2.val)
+ -> Seq Scan on t3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.id = t1.id)
+ -> Seq Scan on t2
+ -> Hash
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+(10 rows)
+
+/*+Leading(((t1 t2) t3))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading(((t1 t2) t3))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ Join Filter: (t2.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Seq Scan on t3
+(9 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop
+ -> Merge Join
+ Merge Cond: (t3.id = t4.id)
+ -> Nested Loop
+ Join Filter: (t1.val = t3.val)
+ -> Index Scan using t3_pkey on t3
+ -> Materialize
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+ -> Index Scan using t2_pkey on t2
+ Index Cond: (id = t1.id)
+(14 rows)
+
+/*+Leading((((t1 t2) t3) t4))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading((((t1 t2) t3) t4))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop
+ -> Nested Loop
+ Join Filter: (t1.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Seq Scan on t3
+ -> Index Scan using t4_pkey on t4
+ Index Cond: (id = t3.id)
+(12 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+ QUERY PLAN
+--------------------------------------------------------
+ Hash Join
+ Hash Cond: (t3.val = t2.val)
+ -> Seq Scan on t3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.id = t1.id)
+ -> Seq Scan on t2
+ -> Hash
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+(10 rows)
+
+/*+Leading(((t1 t2) t3))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading(((t1 t2) t3))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ Join Filter: (t2.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Seq Scan on t3
+(9 rows)
+
+/*+Leading((t1 (t2 t3)))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading((t1 (t2 t3)))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.val = t3.val)
+ -> Seq Scan on t2
+ -> Hash
+ -> Seq Scan on t3
+(10 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop
+ -> Merge Join
+ Merge Cond: (t3.id = t4.id)
+ -> Nested Loop
+ Join Filter: (t1.val = t3.val)
+ -> Index Scan using t3_pkey on t3
+ -> Materialize
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+ -> Index Scan using t2_pkey on t2
+ Index Cond: (id = t1.id)
+(14 rows)
+
+/*+Leading(((t1 t2) (t3 t4)))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading(((t1 t2) (t3 t4)))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ Join Filter: (t1.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Merge Join
+ Merge Cond: (t3.id = t4.id)
+ -> Index Scan using t3_pkey on t3
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+(14 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Hash Join
+ Hash Cond: (t2.val = t3.val)
+ InitPlan 1 (returns $1)
+ -> Limit
+ -> Sort
+ Sort Key: t1_2.id
+ -> Nested Loop
+ -> Index Scan using t2_val on t2 t2_2
+ Index Cond: (val > 100)
+ -> Index Only Scan using t1_pkey on t1 t1_2
+ Index Cond: (id = t2_2.id)
+ -> Merge Join
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < $1)
+ -> Index Scan using t2_pkey on t2
+ -> Hash
+ -> Seq Scan on t3
+(18 rows)
+
+/*+Leading(((t1 t2) t3)) Leading(((t3 t1) t2))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t1.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
+INFO: pg_hint_plan: hint syntax error at or near "Leading(((t1 t2) t3)) Leading(((t3 t1) t2))"
+DETAIL: Conflict leading hint.
+LOG: pg_hint_plan:
+used hint:
+Leading(((t3 t1) t2))
+not used hint:
+duplication hint:
+Leading(((t1 t2) t3))
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------------------
+ Hash Join
+ Hash Cond: (t1.id = t2.id)
+ InitPlan 1 (returns $1)
+ -> Limit
+ -> Sort
+ Sort Key: t1_2.id
+ -> Nested Loop
+ -> Index Scan using t2_val on t2 t2_2
+ Index Cond: (val > 100)
+ -> Index Only Scan using t1_pkey on t1 t1_2
+ Index Cond: (id = t2_2.id)
+ -> Hash Join
+ Hash Cond: (t3.val = t1.val)
+ -> Seq Scan on t3
+ -> Hash
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < $1)
+ -> Hash
+ -> Seq Scan on t2
+(19 rows)
+
+/*+Leading(((t1 t2) t3)) Leading((t1_2 t2_2))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
+LOG: pg_hint_plan:
+used hint:
+Leading(((t1 t2) t3))
+Leading((t1_2 t2_2))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------------
+ Hash Join
+ Hash Cond: (t2.val = t3.val)
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Merge Join
+ Merge Cond: (t1_2.id = t2_2.id)
+ -> Index Only Scan using t1_pkey on t1 t1_2
+ -> Sort
+ Sort Key: t2_2.id
+ -> Index Scan using t2_val on t2 t2_2
+ Index Cond: (val > 100)
+ -> Merge Join
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < $0)
+ -> Index Scan using t2_pkey on t2
+ -> Hash
+ -> Seq Scan on t3
+(18 rows)
+
+/*+Leading(((((t1 t2) t3) t1_2) t2_2))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+Leading(((((t1 t2) t3) t1_2) t2_2))
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------------------
+ Hash Join
+ Hash Cond: (t2.val = t3.val)
+ InitPlan 1 (returns $1)
+ -> Limit
+ -> Sort
+ Sort Key: t1_2.id
+ -> Nested Loop
+ -> Index Scan using t2_val on t2 t2_2
+ Index Cond: (val > 100)
+ -> Index Only Scan using t1_pkey on t1 t1_2
+ Index Cond: (id = t2_2.id)
+ -> Merge Join
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < $1)
+ -> Index Scan using t2_pkey on t2
+ -> Hash
+ -> Seq Scan on t3
+(18 rows)
+
+-- Specified outer/inner leading hint and join method hint at the same time
+/*+Leading(((t1 t2) t3))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading(((t1 t2) t3))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ Join Filter: (t2.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Seq Scan on t3
+(9 rows)
+
+/*+Leading(((t1 t2) t3)) MergeJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t1 t2)
+Leading(((t1 t2) t3))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ Join Filter: (t2.val = t3.val)
+ -> Merge Join
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Index Scan using t2_pkey on t2
+ -> Seq Scan on t3
+(8 rows)
+
+/*+Leading(((t1 t2) t3)) MergeJoin(t1 t2 t3)*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t1 t2 t3)
+Leading(((t1 t2) t3))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------
+ Merge Join
+ Merge Cond: (t2.val = t3.val)
+ -> Sort
+ Sort Key: t2.val
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Sort
+ Sort Key: t3.val
+ -> Seq Scan on t3
+(13 rows)
+
+/*+Leading(((t1 t2) t3)) MergeJoin(t1 t3)*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading(((t1 t2) t3))
+not used hint:
+MergeJoin(t1 t3)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ Join Filter: (t2.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Seq Scan on t3
+(9 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop
+ -> Merge Join
+ Merge Cond: (t3.id = t4.id)
+ -> Nested Loop
+ Join Filter: (t1.val = t3.val)
+ -> Index Scan using t3_pkey on t3
+ -> Materialize
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+ -> Index Scan using t2_pkey on t2
+ Index Cond: (id = t1.id)
+(14 rows)
+
+/*+Leading(((t1 t2) t3)) MergeJoin(t3 t4)*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading(((t1 t2) t3))
+not used hint:
+MergeJoin(t3 t4)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop
+ Join Filter: (t3.id = t4.id)
+ -> Nested Loop
+ Join Filter: (t1.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Seq Scan on t3
+ -> Seq Scan on t4
+(12 rows)
+
+/*+Leading(((t1 t2) t3)) MergeJoin(t1 t2 t3 t4)*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t1 t2 t3 t4)
+Leading(((t1 t2) t3))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------
+ Merge Join
+ Merge Cond: (t3.id = t4.id)
+ -> Sort
+ Sort Key: t3.id
+ -> Nested Loop
+ Join Filter: (t1.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Seq Scan on t3
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+(16 rows)
+
+/*+ Leading ( ( t1 ( t2 t3 ) ) ) */
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading((t1 (t2 t3)))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.val = t3.val)
+ -> Seq Scan on t2
+ -> Hash
+ -> Seq Scan on t3
+(10 rows)
+
+/*+Leading((t1(t2 t3)))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading((t1 (t2 t3)))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.val = t3.val)
+ -> Seq Scan on t2
+ -> Hash
+ -> Seq Scan on t3
+(10 rows)
+
+/*+Leading(("t1(t2" "t3)"))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+Leading(("t1(t2" "t3)"))
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------
+ Hash Join
+ Hash Cond: (t3.val = t2.val)
+ -> Seq Scan on t3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.id = t1.id)
+ -> Seq Scan on t2
+ -> Hash
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+(10 rows)
+
+/*+ Leading ( ( ( t1 t2 ) t3 ) ) */
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading(((t1 t2) t3))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ Join Filter: (t2.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Seq Scan on t3
+(9 rows)
+
+/*+Leading(((t1 t2)t3))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+Leading(((t1 t2) t3))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Nested Loop
+ Join Filter: (t2.val = t3.val)
+ -> Hash Join
+ Hash Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+ -> Hash
+ -> Seq Scan on t2
+ -> Seq Scan on t3
+(9 rows)
+
+/*+Leading(("(t1" "t2)t3"))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+Leading(("(t1" "t2)t3"))
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------
+ Hash Join
+ Hash Cond: (t3.val = t2.val)
+ -> Seq Scan on t3
+ -> Hash
+ -> Hash Join
+ Hash Cond: (t2.id = t1.id)
+ -> Seq Scan on t2
+ -> Hash
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id < 10)
+(10 rows)
+
+/*+Leading((t1(t2(t3(t4 t5)))))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
+LOG: pg_hint_plan:
+used hint:
+Leading((t1 (t2 (t3 (t4 t5)))))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------
+ Merge Join
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t2.id = t3.id)
+ -> Index Scan using t2_pkey on t2
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t3.id = t4.id)
+ -> Index Scan using t3_pkey on t3
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t4.id = t5.id)
+ -> Index Scan using t4_pkey on t4
+ -> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
+(16 rows)
+
+/*+Leading((t5(t4(t3(t2 t1)))))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
+LOG: pg_hint_plan:
+used hint:
+Leading((t5 (t4 (t3 (t2 t1)))))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Hash Join
+ Hash Cond: (t5.id = t1.id)
+ -> Seq Scan on t5
+ -> Hash
+ -> Merge Join
+ Merge Cond: (t4.id = t1.id)
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t3.id = t1.id)
+ -> Sort
+ Sort Key: t3.id
+ -> Seq Scan on t3
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t2.id = t1.id)
+ -> Index Scan using t2_pkey on t2
+ -> Index Scan using t1_pkey on t1
+(20 rows)
+
+/*+Leading(((((t1 t2)t3)t4)t5))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
+LOG: pg_hint_plan:
+used hint:
+Leading(((((t1 t2) t3) t4) t5))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Nested Loop
+ -> Merge Join
+ Merge Cond: (t1.id = t4.id)
+ -> Merge Join
+ Merge Cond: (t1.id = t3.id)
+ -> Merge Join
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ -> Index Scan using t2_pkey on t2
+ -> Sort
+ Sort Key: t3.id
+ -> Seq Scan on t3
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+ -> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
+ Index Cond: (id = t1.id)
+(17 rows)
+
+/*+Leading(((((t5 t4)t3)t2)t1))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
+LOG: pg_hint_plan:
+used hint:
+Leading(((((t5 t4) t3) t2) t1))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (t2.id = t1.id)
+ -> Nested Loop
+ Join Filter: (t3.id = t2.id)
+ -> Merge Join
+ Merge Cond: (t4.id = t3.id)
+ -> Merge Join
+ Merge Cond: (t5.id = t4.id)
+ -> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+ -> Index Scan using t3_pkey on t3
+ -> Index Scan using t2_pkey on t2
+ Index Cond: (id = t5.id)
+ -> Index Scan using t1_pkey on t1
+ Index Cond: (id = t5.id)
+(17 rows)
+
+/*+Leading(((t1 t2)(t3(t4 t5))))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
+LOG: pg_hint_plan:
+used hint:
+Leading(((t1 t2) (t3 (t4 t5))))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------
+ Merge Join
+ Merge Cond: (t1.id = t3.id)
+ -> Merge Join
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ -> Index Scan using t2_pkey on t2
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t3.id = t4.id)
+ -> Index Scan using t3_pkey on t3
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t4.id = t5.id)
+ -> Index Scan using t4_pkey on t4
+ -> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
+(15 rows)
+
+/*+Leading(((t5 t4)(t3(t2 t1))))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
+LOG: pg_hint_plan:
+used hint:
+Leading(((t5 t4) (t3 (t2 t1))))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Merge Join
+ Merge Cond: (t4.id = t1.id)
+ -> Merge Join
+ Merge Cond: (t5.id = t4.id)
+ -> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t3.id = t1.id)
+ -> Sort
+ Sort Key: t3.id
+ -> Seq Scan on t3
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t2.id = t1.id)
+ -> Index Scan using t2_pkey on t2
+ -> Index Scan using t1_pkey on t1
+(19 rows)
+
+/*+Leading((((t1 t2)t3)(t4 t5)))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
+LOG: pg_hint_plan:
+used hint:
+Leading((((t1 t2) t3) (t4 t5)))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Merge Join
+ Merge Cond: (t1.id = t4.id)
+ -> Merge Join
+ Merge Cond: (t1.id = t3.id)
+ -> Merge Join
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1
+ -> Index Scan using t2_pkey on t2
+ -> Sort
+ Sort Key: t3.id
+ -> Seq Scan on t3
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t4.id = t5.id)
+ -> Index Scan using t4_pkey on t4
+ -> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
+(16 rows)
+
+/*+Leading((((t5 t4)t3)(t2 t1)))*/
+EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
+LOG: pg_hint_plan:
+used hint:
+Leading((((t5 t4) t3) (t2 t1)))
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Merge Join
+ Merge Cond: (t3.id = t1.id)
+ -> Merge Join
+ Merge Cond: (t4.id = t3.id)
+ -> Merge Join
+ Merge Cond: (t5.id = t4.id)
+ -> Index Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
+ -> Sort
+ Sort Key: t4.id
+ -> Seq Scan on t4
+ -> Index Scan using t3_pkey on t3
+ -> Materialize
+ -> Merge Join
+ Merge Cond: (t2.id = t1.id)
+ -> Index Scan using t2_pkey on t2
+ -> Index Scan using t1_pkey on t1
+(16 rows)
+
+-- inherite table test to specify the index's name
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Append
+ -> Seq Scan on p2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(9 rows)
+
+/*+IndexScan(p2 p2_pkey)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2): p2_pkey
+LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------
+ Append
+ -> Index Scan using p2_pkey on p2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_pkey on p2_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+(13 rows)
+
+/*+IndexScan(p2 p2_id_val_idx)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2): p2_id_val_idx
+LOG: available indexes for IndexScan(p2_c1): p2_c1_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: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_id_val_idx)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Index Scan using p2_id_val_idx on p2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_id_val_idx on p2_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
+ 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 Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+(13 rows)
+
+/*+IndexScan(p2 p2_val_id_idx)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2): p2_val_id_idx
+LOG: available indexes for IndexScan(p2_c1): p2_c1_val_id_idx
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_id_idx
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_id_idx
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_val_id_idx)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Index Scan using p2_val_id_idx on p2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_val_id_idx on p2_c1
+ 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 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 Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+(13 rows)
+
+EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on p2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c4
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+(19 rows)
+
+-- Inhibit parallel exection to avoid interfaring the hint
+set max_parallel_workers_per_gather to 0;
+/*+ IndexScan(p2 p2_val)*/
+EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2):
+LOG: available indexes for IndexScan(p2_c1):
+LOG: available indexes for IndexScan(p2_c2):
+LOG: available indexes for IndexScan(p2_c3):
+LOG: available indexes for IndexScan(p2_c4):
+LOG: available indexes for IndexScan(p2_c1_c1):
+LOG: available indexes for IndexScan(p2_c1_c2):
+LOG: available indexes for IndexScan(p2_c3_c1):
+LOG: available indexes for IndexScan(p2_c3_c2):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_val)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on p2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c4
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+(19 rows)
+
+/*+IndexScan(p2 p2_pkey)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2): p2_pkey
+LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------
+ Append
+ -> Index Scan using p2_pkey on p2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_pkey on p2_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+(13 rows)
+
+/*+IndexScan(p2 p2_id2_val)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2): p2_id2_val
+LOG: available indexes for IndexScan(p2_c1): p2_c1_id2_val
+LOG: available indexes for IndexScan(p2_c1_c1):
+LOG: available indexes for IndexScan(p2_c1_c2):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_id2_val)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Append
+ -> Index Scan using p2_id2_val on p2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_id2_val on p2_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(11 rows)
+
+/*+IndexScan(p2 p2_val2_id)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2): p2_val2_id
+LOG: available indexes for IndexScan(p2_c1):
+LOG: available indexes for IndexScan(p2_c1_c1):
+LOG: available indexes for IndexScan(p2_c1_c2):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_val2_id)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Append
+ -> Index Scan using p2_val2_id on p2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on p2_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(10 rows)
+
+/*+IndexScan(p2 p2_pkey)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2): p2_pkey
+LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------
+ Append
+ -> Index Scan using p2_pkey on p2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_pkey on p2_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+(13 rows)
+
+/*+IndexScan(p2 p2_c1_id_val_idx)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2):
+LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
+LOG: available indexes for IndexScan(p2_c1_c1):
+LOG: available indexes for IndexScan(p2_c1_c2):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_c1_id_val_idx)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Append
+ -> Seq Scan on p2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Index Scan using p2_c1_id_val_idx on p2_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(10 rows)
+
+/*+IndexScan(p2 no_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2):
+LOG: available indexes for IndexScan(p2_c1):
+LOG: available indexes for IndexScan(p2_c1_c1):
+LOG: available indexes for IndexScan(p2_c1_c2):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 no_exist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Append
+ -> Seq Scan on p2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(9 rows)
+
+/*+IndexScan(p2 p2_pkey p2_c1_id_val_idx)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2): p2_pkey
+LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx p2_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_pkey p2_c1_id_val_idx)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------
+ Append
+ -> Index Scan using p2_pkey on p2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_id_val_idx on p2_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+(13 rows)
+
+/*+IndexScan(p2 p2_pkey no_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2): p2_pkey
+LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_pkey no_exist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------
+ Append
+ -> Index Scan using p2_pkey on p2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_pkey on p2_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+(13 rows)
+
+/*+IndexScan(p2 p2_c1_id_val_idx no_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2):
+LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
+LOG: available indexes for IndexScan(p2_c1_c1):
+LOG: available indexes for IndexScan(p2_c1_c2):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_c1_id_val_idx no_exist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Append
+ -> Seq Scan on p2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Index Scan using p2_c1_id_val_idx on p2_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on p2_c1_c1
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(10 rows)
+
+/*+IndexScan(p2 p2_pkey p2_c1_id_val_idx no_exist)*/
+EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
+LOG: available indexes for IndexScan(p2): p2_pkey
+LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx p2_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_pkey p2_c1_id_val_idx no_exist)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------
+ Append
+ -> Index Scan using p2_pkey on p2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_id_val_idx on p2_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c1_pkey on p2_c1_c1
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c1_c2_pkey on p2_c1_c2
+ Index Cond: ((id >= 50) AND (id <= 51))
+ Filter: (ctid = '(1,1)'::tid)
+(13 rows)
+
+/*+IndexScan(p2 p2_val_idx)*/
+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_idx
+LOG: available indexes for IndexScan(p2_c1): p2_c1_val_idx
+LOG: available indexes for IndexScan(p2_c2): p2_c2_val_idx
+LOG: available indexes for IndexScan(p2_c3): p2_c3_val_idx
+LOG: available indexes for IndexScan(p2_c4): p2_c4_val_idx
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_idx
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_idx
+LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_idx
+LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_idx
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_val_idx)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------------------
+ Append
+ -> Index Scan using p2_val_idx on p2
+ 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 Cond: ((val >= '50'::text) AND (val <= '51'::text))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c2_val_idx on p2_c2
+ 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 Cond: ((val >= '50'::text) AND (val <= '51'::text))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c4_val_idx on p2_c4
+ 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 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 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 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 Cond: ((val >= '50'::text) AND (val <= '51'::text))
+ Filter: (ctid = '(1,1)'::tid)
+(28 rows)
+
+/*+IndexScan(p2 p2_expr)*/
+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_expr
+LOG: available indexes for IndexScan(p2_c1): p2_c1_expr_idx
+LOG: available indexes for IndexScan(p2_c2): p2_c2_expr_idx
+LOG: available indexes for IndexScan(p2_c3): p2_c3_expr_idx
+LOG: available indexes for IndexScan(p2_c4): p2_c4_expr_idx
+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_expr_idx
+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_expr_idx
+LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_expr_idx
+LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_expr_idx
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_expr)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on p2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c4
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+(19 rows)
+
+/*+IndexScan(p2 p2_val_idx6)*/
+EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
+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: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_val_idx6)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on p2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c4
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c1_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c1
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+ -> Seq Scan on p2_c3_c2
+ Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid))
+(19 rows)
+
+/*+IndexScan(p2 p2_val_idx p2_val_idx6)*/
+EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
+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: pg_hint_plan:
+used hint:
+IndexScan(p2 p2_val_idx p2_val_idx6)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------------------
+ Append
+ -> Index Scan using p2_val_idx on p2
+ 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 Cond: ((val >= '50'::text) AND (val <= '51'::text))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c2_val_idx on p2_c2
+ 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 Cond: ((val >= '50'::text) AND (val <= '51'::text))
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using p2_c4_val_idx on p2_c4
+ 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 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 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 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 Cond: ((val >= '50'::text) AND (val <= '51'::text))
+ Filter: (ctid = '(1,1)'::tid)
+(28 rows)
+
+-- regular expression
+-- ordinary table
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Index Only Scan using t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa on t5
+ Index Cond: (id = 1)
+(2 rows)
+
+/*+ IndexScanRegexp(t5 t5_[^i].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for IndexScanRegexp(t5): t5_val t5_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(t5 t5_[^i].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------
+ Index Scan using t5_pkey on t5
+ Index Cond: (id = 1)
+(2 rows)
+
+/*+ IndexScanRegexp(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for IndexScanRegexp(t5): t5_id3 t5_id2 t5_id1
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(t5 t5_id[0-9].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------
+ Index Scan using t5_id3 on t5
+ Index Cond: (id = 1)
+(2 rows)
+
+/*+ IndexScanRegexp(t5 t5[^_].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for IndexScanRegexp(t5):
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(t5 t5[^_].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+/*+ IndexScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for IndexScanRegexp(t5):
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+/*+ IndexScan(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for IndexScan(t5):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t5 t5_id[0-9].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+/*+ IndexOnlyScanRegexp(t5 t5_[^i].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for IndexOnlyScanRegexp(t5): t5_val t5_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScanRegexp(t5 t5_[^i].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------
+ Index Only Scan using t5_pkey on t5
+ Index Cond: (id = 1)
+(2 rows)
+
+/*+ IndexOnlyScanRegexp(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for IndexOnlyScanRegexp(t5): t5_id3 t5_id2 t5_id1
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScanRegexp(t5 t5_id[0-9].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Index Only Scan using t5_id3 on t5
+ Index Cond: (id = 1)
+(2 rows)
+
+/*+ IndexOnlyScanRegexp(t5 t5[^_].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for IndexOnlyScanRegexp(t5):
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScanRegexp(t5 t5[^_].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+/*+ IndexOnlyScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for IndexOnlyScanRegexp(t5):
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+/*+ IndexOnlyScan(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for IndexOnlyScan(t5):
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(t5 t5_id[0-9].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+/*+ BitmapScanRegexp(t5 t5_[^i].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for BitmapScanRegexp(t5): t5_val t5_pkey
+LOG: pg_hint_plan:
+used hint:
+BitmapScanRegexp(t5 t5_[^i].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Bitmap Heap Scan on t5
+ Recheck Cond: (id = 1)
+ -> Bitmap Index Scan on t5_pkey
+ Index Cond: (id = 1)
+(4 rows)
+
+/*+ BitmapScanRegexp(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for BitmapScanRegexp(t5): t5_id3 t5_id2 t5_id1
+LOG: pg_hint_plan:
+used hint:
+BitmapScanRegexp(t5 t5_id[0-9].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------
+ Bitmap Heap Scan on t5
+ Recheck Cond: (id = 1)
+ -> Bitmap Index Scan on t5_id3
+ Index Cond: (id = 1)
+(4 rows)
+
+/*+ BitmapScanRegexp(t5 t5[^_].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for BitmapScanRegexp(t5):
+LOG: pg_hint_plan:
+used hint:
+BitmapScanRegexp(t5 t5[^_].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+/*+ BitmapScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for BitmapScanRegexp(t5):
+LOG: pg_hint_plan:
+used hint:
+BitmapScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+/*+ BitmapScan(t5 t5_id[0-9].*)*/
+EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
+LOG: available indexes for BitmapScan(t5):
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t5 t5_id[0-9].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t5
+ Filter: (id = 1)
+(2 rows)
+
+-- Inheritance
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+ QUERY PLAN
+----------------------------
+ Append
+ -> Seq Scan on p1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3
+ Filter: (val = 1)
+ -> Seq Scan on p1_c4
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c2
+ Filter: (val = 1)
+(19 rows)
+
+/*+ IndexScanRegexp(p1 p1_.*[^0-9]$)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for IndexScanRegexp(p1): p1_pkey
+LOG: available indexes for IndexScanRegexp(p1_c1): p1_c1_pkey
+LOG: available indexes for IndexScanRegexp(p1_c2): p1_c2_pkey
+LOG: available indexes for IndexScanRegexp(p1_c3): p1_c3_pkey
+LOG: available indexes for IndexScanRegexp(p1_c4): p1_c4_pkey
+LOG: available indexes for IndexScanRegexp(p1_c1_c1): p1_c1_c1_pkey
+LOG: available indexes for IndexScanRegexp(p1_c1_c2): p1_c1_c2_pkey
+LOG: available indexes for IndexScanRegexp(p1_c3_c1): p1_c3_c1_pkey
+LOG: available indexes for IndexScanRegexp(p1_c3_c2): p1_c3_c2_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(p1 p1_.*[^0-9]$)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------
+ Append
+ -> Seq Scan on p1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3
+ Filter: (val = 1)
+ -> Seq Scan on p1_c4
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c2
+ Filter: (val = 1)
+(19 rows)
+
+/*+ IndexScanRegexp(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for IndexScanRegexp(p1): p1_val2
+LOG: available indexes for IndexScanRegexp(p1_c1): p1_c1_val2
+LOG: available indexes for IndexScanRegexp(p1_c2): p1_c2_val2
+LOG: available indexes for IndexScanRegexp(p1_c3): p1_c3_val2
+LOG: available indexes for IndexScanRegexp(p1_c4): p1_c4_val2
+LOG: available indexes for IndexScanRegexp(p1_c1_c1): p1_c1_c1_val2
+LOG: available indexes for IndexScanRegexp(p1_c1_c2): p1_c1_c2_val2
+LOG: available indexes for IndexScanRegexp(p1_c3_c1): p1_c3_c1_val2
+LOG: available indexes for IndexScanRegexp(p1_c3_c2): p1_c3_c2_val2
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(p1 p1_.*val2.*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------
+ Append
+ -> Index Scan using p1_val2 on p1
+ Index Cond: (val = 1)
+ -> Index Scan using p1_c1_val2 on p1_c1
+ Index Cond: (val = 1)
+ -> Index Scan using p1_c2_val2 on p1_c2
+ Index Cond: (val = 1)
+ -> Index Scan using p1_c3_val2 on p1_c3
+ Index Cond: (val = 1)
+ -> Index Scan using p1_c4_val2 on p1_c4
+ Index Cond: (val = 1)
+ -> Index Scan using p1_c1_c1_val2 on p1_c1_c1
+ Index Cond: (val = 1)
+ -> Index Scan using p1_c1_c2_val2 on p1_c1_c2
+ Index Cond: (val = 1)
+ -> Index Scan using p1_c3_c1_val2 on p1_c3_c1
+ Index Cond: (val = 1)
+ -> Index Scan using p1_c3_c2_val2 on p1_c3_c2
+ Index Cond: (val = 1)
+(19 rows)
+
+/*+ IndexScanRegexp(p1 p1[^_].*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for IndexScanRegexp(p1):
+LOG: available indexes for IndexScanRegexp(p1_c1):
+LOG: available indexes for IndexScanRegexp(p1_c2):
+LOG: available indexes for IndexScanRegexp(p1_c3):
+LOG: available indexes for IndexScanRegexp(p1_c4):
+LOG: available indexes for IndexScanRegexp(p1_c1_c1):
+LOG: available indexes for IndexScanRegexp(p1_c1_c2):
+LOG: available indexes for IndexScanRegexp(p1_c3_c1):
+LOG: available indexes for IndexScanRegexp(p1_c3_c2):
+LOG: pg_hint_plan:
+used hint:
+IndexScanRegexp(p1 p1[^_].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------
+ Append
+ -> Seq Scan on p1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3
+ Filter: (val = 1)
+ -> Seq Scan on p1_c4
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c2
+ Filter: (val = 1)
+(19 rows)
+
+/*+ IndexScan(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for IndexScan(p1):
+LOG: available indexes for IndexScan(p1_c1):
+LOG: available indexes for IndexScan(p1_c2):
+LOG: available indexes for IndexScan(p1_c3):
+LOG: available indexes for IndexScan(p1_c4):
+LOG: available indexes for IndexScan(p1_c1_c1):
+LOG: available indexes for IndexScan(p1_c1_c2):
+LOG: available indexes for IndexScan(p1_c3_c1):
+LOG: available indexes for IndexScan(p1_c3_c2):
+LOG: pg_hint_plan:
+used hint:
+IndexScan(p1 p1_.*val2.*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------
+ Append
+ -> Seq Scan on p1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3
+ Filter: (val = 1)
+ -> Seq Scan on p1_c4
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c2
+ Filter: (val = 1)
+(19 rows)
+
+/*+ IndexOnlyScanRegexp(p1 p1_.*[^0-9]$)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for IndexOnlyScanRegexp(p1): p1_pkey
+LOG: available indexes for IndexOnlyScanRegexp(p1_c1): p1_c1_pkey
+LOG: available indexes for IndexOnlyScanRegexp(p1_c2): p1_c2_pkey
+LOG: available indexes for IndexOnlyScanRegexp(p1_c3): p1_c3_pkey
+LOG: available indexes for IndexOnlyScanRegexp(p1_c4): p1_c4_pkey
+LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c1): p1_c1_c1_pkey
+LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c2): p1_c1_c2_pkey
+LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c1): p1_c3_c1_pkey
+LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c2): p1_c3_c2_pkey
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScanRegexp(p1 p1_.*[^0-9]$)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------
+ Append
+ -> Seq Scan on p1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3
+ Filter: (val = 1)
+ -> Seq Scan on p1_c4
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c2
+ Filter: (val = 1)
+(19 rows)
+
+/*+ IndexOnlyScanRegexp(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for IndexOnlyScanRegexp(p1): p1_val2
+LOG: available indexes for IndexOnlyScanRegexp(p1_c1): p1_c1_val2
+LOG: available indexes for IndexOnlyScanRegexp(p1_c2): p1_c2_val2
+LOG: available indexes for IndexOnlyScanRegexp(p1_c3): p1_c3_val2
+LOG: available indexes for IndexOnlyScanRegexp(p1_c4): p1_c4_val2
+LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c1): p1_c1_c1_val2
+LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c2): p1_c1_c2_val2
+LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c1): p1_c3_c1_val2
+LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c2): p1_c3_c2_val2
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScanRegexp(p1 p1_.*val2.*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------
+ Append
+ -> Index Only Scan using p1_val2 on p1
+ Index Cond: (val = 1)
+ -> Index Only Scan using p1_c1_val2 on p1_c1
+ Index Cond: (val = 1)
+ -> Index Only Scan using p1_c2_val2 on p1_c2
+ Index Cond: (val = 1)
+ -> Index Only Scan using p1_c3_val2 on p1_c3
+ Index Cond: (val = 1)
+ -> Index Only Scan using p1_c4_val2 on p1_c4
+ Index Cond: (val = 1)
+ -> Index Only Scan using p1_c1_c1_val2 on p1_c1_c1
+ Index Cond: (val = 1)
+ -> Index Only Scan using p1_c1_c2_val2 on p1_c1_c2
+ Index Cond: (val = 1)
+ -> Index Only Scan using p1_c3_c1_val2 on p1_c3_c1
+ Index Cond: (val = 1)
+ -> Index Only Scan using p1_c3_c2_val2 on p1_c3_c2
+ Index Cond: (val = 1)
+(19 rows)
+
+/*+ IndexOnlyScanRegexp(p1 p1[^_].*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for IndexOnlyScanRegexp(p1):
+LOG: available indexes for IndexOnlyScanRegexp(p1_c1):
+LOG: available indexes for IndexOnlyScanRegexp(p1_c2):
+LOG: available indexes for IndexOnlyScanRegexp(p1_c3):
+LOG: available indexes for IndexOnlyScanRegexp(p1_c4):
+LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c1):
+LOG: available indexes for IndexOnlyScanRegexp(p1_c1_c2):
+LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c1):
+LOG: available indexes for IndexOnlyScanRegexp(p1_c3_c2):
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScanRegexp(p1 p1[^_].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------
+ Append
+ -> Seq Scan on p1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3
+ Filter: (val = 1)
+ -> Seq Scan on p1_c4
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c2
+ Filter: (val = 1)
+(19 rows)
+
+/*+ IndexOnlyScan(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for IndexOnlyScan(p1):
+LOG: available indexes for IndexOnlyScan(p1_c1):
+LOG: available indexes for IndexOnlyScan(p1_c2):
+LOG: available indexes for IndexOnlyScan(p1_c3):
+LOG: available indexes for IndexOnlyScan(p1_c4):
+LOG: available indexes for IndexOnlyScan(p1_c1_c1):
+LOG: available indexes for IndexOnlyScan(p1_c1_c2):
+LOG: available indexes for IndexOnlyScan(p1_c3_c1):
+LOG: available indexes for IndexOnlyScan(p1_c3_c2):
+LOG: pg_hint_plan:
+used hint:
+IndexOnlyScan(p1 p1_.*val2.*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------
+ Append
+ -> Seq Scan on p1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3
+ Filter: (val = 1)
+ -> Seq Scan on p1_c4
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c2
+ Filter: (val = 1)
+(19 rows)
+
+/*+ BitmapScanRegexp(p1 p1_.*[^0-9]$)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for BitmapScanRegexp(p1): p1_pkey
+LOG: available indexes for BitmapScanRegexp(p1_c1): p1_c1_pkey
+LOG: available indexes for BitmapScanRegexp(p1_c2): p1_c2_pkey
+LOG: available indexes for BitmapScanRegexp(p1_c3): p1_c3_pkey
+LOG: available indexes for BitmapScanRegexp(p1_c4): p1_c4_pkey
+LOG: available indexes for BitmapScanRegexp(p1_c1_c1): p1_c1_c1_pkey
+LOG: available indexes for BitmapScanRegexp(p1_c1_c2): p1_c1_c2_pkey
+LOG: available indexes for BitmapScanRegexp(p1_c3_c1): p1_c3_c1_pkey
+LOG: available indexes for BitmapScanRegexp(p1_c3_c2): p1_c3_c2_pkey
+LOG: pg_hint_plan:
+used hint:
+BitmapScanRegexp(p1 p1_.*[^0-9]$)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------
+ Append
+ -> Seq Scan on p1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3
+ Filter: (val = 1)
+ -> Seq Scan on p1_c4
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c2
+ Filter: (val = 1)
+(19 rows)
+
+/*+ BitmapScanRegexp(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for BitmapScanRegexp(p1): p1_val2
+LOG: available indexes for BitmapScanRegexp(p1_c1): p1_c1_val2
+LOG: available indexes for BitmapScanRegexp(p1_c2): p1_c2_val2
+LOG: available indexes for BitmapScanRegexp(p1_c3): p1_c3_val2
+LOG: available indexes for BitmapScanRegexp(p1_c4): p1_c4_val2
+LOG: available indexes for BitmapScanRegexp(p1_c1_c1): p1_c1_c1_val2
+LOG: available indexes for BitmapScanRegexp(p1_c1_c2): p1_c1_c2_val2
+LOG: available indexes for BitmapScanRegexp(p1_c3_c1): p1_c3_c1_val2
+LOG: available indexes for BitmapScanRegexp(p1_c3_c2): p1_c3_c2_val2
+LOG: pg_hint_plan:
+used hint:
+BitmapScanRegexp(p1 p1_.*val2.*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------------
+ Append
+ -> Bitmap Heap Scan on p1
+ Recheck Cond: (val = 1)
+ -> Bitmap Index Scan on p1_val2
+ Index Cond: (val = 1)
+ -> Bitmap Heap Scan on p1_c1
+ Recheck Cond: (val = 1)
+ -> Bitmap Index Scan on p1_c1_val2
+ Index Cond: (val = 1)
+ -> Bitmap Heap Scan on p1_c2
+ Recheck Cond: (val = 1)
+ -> Bitmap Index Scan on p1_c2_val2
+ Index Cond: (val = 1)
+ -> Bitmap Heap Scan on p1_c3
+ Recheck Cond: (val = 1)
+ -> Bitmap Index Scan on p1_c3_val2
+ Index Cond: (val = 1)
+ -> Bitmap Heap Scan on p1_c4
+ Recheck Cond: (val = 1)
+ -> Bitmap Index Scan on p1_c4_val2
+ Index Cond: (val = 1)
+ -> Bitmap Heap Scan on p1_c1_c1
+ Recheck Cond: (val = 1)
+ -> Bitmap Index Scan on p1_c1_c1_val2
+ Index Cond: (val = 1)
+ -> Bitmap Heap Scan on p1_c1_c2
+ Recheck Cond: (val = 1)
+ -> Bitmap Index Scan on p1_c1_c2_val2
+ Index Cond: (val = 1)
+ -> Bitmap Heap Scan on p1_c3_c1
+ Recheck Cond: (val = 1)
+ -> Bitmap Index Scan on p1_c3_c1_val2
+ Index Cond: (val = 1)
+ -> Bitmap Heap Scan on p1_c3_c2
+ Recheck Cond: (val = 1)
+ -> Bitmap Index Scan on p1_c3_c2_val2
+ Index Cond: (val = 1)
+(37 rows)
+
+/*+ BitmapScanRegexp(p1 p1[^_].*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for BitmapScanRegexp(p1):
+LOG: available indexes for BitmapScanRegexp(p1_c1):
+LOG: available indexes for BitmapScanRegexp(p1_c2):
+LOG: available indexes for BitmapScanRegexp(p1_c3):
+LOG: available indexes for BitmapScanRegexp(p1_c4):
+LOG: available indexes for BitmapScanRegexp(p1_c1_c1):
+LOG: available indexes for BitmapScanRegexp(p1_c1_c2):
+LOG: available indexes for BitmapScanRegexp(p1_c3_c1):
+LOG: available indexes for BitmapScanRegexp(p1_c3_c2):
+LOG: pg_hint_plan:
+used hint:
+BitmapScanRegexp(p1 p1[^_].*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------
+ Append
+ -> Seq Scan on p1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3
+ Filter: (val = 1)
+ -> Seq Scan on p1_c4
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c2
+ Filter: (val = 1)
+(19 rows)
+
+/*+ BitmapScan(p1 p1_.*val2.*)*/
+EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+LOG: available indexes for BitmapScan(p1):
+LOG: available indexes for BitmapScan(p1_c1):
+LOG: available indexes for BitmapScan(p1_c2):
+LOG: available indexes for BitmapScan(p1_c3):
+LOG: available indexes for BitmapScan(p1_c4):
+LOG: available indexes for BitmapScan(p1_c1_c1):
+LOG: available indexes for BitmapScan(p1_c1_c2):
+LOG: available indexes for BitmapScan(p1_c3_c1):
+LOG: available indexes for BitmapScan(p1_c3_c2):
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(p1 p1_.*val2.*)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------
+ Append
+ -> Seq Scan on p1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3
+ Filter: (val = 1)
+ -> Seq Scan on p1_c4
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c1_c2
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c1
+ Filter: (val = 1)
+ -> Seq Scan on p1_c3_c2
+ Filter: (val = 1)
+(19 rows)
+
+-- search from hint table
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;', '', 'SeqScan(t1)');
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = ?;', '', 'IndexScan(t1)');
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN SELECT * FROM t1 WHERE t1.id = ?;', '', 'BitmapScan(t1)');
+SELECT * FROM hint_plan.hints ORDER BY id;
+ id | norm_query_string | application_name | hints
+----+----------------------------------------------------------+------------------+----------------
+ 1 | EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?; | | SeqScan(t1)
+ 2 | EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = ?; | | IndexScan(t1)
+ 3 | EXPLAIN SELECT * FROM t1 WHERE t1.id = ?; | | BitmapScan(t1)
+(3 rows)
+
+SET pg_hint_plan.enable_hint_table = on;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (id = 1)
+(2 rows)
+
+SET pg_hint_plan.enable_hint_table = off;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+ QUERY PLAN
+--------------------------------
+ Index Scan using t1_pkey on t1
+ Index Cond: (id = 1)
+(2 rows)
+
+TRUNCATE hint_plan.hints;
+VACUUM ANALYZE hint_plan.hints;
+-- plpgsql test
+EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
+ QUERY PLAN
+-------------------------------------
+ Index Only Scan using t1_pkey on t1
+ Index Cond: (id = 1)
+(2 rows)
+
+-- static function
+CREATE FUNCTION testfunc() RETURNS RECORD AS $$
+DECLARE
+ ret record;
+BEGIN
+ SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
+ RETURN ret;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 LIMIT 1"
+PL/pgSQL function testfunc() line 5 at SQL statement
+ testfunc
+----------
+ (1,1)
+(1 row)
+
+-- dynamic function
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+ EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "/*+ SeqScan(t1) */ SELECT * FROM t1"
+PL/pgSQL function testfunc() line 3 at EXECUTE
+ testfunc
+----------
+
+(1 row)
+
+-- This should not use SeqScan(t1)
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ id | val
+----+-----
+ 1 | 1
+(1 row)
+
+-- Perform
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS void AS $$
+BEGIN
+ PERFORM 1, /*+ SeqScan(t1) */ * from t1;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1"
+PL/pgSQL function testfunc() line 3 at PERFORM
+ testfunc
+----------
+
+(1 row)
+
+-- FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ sum int;
+ v int;
+BEGIN
+ sum := 0;
+ FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
+ sum := sum + v;
+ END LOOP;
+ RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 7 at FOR over SELECT rows
+ testfunc
+----------
+
+(1 row)
+
+-- Dynamic FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ sum int;
+ v int;
+ i int;
+BEGIN
+ sum := 0;
+ FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
+ sum := sum + v;
+ END LOOP;
+ RETURN v;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 8 at FOR over EXECUTE statement
+ testfunc
+----------
+ 0
+(1 row)
+
+-- Cursor FOR loop
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS int AS $$
+DECLARE
+ ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+ rec record;
+ sum int := 0;
+BEGIN
+ FOR rec IN ref LOOP
+ sum := sum + rec.val;
+ END LOOP;
+ RETURN sum;
+END;
+$$ LANGUAGE plpgsql;
+SELECT testfunc();
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 7 at FOR over cursor
+ testfunc
+----------
+ 495000
+(1 row)
+
+-- RETURN QUERY
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT: SQL statement "SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id"
+PL/pgSQL function testfunc() line 3 at RETURN QUERY
+ id | val
+----+-----
+ 1 | 1
+(1 row)
+
+-- Test for error exit from inner SQL statement.
+DROP FUNCTION testfunc();
+CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
+BEGIN
+ RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
+END;
+$$ LANGUAGE plpgsql;
+SELECT * FROM testfunc() LIMIT 1;
+ERROR: relation "ttx" does not exist
+LINE 1: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
+ ^
+QUERY: SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id
+CONTEXT: PL/pgSQL function testfunc() line 3 at RETURN QUERY
+-- this should not use SeqScan(t1) hint.
+/*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ id | val
+----+-----
+ 1 | 1
+(1 row)
+
+DROP FUNCTION testfunc();
+DROP EXTENSION pg_hint_plan;
+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 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)
+
+--
+-- Rows hint tests
+--
+-- Explain result includes "Planning time" if COSTS is enabled, but
+-- this test needs it enabled for get rows count. So do tests via psql
+-- and grep -v the mutable line.
+-- value types
+\o results/pg_hint_plan.tmpout
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 #99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 #99)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=99 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 +99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 +99)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1099 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 -99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 -99)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=901 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 *99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 *99)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=99000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 *0.01) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 *0.01)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=10 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 #aa) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- ERROR
+INFO: pg_hint_plan: hint syntax error at or near "aa"
+DETAIL: Rows hint requires valid number as rows estimation.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+Rows(t1 t2 #aa)
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 /99) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- ERROR
+INFO: pg_hint_plan: hint syntax error at or near "/99"
+DETAIL: Unrecognized rows value type notation.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+Rows(t1 t2 /99)
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+-- round up to 1
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 -99999) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
+WARNING: Force estimate to be at least one row, to avoid possible divide-by-zero when interpolating costs : Rows(t1 t2 -99999)
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 -99999)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+(4 rows)
+
+-- complex join tree
+\o results/pg_hint_plan.tmpout
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=10 width=xxx)
+ Merge Cond: (t1.id = t3.id)
+ -> Merge Join (cost=xxx rows=1000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx rows=100 width=xxx)
+ Sort Key: t3.id
+ -> Seq Scan on t3 (cost=xxx rows=100 width=xxx)
+(9 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t2 #22) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 #22)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=1 width=xxx)
+ Merge Cond: (t1.id = t3.id)
+ -> Merge Join (cost=xxx rows=22 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx rows=100 width=xxx)
+ Sort Key: t3.id
+ -> Seq Scan on t3 (cost=xxx rows=100 width=xxx)
+(9 rows)
+
+\o results/pg_hint_plan.tmpout
+/*+ Rows(t1 t3 *10) */
+EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t3 *10)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+set max_parallel_workers_per_gather to DEFAULT;
+\! sql/maskout.sh results/pg_hint_plan.tmpout
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=100 width=xxx)
+ Merge Cond: (t1.id = t3.id)
+ -> Merge Join (cost=xxx rows=1000 width=xxx)
+ Merge Cond: (t1.id = t2.id)
+ -> Index Scan using t1_pkey on t1 (cost=xxx rows=10000 width=xxx)
+ -> Index Scan using t2_pkey on t2 (cost=xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx rows=100 width=xxx)
+ Sort Key: t3.id
+ -> Seq Scan on t3 (cost=xxx rows=100 width=xxx)
+(9 rows)
+
+\! rm results/pg_hint_plan.tmpout
+-- 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)
+