X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8255A475D0F for ; Tue, 4 Feb 2003 13:37:57 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id C138C475AD7 for ; Tue, 4 Feb 2003 13:37:10 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h14IbA5u015717; Tue, 4 Feb 2003 13:37:10 -0500 (EST) To: Jan Wieck Cc: Christoph Haller , pgsql-sql@postgresql.org, wweng@kencast.com Subject: Re: iceberg queries In-reply-to: <3E40011C.94F11C92@Yahoo.com> References: <3E3F7C59.E6C5278C@rodos.fzk.de> <3E40011C.94F11C92@Yahoo.com> Comments: In-reply-to Jan Wieck message dated "Tue, 04 Feb 2003 13:06:20 -0500" Date: Tue, 04 Feb 2003 13:37:09 -0500 Message-ID: <15716.1044383829@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/80 X-Sequence-Number: 11478 Jan Wieck 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