Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A1390476108 for ; Tue, 19 Nov 2002 10:21:25 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 2B35447602A for ; Tue, 19 Nov 2002 10:21:23 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id gAJFLRhR020978; Tue, 19 Nov 2002 10:21:27 -0500 (EST) To: Ellen Cyran Cc: pgsql-sql@postgresql.org Subject: Re: slow group by query In-reply-to: <5.1.1.6.0.20021118124632.01b5dcf0@wolf.csuohio.edu> References: <5.1.1.6.0.20021118124632.01b5dcf0@wolf.csuohio.edu> Comments: In-reply-to Ellen Cyran message dated "Mon, 18 Nov 2002 12:55:33 -0500" Date: Tue, 19 Nov 2002 10:21:26 -0500 Message-ID: <20977.1037719286@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/275 X-Sequence-Number: 10417 Ellen Cyran writes: > Is there any way to make this query faster? I have indexes on year, > msa_code, and sic. I've also tried it with > an index on the combined group by columns. I've made both sort_mem and > shared_buffers bigger, but still this query > takes 40 seconds when I select 4 msa_codes and 7 minutes when I select 40 > msa_codes. Hm, have you vacuum analyzed lately? Those cost estimates seem awfully low for a query that is taking 40 sec. Also, if you're using 7.2 (which you should be ;-)) then showing EXPLAIN ANALYZE results would be more useful than plain EXPLAIN. regards, tom lane