slow group by query
От | Ellen Cyran |
---|---|
Тема | slow group by query |
Дата | |
Msg-id | 5.1.1.6.0.20021118124632.01b5dcf0@wolf.csuohio.edu обсуждение исходный текст |
Ответы |
Re: slow group by query
Re: slow group by query |
Список | 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 Scanusing 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_keyon 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 -------
В списке pgsql-sql по дате отправления: