Re: small table, huge table, and a join = slow and tough query. cake inside!

edfialk wrote:
> So, what I need is all kinds of things, but to start (pseudo-query):
>
> SELECT small.fips, small.geom, small.name, SUM(huge.value) from small
> JOIN huge on huge.fips = small.fips WHERE (SUM(huge.value)) > 500 AND
> huge.pollutant='co';
>
> wonder if that makes sense. Obviously, can't have an aggregate in
> where clause, so I've tried a couple WHERE (SELECT) kind of things,
> nothing working out too well.
> So first, if anyone has any idea on the best way I can do a WHERE
> (sum(huge.value) > 500)
>
See GROUP BY and HAVING

> or...
> any ideas on how I could speed up the query, I would be so extremely
> grateful.
>
>
What columns are primary keys or indexed?

Run this and post what it says

vacuum;
analyze;
explain <insert query here>

If you can wait for the query to finish, change the last line to
"explain analyze ..."

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4(at)une(dot)edu(dot)au