From: | proghome(at)silesky(dot)com (krystoffff) |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How to optimize this query ? |
Date: | 2003-08-13 18:54:16 |
Message-ID: | 85898f7e.0308131054.1b1801d2@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
OK, here is the final query without any subquery ...
--------------------------
SELECT L. *
FROM lead L
LEFT JOIN purchase P ON ( L.id = P.lead_id )
LEFT JOIN member_exclusion M ON ( P.member_id = M.member_id_to_exclude
)
LEFT JOIN (
SELECT DISTINCT affiliate_locked_id
FROM affiliate_lockout
WHERE member_id = 21101
) AS A ON ( L.affiliate_id = A.affiliate_locked_id )
WHERE L.exclusive IS NULL OR (
L.exclusive = 0 AND L.nb_purchases < 3
) AND (
P.lead_id IS NULL OR P.lead_id <> 21101
) AND (
M.member_id IS NULL
)
GROUP BY L.id
-----------------------
I've got the same result as before, so it should be correct ;)
By the way, the time seems to be the same (1.41s for the last form,
and 1.44s now) but I think it's because I don't have much stuff in
Member_exclusion (6 rows) so I will keep this query without subquery
...
Thanks, Franco !
PS : definitively, try to avoid the subqueries ! It's easy to program,
but very slow to execute !
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-08-13 19:35:23 | Re: Porting from PL/SQL to PLPGSQL |
Previous Message | Stephan Szabo | 2003-08-13 18:39:39 | Re: How to optimize this query ? |