I looked up that thread....
1st:) p.s. I am using PostgreSQL 7.4.17
Any reason for that? Actual version is 8.2.4; or at least 8.1.9
2nd) your query is:
SELECT DISTINCT c.*
FROM customer c
LEFT OUTER JOIN weborders w
ON c.username = w.username
WHERE w.username IS NULL
AND c.sourceid IS NOT NULL
AND c.usertype = 0
AND c.emailrestrict = 1
ORDER BY c.addcountrycode, c.surname, c.initials
so you force the poor database to do an left outer join just to find the outer records? Why?
I would start with:
select c.* from customer c
where
c.sourceid IS NOT NULL
AND c.usertype = 0
AND c.emailrestrict = 1
and
c.username not in (select username from weborders)
BTW: I have a BAD feeling that your relation seems to be on "USERNAME", which should make it a primary key on "customer" - and a user entered primary key is a bad idea 104% of the time.
you can also save on the distinct, as every customer should have exactly one line in table customer. If not, you should dump that complete database and start from scratch.
Harald
On 8/18/07, Rishi Daryanani <rishijd@xxxxxxxxx> wrote:
Hi all,
I'm having problems with a query that's just
"stalling" my database. If someone could help me out -
I posted a forum topic on
http://forums.devshed.com/postgresql-help-21/postgresql-new-index-pros-and-cons-467120.html
There's just this one integer field, which when
searched on, "stalls" my query and I don't get any
results. I tried adding an index to that integer, but
that didn't seem to help..
Please let me know if there's any solution to this.
Any help would be really appreciated!
Many thanks!
____________________________________________________________________________________Ready for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.
http://tv.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
