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 3CB48D1B502; Mon, 10 Nov 2003 21:02:07 +0000 (GMT) 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 82717-05; Mon, 10 Nov 2003 17:01:36 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 7F794D1B4E1; Mon, 10 Nov 2003 17:01:34 -0400 (AST) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3896758; Mon, 10 Nov 2003 13:02:18 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "Marc G. Fournier" , pgsql-performance@postgresql.org Subject: Re: *very* slow query to summarize data for a month ... Date: Mon, 10 Nov 2003 12:59:03 -0800 User-Agent: KMail/1.4.3 References: <20031110160144.C727@ganymede.hub.org> In-Reply-To: <20031110160144.C727@ganymede.hub.org> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200311101259.03389.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/30 X-Sequence-Number: 4590 Marc, I'd say your machine is very low on available RAM, particularly sort_mem.= =20=20=20 The steps which are taking a long time are: =20 > Aggregate (cost=3D32000.94..32083.07 rows=3D821 width=3D41) (actual=20 time=3D32983.36..47586.17 rows=3D144 loops=3D1) > -> Group (cost=3D32000.94..32062.54 rows=3D8213 width=3D41) (actual= =20 time=3D32957.40..42817.88 rows=3D462198 loops=3D1) and: > -> Merge Join (cost=3D31321.45..31466.92 rows=3D8213 wid= th=3D41)=20 (actual time=3D13983.07..22642.14 rows=3D462198 loops=3D1) > Merge Cond: ("outer".company_id =3D "inner".company_= id) > -> Sort (cost=3D24.41..25.29 rows=3D352 width=3D25= ) (actual=20 time=3D5.52..7.40 rows=3D348 loops=3D1) There are also *large* delays between steps. Either your I/O is saturate= d,=20 or you haven't run a VACUUM FULL ANALYZE in a while (which would also expla= in=20 the estimates being off). --=20 -Josh Berkus Aglio Database Solutions San Francisco