From: Jeffrey W. Baker <jwbaker@acm.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Huge Data sets, simple queries
Date: Sat, 28 Jan 2006 09:08:53 -0800
Message-ID: <1138468133.9336.5.camel@noodles> (raw)
In-Reply-To: <11814.1138463702@sss.pgh.pa.us>
References: <007101c623a9$82207c10$0200a8c0@videobox>
<11814.1138463702@sss.pgh.pa.us>
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?
select date
, count(1) as nads
, sum(case when premium then 1 else 0 end) as npremium
, count(distinct(keyword)) as nwords
, count(distinct(advertiser)) as nadvertisers
from data
group by date
order by date asc
QUERY PLAN
-----------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..14452743.09 rows=721 width=13)
-> Index Scan using data_date_idx on data (cost=0.00..9075144.27 rows=430206752 width=13)
(2 rows)
=# show server_version;
server_version
----------------
8.1.2
(1 row)
-jwb
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: jwbaker@acm.org, tgl@sss.pgh.pa.us
Subject: Re: Huge Data sets, simple queries
In-Reply-To: <1138468133.9336.5.camel@noodles>
* 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