Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf867ce5

Browse files
committed
ExecHashRemoveNextSkewBucket must physically copy tuples to main hashtable.
Commit45f6240 added an assumption in ExecHashIncreaseNumBatchesand ExecHashIncreaseNumBuckets that they could find all tuples in the mainhash table by iterating over the "dense storage" introduced by that patch.However, ExecHashRemoveNextSkewBucket continued its old practice of simplyre-linking deleted skew tuples into the main table's hashchains. Hence,such tuples got lost during any subsequent increase in nbatch or nbuckets,and would never get joined, as reported in bug #13908 from Seth P.I (tgl) think that the aforesaid commit has got multiple design issuesand should be reworked rather completely; but there is no time for thatright now, so band-aid the problem by making ExecHashRemoveNextSkewBucketphysically copy deleted skew tuples into the "dense storage" arena.The added test case is able to exhibit the problem by means of fooling theplanner with a WHERE condition that it will underestimate the selectivityof, causing the initial nbatch estimate to be too small.Tomas Vondra and Tom Lane. Thanks to David Johnston for initialinvestigation into the bug report.
1 parentd89f06f commitf867ce5

File tree

3 files changed

+57
-2
lines changed

3 files changed

+57
-2
lines changed

‎src/backend/executor/nodeHash.c

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1575,8 +1575,19 @@ ExecHashRemoveNextSkewBucket(HashJoinTable hashtable)
15751575
if (batchno==hashtable->curbatch)
15761576
{
15771577
/* Move the tuple to the main hash table */
1578-
hashTuple->next=hashtable->buckets[bucketno];
1579-
hashtable->buckets[bucketno]=hashTuple;
1578+
HashJoinTuplecopyTuple;
1579+
1580+
/*
1581+
* We must copy the tuple into the dense storage, else it will not
1582+
* be found by, eg, ExecHashIncreaseNumBatches.
1583+
*/
1584+
copyTuple= (HashJoinTuple)dense_alloc(hashtable,tupleSize);
1585+
memcpy(copyTuple,hashTuple,tupleSize);
1586+
pfree(hashTuple);
1587+
1588+
copyTuple->next=hashtable->buckets[bucketno];
1589+
hashtable->buckets[bucketno]=copyTuple;
1590+
15801591
/* We have reduced skew space, but overall space doesn't change */
15811592
hashtable->spaceUsedSkew-=tupleSize;
15821593
}

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

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2331,6 +2331,34 @@ select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol;
23312331
reset enable_hashjoin;
23322332
reset enable_nestloop;
23332333
--
2334+
-- regression test for bug #13908 (hash join with skew tuples & nbatch increase)
2335+
--
2336+
set work_mem to '64kB';
2337+
set enable_mergejoin to off;
2338+
explain (costs off)
2339+
select count(*) from tenk1 a, tenk1 b
2340+
where a.hundred = b.thousand and (b.fivethous % 10) < 10;
2341+
QUERY PLAN
2342+
------------------------------------------------------------
2343+
Aggregate
2344+
-> Hash Join
2345+
Hash Cond: (a.hundred = b.thousand)
2346+
-> Index Only Scan using tenk1_hundred on tenk1 a
2347+
-> Hash
2348+
-> Seq Scan on tenk1 b
2349+
Filter: ((fivethous % 10) < 10)
2350+
(7 rows)
2351+
2352+
select count(*) from tenk1 a, tenk1 b
2353+
where a.hundred = b.thousand and (b.fivethous % 10) < 10;
2354+
count
2355+
--------
2356+
100000
2357+
(1 row)
2358+
2359+
reset work_mem;
2360+
reset enable_mergejoin;
2361+
--
23342362
-- regression test for 8.2 bug with improper re-ordering of left joins
23352363
--
23362364
create temp table tt3(f1 int, f2 text);

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -463,6 +463,22 @@ select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol;
463463
reset enable_hashjoin;
464464
reset enable_nestloop;
465465

466+
--
467+
-- regression test for bug #13908 (hash join with skew tuples & nbatch increase)
468+
--
469+
470+
set work_mem to'64kB';
471+
set enable_mergejoin to off;
472+
473+
explain (costs off)
474+
selectcount(*)from tenk1 a, tenk1 b
475+
wherea.hundred=b.thousandand (b.fivethous %10)<10;
476+
selectcount(*)from tenk1 a, tenk1 b
477+
wherea.hundred=b.thousandand (b.fivethous %10)<10;
478+
479+
reset work_mem;
480+
reset enable_mergejoin;
481+
466482
--
467483
-- regression test for 8.2 bug with improper re-ordering of left joins
468484
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp