From: ProgHome <proghome@silesky.com>
To: 'Stephan Szabo' <sszabo@megazone.bigpanda.com>
To: 'Franco Bruno Borghesi' <franco@akyasociados.com.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to optimize this query ?
Date: Wed, 13 Aug 2003 13:58:23 -0400
Message-ID: <001a01c361c4$7d5d59e0$0700a8c0@Office3> (raw)
In-Reply-To: <20030813093224.V50295-100000@megazone.bigpanda.com>
Sorry, I posted the following message on the newsgroups, but it seems
that you didn't see it ...
I tried with some LEFT JOINS, which give me the possibility to keep
the information of the right table.
I have now the following query, which is 10 times faster !!! (from 16s
to 1.6s)
But I'd like to remove the last subquery, to see if it faster ;)
-------------------------
SELECT lead. *
FROM lead
LEFT JOIN purchase ON ( lead.id = purchase.lead_id )
LEFT JOIN affiliate_lockout ON ( lead.affiliate_id =
affiliate_lockout.affiliate_locked_id )
WHERE (
exclusive IS NULL OR (
exclusive = 0 AND nb_purchases < 3
)
) AND id NOT
IN (
SELECT lead_id
FROM purchase
INNER JOIN member_exclusion
WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
purchase.member_id = 21101
) AND (
affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS
NULL
) AND purchase.member_id <> 21101
GROUP BY lead.id
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Wednesday, August 13, 2003 1:10 PM
To: Franco Bruno Borghesi
Cc: proghome@silesky.com; pgsql-sql@postgresql.org
Subject: Re: [SQL] How to optimize this query ?
On 13 Aug 2003, Franco Bruno Borghesi wrote:
> Maybe its better now. Anyway, what I think is that joining will
perform
> better than using IN. Am I wrong?
Generally that's true (for 7.3 and earlier). For 7.4 IN has gotten much
better, and you probably want to retry with IN. However, it's possible
that NOT EXISTS will work better than left joins even in 7.3 and
earlier,
I'm not sure, I think it's probably situational.
I think that you're still going to have a problem in the below if there
are purchase rows with member_id 21101 and some other value that both
match. I think you need to do something like the subselect on
affiliate_lockout in the from on purchase as well.
> 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
> ) A ON (L.affiliate_id=A.affiliated_locled_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
[I think this was meant to be member_id from the original query]
> (M.member_id IS NULL) AND
> (A.member_id IS NULL)
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, sszabo@megazone.bigpanda.com, franco@akyasociados.com.ar
Subject: Re: How to optimize this query ?
In-Reply-To: <001a01c361c4$7d5d59e0$0700a8c0@Office3>
* 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