Skip site navigation (1) Skip section navigation (2)

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

From: edfialk <edfialk(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: small table, huge table, and a join = slow and tough query. cake inside!
Date: 2008-05-28 19:27:53
Message-ID: 672f6520-e521-49d9-8523-cbf3a6880db6@k37g2000hsf.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-general
Hey everybody, I'm having an issue executing the query I want.  I've
let a couple queries run a good 20 minutes and it still hasn't
finished.  For my app, this is way too long to be useful, and I'm not
real sure what else I can do, let alone if my query is even what I
want.

So I'm really hoping the great internets will help me out.

example rows from huge table (almost 900,000 rows):
fips, pollutant, value
1, co, 0.1
1, co, 0.2
1, co, 0.3
1, so, 1.0
1, so, 2.0
2, co, 0.5
2, co, 0.3
etc.

example from small table (3233 rows)
fips, geom, name
1, some geometry, "Some County, Some State"
2, some geometry, "Some Other County, Some State"

Every fips in the small table has MULTIPLE rows in the huge table
(roughly 300 per).

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)
or...
any ideas on how I could speed up the query, I would be so extremely
grateful.

Thanks in advance!
-Ed

P.S. Sorry, the cake was a lie.

Responses

pgsql-general by date

Next:From: Francisco ReyesDate: 2008-05-28 19:42:47
Subject: Re: Psql crashes with Segmentation fault on copy from
Previous:From: Gregory StarkDate: 2008-05-28 19:18:51
Subject: Re: Bottom Posting

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group