From: Ellen Cyran <ellen@urban.csuohio.edu>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: slow group by query
Date: Tue, 19 Nov 2002 11:26:15 -0500
Message-ID: <5.1.1.6.0.20021119112128.01ab52e0@wolf.csuohio.edu> (raw)
In-Reply-To: <20021119065715.V65980-100000@megazone23.bigpanda.com>
References: <5.1.1.6.0.20021118124632.01b5dcf0@wolf.csuohio.edu>
I had to modify your query somewhat, but the one below that is pretty much
the same took
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');
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ellen@urban.csuohio.edu, sszabo@megazone23.bigpanda.com
Subject: Re: slow group by query
In-Reply-To: <5.1.1.6.0.20021119112128.01ab52e0@wolf.csuohio.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox