X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 89691D1E96D for ; Fri, 16 Apr 2004 12:17:14 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 51321-08 for ; Fri, 16 Apr 2004 12:17:08 -0300 (ADT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by svr1.postgresql.org (Postfix) with SMTP id 98BA2D1E970 for ; Fri, 16 Apr 2004 12:17:07 -0300 (ADT) Received: (qmail 45700 invoked by uid 1001); 16 Apr 2004 15:17:06 -0000 Date: Fri, 16 Apr 2004 10:17:06 -0500 From: "Jim C. Nasby" To: pgsql-performance@postgresql.org Subject: Poor performance of group by query Message-ID: <20040416151706.GE87362@nasby.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline X-Operating-System: FreeBSD 4.9-RELEASE-p3 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/208 X-Sequence-Number: 6508 Anyone have any ideas why this query would be so slow? stats=# explain analyze SELECT work_units, min(raw_rank) AS rank FROM Trank_work_overall GROUP BY work_units; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1050.12..1085.98 rows=14347 width=16) (actual time=163149.981..163227.758 rows=17849 loops=1) -> Seq Scan on trank_work_overall (cost=0.00..804.41 rows=49141 width=16) (actual time=0.071..328.682 rows=49091 loops=1) Total runtime: 163296.212 ms (3 rows) stats=# \d Trank_work_overall Table "pg_temp_1.trank_work_overall" Column | Type | Modifiers ------------+--------+----------- raw_rank | bigint | work_units | bigint | stats=# FreeBSD fritz.distributed.net 5.2.1-RELEASE FreeBSD 5.2.1-RELEASE #1: Wed Apr 7 18:42:52 CDT 2004 root@fritz.distributed.net:/usr/obj/usr/src/sys/FRITZ amd64 The machine is a dual opteron with 4G of memory. The query in question was not hitting the disk at all. PostgreSQL 7.4.2 compiled with -O3. Also, if I set enable_hashagg = false, it runs in less than a second. -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"