Skip to content

Commit 7d3583a

Browse files
robertmhaasrobertmhaas
committed
Test instrumentation of Hash nodes with parallel query.
Commit 8526bcb fixed bugs related to both Sort and Hash, but only added a test case for Sort. This adds a test case for Hash to match. Thomas Munro Discussion: http://postgr.es/m/CAEepm=2-LRnfwUBZDqQt+XAcd0af_ykNyyVvP3h1uB1AQ=e-eA@mail.gmail.com
1 parent 8526bcb commit 7d3583a

File tree

2 files changed

+166
-0
lines changed

2 files changed

+166
-0
lines changed

‎src/test/regress/expected/join.out

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6187,6 +6187,112 @@ $$);
61876187
1 | 1
61886188
(1 row)
61896189

6190+
rollback to settings;
6191+
-- Exercise rescans. We'll turn off parallel_leader_participation so
6192+
-- that we can check that instrumentation comes back correctly.
6193+
create table foo as select generate_series(1, 3) as id, 'xxxxx'::text as t;
6194+
alter table foo set (parallel_workers = 0);
6195+
create table bar as select generate_series(1, 5000) as id, 'xxxxx'::text as t;
6196+
alter table bar set (parallel_workers = 2);
6197+
-- multi-batch with rescan, parallel-oblivious
6198+
savepoint settings;
6199+
set parallel_leader_participation = off;
6200+
set min_parallel_table_scan_size = 0;
6201+
set parallel_setup_cost = 0;
6202+
set parallel_tuple_cost = 0;
6203+
set max_parallel_workers_per_gather = 2;
6204+
set enable_material = off;
6205+
set enable_mergejoin = off;
6206+
set work_mem = '64kB';
6207+
explain (costs off)
6208+
select count(*) from foo
6209+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
6210+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
6211+
QUERY PLAN
6212+
--------------------------------------------------------------------------
6213+
Aggregate
6214+
-> Nested Loop Left Join
6215+
Join Filter: ((foo.id < (b1.id + 1)) AND (foo.id > (b1.id - 1)))
6216+
-> Seq Scan on foo
6217+
-> Gather
6218+
Workers Planned: 2
6219+
-> Hash Join
6220+
Hash Cond: (b1.id = b2.id)
6221+
-> Parallel Seq Scan on bar b1
6222+
-> Hash
6223+
-> Seq Scan on bar b2
6224+
(11 rows)
6225+
6226+
select count(*) from foo
6227+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
6228+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
6229+
count
6230+
-------
6231+
3
6232+
(1 row)
6233+
6234+
select final > 1 as multibatch
6235+
from hash_join_batches(
6236+
$$
6237+
select count(*) from foo
6238+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
6239+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
6240+
$$);
6241+
multibatch
6242+
------------
6243+
t
6244+
(1 row)
6245+
6246+
rollback to settings;
6247+
-- single-batch with rescan, parallel-oblivious
6248+
savepoint settings;
6249+
set parallel_leader_participation = off;
6250+
set min_parallel_table_scan_size = 0;
6251+
set parallel_setup_cost = 0;
6252+
set parallel_tuple_cost = 0;
6253+
set max_parallel_workers_per_gather = 2;
6254+
set enable_material = off;
6255+
set enable_mergejoin = off;
6256+
set work_mem = '4MB';
6257+
explain (costs off)
6258+
select count(*) from foo
6259+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
6260+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
6261+
QUERY PLAN
6262+
--------------------------------------------------------------------------
6263+
Aggregate
6264+
-> Nested Loop Left Join
6265+
Join Filter: ((foo.id < (b1.id + 1)) AND (foo.id > (b1.id - 1)))
6266+
-> Seq Scan on foo
6267+
-> Gather
6268+
Workers Planned: 2
6269+
-> Hash Join
6270+
Hash Cond: (b1.id = b2.id)
6271+
-> Parallel Seq Scan on bar b1
6272+
-> Hash
6273+
-> Seq Scan on bar b2
6274+
(11 rows)
6275+
6276+
select count(*) from foo
6277+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
6278+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
6279+
count
6280+
-------
6281+
3
6282+
(1 row)
6283+
6284+
select final > 1 as multibatch
6285+
from hash_join_batches(
6286+
$$
6287+
select count(*) from foo
6288+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
6289+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
6290+
$$);
6291+
multibatch
6292+
------------
6293+
f
6294+
(1 row)
6295+
61906296
rollback to settings;
61916297
-- A full outer join where every record is matched.
61926298
-- non-parallel

‎src/test/regress/sql/join.sql

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2170,6 +2170,66 @@ $$
21702170
$$);
21712171
rollback to settings;
21722172

2173+
-- Exercise rescans. We'll turn off parallel_leader_participation so
2174+
-- that we can check that instrumentation comes back correctly.
2175+
2176+
create table foo as select generate_series(1, 3) as id, 'xxxxx'::text as t;
2177+
alter table foo set (parallel_workers = 0);
2178+
create table bar as select generate_series(1, 5000) as id, 'xxxxx'::text as t;
2179+
alter table bar set (parallel_workers = 2);
2180+
2181+
-- multi-batch with rescan, parallel-oblivious
2182+
savepoint settings;
2183+
set parallel_leader_participation = off;
2184+
set min_parallel_table_scan_size = 0;
2185+
set parallel_setup_cost = 0;
2186+
set parallel_tuple_cost = 0;
2187+
set max_parallel_workers_per_gather = 2;
2188+
set enable_material = off;
2189+
set enable_mergejoin = off;
2190+
set work_mem = '64kB';
2191+
explain (costs off)
2192+
select count(*) from foo
2193+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
2194+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
2195+
select count(*) from foo
2196+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
2197+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
2198+
select final > 1 as multibatch
2199+
from hash_join_batches(
2200+
$$
2201+
select count(*) from foo
2202+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
2203+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
2204+
$$);
2205+
rollback to settings;
2206+
2207+
-- single-batch with rescan, parallel-oblivious
2208+
savepoint settings;
2209+
set parallel_leader_participation = off;
2210+
set min_parallel_table_scan_size = 0;
2211+
set parallel_setup_cost = 0;
2212+
set parallel_tuple_cost = 0;
2213+
set max_parallel_workers_per_gather = 2;
2214+
set enable_material = off;
2215+
set enable_mergejoin = off;
2216+
set work_mem = '4MB';
2217+
explain (costs off)
2218+
select count(*) from foo
2219+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
2220+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
2221+
select count(*) from foo
2222+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
2223+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
2224+
select final > 1 as multibatch
2225+
from hash_join_batches(
2226+
$$
2227+
select count(*) from foo
2228+
left join (select b1.id, b1.t from bar b1 join bar b2 using (id)) ss
2229+
on foo.id < ss.id + 1 and foo.id > ss.id - 1;
2230+
$$);
2231+
rollback to settings;
2232+
21732233
-- A full outer join where every record is matched.
21742234

21752235
-- non-parallel

0 commit comments

Comments
 (0)