pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: Disk-based Hash Aggregation.
5+ messages / 4 participants
[nested] [flat]
* pgsql: Disk-based Hash Aggregation.
@ 2020-03-18 22:54 Jeff Davis <jdavis@postgresql.org>
2020-03-18 23:24 ` Re: pgsql: Disk-based Hash Aggregation. Alvaro Herrera <alvherre@2ndquadrant.com>
2020-03-23 06:05 ` Re: pgsql: Disk-based Hash Aggregation. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Jeff Davis @ 2020-03-18 22:54 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Disk-based Hash Aggregation.
While performing hash aggregation, track memory usage when adding new
groups to a hash table. If the memory usage exceeds work_mem, enter
"spill mode".
In spill mode, new groups are not created in the hash table(s), but
existing groups continue to be advanced if input tuples match. Tuples
that would cause a new group to be created are instead spilled to a
logical tape to be processed later.
The tuples are spilled in a partitioned fashion. When all tuples from
the outer plan are processed (either by advancing the group or
spilling the tuple), finalize and emit the groups from the hash
table. Then, create new batches of work from the spilled partitions,
and select one of the saved batches and process it (possibly spilling
recursively).
Author: Jeff Davis
Reviewed-by: Tomas Vondra, Adam Lee, Justin Pryzby, Taylor Vesely, Melanie Plageman
Discussion: https://postgr.es/m/507ac540ec7c20136364b5272acbcd4574aa76ef.camel@j-davis.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/1f39bce021540fde00990af55b4432c55ef4b3c7
Modified Files
--------------
doc/src/sgml/config.sgml | 32 +
src/backend/commands/explain.c | 37 +
src/backend/executor/nodeAgg.c | 1092 ++++++++++++++++++++++++-
src/backend/optimizer/path/costsize.c | 70 +-
src/backend/optimizer/plan/planner.c | 19 +-
src/backend/optimizer/prep/prepunion.c | 2 +-
src/backend/optimizer/util/pathnode.c | 14 +-
src/backend/utils/misc/guc.c | 20 +
src/include/executor/nodeAgg.h | 8 +
src/include/nodes/execnodes.h | 22 +-
src/include/optimizer/cost.h | 4 +-
src/test/regress/expected/aggregates.out | 184 +++++
src/test/regress/expected/groupingsets.out | 122 +++
src/test/regress/expected/select_distinct.out | 62 ++
src/test/regress/expected/sysviews.out | 4 +-
src/test/regress/sql/aggregates.sql | 131 +++
src/test/regress/sql/groupingsets.sql | 103 +++
src/test/regress/sql/select_distinct.sql | 62 ++
18 files changed, 1950 insertions(+), 38 deletions(-)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgsql: Disk-based Hash Aggregation.
2020-03-18 22:54 pgsql: Disk-based Hash Aggregation. Jeff Davis <jdavis@postgresql.org>
@ 2020-03-18 23:24 ` Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2020-03-18 23:24 UTC (permalink / raw)
To: Jeff Davis <jdavis@postgresql.org>; +Cc: pgsql-hackers@lists.postgresql.org
On 2020-Mar-18, Jeff Davis wrote:
> Disk-based Hash Aggregation.
>
> While performing hash aggregation, track memory usage when adding new
> groups to a hash table. If the memory usage exceeds work_mem, enter
> "spill mode".
Kudos!!
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgsql: Disk-based Hash Aggregation.
2020-03-18 22:54 pgsql: Disk-based Hash Aggregation. Jeff Davis <jdavis@postgresql.org>
@ 2020-03-23 06:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-03-23 21:49 ` Re: pgsql: Disk-based Hash Aggregation. Jeff Davis <pgsql@j-davis.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2020-03-23 06:05 UTC (permalink / raw)
To: Jeff Davis <jdavis@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org
Jeff Davis <jdavis@postgresql.org> writes:
> Disk-based Hash Aggregation.
I noticed that the regression tests seemed suddenly slower than they
have been. A bit of poking around reveals that this patch made
groupingsets.sql take approximately 8X longer than it used to,
and more than twice as long as any other core regression test.
This is absolutely, positively, not acceptable for a test that gets
run hundreds of times a day by lots of people and buildfarm animals.
If there's no way to test the feature in some significantly-cheaper way,
perhaps we should move this test out to a separate script that's not run
by default.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgsql: Disk-based Hash Aggregation.
2020-03-18 22:54 pgsql: Disk-based Hash Aggregation. Jeff Davis <jdavis@postgresql.org>
2020-03-23 06:05 ` Re: pgsql: Disk-based Hash Aggregation. Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-03-23 21:49 ` Jeff Davis <pgsql@j-davis.com>
2020-03-23 21:58 ` Re: pgsql: Disk-based Hash Aggregation. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Jeff Davis @ 2020-03-23 21:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Jeff Davis <jdavis@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org
On Mon, 2020-03-23 at 02:05 -0400, Tom Lane wrote:
> If there's no way to test the feature in some significantly-cheaper
> way,
> perhaps we should move this test out to a separate script that's not
> run
> by default.
I'll rework the tests. I wanted to be a bit more aggressive about
testing right after the checkin to shake out any problems, but I don't
think that's necessary any more.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgsql: Disk-based Hash Aggregation.
2020-03-18 22:54 pgsql: Disk-based Hash Aggregation. Jeff Davis <jdavis@postgresql.org>
2020-03-23 06:05 ` Re: pgsql: Disk-based Hash Aggregation. Tom Lane <tgl@sss.pgh.pa.us>
2020-03-23 21:49 ` Re: pgsql: Disk-based Hash Aggregation. Jeff Davis <pgsql@j-davis.com>
@ 2020-03-23 21:58 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2020-03-23 21:58 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: Jeff Davis <jdavis@postgresql.org>; pgsql-committers@lists.postgresql.org
Jeff Davis <pgsql@j-davis.com> writes:
> On Mon, 2020-03-23 at 02:05 -0400, Tom Lane wrote:
>> If there's no way to test the feature in some significantly-cheaper
>> way,
>> perhaps we should move this test out to a separate script that's not
>> run
>> by default.
> I'll rework the tests. I wanted to be a bit more aggressive about
> testing right after the checkin to shake out any problems, but I don't
> think that's necessary any more.
Fair enough.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-03-23 21:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-18 22:54 pgsql: Disk-based Hash Aggregation. Jeff Davis <jdavis@postgresql.org>
2020-03-18 23:24 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-03-23 06:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-03-23 21:49 ` Jeff Davis <pgsql@j-davis.com>
2020-03-23 21:58 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox