Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 92A53475AD7
	for <pgsql-sql@postgresql.org>; Tue, 19 Nov 2002 11:08:16 -0500 (EST)
Received: from wolf.urban.csuohio.edu (wolf.urban.csuohio.edu [137.148.96.16])
	by postgresql.org (Postfix) with ESMTP id 4C4FA4758F1
	for <pgsql-sql@postgresql.org>; Tue, 19 Nov 2002 11:08:14 -0500 (EST)
Received: from UR016B.urban.csuohio.edu (urban96-236.dhcp.csuohio.edu
	[137.148.96.236])
	by wolf.urban.csuohio.edu (8.11.3/8.11.3) with ESMTP id gAJG8EX11249;
	Tue, 19 Nov 2002 11:08:14 -0500 (EST)
Message-Id: <5.1.1.6.0.20021119105754.01a12448@wolf.csuohio.edu>
X-Sender: ellen@wolf.csuohio.edu
X-Mailer: QUALCOMM Windows Eudora Version 5.1.1
Date: Tue, 19 Nov 2002 11:08:07 -0500
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Ellen Cyran <ellen@urban.csuohio.edu>
Subject: Re: slow group by query 
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20977.1037719286@sss.pgh.pa.us>
References: <5.1.1.6.0.20021118124632.01b5dcf0@wolf.csuohio.edu>
	<5.1.1.6.0.20021118124632.01b5dcf0@wolf.csuohio.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
X-MailScanner: Found to be clean
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/277
X-Sequence-Number: 10419

I have vacuum analyzed recently.  I do it after a large number of inserts
and after indexing.  This database is in the development stages so there is
a lot of data loading at this time.  We are also using 7.2.3.  Here is
the explain analyze:

Group  (cost=637.18..696.52 rows=593 width=22) (actual 
time=982.67..67581.85 row
s=435 loops=1)
   ->  Sort  (cost=637.18..637.18 rows=5934 width=22) (actual 
time=833.27..844.78
  rows=6571 loops=1)
         ->  Index Scan using msa_code_tbl_bls_msa_key, 
msa_code_tbl_bls_msa_key,
  msa_code_tbl_bls_msa_key, msa_code_tbl_bls_msa_key, 
msa_code_tbl_bls_msa_key on
  tbl_bls_msa b  (cost=0.00..265.30 rows=5934 width=22) (actual 
time=0.80..367.64
  rows=6571 loops=1)
   SubPlan
     ->  Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa 
bls  (cost=0.00
..53.80 rows=1 width=10) (actual time=16.46..30.36 rows=1 loops=435)
     ->  Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa 
bls  (cost=0.00
..53.80 rows=1 width=10) (actual time=16.54..30.41 rows=1 loops=435)
     ->  Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa 
bls  (cost=0.00
..53.80 rows=1 width=10) (actual time=16.53..30.69 rows=1 loops=435)
     ->  Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa 
bls  (cost=0.00
..53.80 rows=1 width=10) (actual time=16.19..30.66 rows=1 loops=435)
     ->  Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa 
bls  (cost=0.00
..53.80 rows=1 width=10) (actual time=16.19..30.80 rows=1 loops=435)
Total runtime: 67589.69 msec

EXPLAIN

Thanks for the help.

At 10:21 AM 11/19/2002 -0500, Tom Lane wrote:
>Ellen Cyran <ellen@urban.csuohio.edu> 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