From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | Rhys Stewart <rhys(dot)stewart(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: JUST NOT ADDING UP |
Date: | 2006-06-23 14:00:02 |
Message-ID: | 449BF3E2.8060408@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Rhys Stewart wrote:
> hi all,
> something is not adding up. the following query is taking a long time
> to run.(its still running right now)
>
> select distinct on (prem) prem, num, addy, mynum,myad,ff.address,
> matchtype,
> the_geom
> from daily.recheck2, _sp_myparcels ff
> where
> prem not in (
> select distinct on (prem) prem from daily.recheck2 dr, _sp_myparcels ff
> where ff.address = unabrev
> )
> AND btrim(addy) = btrim(myad)
> AND num = mynum
You're probably better of with a NOT EXISTS here, instead of a NOT IN.
> UNION
And a UNION ALL here, considering you're already using distinct. Or you
could remove the distincts, maybe.
> select distinct on (prem) prem, num, addy, mynum,myad,dr.unabrev,
> matchtype, ff.the_geom
> from daily.recheck2 dr, _sp_myparcels ff--, feeder_polygon
> where ff.address = unabrev
You could have made your query a bit more readable. For example, leaving
the commented out feeder_polygon attribute there could confuse people
who're used to a -- (decrement) operator from other languages.
> the thing is if i run the first query by itself, it takes like about 2
> seconds, and if i run the subquery that takes about 2 seconds also, so
> why (well its now finished, took all of 3.31 minutes) does it take so
> long?
Why do you ask us, instead of the database? EXPLAIN ANALYZE is your (and
our) friend. Without that we can only guess what's slowing down your query.
--
Alban Hertroys
alban(at)magproductions(dot)nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2006-06-23 14:06:13 | Re: Plperl and my() lexical variables bug? |
Previous Message | Michael Fuhr | 2006-06-23 13:49:19 | Re: Plperl and my() lexical variables bug? |