From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: pgsql-hackers@postgresql.org
Subject: Excessive disk usage in WindowAgg
Date: Tue, 24 Sep 2019 12:49:54 +0100
Message-ID: <87a7ator8n.fsf@news-spur.riddles.org.uk> (raw)
This one just came up on IRC:
create table tltest(a integer, b text, c text, d text);
insert into tltest
select i, repeat('foo',100), repeat('foo',100), repeat('foo',100)
from generate_series(1,100000) i;
set log_temp_files=0;
set client_min_messages=log;
select count(a+c) from (select a, count(*) over () as c from tltest s1) s;
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp82513.3", size 92600000
Using 92MB of disk for one integer seems excessive; the reason is clear
from the explain:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16250.00..16250.01 rows=1 width=8) (actual time=1236.260..1236.260 rows=1 loops=1)
Output: count((tltest.a + (count(*) OVER (?))))
-> WindowAgg (cost=0.00..14750.00 rows=100000 width=12) (actual time=1193.846..1231.216 rows=100000 loops=1)
Output: tltest.a, count(*) OVER (?)
-> Seq Scan on public.tltest (cost=0.00..13500.00 rows=100000 width=4) (actual time=0.006..14.361 rows=100000 loops=1)
Output: tltest.a, tltest.b, tltest.c, tltest.d
so the whole width of the table is being stored in the tuplestore used
by the windowagg.
In create_windowagg_plan, we have:
/*
* WindowAgg can project, so no need to be terribly picky about child
* tlist, but we do need grouping columns to be available
*/
subplan = create_plan_recurse(root, best_path->subpath, CP_LABEL_TLIST);
Obviously we _do_ need to be more picky about this; it seems clear that
using CP_SMALL_TLIST | CP_LABEL_TLIST would be a win in many cases.
Opinions?
--
Andrew (irc:RhodiumToad)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andrew@tao11.riddles.org.uk
Subject: Re: Excessive disk usage in WindowAgg
In-Reply-To: <87a7ator8n.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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