From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeffrey W. Baker <jwbaker@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Huge Data sets, simple queries
Date: Sat, 28 Jan 2006 12:37:00 -0500
Message-ID: <18415.1138469820@sss.pgh.pa.us> (raw)
In-Reply-To: <1138468133.9336.5.camel@noodles>
References: <007101c623a9$82207c10$0200a8c0@videobox>
<11814.1138463702@sss.pgh.pa.us>
<1138468133.9336.5.camel@noodles>
"Jeffrey W. Baker" <jwbaker@acm.org> writes:
> On Sat, 2006-01-28 at 10:55 -0500, Tom Lane wrote:
>> Assuming that "month" means what it sounds like, the above would result
>> in running twelve parallel sort/uniq operations, one for each month
>> grouping, to eliminate duplicates before counting. You've got sortmem
>> set high enough to blow out RAM in that scenario ...
> Hrmm, why is it that with a similar query I get a far simpler plan than
> you describe, and relatively snappy runtime?
You can't see the sort operations in the plan, because they're invoked
implicitly by the GroupAggregate node. But they're there.
Also, a plan involving GroupAggregate is going to run the "distinct"
sorts sequentially, because it's dealing with only one grouping value at
a time. In the original case, the planner probably realizes there are
only 12 groups and therefore prefers a HashAggregate, which will try
to run all the sorts in parallel. Your "group by date" isn't a good
approximation of the original conditions because there will be a lot
more groups.
(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 ...)
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: <18415.1138469820@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