JUST NOT ADDING UP

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@magproductions.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 //