* has_join_restriction()
* is_dummy_rel()
* restriction_is_constant_false()
+ * update_child_rel_info()
+ * build_child_join_sjinfo()
*
*
* Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
return false;
}
+
+/*
+ * Set up tlist expressions for the childrel, and add EC members referencing
+ * the childrel.
+ */
+static void
+update_child_rel_info(PlannerInfo *root,
+ RelOptInfo *rel, RelOptInfo *childrel)
+{
+ AppendRelInfo *appinfo = root->append_rel_array[childrel->relid];
+
+ /* Make child tlist expressions */
+ childrel->reltarget->exprs = (List *)
+ adjust_appendrel_attrs(root,
+ (Node *) rel->reltarget->exprs,
+ 1, &appinfo);
+
+ /* Make child entries in the EquivalenceClass as well */
+ if (rel->has_eclass_joins || has_useful_pathkeys(root, rel))
+ add_child_rel_equivalences(root, appinfo, rel, childrel);
+ childrel->has_eclass_joins = rel->has_eclass_joins;
+}
+
+/*
+ * Construct the SpecialJoinInfo for a child-join by translating
+ * SpecialJoinInfo for the join between parents. left_relids and right_relids
+ * are the relids of left and right side of the join respectively.
+ */
+static SpecialJoinInfo *
+build_child_join_sjinfo(PlannerInfo *root, SpecialJoinInfo *parent_sjinfo,
+ Relids left_relids, Relids right_relids)
+{
+ SpecialJoinInfo *sjinfo = makeNode(SpecialJoinInfo);
+ AppendRelInfo **left_appinfos;
+ int left_nappinfos;
+ AppendRelInfo **right_appinfos;
+ int right_nappinfos;
+
+ memcpy(sjinfo, parent_sjinfo, sizeof(SpecialJoinInfo));
+ left_appinfos = find_appinfos_by_relids(root, left_relids,
+ &left_nappinfos);
+ right_appinfos = find_appinfos_by_relids(root, right_relids,
+ &right_nappinfos);
+
+ sjinfo->min_lefthand = adjust_child_relids(sjinfo->min_lefthand,
+ left_nappinfos, left_appinfos);
+ sjinfo->min_righthand = adjust_child_relids(sjinfo->min_righthand,
+ right_nappinfos,
+ right_appinfos);
+ sjinfo->syn_lefthand = adjust_child_relids(sjinfo->syn_lefthand,
+ left_nappinfos, left_appinfos);
+ sjinfo->syn_righthand = adjust_child_relids(sjinfo->syn_righthand,
+ right_nappinfos,
+ right_appinfos);
+ sjinfo->semi_rhs_exprs = (List *) adjust_appendrel_attrs(root,
+ (Node *) sjinfo->semi_rhs_exprs,
+ right_nappinfos,
+ right_appinfos);
+
+ pfree(left_appinfos);
+ pfree(right_appinfos);
+
+ return sjinfo;
+}
+
/*
* Assess whether join between given two partitioned relations can be broken
* down into joins between matching partitions; a technique called
RelOptInfo *joinrel, SpecialJoinInfo *parent_sjinfo,
List *parent_restrictlist)
{
+ bool rel1_is_simple = IS_SIMPLE_REL(rel1);
+ bool rel2_is_simple = IS_SIMPLE_REL(rel2);
int nparts;
int cnt_parts;
AppendRelInfo **appinfos;
int nappinfos;
+ /*
+ * If a child table has consider_partitionwise_join=false, it means
+ * that it's a dummy relation for which we skipped setting up tlist
+ * expressions and adding EC members in set_append_rel_size(), so do
+ * that now for use later.
+ */
+ if (rel1_is_simple && !child_rel1->consider_partitionwise_join)
+ {
+ Assert(child_rel1->reloptkind == RELOPT_OTHER_MEMBER_REL);
+ Assert(IS_DUMMY_REL(child_rel1));
+ update_child_rel_info(root, rel1, child_rel1);
+ child_rel1->consider_partitionwise_join = true;
+ }
+ if (rel2_is_simple && !child_rel2->consider_partitionwise_join)
+ {
+ Assert(child_rel2->reloptkind == RELOPT_OTHER_MEMBER_REL);
+ Assert(IS_DUMMY_REL(child_rel2));
+ update_child_rel_info(root, rel2, child_rel2);
+ child_rel2->consider_partitionwise_join = true;
+ }
+
/* We should never try to join two overlapping sets of rels. */
Assert(!bms_overlap(child_rel1->relids, child_rel2->relids));
child_joinrelids = bms_union(child_rel1->relids, child_rel2->relids);
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Merge Join
- Merge Cond: (t1_5.id = t2_5.id)
- -> Index Only Scan using t1_pkey on t1 t1_5
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_1
- Filter: (id = $2)
+ Filter: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_3
- Filter: (id = $2)
- -> CTE Scan on c1_1
- Filter: (id = $2)
-(53 rows)
+ Filter: (id = $1)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $1)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t2_5.id)
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)*/
EXPLAIN (COSTS false)
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t1_1 t1_2 t1_4 t1_5)*/
EXPLAIN (COSTS false)
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t3_2 t3_5 t2_2 c1_1 t3_4 t3_3 t2_3 t2_4 t1_3 t2_5 t1_2 t3_1 t1_4 t2_1 t1_5 t1_1)*/
EXPLAIN (COSTS false)
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(t3_5 t2_5 t1_5)Leading(t3_2 t2_2 t1_2)Leading(t3_4 t2_4 t1_4)Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)*/
EXPLAIN (COSTS false)
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t2_5.id = t1_5.id)
- -> Merge Join
- Merge Cond: (t2_5.id = t3_5.id)
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t2_2.id = t1_2.id)
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
-> Index Only Scan using t1_pkey on t1 t1_2
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t2_4.id = t1_4.id)
-> Nested Loop
-> Nested Loop
-> Seq Scan on t3 t3_3
- Filter: (id = $2)
- -> CTE Scan on c1_1
- Filter: (id = $2)
+ Filter: (id = $1)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $1)
+ -> Merge Join
+ Merge Cond: (t2_5.id = t1_5.id)
+ -> Merge Join
+ Merge Cond: (t2_5.id = t3_5.id)
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_1
- Filter: (id = $2)
+ Filter: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $2)
-(53 rows)
+ Index Cond: (id = $1)
+(51 rows)
SET from_collapse_limit TO 1;
EXPLAIN (COSTS false)
QUERY PLAN
--------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Merge Join
- Merge Cond: (t1_5.id = t2_5.id)
- -> Index Only Scan using t1_pkey on t1 t1_5
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_1
- Filter: (id = $2)
+ Filter: (id = $1)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_3
- Filter: (id = $2)
- -> CTE Scan on c1_1
- Filter: (id = $2)
-(53 rows)
+ Filter: (id = $1)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $1)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t2_5.id)
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)*/
EXPLAIN (COSTS false)
QUERY PLAN
--------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t1_1 t1_2 t1_4 t1_5)*/
EXPLAIN (COSTS false)
QUERY PLAN
--------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t3_2 t3_5 t2_2 c1_1 t3_4 t3_3 t2_3 t2_4 t1_3 t2_5 t1_2 t3_1 t1_4 t2_1 t1_5 t1_1)*/
EXPLAIN (COSTS false)
QUERY PLAN
--------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_5.id = t3_5.id)
- -> Nested Loop
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- Index Cond: (id = t2_5.id)
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t1_2.id = t3_2.id)
-> Sort
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Merge Join
Merge Cond: (t1_4.id = t3_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_1
- Filter: (id = $5)
+ Filter: (id = $3)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $5)
+ Index Cond: (id = $3)
-> Seq Scan on t3 t3_3
- Filter: (id = $5)
- -> CTE Scan on c1_1
- Filter: (id = $5)
-(53 rows)
+ Filter: (id = $3)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $3)
+ -> Merge Join
+ Merge Cond: (t1_5.id = t3_5.id)
+ -> Nested Loop
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
+ Index Cond: (id = t2_5.id)
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+(51 rows)
/*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(t3_5 t2_5 t1_5)Leading(t3_2 t2_2 t1_2)Leading(t3_4 t2_4 t1_4)Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)*/
EXPLAIN (COSTS false)
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
Nested Loop
- CTE c1_1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t2_5.id = t1_5.id)
- -> Merge Join
- Merge Cond: (t2_5.id = t3_5.id)
- -> Index Only Scan using t2_pkey on t2 t2_5
- -> Sort
- Sort Key: t3_5.id
- -> Seq Scan on t3 t3_5
- -> Index Only Scan using t1_pkey on t1 t1_5
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (t2_2.id = t1_2.id)
Sort Key: t3_2.id
-> Seq Scan on t3 t3_2
-> Index Only Scan using t1_pkey on t1 t1_2
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (t2_4.id = t1_4.id)
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_pkey on t1 t1_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_3
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Seq Scan on t3 t3_3
- Filter: (id = $2)
- -> CTE Scan on c1_1
- Filter: (id = $2)
+ Filter: (id = $1)
+ -> Aggregate
+ Filter: (max(t1_5.id) = $1)
+ -> Merge Join
+ Merge Cond: (t2_5.id = t1_5.id)
+ -> Merge Join
+ Merge Cond: (t2_5.id = t3_5.id)
+ -> Index Only Scan using t2_pkey on t2 t2_5
+ -> Sort
+ Sort Key: t3_5.id
+ -> Seq Scan on t3 t3_5
+ -> Index Only Scan using t1_pkey on t1 t1_5
-> Seq Scan on t3 t3_1
- Filter: (id = $2)
+ Filter: (id = $1)
-> Index Only Scan using t2_pkey on t2 t2_1
- Index Cond: (id = $2)
+ Index Cond: (id = $1)
-> Index Only Scan using t1_pkey on t1 t1_1
- Index Cond: (id = $2)
-(53 rows)
+ Index Cond: (id = $1)
+(51 rows)
-- ambigous error
EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
-- No. J-1-6-9
EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------
Nested Loop
- CTE c1
- -> Aggregate
- -> Merge Join
- Merge Cond: (t1_1.c1 = t2.c1)
- -> Index Only Scan using t1_i1 on t1 t1_1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
- -> CTE Scan on c1
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (t1_1.c1 = t2.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
-> Index Scan using t1_i1 on t1
- Index Cond: (c1 = c1.c1)
-(12 rows)
+ Index Cond: (c1 = (max(t1_1.c1)))
+(10 rows)
/*+NestLoop(t1 t2)HashJoin(t1 c1)*/
EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1;
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Hash Join
- Hash Cond: (t1.c1 = c1.c1)
- CTE c1
- -> Aggregate
- -> Nested Loop
- -> Seq Scan on t2
- -> Index Only Scan using t1_i1 on t1 t1_1
- Index Cond: (c1 = t2.c1)
+ Hash Cond: (t1.c1 = (max(t1_1.c1)))
-> Seq Scan on t1
-> Hash
- -> CTE Scan on c1
-(11 rows)
+ -> Aggregate
+ -> Nested Loop
+ -> Seq Scan on t2
+ -> Index Only Scan using t1_i1 on t1 t1_1
+ Index Cond: (c1 = t2.c1)
+(9 rows)
-- No. J-1-6-10
EXPLAIN (COSTS false) SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1;
AND bmt1.c1 = c1.c1
AND bmt1.c1 = c2.c1
;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
- -> CTE Scan on c2
-(53 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(49 rows)
/*+
Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join
- Hash Cond: (b1t4.c1 = b1t2.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b1t2.c1 = b1t3.c1)
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t3.c1
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Hash Join
- Hash Cond: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b2t3.c1 = b2t4.c1)
- -> Sort
- Sort Key: b2t3.c1
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b2t4.c1
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (bmt4.c1 = bmt1.c1)
-> Tid Scan on t4 bmt4
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Hash Join
- Hash Cond: (bmt1.c1 = c1.c1)
+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
-> Hash
-> Merge Join
- Merge Cond: (c1.c1 = c2.c1)
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b1t2.c1 = b1t3.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t3.c1
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c2.c1
- -> CTE Scan on c2
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Hash Join
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t3.c1
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b2t4.c1
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Sort
Sort Key: bmt3.c1
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-(73 rows)
+(69 rows)
-- No. J-2-1-8
EXPLAIN (COSTS false)
AND bmt1.c1 = c2.c1
AND bmt1.c1 = c3.c1
;
- QUERY PLAN
------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c3
- -> Aggregate
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t4.c1)
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t3.c1)
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t2.c1)
- -> Tid Scan on t1 b3t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c3.c1)
+ Join Filter: (bmt1.c1 = (max(b3t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt3.c1)
- -> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
- -> Tid Scan on t1 bmt1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 bmt3
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
+ -> Merge Join
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
+ -> Sort
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 bmt4
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t4.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t2.c1)
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b3t3
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
- -> CTE Scan on c2
- -> CTE Scan on c3
-(72 rows)
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(70 rows)
/*+
Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join
- Hash Cond: (b1t4.c1 = b1t2.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b1t2.c1 = b1t3.c1)
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t3.c1
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Hash Join
- Hash Cond: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b2t3.c1 = b2t4.c1)
- -> Sort
- Sort Key: b2t3.c1
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b2t4.c1
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- CTE c3
- -> Aggregate
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t3.c1)
- -> Hash Join
- Hash Cond: (b3t2.c1 = b3t1.c1)
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b3t1.c1 = b3t4.c1)
- -> Sort
- Sort Key: b3t1.c1
- -> Tid Scan on t1 b3t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b3t4.c1
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Hash Join
-> Sort
Sort Key: bmt1.c1
-> Nested Loop
- Join Filter: (c1.c1 = bmt1.c1)
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
-> Hash Join
- Hash Cond: (c2.c1 = c1.c1)
+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
-> Merge Join
- Merge Cond: (c2.c1 = c3.c1)
+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
-> Sort
- Sort Key: c2.c1
- -> CTE Scan on c2
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Hash Join
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t3.c1
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b2t4.c1
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c3.c1
- -> CTE Scan on c3
+ Sort Key: (max(b3t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Hash Join
+ Hash Cond: (b3t2.c1 = b3t1.c1)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Sort
+ Sort Key: b3t1.c1
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b3t4.c1
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
-> Hash
- -> CTE Scan on c1
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b1t2.c1 = b1t3.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t3.c1
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
-> Sort
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-(97 rows)
+(91 rows)
----
---- No. J-2-2 the number of the tables per quiry block
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
)
;
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
+ -> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
- -> Result
- InitPlan 5 (returns $4)
- -> Limit
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 <> $5) AND (c1 = 1))
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(28 rows)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+ One-Time Filter: ($4 = 1)
+ InitPlan 5 (returns $4)
+ -> Limit
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+(26 rows)
/*+
Leading(c1 bmt1)
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
+ -> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
- -> Result
- InitPlan 5 (returns $4)
- -> Limit
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 <> $5) AND (c1 = 1))
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(28 rows)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+ One-Time Filter: ($4 = 1)
+ InitPlan 5 (returns $4)
+ -> Limit
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+(26 rows)
-- No. J-2-2-2
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
-> Seq Scan on t2 b3t2
Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(35 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(33 rows)
/*+
Leading(c1 bmt2 bmt1)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Sort
- Sort Key: b1t1.c1
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (b2t1.c1 = b2t2.c1)
Sort Key: b2t2.c1
-> Seq Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (b3t1.c1 = b3t2.c1)
Hash Cond: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Hash
-> Merge Join
- Merge Cond: (bmt2.c1 = c1.c1)
+ Merge Cond: (bmt2.c1 = (max(b1t1.c1)))
-> Sort
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
-(52 rows)
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Sort
+ Sort Key: b1t1.c1
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(50 rows)
-- No. J-2-2-3
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t4.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b2t4
TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t4.c1)
-> Tid Scan on t4 b3t4
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(67 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(65 rows)
/*+
Leading(c1 bmt4 bmt3 bmt2 bmt1)
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Merge Join
- Merge Cond: (b1t2.c1 = b1t1.c1)
- -> Sort
- Sort Key: b1t2.c1
- -> Nested Loop
- Join Filter: (b1t3.c1 = b1t2.c1)
- -> Hash Join
- Hash Cond: (b1t3.c1 = b1t4.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Hash
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t1.c1
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (b2t2.c1 = b2t1.c1)
Sort Key: b2t1.c1
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (b3t2.c1 = b3t1.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Hash
-> Merge Join
- Merge Cond: (bmt4.c1 = c1.c1)
+ Merge Cond: (bmt4.c1 = (max(b1t1.c1)))
-> Sort
Sort Key: bmt4.c1
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (b1t2.c1 = b1t1.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Nested Loop
+ Join Filter: (b1t3.c1 = b1t2.c1)
+ -> Hash Join
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t1.c1
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Sort
Sort Key: bmt1.c1
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
-(91 rows)
+ Filter: (c1 <> $1)
+(89 rows)
-- No. J-2-2-4
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $4)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $4)
+ Filter: (c1 <> $3)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(46 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(44 rows)
/*+
Leading(c1 bmt4 bmt3 bmt2 bmt1)
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join
- Hash Cond: (b1t2.c1 = b1t3.c1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b1t3.c1 = b1t4.c1)
- -> Sort
- Sort Key: b1t3.c1
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t4.c1
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $4)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
TID Cond: (ctid = '(1,1)'::tid)
-> Hash
-> Merge Join
- Merge Cond: (bmt4.c1 = c1.c1)
+ Merge Cond: (bmt4.c1 = (max(b1t1.c1)))
-> Sort
Sort Key: bmt4.c1
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join
+ Hash Cond: (b1t2.c1 = b1t3.c1)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b1t3.c1 = b1t4.c1)
+ -> Sort
+ Sort Key: b1t3.c1
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t4.c1
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Sort
Sort Key: bmt1.c1
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $4)
-(60 rows)
+ Filter: (c1 <> $3)
+(58 rows)
----
---- No. J-2-3 RULE or VIEW
\o
\! sql/maskout.sh results/ut-J.tmpout
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Hash Full Join (cost={inf}..{inf} rows=1000 width=xxx)
Hash Cond: (t1.c1 = t2.c1)
-> Seq Scan on t1 (cost=xxx..xxx rows=1000 width=xxx)
AND bmt1.c1 = c1.c1
AND bmt1.c1 = c2.c1
;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
- -> CTE Scan on c2
-(53 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(49 rows)
/*+
Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Nested Loop
- Join Filter: (b2t3.c1 = b2t1.c1)
- -> Nested Loop
- Join Filter: (b2t3.c1 = b2t4.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Nested Loop
- Join Filter: (c1.c1 = bmt1.c1)
- -> Nested Loop
- Join Filter: (c1.c1 = c2.c1)
- -> CTE Scan on c1
- -> CTE Scan on c2
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
+ -> Merge Join
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
+ -> Sort
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ Join Filter: (b2t3.c1 = b2t1.c1)
+ -> Nested Loop
+ Join Filter: (b2t3.c1 = b2t4.c1)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 bmt2
AND bmt1.c1 = c2.c1
AND bmt1.c1 = c3.c1
;
- QUERY PLAN
------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c3
- -> Aggregate
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t4.c1)
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t3.c1)
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t2.c1)
- -> Tid Scan on t1 b3t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c3.c1)
+ Join Filter: (bmt1.c1 = (max(b3t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt3.c1)
- -> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
- -> Tid Scan on t1 bmt1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 bmt3
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
+ -> Merge Join
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
+ -> Sort
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 bmt4
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t4.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t2.c1)
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b3t3
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
- -> CTE Scan on c2
- -> CTE Scan on c3
-(72 rows)
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(70 rows)
/*+
Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Nested Loop
- Join Filter: (b2t3.c1 = b2t1.c1)
- -> Nested Loop
- Join Filter: (b2t3.c1 = b2t4.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- CTE c3
- -> Aggregate
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t3.c1)
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t2.c1)
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t4.c1)
- -> Tid Scan on t1 b3t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Nested Loop
- Join Filter: (c1.c1 = bmt1.c1)
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
-> Nested Loop
- Join Filter: (c2.c1 = c1.c1)
- -> Nested Loop
- Join Filter: (c2.c1 = c3.c1)
- -> CTE Scan on c2
- -> CTE Scan on c3
- -> CTE Scan on c1
+ Join Filter: ((max(b2t1.c1)) = (max(b1t1.c1)))
+ -> Merge Join
+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
+ -> Sort
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ Join Filter: (b2t3.c1 = b2t1.c1)
+ -> Nested Loop
+ Join Filter: (b2t3.c1 = b2t4.c1)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: (max(b3t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t2.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t4.c1)
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 bmt2
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-(72 rows)
+(70 rows)
----
---- No. L-2-2 the number of the tables per quiry block
QUERY PLAN
-----------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
+ -> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
- -> Result
- InitPlan 5 (returns $4)
- -> Limit
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 <> $5) AND (c1 = 1))
- -> CTE Scan on c1
-(27 rows)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+(20 rows)
/*+
Leading(c1 bmt1)
QUERY PLAN
-----------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
+ -> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
- -> Result
- InitPlan 5 (returns $4)
- -> Limit
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 <> $5) AND (c1 = 1))
- -> CTE Scan on c1
-(27 rows)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+(20 rows)
-- No. L-2-2-2
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
-> Seq Scan on t2 b3t2
Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(35 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(33 rows)
/*+
Leading(c1 bmt2 bmt1)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
-> Nested Loop
Join Filter: (bmt2.c1 = bmt1.c1)
-> Nested Loop
- Join Filter: (bmt2.c1 = c1.c1)
+ Join Filter: (bmt2.c1 = (max(b1t1.c1)))
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
-(35 rows)
+ Filter: (c1 <> $1)
+(33 rows)
-- No. L-2-2-3
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t4.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b2t4
TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t4.c1)
-> Tid Scan on t4 b3t4
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(67 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(65 rows)
/*+
Leading(c1 bmt4 bmt3 bmt2 bmt1)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Nested Loop
- Join Filter: (b1t3.c1 = b1t2.c1)
- -> Nested Loop
- Join Filter: (b1t3.c1 = b1t4.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t2.c1 = b2t1.c1)
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t2.c1 = b3t1.c1)
-> Nested Loop
Join Filter: (bmt4.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt4.c1 = c1.c1)
+ Join Filter: (bmt4.c1 = (max(b1t1.c1)))
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Nested Loop
+ Join Filter: (b1t3.c1 = b1t2.c1)
+ -> Nested Loop
+ Join Filter: (b1t3.c1 = b1t4.c1)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
-(67 rows)
+ Filter: (c1 <> $1)
+(65 rows)
-- No. L-2-2-4
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $4)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $4)
+ Filter: (c1 <> $3)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(46 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(44 rows)
/*+
Leading(c1 bmt4 bmt3 bmt2 bmt1)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Nested Loop
- Join Filter: (b1t3.c1 = b1t2.c1)
- -> Nested Loop
- Join Filter: (b1t3.c1 = b1t4.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $4)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt4.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt4.c1 = c1.c1)
+ Join Filter: (bmt4.c1 = (max(b1t1.c1)))
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Nested Loop
+ Join Filter: (b1t3.c1 = b1t2.c1)
+ -> Nested Loop
+ Join Filter: (b1t3.c1 = b1t4.c1)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $4)
-(46 rows)
+ Filter: (c1 <> $3)
+(44 rows)
----
---- No. L-2-3 RULE or VIEW
EXPLAIN WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1;
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
---------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- CTE c1
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
- Merge Cond: (t1_1.c1 = t2.c1)
- -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: t2.c1
- -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1_1.c1 = t2.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
-> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = c1.c1)
+ Index Cond: (c1 = (max(t1_1.c1)))
\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)Rows(t1 c1 +1)*/
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
---------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Nested Loop (cost=xxx..xxx rows=2 width=xxx)
- CTE c1
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (t1_1.c1 = t2.c1)
- -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: t2.c1
- -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1_1.c1 = t2.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
-> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = c1.c1)
+ Index Cond: (c1 = (max(t1_1.c1)))
-- No. R-1-6-10
\o results/ut-R.tmpout
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=xxx..xxx rows=1 width=xxx)
- CTE c1
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
- Hash Cond: (b1t4.c1 = b1t2.c1)
- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=100 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
- Merge Cond: (b1t3.c1 = b1t2.c1)
- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b1t3.c1)
- CTE c2
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
- Hash Cond: (b2t3.c1 = b2t1.c1)
- -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
- Merge Cond: (b2t3.c1 = b2t4.c1)
- -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=1000 width=xxx)
- -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b2t1.c1)
-> Hash Join (cost=xxx..xxx rows=1 width=xxx)
Hash Cond: (bmt4.c1 = bmt1.c1)
-> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
Sort Key: bmt1.c1
-> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
-> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (bmt1.c1 = c1.c1)
+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
-> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
-> Hash (cost=xxx..xxx rows=1 width=xxx)
-> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (c1.c1 = c2.c1)
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
-> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c1.c1
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
-> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c2.c1
- -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
-> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = bmt1.c1)
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=xxx..xxx rows=1 width=xxx)
- CTE c1
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (b1t4.c1 = b1t2.c1)
- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=1 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (b1t3.c1 = b1t2.c1)
- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b1t3.c1)
- CTE c2
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (b2t1.c1 = b2t3.c1)
- -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Hash (cost=xxx..xxx rows=1 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (b2t3.c1 = b2t4.c1)
- -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b2t1.c1)
-> Hash Join (cost=xxx..xxx rows=1 width=xxx)
Hash Cond: (bmt4.c1 = bmt1.c1)
-> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
Sort Key: bmt1.c1
-> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
-> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (bmt1.c1 = c1.c1)
+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
-> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
-> Hash (cost=xxx..xxx rows=1 width=xxx)
-> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (c1.c1 = c2.c1)
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
-> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c1.c1
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
-> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c2.c1
- -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
-> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = bmt1.c1)
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=xxx..xxx rows=1 width=xxx)
- CTE c1
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
- Hash Cond: (b1t4.c1 = b1t2.c1)
- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=100 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
- Merge Cond: (b1t3.c1 = b1t2.c1)
- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b1t3.c1)
- CTE c2
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
- Hash Cond: (b2t3.c1 = b2t1.c1)
- -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
- Merge Cond: (b2t3.c1 = b2t4.c1)
- -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=1000 width=xxx)
- -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b2t1.c1)
- CTE c3
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
- Hash Cond: (b3t1.c1 = b3t2.c1)
- -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
- Merge Cond: (b3t1.c1 = b3t4.c1)
- -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=100 width=xxx)
- -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
- -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b3t1.c1)
-> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
-> Hash Join (cost=xxx..xxx rows=1 width=xxx)
Hash Cond: (bmt3.c1 = bmt1.c1)
-> Sort (cost=xxx..xxx rows=1 width=xxx)
Sort Key: bmt1.c1
-> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- Join Filter: (c1.c1 = bmt1.c1)
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
-> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (c2.c1 = c1.c1)
+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
-> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (c2.c1 = c3.c1)
+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
-> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c2.c1
- -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
-> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c3.c1
- -> CTE Scan on c3 (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b3t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b3t1.c1 = b3t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b3t1.c1)
-> Hash (cost=xxx..xxx rows=1 width=xxx)
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
-> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = c2.c1)
+ Index Cond: (c1 = (max(b2t1.c1)))
-> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=xxx..xxx rows=1 width=xxx)
- CTE c1
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (b1t4.c1 = b1t2.c1)
- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=1 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (b1t3.c1 = b1t2.c1)
- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b1t3.c1)
- CTE c2
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (b2t1.c1 = b2t3.c1)
- -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Hash (cost=xxx..xxx rows=1 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (b2t3.c1 = b2t4.c1)
- -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b2t1.c1)
- CTE c3
- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (b3t1.c1 = b3t2.c1)
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (b3t1.c1 = b3t4.c1)
- -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=100 width=xxx)
- -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
- -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b3t1.c1)
-> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
-> Hash Join (cost=xxx..xxx rows=1 width=xxx)
Hash Cond: (bmt3.c1 = bmt1.c1)
-> Sort (cost=xxx..xxx rows=1 width=xxx)
Sort Key: bmt1.c1
-> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- Join Filter: (c1.c1 = bmt1.c1)
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
-> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (c2.c1 = c1.c1)
+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
-> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (c2.c1 = c3.c1)
+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
-> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c2.c1
- -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
-> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c3.c1
- -> CTE Scan on c3 (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b3t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b3t1.c1 = b3t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b3t1.c1)
-> Hash (cost=xxx..xxx rows=1 width=xxx)
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
-> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = c2.c1)
+ Index Cond: (c1 = (max(b2t1.c1)))
-> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
);
LOG: pg_hint_plan:
used hint:
-Leading(c1 bmt1)
not used hint:
+Leading(c1 bmt1)
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- CTE c1
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = 1)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = 1)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = 1)
-> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = 1)
- Filter: (c1 <> $2)
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
- Filter: (c1 = 1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
\o results/ut-R.tmpout
/*+
);
LOG: pg_hint_plan:
used hint:
-Leading(c1 bmt1)
-Rows(bmt1 c1 #1)
not used hint:
+Leading(c1 bmt1)
Rows(b1t1 c1 #1)
Rows(b2t1 c1 #1)
Rows(b3t1 c1 #1)
+Rows(bmt1 c1 #1)
duplication hint:
error hint:
QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- CTE c1
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = 1)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = 1)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = 1)
-> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = 1)
- Filter: (c1 <> $2)
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
- Filter: (c1 = 1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
-- No. R-2-2-2
\o results/ut-R.tmpout
MergeJoin(b1t1 b1t2)
MergeJoin(b2t1 b2t2)
MergeJoin(b3t1 b3t2)
-MergeJoin(bmt2 c1)
-HashJoin(bmt1 bmt2 c1)
-Leading(c1 bmt2 bmt1)
Leading(b1t2 b1t1)
Leading(b2t2 b2t1)
Leading(b3t2 b3t1)
not used hint:
+MergeJoin(bmt2 c1)
+HashJoin(bmt1 bmt2 c1)
+Leading(c1 bmt2 bmt1)
duplication hint:
error hint:
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
---------------------------------------------------------------------------------------------
- Hash Join (cost=xxx..xxx rows=10 width=xxx)
- Hash Cond: (bmt1.c1 = bmt2.c1)
- CTE c1
- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
- InitPlan 2 (returns $1)
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ InitPlan 1 (returns $0)
-> Merge Join (cost=xxx..xxx rows=100 width=xxx)
Merge Cond: (b2t1.c1 = b2t2.c1)
-> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
-> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: b2t2.c1
-> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=100 width=xxx)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Merge Join (cost=xxx..xxx rows=100 width=xxx)
Merge Cond: (b3t1.c1 = b3t2.c1)
-> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
-> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: b3t2.c1
-> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
- -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
- Filter: (c1 <> $2)
- -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
-> Merge Join (cost=xxx..xxx rows=100 width=xxx)
- Merge Cond: (bmt2.c1 = c1.c1)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
-> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: bmt2.c1
- -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: c1.c1
- -> CTE Scan on c1 (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t1.c1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t1.c1)
\o results/ut-R.tmpout
/*+
MergeJoin(b1t1 b1t2)
MergeJoin(b2t1 b2t2)
MergeJoin(b3t1 b3t2)
-MergeJoin(bmt2 c1)
-HashJoin(bmt1 bmt2 c1)
-Leading(c1 bmt2 bmt1)
Leading(b1t2 b1t1)
Leading(b2t2 b2t1)
Leading(b3t2 b3t1)
Rows(b1t1 b1t2 #1)
Rows(b2t1 b2t2 #1)
Rows(b3t1 b3t2 #1)
+not used hint:
+MergeJoin(bmt2 c1)
+HashJoin(bmt1 bmt2 c1)
+Leading(c1 bmt2 bmt1)
Rows(bmt2 c1 #1)
Rows(bmt1 bmt2 c1 #1)
-not used hint:
duplication hint:
error hint:
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
---------------------------------------------------------------------------------------------
- Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (bmt1.c1 = bmt2.c1)
- CTE c1
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
- InitPlan 2 (returns $1)
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ InitPlan 1 (returns $0)
-> Merge Join (cost=xxx..xxx rows=1 width=xxx)
Merge Cond: (b2t1.c1 = b2t2.c1)
-> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
-> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: b2t2.c1
-> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=100 width=xxx)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Merge Join (cost=xxx..xxx rows=1 width=xxx)
Merge Cond: (b3t1.c1 = b3t2.c1)
-> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
-> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: b3t2.c1
-> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
- -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
- Filter: (c1 <> $2)
- -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
-> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (bmt2.c1 = c1.c1)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
-> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: bmt2.c1
- -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
- -> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c1.c1
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t1.c1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t1.c1)
-- No. R-2-2-3
\o results/ut-R.tmpout
HashJoin(b1t3 b1t4)
HashJoin(b2t3 b2t4)
HashJoin(b3t3 b3t4)
-MergeJoin(bmt4 c1)
NestLoop(b1t2 b1t3 b1t4)
NestLoop(b2t2 b2t3 b2t4)
NestLoop(b3t2 b3t3 b3t4)
-HashJoin(bmt3 bmt4 c1)
MergeJoin(b1t1 b1t2 b1t3 b1t4)
MergeJoin(b2t1 b2t2 b2t3 b2t4)
MergeJoin(b3t1 b3t2 b3t3 b3t4)
-NestLoop(bmt2 bmt3 bmt4 c1)
-MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
-Leading(c1 bmt4 bmt3 bmt2 bmt1)
Leading(b1t4 b1t3 b1t2 b1t1)
Leading(b2t4 b2t3 b2t2 b2t1)
Leading(b3t4 b3t3 b3t2 b3t1)
not used hint:
+MergeJoin(bmt4 c1)
+HashJoin(bmt3 bmt4 c1)
+NestLoop(bmt2 bmt3 bmt4 c1)
+MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
duplication hint:
error hint:
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
- Merge Join (cost=xxx..xxx rows=10 width=xxx)
- Merge Cond: (bmt1.c1 = bmt2.c1)
- CTE c1
- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: b1t2.c1
- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
- Hash Cond: (b1t3.c1 = b1t4.c1)
- -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=1130 width=xxx)
- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b1t3.c1)
- InitPlan 2 (returns $3)
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ InitPlan 1 (returns $1)
-> Merge Join (cost=xxx..xxx rows=100 width=xxx)
Merge Cond: (b2t1.c1 = b2t2.c1)
-> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
-> Seq Scan on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
-> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = b2t3.c1)
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Merge Join (cost=xxx..xxx rows=100 width=xxx)
Merge Cond: (b3t1.c1 = b3t2.c1)
-> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
-> Seq Scan on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
-> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = b3t3.c1)
- -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
- Filter: (c1 <> $5)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: bmt2.c1
- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
- Hash Cond: (bmt3.c1 = bmt4.c1)
- -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=100 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
- Merge Cond: (bmt4.c1 = c1.c1)
- -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: c1.c1
- -> CTE Scan on c1 (cost=xxx..xxx rows=100 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
+ Join Filter: (b1t1.c1 = bmt1.c1)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ Filter: (c1 <> $3)
-> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = bmt3.c1)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
\o results/ut-R.tmpout
/*+
HashJoin(b1t3 b1t4)
HashJoin(b2t3 b2t4)
HashJoin(b3t3 b3t4)
-MergeJoin(bmt4 c1)
NestLoop(b1t2 b1t3 b1t4)
NestLoop(b2t2 b2t3 b2t4)
NestLoop(b3t2 b3t3 b3t4)
-HashJoin(bmt3 bmt4 c1)
MergeJoin(b1t1 b1t2 b1t3 b1t4)
MergeJoin(b2t1 b2t2 b2t3 b2t4)
MergeJoin(b3t1 b3t2 b3t3 b3t4)
-NestLoop(bmt2 bmt3 bmt4 c1)
-MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
-Leading(c1 bmt4 bmt3 bmt2 bmt1)
Leading(b1t4 b1t3 b1t2 b1t1)
Leading(b2t4 b2t3 b2t2 b2t1)
Leading(b3t4 b3t3 b3t2 b3t1)
Rows(b1t3 b1t4 #1)
Rows(b2t3 b2t4 #1)
Rows(b3t3 b3t4 #1)
-Rows(bmt4 c1 #1)
Rows(b1t2 b1t3 b1t4 #1)
Rows(b2t2 b2t3 b2t4 #1)
Rows(b3t2 b3t3 b3t4 #1)
-Rows(bmt3 bmt4 c1 #1)
Rows(b1t1 b1t2 b1t3 b1t4 #1)
Rows(b2t1 b2t2 b2t3 b2t4 #1)
Rows(b3t1 b3t2 b3t3 b3t4 #1)
+not used hint:
+MergeJoin(bmt4 c1)
+HashJoin(bmt3 bmt4 c1)
+NestLoop(bmt2 bmt3 bmt4 c1)
+MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Rows(bmt4 c1 #1)
+Rows(bmt3 bmt4 c1 #1)
Rows(bmt2 bmt3 bmt4 c1 #1)
Rows(bmt1 bmt2 bmt3 bmt4 c1 #1)
-not used hint:
duplication hint:
error hint:
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
- Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (bmt1.c1 = bmt2.c1)
- CTE c1
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: b1t2.c1
- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (b1t3.c1 = b1t4.c1)
- -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=1130 width=xxx)
- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b1t3.c1)
- InitPlan 2 (returns $3)
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ InitPlan 1 (returns $1)
-> Merge Join (cost=xxx..xxx rows=1 width=xxx)
Merge Cond: (b2t1.c1 = b2t2.c1)
-> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
-> Seq Scan on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
-> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = b2t3.c1)
- InitPlan 3 (returns $5)
+ InitPlan 2 (returns $3)
-> Merge Join (cost=xxx..xxx rows=1 width=xxx)
Merge Cond: (b3t1.c1 = b3t2.c1)
-> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
-> Seq Scan on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
-> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = b3t3.c1)
- -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
- Filter: (c1 <> $5)
- -> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: bmt2.c1
- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (bmt3.c1 = bmt4.c1)
- -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=1 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (bmt4.c1 = c1.c1)
- -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c1.c1
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
+ Join Filter: (b1t1.c1 = bmt1.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b1t2.c1
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ Filter: (c1 <> $3)
-> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = bmt3.c1)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
-- No. R-2-2-4
\o results/ut-R.tmpout
LOG: pg_hint_plan:
used hint:
MergeJoin(b1t3 b1t4)
-MergeJoin(bmt4 c1)
HashJoin(b1t2 b1t3 b1t4)
-HashJoin(bmt3 bmt4 c1)
NestLoop(b1t1 b1t2 b1t3 b1t4)
+Leading(b1t4 b1t3 b1t2 b1t1)
+not used hint:
+MergeJoin(bmt4 c1)
+HashJoin(bmt3 bmt4 c1)
NestLoop(bmt2 bmt3 bmt4 c1)
MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
Leading(c1 bmt4 bmt3 bmt2 bmt1)
-Leading(b1t4 b1t3 b1t2 b1t1)
-not used hint:
duplication hint:
error hint:
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
- Merge Join (cost=xxx..xxx rows=10 width=xxx)
- Merge Cond: (bmt1.c1 = bmt2.c1)
- CTE c1
- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
- Hash Cond: (b1t3.c1 = b1t2.c1)
- -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
- Merge Cond: (b1t3.c1 = b1t4.c1)
- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=100 width=xxx)
- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b1t3.c1)
- InitPlan 2 (returns $2)
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ InitPlan 1 (returns $0)
-> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = 1)
- InitPlan 3 (returns $3)
+ InitPlan 2 (returns $1)
-> Seq Scan on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
- Filter: (c1 <> $3)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: bmt2.c1
- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
- Hash Cond: (bmt3.c1 = bmt4.c1)
- -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=100 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
- Merge Cond: (bmt4.c1 = c1.c1)
- -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Sort (cost=xxx..xxx rows=100 width=xxx)
- Sort Key: c1.c1
- -> CTE Scan on c1 (cost=xxx..xxx rows=100 width=xxx)
- -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = bmt3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt2.c1 = bmt1.c1)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
+ Join Filter: (b1t1.c1 = bmt2.c1)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Materialize (cost=xxx..xxx rows=100 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b1t3.c1 = b1t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
\o results/ut-R.tmpout
/*+
LOG: pg_hint_plan:
used hint:
MergeJoin(b1t3 b1t4)
-MergeJoin(bmt4 c1)
HashJoin(b1t2 b1t3 b1t4)
-HashJoin(bmt3 bmt4 c1)
NestLoop(b1t1 b1t2 b1t3 b1t4)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Rows(b1t3 b1t4 #1)
+Rows(b1t2 b1t3 b1t4 #1)
+Rows(b1t1 b1t2 b1t3 b1t4 #1)
+not used hint:
+MergeJoin(bmt4 c1)
+HashJoin(bmt3 bmt4 c1)
NestLoop(bmt2 bmt3 bmt4 c1)
MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
Leading(c1 bmt4 bmt3 bmt2 bmt1)
-Leading(b1t4 b1t3 b1t2 b1t1)
-Rows(b1t3 b1t4 #1)
Rows(bmt4 c1 #1)
-Rows(b1t2 b1t3 b1t4 #1)
Rows(bmt3 bmt4 c1 #1)
-Rows(b1t1 b1t2 b1t3 b1t4 #1)
Rows(bmt2 bmt3 bmt4 c1 #1)
Rows(bmt1 bmt2 bmt3 bmt4 c1 #1)
-not used hint:
duplication hint:
error hint:
\o
\! sql/maskout.sh results/ut-R.tmpout
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
- Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (bmt1.c1 = bmt2.c1)
- CTE c1
- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (b1t3.c1 = b1t2.c1)
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (b1t3.c1 = b1t4.c1)
- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=100 width=xxx)
- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = b1t3.c1)
- InitPlan 2 (returns $2)
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ InitPlan 1 (returns $0)
-> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = 1)
- InitPlan 3 (returns $3)
+ InitPlan 2 (returns $1)
-> Seq Scan on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
- -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
- Filter: (c1 <> $3)
- -> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: bmt2.c1
- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
- Hash Cond: (bmt3.c1 = bmt4.c1)
- -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
- -> Hash (cost=xxx..xxx rows=1 width=xxx)
- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
- Merge Cond: (bmt4.c1 = c1.c1)
- -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
- -> Sort (cost=xxx..xxx rows=1 width=xxx)
- Sort Key: c1.c1
- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
- -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
- Index Cond: (c1 = bmt3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt2.c1 = bmt1.c1)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
+ Join Filter: (b1t1.c1 = bmt2.c1)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Materialize (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t3.c1 = b1t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
----
---- No. R-2-3 RULE or VIEW
-- No. S-1-5-9
EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1)
SELECT * FROM s1.t1, c1 WHERE t1.c1 = 1 AND t1.c1 = c1.c1;
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Nested Loop
- CTE c1
- -> Result
- InitPlan 1 (returns $0)
- -> Limit
- -> Index Only Scan using t1_i1 on t1 t1_1
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
-> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(11 rows)
+ -> Result
+ One-Time Filter: ($0 = 1)
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Index Only Scan using t1_i1 on t1 t1_1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+(9 rows)
/*+SeqScan(c1)*/
EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Nested Loop
- CTE c1
- -> Result
- InitPlan 1 (returns $0)
- -> Limit
- -> Index Only Scan using t1_i1 on t1 t1_1
- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
-> Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(11 rows)
+ -> Result
+ One-Time Filter: ($0 = 1)
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Index Only Scan using t1_i1 on t1 t1_1
+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
+(9 rows)
-- No. S-1-5-10
EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1;
WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
AND bmt1.c1 = c1.c1
;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t3.c1)
- -> Index Only Scan using t1_i1 on t1 b1t1
- -> Index Only Scan using t3_i1 on t3 b1t3
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- -> Index Only Scan using t4_i1 on t4 b1t4
- Index Cond: (c1 = b1t1.c1)
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Hash Join
- Hash Cond: (bmt2.c1 = c1.c1)
+ Hash Cond: (bmt2.c1 = (max(b1t1.c1)))
-> Seq Scan on t2 bmt2
-> Hash
- -> CTE Scan on c1
+ -> Aggregate
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ -> Index Only Scan using t3_i1 on t3 b1t3
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t1.c1)
-> Index Only Scan using t1_i1 on t1 bmt1
Index Cond: (c1 = bmt2.c1)
-> Index Only Scan using t3_i1 on t3 bmt3
Index Cond: (c1 = bmt1.c1)
-> Index Only Scan using t4_i1 on t4 bmt4
Index Cond: (c1 = bmt1.c1)
-(29 rows)
+(27 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Hash Join
- Hash Cond: (b1t1.c1 = b1t2.c1)
- -> Seq Scan on t1 b1t1
- -> Hash
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Nested Loop
- -> Seq Scan on t2 b1t2
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t4.c1)
-> Hash Join
Hash Cond: (bmt4.c1 = bmt1.c1)
-> Seq Scan on t4 bmt4
-> Hash
-> Nested Loop
- -> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = c1.c1)
- -> Seq Scan on t1 bmt1
- -> Hash
- -> CTE Scan on c1
- -> Index Scan using t2_pkey on t2 bmt2
- Index Cond: (c1 = bmt1.c1)
+ -> Merge Join
+ Merge Cond: (bmt1.c1 = (max(b1t1.c1)))
+ -> Sort
+ Sort Key: bmt1.c1
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Seq Scan on t1 bmt1
+ -> Hash
+ -> Index Scan using t2_pkey on t2 bmt2
+ -> Sort
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
+ -> Hash
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
-> Bitmap Heap Scan on t3 bmt3
Recheck Cond: (c1 = bmt1.c1)
-> Bitmap Index Scan on t3_pkey
Index Cond: (c1 = bmt1.c1)
-(34 rows)
+(36 rows)
-- No. S-2-1-8
EXPLAIN (COSTS false)
AND bmt1.c1 = c1.c1
AND bmt1.c1 = c2.c1
;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t3.c1)
- -> Index Only Scan using t1_i1 on t1 b1t1
- -> Index Only Scan using t3_i1 on t3 b1t3
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- -> Index Only Scan using t4_i1 on t4 b1t4
- Index Cond: (c1 = b1t1.c1)
- CTE c2
- -> Aggregate
- -> Nested Loop
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t2.c1)
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t3.c1)
- -> Index Only Scan using t1_i1 on t1 b2t1
- -> Index Only Scan using t3_i1 on t3 b2t3
- -> Sort
- Sort Key: b2t2.c1
- -> Seq Scan on t2 b2t2
- -> Index Only Scan using t4_i1 on t4 b2t4
- Index Cond: (c1 = b2t1.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
-> Nested Loop
-> Nested Loop
-> Nested Loop
-> Hash Join
- Hash Cond: (bmt2.c1 = c1.c1)
+ Hash Cond: (bmt2.c1 = (max(b1t1.c1)))
-> Seq Scan on t2 bmt2
-> Hash
- -> CTE Scan on c1
+ -> Aggregate
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ -> Index Only Scan using t3_i1 on t3 b1t3
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ -> Index Only Scan using t4_i1 on t4 b1t4
+ Index Cond: (c1 = b1t1.c1)
-> Index Only Scan using t1_i1 on t1 bmt1
Index Cond: (c1 = bmt2.c1)
-> Index Only Scan using t3_i1 on t3 bmt3
Index Cond: (c1 = bmt1.c1)
-> Index Only Scan using t4_i1 on t4 bmt4
Index Cond: (c1 = bmt1.c1)
- -> CTE Scan on c2
-(46 rows)
+ -> Aggregate
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t3.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1
+ -> Index Only Scan using t3_i1 on t3 b2t3
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ -> Index Only Scan using t4_i1 on t4 b2t4
+ Index Cond: (c1 = b2t1.c1)
+(42 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Hash Join
- Hash Cond: (b1t1.c1 = b1t2.c1)
- -> Seq Scan on t1 b1t1
- -> Hash
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t3.c1)
- -> Nested Loop
- -> Seq Scan on t2 b1t2
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t4.c1)
- CTE c2
- -> Aggregate
- -> Hash Join
- Hash Cond: (b2t3.c1 = b2t1.c1)
- -> Seq Scan on t3 b2t3
- -> Hash
- -> Merge Join
- Merge Cond: (b2t1.c1 = b2t2.c1)
- -> Nested Loop
- -> Index Scan using t4_pkey on t4 b2t4
- -> Bitmap Heap Scan on t1 b2t1
- Recheck Cond: (c1 = b2t4.c1)
- -> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = b2t4.c1)
- -> Sort
- Sort Key: b2t2.c1
- -> Seq Scan on t2 b2t2
-> Hash Join
Hash Cond: (bmt4.c1 = bmt1.c1)
-> Seq Scan on t4 bmt4
-> Hash
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
-> Nested Loop
-> Nested Loop
- -> Hash Join
- Hash Cond: (bmt1.c1 = c1.c1)
- -> Seq Scan on t1 bmt1
- -> Hash
- -> CTE Scan on c1
- -> Index Scan using t2_pkey on t2 bmt2
- Index Cond: (c1 = bmt1.c1)
- -> Bitmap Heap Scan on t3 bmt3
- Recheck Cond: (c1 = bmt1.c1)
- -> Bitmap Index Scan on t3_pkey
- Index Cond: (c1 = bmt1.c1)
- -> CTE Scan on c2
-(54 rows)
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
+ -> Merge Join
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
+ -> Sort
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t1 b1t1
+ -> Hash
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t3.c1)
+ -> Nested Loop
+ -> Seq Scan on t2 b1t2
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t4.c1)
+ -> Sort
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Seq Scan on t3 b2t3
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop
+ -> Index Scan using t4_pkey on t4 b2t4
+ -> Bitmap Heap Scan on t1 b2t1
+ Recheck Cond: (c1 = b2t4.c1)
+ -> Bitmap Index Scan on t1_pkey
+ Index Cond: (c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ -> Seq Scan on t1 bmt1
+ -> Index Scan using t2_pkey on t2 bmt2
+ Index Cond: (c1 = bmt1.c1)
+ -> Bitmap Heap Scan on t3 bmt3
+ Recheck Cond: (c1 = bmt1.c1)
+ -> Bitmap Index Scan on t3_pkey
+ Index Cond: (c1 = bmt1.c1)
+(53 rows)
----
---- No. S-2-2 the number of the tables per quiry block
QUERY PLAN
-----------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
+ -> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
- -> Result
- InitPlan 5 (returns $4)
- -> Limit
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 <> $5) AND (c1 = 1))
- -> CTE Scan on c1
-(27 rows)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+(20 rows)
/*+SeqScan(bmt1)
TidScan(b1t1)
QUERY PLAN
---------------------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $3)
- -> Result
- InitPlan 3 (returns $2)
- -> Limit
-> Bitmap Heap Scan on t1 b2t1
Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
+ InitPlan 4 (returns $3)
-> Result
- InitPlan 5 (returns $4)
+ InitPlan 3 (returns $2)
-> Limit
-> Index Scan using t1_pkey on t1 b3t1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
-> Seq Scan on t1 bmt1
- Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid) AND (c1 = 1))
- -> CTE Scan on c1
-(28 rows)
+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid) AND (c1 = 1))
+ -> Result
+(21 rows)
-- No. S-2-2-2
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(34 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(32 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)
TidScan(b1t1)SeqScan(b1t2)
QUERY PLAN
--------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $2)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Nested Loop
-> Tid Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: (c1 = b2t2.c1)
- InitPlan 3 (returns $4)
+ InitPlan 2 (returns $3)
-> Aggregate
-> Nested Loop
-> Index Scan using t1_pkey on t1 b3t1
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Seq Scan on t1 bmt1
- Filter: ((c1 <> $4) AND (ctid = '(1,1)'::tid))
+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid))
-> Index Scan using t2_pkey on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(37 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(35 rows)
-- No. S-2-2-3
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t4.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b2t4
TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t4.c1)
-> Tid Scan on t4 b3t4
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(67 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(65 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)'
)
;
-LOG: available indexes for IndexScan(b1t3): t3_pkey
-LOG: available indexes for BitmapScan(b1t4): t4_pkey
LOG: available indexes for BitmapScan(b2t1): t1_pkey
LOG: available indexes for IndexScan(b2t4): t4_pkey
LOG: available indexes for IndexScan(b3t1): t1_pkey
LOG: available indexes for BitmapScan(b3t2): t2_pkey
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
LOG: available indexes for IndexScan(bmt2): t2_pkey
LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t2.c1)
- Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- InitPlan 2 (returns $5)
+ InitPlan 1 (returns $2)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t4.c1)
-> Index Scan using t4_pkey on t4 b2t4
Index Cond: (c1 = b2t2.c1)
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $8)
+ InitPlan 2 (returns $5)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
-> Bitmap Index Scan on t2_pkey
Index Cond: (c1 = b3t3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Seq Scan on t1 bmt1
- Filter: ((c1 <> $8) AND (ctid = '(1,1)'::tid))
+ Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid))
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t3_pkey
Index Cond: (c1 = bmt2.c1)
- -> CTE Scan on c1
-(82 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+(80 rows)
-- No. S-2-2-4
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $4)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $4)
+ Filter: (c1 <> $3)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(46 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(44 rows)
/*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
)
;
-LOG: available indexes for IndexScan(b1t3): t3_pkey
-LOG: available indexes for BitmapScan(b1t4): t4_pkey
LOG: available indexes for BitmapScan(b2t1): t1_pkey
LOG: available indexes for BitmapScan(b2t1): t1_pkey
LOG: available indexes for IndexScan(b3t1): t1_pkey
LOG: available indexes for IndexScan(b3t1): t1_pkey
+LOG: available indexes for IndexScan(b1t3): t3_pkey
+LOG: available indexes for BitmapScan(b1t4): t4_pkey
LOG: available indexes for IndexScan(bmt2): t2_pkey
LOG: available indexes for BitmapScan(bmt3): t3_pkey
LOG: pg_hint_plan:
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using t3_pkey on t3 b1t3
- Index Cond: (c1 = b1t2.c1)
- Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Heap Scan on t4 b1t4
- Recheck Cond: (c1 = b1t2.c1)
- Filter: (ctid = '(1,1)'::tid)
- -> Bitmap Index Scan on t4_pkey
- Index Cond: (c1 = b1t2.c1)
- InitPlan 3 (returns $4)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $3)
+ InitPlan 1 (returns $0)
-> Limit
-> Bitmap Heap Scan on t1 b2t1
Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t1_pkey
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $6)
+ InitPlan 4 (returns $3)
-> Result
- InitPlan 4 (returns $5)
+ InitPlan 3 (returns $2)
-> Limit
-> Index Scan Backward using t1_pkey on t1 b3t1
Index Cond: (c1 IS NOT NULL)
Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Seq Scan on t1 bmt1
- Filter: ((c1 <> $6) AND (ctid = '(1,1)'::tid))
+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid))
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-> Index Scan using t2_pkey on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Bitmap Index Scan on t3_pkey
Index Cond: (c1 = bmt2.c1)
- -> CTE Scan on c1
-(58 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Index Scan using t3_pkey on t3 b1t3
+ Index Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Heap Scan on t4 b1t4
+ Recheck Cond: (c1 = b1t2.c1)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Bitmap Index Scan on t4_pkey
+ Index Cond: (c1 = b1t2.c1)
+(56 rows)
----
---- No. S-2-3 RULE or VIEW
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ti1_expr on ti1
Index Cond: ((c1 < 100) = true)
- Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
(3 rows)
-- No. S-3-4-6
-----------------------------------
Index Scan using ti1_expr on ti1
Index Cond: ((c1 < 100) = true)
- Filter: (c1 < 100)
-(3 rows)
+(2 rows)
-- No. S-3-4-28
EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1';
\o
\! sql/maskout.sh results/ut-S.tmpout
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
\o
\! sql/maskout.sh results/ut-S.tmpout
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
\o
\! sql/maskout.sh results/ut-S.tmpout
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
\o
\! sql/maskout.sh results/ut-S.tmpout
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
\o
\! sql/maskout.sh results/ut-S.tmpout
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
\o
\! sql/maskout.sh results/ut-S.tmpout
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 100)
\o
\! sql/maskout.sh results/ut-S.tmpout
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Seq Scan on t1 (cost={inf}..{inf} rows=1 width=xxx)
Filter: (c1 = 1)
SELECT max(b2t1.c1) FROM s1.t1 b2t1 JOIN s1.t2 b2t2 ON(b2t1.c1 = b2t2.c1) WHERE b2t1.c1 = 1
) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE b4t1.c1 = 1);
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Aggregate
- CTE cte1
- -> Aggregate
- -> Nested Loop
- -> Index Only Scan using t1_i1 on t1 b1t1
- Index Cond: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (c1 = 1)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
-> Index Only Scan using t1_i1 on t1 b2t1
Index Cond: (c1 = 1)
-> Seq Scan on t2 b2t2
Filter: (c1 = 1)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
-> Index Only Scan using t1_i1 on t1 b4t1
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_i1 on t1 b3t1
- Index Cond: (c1 = $2)
+ Index Cond: (c1 = $1)
-> Seq Scan on t2 b3t2
- Filter: (c1 = $2)
- -> CTE Scan on cte1
- Filter: (c1 = $2)
-(30 rows)
+ Filter: (c1 = $1)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $1)
+ -> Nested Loop
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ Index Cond: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(28 rows)
/*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
TidScan(b1t2)SeqScan(b2t2)IndexScan(b3t2 t2_pkey)BitmapScan(b4t2 t2_pkey)
QUERY PLAN
---------------------------------------------------------
Aggregate
- CTE cte1
- -> Aggregate
- -> Nested Loop
- -> Seq Scan on t1 b1t1
- Filter: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (c1 = 1)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
-> Index Scan using t1_pkey on t1 b2t1
Index Cond: (c1 = 1)
-> Seq Scan on t2 b2t2
Filter: (c1 = 1)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
-> Seq Scan on t1 b4t1
-> Nested Loop
-> Nested Loop
-> Bitmap Heap Scan on t1 b3t1
- Recheck Cond: (c1 = $2)
+ Recheck Cond: (c1 = $1)
-> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = $2)
+ Index Cond: (c1 = $1)
-> Index Scan using t2_pkey on t2 b3t2
- Index Cond: (c1 = $2)
- -> CTE Scan on cte1
- Filter: (c1 = $2)
-(34 rows)
+ Index Cond: (c1 = $1)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $1)
+ -> Nested Loop
+ -> Seq Scan on t1 b1t1
+ Filter: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(32 rows)
-- No. S-3-7-3
EXPLAIN (COSTS false)
QUERY PLAN
---------------------------------------------------------------------
Aggregate
- CTE cte1
- -> Aggregate
- -> Nested Loop
- -> Index Only Scan using t1_i1 on t1 b1t1
- Index Cond: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (c1 = 1)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan using t1_i1 on t1 b2t1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $4)
+ InitPlan 4 (returns $3)
-> Result
- InitPlan 4 (returns $3)
+ InitPlan 3 (returns $2)
-> Limit
-> Index Only Scan using t1_i1 on t1 b4t1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Nested Loop
-> Index Only Scan using t1_i1 on t1 b3t1
- Index Cond: (c1 = $4)
+ Index Cond: (c1 = $3)
-> Seq Scan on t2 b3t2
- Filter: (c1 = $4)
- -> CTE Scan on cte1
- Filter: (c1 = $4)
-(28 rows)
+ Filter: (c1 = $3)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $3)
+ -> Nested Loop
+ -> Index Only Scan using t1_i1 on t1 b1t1
+ Index Cond: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(26 rows)
/*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
TidScan(b1t2)IndexScan(b3t2 t2_pkey)
QUERY PLAN
---------------------------------------------------------------------
Aggregate
- CTE cte1
- -> Aggregate
- -> Nested Loop
- -> Seq Scan on t1 b1t1
- Filter: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (c1 = 1)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Index Scan using t1_pkey on t1 b2t1
Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 5 (returns $4)
+ InitPlan 4 (returns $3)
-> Result
- InitPlan 4 (returns $3)
+ InitPlan 3 (returns $2)
-> Limit
-> Seq Scan on t1 b4t1
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Nested Loop
-> Bitmap Heap Scan on t1 b3t1
- Recheck Cond: (c1 = $4)
+ Recheck Cond: (c1 = $3)
-> Bitmap Index Scan on t1_pkey
- Index Cond: (c1 = $4)
+ Index Cond: (c1 = $3)
-> Index Scan using t2_pkey on t2 b3t2
- Index Cond: (c1 = $4)
- -> CTE Scan on cte1
- Filter: (c1 = $4)
-(30 rows)
+ Index Cond: (c1 = $3)
+ -> Aggregate
+ Filter: (max(b1t1.c1) = $3)
+ -> Nested Loop
+ -> Seq Scan on t1 b1t1
+ Filter: (c1 = 1)
+ -> Seq Scan on t2 b1t2
+ Filter: (c1 = 1)
+(28 rows)
----
---- No. S-3-8 inheritance table select/update type
\o
\! sql/maskout.sh results/ut-S.tmpout
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Append (cost=xxx..xxx rows=4 width=xxx)
-> Index Scan using p1_i2 on p1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c2 = 1)
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
- Gather
- Workers Planned: 8
- -> Parallel Hash Join
- Hash Cond: (p1.id = p2.id)
- -> Append
- -> Parallel Seq Scan on p1
- -> Parallel Seq Scan on p1_c1
- -> Parallel Seq Scan on p1_c2
- -> Parallel Seq Scan on p1_c3
- -> Parallel Seq Scan on p1_c4
- -> Parallel Seq Scan on p1_c1_c1
- -> Parallel Seq Scan on p1_c1_c2
- -> Parallel Seq Scan on p1_c3_c1
- -> Parallel Seq Scan on p1_c3_c2
- -> Parallel Hash
+ QUERY PLAN
+--------------------------------------------------------------
+ Hash Join
+ Hash Cond: (p2.id = p1.id)
+ -> Append
+ -> Index Scan using p2_id2_val on p2
+ -> Index Scan using p2_c1_id2_val on p2_c1
+ -> Index Scan using p2_c2_id2_val on p2_c2
+ -> Index Scan using p2_c3_id_val_idx on p2_c3
+ -> Index Scan using p2_c4_id_val_idx on p2_c4
+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
+ -> Hash
+ -> Gather
+ Workers Planned: 8
-> Append
- -> Parallel Index Scan using p2_id2_val on p2
- -> Parallel Index Scan using p2_c1_id2_val on p2_c1
- -> Parallel Index Scan using p2_c2_id2_val on p2_c2
- -> Parallel Index Scan using p2_c3_id_val_idx on p2_c3
- -> Parallel Index Scan using p2_c4_id_val_idx on p2_c4
- -> Parallel Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
- -> Parallel Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
- -> Parallel Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
- -> Parallel Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
+ -> Parallel Seq Scan on p1
+ -> Parallel Seq Scan on p1_c1
+ -> Parallel Seq Scan on p1_c2
+ -> Parallel Seq Scan on p1_c3
+ -> Parallel Seq Scan on p1_c4
+ -> Parallel Seq Scan on p1_c1_c1
+ -> Parallel Seq Scan on p1_c1_c2
+ -> Parallel Seq Scan on p1_c3_c1
+ -> Parallel Seq Scan on p1_c3_c2
(25 rows)
SET enable_parallel_append to true;
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
- Gather
- Workers Planned: 8
- -> Parallel Hash Join
- Hash Cond: (p1.id = p2_c2.id)
- -> Parallel Append
- -> Seq Scan on p1
- -> Seq Scan on p1_c1
- -> Seq Scan on p1_c3
- -> Parallel Seq Scan on p1_c2
- -> Parallel Seq Scan on p1_c4
- -> Parallel Seq Scan on p1_c1_c1
- -> Parallel Seq Scan on p1_c1_c2
- -> Parallel Seq Scan on p1_c3_c1
- -> Parallel Seq Scan on p1_c3_c2
- -> Parallel Hash
+ QUERY PLAN
+--------------------------------------------------------------
+ Hash Join
+ Hash Cond: (p2.id = p1.id)
+ -> Append
+ -> Index Scan using p2_id2_val on p2
+ -> Index Scan using p2_c1_id2_val on p2_c1
+ -> Index Scan using p2_c2_id2_val on p2_c2
+ -> Index Scan using p2_c3_id_val_idx on p2_c3
+ -> Index Scan using p2_c4_id_val_idx on p2_c4
+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
+ -> Hash
+ -> Gather
+ Workers Planned: 8
-> Parallel Append
- -> Parallel Index Scan using p2_c2_id2_val on p2_c2
- -> Parallel Index Scan using p2_c4_id_val_idx on p2_c4
- -> Parallel Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
- -> Parallel Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
- -> Parallel Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
- -> Parallel Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
- -> Parallel Index Scan using p2_id2_val on p2
- -> Parallel Index Scan using p2_c1_id2_val on p2_c1
- -> Parallel Index Scan using p2_c3_id_val_idx on p2_c3
+ -> Seq Scan on p1
+ -> Seq Scan on p1_c1
+ -> Seq Scan on p1_c3
+ -> Parallel Seq Scan on p1_c2
+ -> Parallel Seq Scan on p1_c4
+ -> Parallel Seq Scan on p1_c1_c1
+ -> Parallel Seq Scan on p1_c1_c2
+ -> Parallel Seq Scan on p1_c3_c1
+ -> Parallel Seq Scan on p1_c3_c2
(25 rows)
-- Parallel sequential scan
-> Sample Scan on p1_c1_c1 s1
Sampling: system ('10'::real)
-> Foreign Scan on ft1
- Foreign File: @abs_srcdir@/data/data.csv
- -> CTE Scan on cte1
- CTE cte1
- -> Gather
- Workers Planned: 5
- -> Parallel Append
- -> Seq Scan on p1
- Filter: ((id % 2) = 0)
- -> Seq Scan on p1_c1
- Filter: ((id % 2) = 0)
- -> Seq Scan on p1_c3
- Filter: ((id % 2) = 0)
- -> Parallel Seq Scan on p1_c2
- Filter: ((id % 2) = 0)
- -> Parallel Seq Scan on p1_c4
- Filter: ((id % 2) = 0)
- -> Parallel Seq Scan on p1_c1_c1
- Filter: ((id % 2) = 0)
- -> Parallel Seq Scan on p1_c1_c2
- Filter: ((id % 2) = 0)
- -> Parallel Seq Scan on p1_c3_c1
- Filter: ((id % 2) = 0)
- -> Parallel Seq Scan on p1_c3_c2
- Filter: ((id % 2) = 0)
+ Foreign File: /home/horiguti/work/pg_hint_plan/pg_hint_plan/data/data.csv
+ -> Gather
+ Workers Planned: 5
+ -> Parallel Append
+ -> Seq Scan on p1
+ Filter: ((id % 2) = 0)
+ -> Seq Scan on p1_c1
+ Filter: ((id % 2) = 0)
+ -> Seq Scan on p1_c3
+ Filter: ((id % 2) = 0)
+ -> Parallel Seq Scan on p1_c2
+ Filter: ((id % 2) = 0)
+ -> Parallel Seq Scan on p1_c4
+ Filter: ((id % 2) = 0)
+ -> Parallel Seq Scan on p1_c1_c1
+ Filter: ((id % 2) = 0)
+ -> Parallel Seq Scan on p1_c1_c2
+ Filter: ((id % 2) = 0)
+ -> Parallel Seq Scan on p1_c3_c1
+ Filter: ((id % 2) = 0)
+ -> Parallel Seq Scan on p1_c3_c2
+ Filter: ((id % 2) = 0)
-> Function Scan on pg_stat_statements
-> Subquery Scan on "*SELECT* 5"
-> Values Scan on "*VALUES*"
-(33 rows)
+(31 rows)
ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
SELECT pg_reload_conf();
#include "postgres.h"
#include "access/genam.h"
#include "access/heapam.h"
+#include "access/relation.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_index.h"
#include "commands/prepare.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
#include "nodes/params.h"
-#include "nodes/relation.h"
#include "optimizer/appendinfo.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/geqo.h"
#include "optimizer/joininfo.h"
+#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/plancat.h"
if (jumble_len >= JUMBLE_SIZE)
{
- uint32 start_hash = hash_any(jumble, JUMBLE_SIZE);
+ uint64 start_hash;
+ start_hash = DatumGetUInt64(hash_any_extended(jumble,
+ JUMBLE_SIZE, 0));
memcpy(jumble, &start_hash, sizeof(start_hash));
jumble_len = sizeof(start_hash);
}
case RTE_NAMEDTUPLESTORE:
APP_JUMB_STRING(rte->enrname);
break;
+ case RTE_RESULT:
+ break;
default:
elog(ERROR, "unrecognized RTE kind: %d", (int) rte->rtekind);
break;
JumbleExpr(jstate, (Node *) expr->aggfilter);
}
break;
- case T_ArrayRef:
+ case T_SubscriptingRef:
{
- ArrayRef *aref = (ArrayRef *) node;
+ SubscriptingRef *sbsref = (SubscriptingRef *) node;
- JumbleExpr(jstate, (Node *) aref->refupperindexpr);
- JumbleExpr(jstate, (Node *) aref->reflowerindexpr);
- JumbleExpr(jstate, (Node *) aref->refexpr);
- JumbleExpr(jstate, (Node *) aref->refassgnexpr);
+ JumbleExpr(jstate, (Node *) sbsref->refupperindexpr);
+ JumbleExpr(jstate, (Node *) sbsref->reflowerindexpr);
+ JumbleExpr(jstate, (Node *) sbsref->refexpr);
+ JumbleExpr(jstate, (Node *) sbsref->refassgnexpr);
}
break;
case T_FuncExpr:
APP_JUMB(acexpr->resulttype);
JumbleExpr(jstate, (Node *) acexpr->arg);
+ JumbleExpr(jstate, (Node *) acexpr->elemexpr);
}
break;
case T_ConvertRowtypeExpr:
/* we store the string name because RTE_CTE RTEs need it */
APP_JUMB_STRING(cte->ctename);
+ APP_JUMB(cte->ctematerialized);
JumbleQuery(jstate, castNode(Query, cte->ctequery));
}
break;