From: | Ellen Cyran <ellen(at)urban(dot)csuohio(dot)edu> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: slow group by query |
Date: | 2002-11-19 16:08:07 |
Message-ID: | 5.1.1.6.0.20021119105754.01a12448@wolf.csuohio.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)urban(dot)csuohio(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ellen Cyran | 2002-11-19 16:26:15 | Re: slow group by query |
Previous Message | Hugh Esco | 2002-11-19 15:56:15 | Re: Problems invoking psql. Help please. |