From: proghome@silesky.com
To: pgsql-sql@postgresql.org
Subject: Re: How to optimize this query ?
Date: 13 Aug 2003 11:54:16 -0700
Message-ID: <85898f7e.0308131054.1b1801d2@posting.google.com> (raw)
References: <4848.200.59.66.253.1060736672.squirrel@webmail.akyasociados.com.ar>
<20030812180544.L27665-100000@megazone.bigpanda.com>
<85898f7e.0308130520.5af10fd0@posting.google.com>
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 !
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: proghome@silesky.com
Subject: Re: How to optimize this query ?
In-Reply-To: <85898f7e.0308131054.1b1801d2@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox