On Fri, Jul 24, 2020 at 10:40:47AM +0200, Tomas Vondra wrote:
On Thu, Jul 23, 2020 at 07:33:45PM -0700, Peter Geoghegan wrote:
On Thu, Jul 23, 2020 at 6:22 PM Tomas Vondra
<tomas.von...@2ndquadrant.com> wrote:
So let me share some fresh I/O statistics collected on the current code
using iosnoop. I've done the tests on two different machines using the
"aggregate part" of TPC-H Q17, i.e. essentially this:
SELECT * FROM (
SELECT
l_partkey AS agg_partkey,
0.2 * avg(l_quantity) AS avg_quantity
FROM lineitem GROUP BY l_partkey OFFSET 1000000000
) part_agg;
The OFFSET is there just to ensure we don't need to send anything to
the client, etc.
Thanks for testing this.
So sort writes ~3.4GB of data, give or take. But hashagg/master writes
almost 6-7GB of data, i.e. almost twice as much. Meanwhile, with the
original CP_SMALL_TLIST we'd write "only" ~5GB of data. That's still
much more than the 3.4GB of data written by sort (which has to spill
everything, while hashagg only spills rows not covered by the groups
that fit into work_mem).
What I find when I run your query (with my own TPC-H DB that is
smaller than what you used here -- 59,986,052 lineitem tuples) is that
the sort required about 7x more memory than the hash agg to do
everything in memory: 4,384,711KB for the quicksort vs 630,801KB peak
hash agg memory usage. I'd be surprised if the ratio was very
different for you -- but can you check?
I can check, but it's not quite clear to me what are we looking for?
Increase work_mem until there's no need to spill in either case?
FWIW the hashagg needs about 4775953kB and the sort 33677586kB. So yeah,
that's about 7x more. I think that's probably built into the TPC-H data
set. It'd be easy to construct cases with much higher/lower factors.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=15493271.13..15493271.13 rows=1 width=36) (actual
time=331351.099..331351.099 rows=0 loops=1)
-> HashAggregate (cost=15186647.64..15493271.13 rows=20441566 width=36)
(actual time=318190.465..330956.383 rows=15000000 loops=1)
Group Key: lineitem.l_partkey
Peak Memory Usage: 4775953kB
-> Seq Scan on lineitem (cost=0.00..12936556.76 rows=450018176
width=9) (actual time=0.156..56051.850 rows=450019701 loops=1)
Planning Time: 0.151 ms
Execution Time: 331931.239 ms
(7 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=81298097.02..81298097.02 rows=1 width=36) (actual
time=415344.639..415344.639 rows=0 loops=1)
-> GroupAggregate (cost=77616337.21..81298097.02 rows=20441566 width=36)
(actual time=209292.469..414951.954 rows=15000000 loops=1)
Group Key: lineitem.l_partkey
-> Sort (cost=77616337.21..78741382.65 rows=450018176 width=9)
(actual time=209292.435..329583.999 rows=450019701 loops=1)
Sort Key: lineitem.l_partkey
Sort Method: quicksort Memory: 33677586kB
-> Seq Scan on lineitem (cost=0.00..12936556.76 rows=450018176
width=9) (actual time=0.096..72474.733 rows=450019701 loops=1)
Planning Time: 0.145 ms
Execution Time: 417157.598 ms
(9 rows)
