pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feediceberg queries
7+ messages / 5 participants
[nested] [flat]
* iceberg queries
@ 2003-02-04 05:38 Wei Weng <wweng@kencast.com>
0 siblings, 0 replies; 7+ messages in thread
From: Wei Weng @ 2003-02-04 05:38 UTC (permalink / raw)
To: pgsql-sql
Does PostgreSQL optimizer handle iceberg queries well?
Thanks
Wei
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: iceberg queries
@ 2003-02-04 08:39 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 2 replies; 7+ messages in thread
From: Christoph Haller @ 2003-02-04 08:39 UTC (permalink / raw)
To: pgsql-sql; +Cc: wweng@kencast.com
>
> Does PostgreSQL optimizer handle iceberg queries well?
>
What do you mean by "iceberg query" ?
I've never heard this term.
Regards, Christoph
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: iceberg queries
@ 2003-02-04 14:08 Wei Weng <wweng@kencast.com>
parent: Christoph Haller <ch@rodos.fzk.de>
1 sibling, 1 reply; 7+ messages in thread
From: Wei Weng @ 2003-02-04 14:08 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
It is a query that looks like
SELECT target1, target2... targetn, SUN(t.qty)
FROM Table t
GROUP BY target1
HAVING SUM(t.qty)>=10
You can replace SUM(t.qty)>=10 with other aggregate constraints.
----- Original Message -----
From: Christoph Haller
To: pgsql-sql@postgresql.org
Cc: wweng@kencast.com
Sent: Tuesday, February 04, 2003 3:39 AM
Subject: Re: [SQL] iceberg queries
>
> Does PostgreSQL optimizer handle iceberg queries well?
>
What do you mean by "iceberg query" ?
I've never heard this term.
Regards, Christoph
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: iceberg queries
@ 2003-02-04 15:50 Bruno Wolff III <bruno@wolff.to>
parent: Wei Weng <wweng@kencast.com>
0 siblings, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2003-02-04 15:50 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Tue, Feb 04, 2003 at 09:08:56 -0500,
Wei Weng <wweng@kencast.com> wrote:
> It is a query that looks like
>
> SELECT target1, target2... targetn, SUN(t.qty)
> FROM Table t
> GROUP BY target1
> HAVING SUM(t.qty)>=10
>
> You can replace SUM(t.qty)>=10 with other aggregate constraints.
There were some recent changes to allow groups to use hashes that may
help for queries like this. This can save a sort step or using an
index scan.
In theory you might be able to make some other speed ups by taking advantage
of properties of specific aggregate functions (in particular that several
common ones monotonicly increase or decrease as they are being calculated)
and if doing an index scan on the fields used for group you might be able to
skip a lot of rows. I expect that this situation would come up pretty rarely
though.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: iceberg queries
@ 2003-02-04 18:06 Jan Wieck <JanWieck@Yahoo.com>
parent: Christoph Haller <ch@rodos.fzk.de>
1 sibling, 1 reply; 7+ messages in thread
From: Jan Wieck @ 2003-02-04 18:06 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql; wweng@kencast.com
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 #
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: iceberg queries
@ 2003-02-04 18:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2003-02-04 18:37 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; wweng@kencast.com
Jan Wieck <JanWieck@Yahoo.com> writes:
> 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.
Note that as of 7.4, the planner will probably pick hashed aggregation
rather than sort-based aggregation, if it can predict that the number
of groups will not be too large for a hash table to fit in memory.
This means we can do a seqscan (or, perhaps, an indexscan to match
WHERE conditions) and avoid sorting. So I expect performance on this
type of query to be a good deal better in 7.4. There are a few
benchmark comparisons in the pghackers archives a couple months back.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: iceberg queries
@ 2003-02-04 23:14 Jan Wieck <JanWieck@Yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Jan Wieck @ 2003-02-04 23:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; wweng@kencast.com
Tom Lane wrote:
>
> Jan Wieck <JanWieck@Yahoo.com> writes:
> > 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.
>
> Note that as of 7.4, the planner will probably pick hashed aggregation
> rather than sort-based aggregation, if it can predict that the number
> of groups will not be too large for a hash table to fit in memory.
> This means we can do a seqscan (or, perhaps, an indexscan to match
> WHERE conditions) and avoid sorting. So I expect performance on this
> type of query to be a good deal better in 7.4. There are a few
> benchmark comparisons in the pghackers archives a couple months back.
>
> regards, tom lane
If it can predict.
I guess the question was asked because one expects performance problems.
From that I conclude that the amount of data is significant in this
particular case. That does not necessarily but usually mean a large
number of unique groups.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-02-04 23:14 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-04 05:38 iceberg queries Wei Weng <wweng@kencast.com>
2003-02-04 08:39 Re: iceberg queries Christoph Haller <ch@rodos.fzk.de>
2003-02-04 14:08 ` Wei Weng <wweng@kencast.com>
2003-02-04 15:50 ` Bruno Wolff III <bruno@wolff.to>
2003-02-04 18:06 ` Jan Wieck <JanWieck@Yahoo.com>
2003-02-04 18:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-04 23:14 ` Jan Wieck <JanWieck@Yahoo.com>
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