From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeffrey W. Baker <jwbaker@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Huge Data sets, simple queries
Date: Sat, 28 Jan 2006 13:55:08 -0500
Message-ID: <18925.1138474508@sss.pgh.pa.us> (raw)
In-Reply-To: <18415.1138469820@sss.pgh.pa.us>
References: <007101c623a9$82207c10$0200a8c0@videobox>
<11814.1138463702@sss.pgh.pa.us>
<1138468133.9336.5.camel@noodles>
<18415.1138469820@sss.pgh.pa.us>
I wrote:
> (We might need to tweak the planner to discourage selecting
> HashAggregate in the presence of DISTINCT aggregates --- I don't
> remember whether it accounts for the sortmem usage in deciding
> whether the hash will fit in memory or not ...)
Ah, I take that all back after checking the code: we don't use
HashAggregate at all when there are DISTINCT aggregates, precisely
because of this memory-blow-out problem.
For both your group-by-date query and the original group-by-month query,
the plan of attack is going to be to read the original input in grouping
order (either via sort or indexscan, with sorting probably preferred
unless the table is pretty well correlated with the index) and then
sort/uniq on the DISTINCT value within each group. The OP is probably
losing on that step compared to your test because it's over much larger
groups than yours, forcing some spill to disk. And most likely he's not
got an index on month, so the first sort is in fact a sort and not an
indexscan.
Bottom line is that he's probably doing a ton of on-disk sorting
where you're not doing any. This makes me think Luke's theory about
inadequate disk horsepower may be on the money.
regards, tom lane
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jwbaker@acm.org
Subject: Re: Huge Data sets, simple queries
In-Reply-To: <18925.1138474508@sss.pgh.pa.us>
* 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