From: | Ellen Cyran <ellen(at)urban(dot)csuohio(dot)edu> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | slow group by query |
Date: | 2002-11-18 17:55:33 |
Message-ID: | 5.1.1.6.0.20021118124632.01b5dcf0@wolf.csuohio.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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. Would it just be better
to transpose the table to begin with and avoid the group by all together?
SELECT b.msa_code, b.sic, b.own, b.ind_div,
(select emp from tbl_bls_msa as bls
where bls.year = '1975' and bls.msa_code = b.msa_code and bls.sic = b.sic
and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1975,
(select emp from tbl_bls_msa as bls
where bls.year = '1976' and bls.msa_code = b.msa_code and bls.sic = b.sic
and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1976,
(select emp from tbl_bls_msa as bls
where bls.year = '1977' and bls.msa_code = b.msa_code and bls.sic = b.sic
and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1977,
(select emp from tbl_bls_msa as bls
where bls.year = '1978' and bls.msa_code = b.msa_code and bls.sic = b.sic
and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1978,
(select emp from tbl_bls_msa as bls
where bls.year = '1990' and bls.msa_code = b.msa_code and bls.sic = b.sic
and bls.own = b.own and bls.ind_div = b.ind_div) AS emp1990
FROM tbl_bls_msa AS b
where msa_code in ('1680','1640','0040','0120','0080')
GROUP BY b.msa_code, b.sic, b.ind_div, b.own ;
NOTICE: QUERY PLAN:
Group (cost=635.97..695.18 rows=592 width=22)
-> Sort (cost=635.97..635.97 rows=5921 width=22)
-> 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..264.99 rows=5921 width=22)
SubPlan
-> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa
bls (cost=0.00
..53.71 rows=1 width=10)
-> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa
bls (cost=0.00
..53.71 rows=1 width=10)
-> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa
bls (cost=0.00
..53.71 rows=1 width=10)
-> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa
bls (cost=0.00
..53.71 rows=1 width=10)
-> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa
bls (cost=0.00
..53.71 rows=1 width=10)
Thanks.
Ellen
-------
From | Date | Subject | |
---|---|---|---|
Next Message | Jason Hihn | 2002-11-18 18:04:00 | Re: Press Release -- Just Waiting for Tom |
Previous Message | Jason Hihn | 2002-11-18 16:39:57 | Re: Press Release -- Just Waiting for Tom |