From: Jan Wieck <JanWieck@Yahoo.com>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Cc: wweng@kencast.com
Subject: Re: iceberg queries
Date: Tue, 04 Feb 2003 13:06:20 -0500
Message-ID: <3E40011C.94F11C92@Yahoo.com> (raw)
References: <3E3F7C59.E6C5278C@rodos.fzk.de>
Christoph Haller wrote:
>
> >
> > Does PostgreSQL optimizer handle iceberg queries well?
> >
> What do you mean by "iceberg query" ?
> I've never heard this term.
Iceberg queries compute one or more aggregate functions to find
aggregate values above a specified threshold. A typical iceberg query
would be
SELECT a, count(a)
FROM tab
GROUP BY a
HAVING count(a) >= 100;
This base form can easily be made more complicated by doing self joins
and the like. This type of query is often found in market research, data
warehousing and search engines.
As to the original question, if an index is available that returns the
rows in the sort order of the GROUP BY clause, PostgreSQL defaults to an
index scan, otherwise it will do a sort of the rows matching an optional
WHERE clause. This sorted set is then grouped and aggregated and
filtered by the HAVING clause after aggregation.
It is well known that this approach does not scale well for large data
sets. But in contrast to a specialized statistical software, PostgreSQL
has to answer the query precisely. So sampling or bucket methods aren't
options.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
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-sql@postgresql.org
Cc: JanWieck@Yahoo.com, ch@rodos.fzk.de, wweng@kencast.com
Subject: Re: iceberg queries
In-Reply-To: <3E40011C.94F11C92@Yahoo.com>
* 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