about 12 seconds so once I run it on five years it will take just as long. Thanks for the
suggestion though.
select distinct on (b.msa_code, b.sic, b.own, b.ind_div)
b.msa_code, b.sic, b.own, b.ind_div, y1975.emp from
tbl_bls_msa as b left outer join (select msa_code, sic, own, ind_div, emp from tbl_bls_msa as bls
where bls.year='1975' ) as y1975
on (b.msa_code=y1975.msa_code and
b.sic=y1975.sic and b.own=y1975.own and b.ind_div=y1975.ind_div)
where b.msa_code in ('1680', '1640', '0040', '0120', '0080');
I would be interested in knowing more about what postgres extensions are available. Where
are those documented at?
Have you tried doing the subqueries in from? Right now you're running each subquery once for each output row AFAICS.Maybe something like (doing only one year for example - and using a postgres extension), would work... select distinct on (b.msa_code, b.sic, b.own, b.ind_div) b.msa_code, b.sic, b.own, b.ind_div, y1975.emp1975 from tbl_bls_msa as b left outer join (select emp from tbl_bls_mas as bls where bls.year='1975') y1975 on (b.msa_code=y1975.msa_code and b.sic=y1975.sic and b.own=y1975.own and b.ind_div=y1975.ind_div) where msa_code in ('1680', '1640', '0040', '0120', '0080');
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster