From dc26a1027f61cf9b45a2c3129fab222d96c83eda Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Thu, 26 Sep 2019 15:10:58 -0700 Subject: [PATCH v2 7/8] WIP: explain: Output hash keys in verbose mode. Author: Reviewed-By: Discussion: https://postgr.es/m/ Backpatch: --- src/backend/commands/explain.c | 29 ++++++- src/test/regress/expected/join.out | 82 +++++++++++++++---- src/test/regress/expected/join_hash.out | 12 ++- src/test/regress/expected/plpgsql.out | 2 + src/test/regress/expected/select_parallel.out | 6 +- 5 files changed, 109 insertions(+), 22 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 2f3bd8a459a..1f613d31376 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -96,7 +96,7 @@ static void show_tablesample(TableSampleClause *tsc, PlanState *planstate, List *ancestors, ExplainState *es); static void show_sort_info(SortState *sortstate, ExplainState *es); static void show_agg_info(AggState *aggstate, List *ancestors, ExplainState *es); -static void show_hash_info(HashState *hashstate, ExplainState *es); +static void show_hash_info(HashState *hashstate, List *ancestors, ExplainState *es); static void show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es); static void show_instrumentation_count(const char *qlabel, int which, @@ -1863,6 +1863,17 @@ ExplainNode(PlanState *planstate, List *ancestors, if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 2, planstate, es); + if (es->verbose) + { + ListCell *lc1, *lc2; + + forboth(lc1, ((HashJoin *) plan)->hashkeys, + lc2, ((HashJoinState *) planstate)->hj_OuterHashKeys) + { + show_expression(lfirst(lc1), lfirst(lc2), "Outer Hash Key", + planstate, ancestors, true, es); + } + } break; case T_Agg: show_upper_qual(plan->qual, planstate->qual, "Filter", planstate, @@ -1903,7 +1914,7 @@ ExplainNode(PlanState *planstate, List *ancestors, es); break; case T_Hash: - show_hash_info(castNode(HashState, planstate), es); + show_hash_info(castNode(HashState, planstate), ancestors, es); break; default: break; @@ -3087,7 +3098,7 @@ show_agg_info(AggState *aggstate, List *ancestors, ExplainState *es) * Show information on hash buckets/batches. */ static void -show_hash_info(HashState *hashstate, ExplainState *es) +show_hash_info(HashState *hashstate, List *ancestors, ExplainState *es) { HashInstrumentation hinstrument = {0}; @@ -3184,6 +3195,18 @@ show_hash_info(HashState *hashstate, ExplainState *es) spacePeakKb); } } + + if (es->verbose) + { + ListCell *lc1, *lc2; + + forboth(lc1, ((Hash *) hashstate->ps.plan)->hashkeys, + lc2, hashstate->hashkeys) + { + show_expression(lfirst(lc1), (ExprState *) lfirst(lc2), "Hash Key", + &hashstate->ps, ancestors, true, es); + } + } } /* diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 1ddc4423888..2ba48596622 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -3792,6 +3792,7 @@ select t1.* from Hash Left Join Project: t1.f1 Hash Cond: (i8.q2 = i4.f1) + Outer Hash Key: i8.q2 -> Nested Loop Left Join Project: t1.f1, i8.q2 Join Filter: (t1.f1 = '***'::text) @@ -3802,24 +3803,29 @@ select t1.* from -> Hash Right Join Project: i8.q2 Hash Cond: ((NULL::integer) = i8b1.q2) + Outer Hash Key: (NULL::integer) -> Hash Join Project: i8.q2, (NULL::integer) Hash Cond: (i8.q1 = i8b2.q1) + Outer Hash Key: i8.q1 -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 -> Hash Output: i8b2.q1, (NULL::integer) + Hash Key: i8b2.q1 -> Seq Scan on public.int8_tbl i8b2 Project: i8b2.q1, NULL::integer -> Hash Output: i8b1.q2 + Hash Key: i8b1.q2 -> Seq Scan on public.int8_tbl i8b1 Project: i8b1.q2 -> Hash Output: i4.f1 + Hash Key: i4.f1 -> Seq Scan on public.int4_tbl i4 Output: i4.f1 -(30 rows) +(36 rows) select t1.* from text_tbl t1 @@ -3853,6 +3859,7 @@ select t1.* from Hash Left Join Project: t1.f1 Hash Cond: (i8.q2 = i4.f1) + Outer Hash Key: i8.q2 -> Nested Loop Left Join Project: t1.f1, i8.q2 Join Filter: (t1.f1 = '***'::text) @@ -3863,9 +3870,11 @@ select t1.* from -> Hash Right Join Project: i8.q2 Hash Cond: ((NULL::integer) = i8b1.q2) + Outer Hash Key: (NULL::integer) -> Hash Right Join Project: i8.q2, (NULL::integer) Hash Cond: (i8b2.q1 = i8.q1) + Outer Hash Key: i8b2.q1 -> Nested Loop Project: i8b2.q1, NULL::integer -> Seq Scan on public.int8_tbl i8b2 @@ -3874,17 +3883,20 @@ select t1.* from -> Seq Scan on public.int4_tbl i4b2 -> Hash Output: i8.q1, i8.q2 + Hash Key: i8.q1 -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 -> Hash Output: i8b1.q2 + Hash Key: i8b1.q2 -> Seq Scan on public.int8_tbl i8b1 Project: i8b1.q2 -> Hash Output: i4.f1 + Hash Key: i4.f1 -> Seq Scan on public.int4_tbl i4 Output: i4.f1 -(34 rows) +(40 rows) select t1.* from text_tbl t1 @@ -3919,6 +3931,7 @@ select t1.* from Hash Left Join Project: t1.f1 Hash Cond: (i8.q2 = i4.f1) + Outer Hash Key: i8.q2 -> Nested Loop Left Join Project: t1.f1, i8.q2 Join Filter: (t1.f1 = '***'::text) @@ -3929,31 +3942,38 @@ select t1.* from -> Hash Right Join Project: i8.q2 Hash Cond: ((NULL::integer) = i8b1.q2) + Outer Hash Key: (NULL::integer) -> Hash Right Join Project: i8.q2, (NULL::integer) Hash Cond: (i8b2.q1 = i8.q1) + Outer Hash Key: i8b2.q1 -> Hash Join Project: i8b2.q1, NULL::integer Hash Cond: (i8b2.q1 = i4b2.f1) + Outer Hash Key: i8b2.q1 -> Seq Scan on public.int8_tbl i8b2 Output: i8b2.q1, i8b2.q2 -> Hash Output: i4b2.f1 + Hash Key: i4b2.f1 -> Seq Scan on public.int4_tbl i4b2 Output: i4b2.f1 -> Hash Output: i8.q1, i8.q2 + Hash Key: i8.q1 -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 -> Hash Output: i8b1.q2 + Hash Key: i8b1.q2 -> Seq Scan on public.int8_tbl i8b1 Project: i8b1.q2 -> Hash Output: i4.f1 + Hash Key: i4.f1 -> Seq Scan on public.int4_tbl i4 Output: i4.f1 -(37 rows) +(45 rows) select t1.* from text_tbl t1 @@ -4177,6 +4197,7 @@ where ss1.c2 = 0; -> Hash Join Project: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42 Hash Cond: (i41.f1 = i42.f1) + Outer Hash Key: i41.f1 -> Nested Loop Project: i8.q1, i8.q2, i43.f1, i41.f1 -> Nested Loop @@ -4191,13 +4212,14 @@ where ss1.c2 = 0; Output: i41.f1 -> Hash Output: i42.f1 + Hash Key: i42.f1 -> Seq Scan on public.int4_tbl i42 Output: i42.f1 -> Limit Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42)) -> Seq Scan on public.text_tbl Project: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42) -(25 rows) +(27 rows) select ss2.* from int4_tbl i41 @@ -5259,13 +5281,15 @@ select * from int4_tbl i left join Hash Left Join Project: i.f1, j.f1 Hash Cond: (i.f1 = j.f1) + Outer Hash Key: i.f1 -> Seq Scan on public.int4_tbl i Output: i.f1 -> Hash Output: j.f1 + Hash Key: j.f1 -> Seq Scan on public.int2_tbl j Output: j.f1 -(9 rows) +(11 rows) select * from int4_tbl i left join lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; @@ -5317,14 +5341,16 @@ select * from int4_tbl a, -> Hash Left Join Project: b.f1, c.q1, c.q2 Hash Cond: (b.f1 = c.q1) + Outer Hash Key: b.f1 -> Seq Scan on public.int4_tbl b Output: b.f1 -> Hash Output: c.q1, c.q2 + Hash Key: c.q1 -> Seq Scan on public.int8_tbl c Output: c.q1, c.q2 Filter: (a.f1 = c.q2) -(14 rows) +(16 rows) select * from int4_tbl a, lateral ( @@ -5449,26 +5475,30 @@ select * from -> Hash Right Join Project: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) Hash Cond: (d.q1 = c.q2) + Outer Hash Key: d.q1 -> Nested Loop Project: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) -> Hash Left Join Project: a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)) Hash Cond: (a.q2 = b.q1) + Outer Hash Key: a.q2 -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Hash Output: b.q1, (COALESCE(b.q2, '42'::bigint)) + Hash Key: b.q1 -> Seq Scan on public.int8_tbl b Project: b.q1, COALESCE(b.q2, '42'::bigint) -> Seq Scan on public.int8_tbl d Project: d.q1, COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2) -> Hash Output: c.q1, c.q2 + Hash Key: c.q2 -> Seq Scan on public.int8_tbl c Output: c.q1, c.q2 -> Result Project: (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) -(24 rows) +(28 rows) -- case that breaks the old ph_may_need optimization explain (verbose, costs off) @@ -5490,11 +5520,13 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from -> Hash Right Join Project: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) Hash Cond: (d.q1 = c.q2) + Outer Hash Key: d.q1 -> Nested Loop Project: a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) -> Hash Right Join Project: a.q1, a.q2, b.q1, (COALESCE(b.q2, (b2.f1)::bigint)) Hash Cond: (b.q1 = a.q2) + Outer Hash Key: b.q1 -> Nested Loop Project: b.q1, COALESCE(b.q2, (b2.f1)::bigint) Join Filter: (b.q1 < b2.f1) @@ -5506,19 +5538,21 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from Output: b2.f1 -> Hash Output: a.q1, a.q2 + Hash Key: a.q2 -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Seq Scan on public.int8_tbl d Project: d.q1, COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2) -> Hash Output: c.q1, c.q2 + Hash Key: c.q2 -> Seq Scan on public.int8_tbl c Output: c.q1, c.q2 -> Materialize Output: i.f1 -> Seq Scan on public.int4_tbl i Output: i.f1 -(34 rows) +(38 rows) -- check processing of postponed quals (bug #9041) explain (verbose, costs off) @@ -5791,10 +5825,12 @@ select t1.b, ss.phv from join_ut1 t1 left join lateral -> Hash Join Project: t2.a, LEAST(t1.a, t2.a, t3.a) Hash Cond: (t3.b = t2.a) + Outer Hash Key: t3.b -> Seq Scan on public.join_ut1 t3 Output: t3.a, t3.b, t3.c -> Hash Output: t2.a + Hash Key: t2.a -> Append -> Seq Scan on public.join_pt1p1p1 t2 Project: t2.a @@ -5802,7 +5838,7 @@ select t1.b, ss.phv from join_ut1 t1 left join lateral -> Seq Scan on public.join_pt1p2 t2_1 Project: t2_1.a Filter: (t1.a = t2_1.a) -(21 rows) +(23 rows) select t1.b, ss.phv from join_ut1 t1 left join lateral (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv @@ -5872,13 +5908,15 @@ select * from j1 inner join j2 on j1.id = j2.id; Project: j1.id, j2.id Inner Unique: true Hash Cond: (j1.id = j2.id) + Outer Hash Key: j1.id -> Seq Scan on public.j1 Output: j1.id -> Hash Output: j2.id + Hash Key: j2.id -> Seq Scan on public.j2 Output: j2.id -(10 rows) +(12 rows) -- ensure join is not unique when not an equi-join explain (verbose, costs off) @@ -5905,13 +5943,15 @@ select * from j1 inner join j3 on j1.id = j3.id; Project: j1.id, j3.id Inner Unique: true Hash Cond: (j3.id = j1.id) + Outer Hash Key: j3.id -> Seq Scan on public.j3 Output: j3.id -> Hash Output: j1.id + Hash Key: j1.id -> Seq Scan on public.j1 Output: j1.id -(10 rows) +(12 rows) -- ensure left join is marked as unique explain (verbose, costs off) @@ -5922,13 +5962,15 @@ select * from j1 left join j2 on j1.id = j2.id; Project: j1.id, j2.id Inner Unique: true Hash Cond: (j1.id = j2.id) + Outer Hash Key: j1.id -> Seq Scan on public.j1 Output: j1.id -> Hash Output: j2.id + Hash Key: j2.id -> Seq Scan on public.j2 Output: j2.id -(10 rows) +(12 rows) -- ensure right join is marked as unique explain (verbose, costs off) @@ -5939,13 +5981,15 @@ select * from j1 right join j2 on j1.id = j2.id; Project: j1.id, j2.id Inner Unique: true Hash Cond: (j2.id = j1.id) + Outer Hash Key: j2.id -> Seq Scan on public.j2 Output: j2.id -> Hash Output: j1.id + Hash Key: j1.id -> Seq Scan on public.j1 Output: j1.id -(10 rows) +(12 rows) -- ensure full join is marked as unique explain (verbose, costs off) @@ -5956,13 +6000,15 @@ select * from j1 full join j2 on j1.id = j2.id; Project: j1.id, j2.id Inner Unique: true Hash Cond: (j1.id = j2.id) + Outer Hash Key: j1.id -> Seq Scan on public.j1 Output: j1.id -> Hash Output: j2.id + Hash Key: j2.id -> Seq Scan on public.j2 Output: j2.id -(10 rows) +(12 rows) -- a clauseless (cross) join can't be unique explain (verbose, costs off) @@ -5988,13 +6034,15 @@ select * from j1 natural join j2; Project: j1.id Inner Unique: true Hash Cond: (j1.id = j2.id) + Outer Hash Key: j1.id -> Seq Scan on public.j1 Output: j1.id -> Hash Output: j2.id + Hash Key: j2.id -> Seq Scan on public.j2 Output: j2.id -(10 rows) +(12 rows) -- ensure a distinct clause allows the inner to become unique explain (verbose, costs off) @@ -6170,6 +6218,7 @@ where exists (select 1 from tenk1 t3 -> Hash Join Project: t1.unique1, t3.tenthous Hash Cond: (t3.thousand = t1.unique1) + Outer Hash Key: t3.thousand -> HashAggregate Project: t3.thousand, t3.tenthous Phase 0 using strategy "Hash": @@ -6178,13 +6227,14 @@ where exists (select 1 from tenk1 t3 Output: t3.thousand, t3.tenthous -> Hash Output: t1.unique1 + Hash Key: t1.unique1 -> Index Only Scan using onek_unique1 on public.onek t1 Output: t1.unique1 Index Cond: (t1.unique1 < 1) -> Index Only Scan using tenk1_hundred on public.tenk1 t2 Output: t2.hundred Index Cond: (t2.hundred = t3.tenthous) -(19 rows) +(21 rows) -- ... unless it actually is unique create table j3 as select unique1, tenthous from onek; diff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/expected/join_hash.out index 4e405ebbd76..379b3b1566e 100644 --- a/src/test/regress/expected/join_hash.out +++ b/src/test/regress/expected/join_hash.out @@ -919,6 +919,8 @@ WHERE Project: hjtest_1.a, hjtest_2.a, (hjtest_1.tableoid)::regclass, (hjtest_2.tableoid)::regclass Hash Cond: ((hjtest_1.id = (SubPlan 1)) AND ((SubPlan 2) = (SubPlan 3))) Join Filter: (hjtest_1.a <> hjtest_2.b) + Outer Hash Key: hjtest_1.id + Outer Hash Key: (SubPlan 2) -> Seq Scan on public.hjtest_1 Project: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b Filter: ((SubPlan 4) < 50) @@ -927,6 +929,8 @@ WHERE Project: (hjtest_1.b * 5) -> Hash Output: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b + Hash Key: (SubPlan 1) + Hash Key: (SubPlan 3) -> Seq Scan on public.hjtest_2 Project: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b Filter: ((SubPlan 5) < 55) @@ -943,7 +947,7 @@ WHERE SubPlan 2 -> Result Project: (hjtest_1.b * 5) -(28 rows) +(32 rows) SELECT hjtest_1.a a1, hjtest_2.a a2,hjtest_1.tableoid::regclass t1, hjtest_2.tableoid::regclass t2 FROM hjtest_1, hjtest_2 @@ -973,6 +977,8 @@ WHERE Project: hjtest_1.a, hjtest_2.a, (hjtest_1.tableoid)::regclass, (hjtest_2.tableoid)::regclass Hash Cond: (((SubPlan 1) = hjtest_1.id) AND ((SubPlan 3) = (SubPlan 2))) Join Filter: (hjtest_1.a <> hjtest_2.b) + Outer Hash Key: (SubPlan 1) + Outer Hash Key: (SubPlan 3) -> Seq Scan on public.hjtest_2 Project: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b Filter: ((SubPlan 5) < 55) @@ -981,6 +987,8 @@ WHERE Project: (hjtest_2.c * 5) -> Hash Output: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b + Hash Key: hjtest_1.id + Hash Key: (SubPlan 2) -> Seq Scan on public.hjtest_1 Project: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b Filter: ((SubPlan 4) < 50) @@ -997,7 +1005,7 @@ WHERE SubPlan 3 -> Result Project: (hjtest_2.c * 5) -(28 rows) +(32 rows) SELECT hjtest_1.a a1, hjtest_2.a a2,hjtest_1.tableoid::regclass t1, hjtest_2.tableoid::regclass t2 FROM hjtest_2, hjtest_1 diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 92421090755..9d06f8467b2 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -5209,10 +5209,12 @@ UPDATE transition_table_base INFO: Hash Full Join Project: COALESCE(ot.id, nt.id), ot.val, nt.val Hash Cond: (ot.id = nt.id) + Outer Hash Key: ot.id -> Named Tuplestore Scan Output: ot.id, ot.val -> Hash Output: nt.id, nt.val + Hash Key: nt.id -> Named Tuplestore Scan Output: nt.id, nt.val diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index f561e41f6f3..76be808c127 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -971,6 +971,8 @@ explain (costs off, verbose) All Group -> Hash Semi Join Hash Cond: ((a.unique1 = b.unique1) AND (a.two = (row_number() OVER (?)))) + Outer Hash Key: a.unique1 + Outer Hash Key: a.two -> Gather Output: a.unique1, a.two Workers Planned: 4 @@ -978,6 +980,8 @@ explain (costs off, verbose) Project: a.unique1, a.two -> Hash Output: b.unique1, (row_number() OVER (?)) + Hash Key: b.unique1 + Hash Key: (row_number() OVER (?)) -> WindowAgg Project: b.unique1, row_number() OVER (?) -> Gather @@ -985,7 +989,7 @@ explain (costs off, verbose) Workers Planned: 4 -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b Output: b.unique1 -(21 rows) +(25 rows) -- LIMIT/OFFSET within sub-selects can't be pushed to workers. explain (costs off) -- 2.23.0.385.gbc12974a89