From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: ProgHome <proghome@silesky.com>
Cc: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to optimize this query ?
Date: Wed, 13 Aug 2003 11:29:19 -0700 (PDT)
Message-ID: <20030813112311.J53899-100000@megazone.bigpanda.com> (raw)
In-Reply-To: <001a01c361c4$7d5d59e0$0700a8c0@Office3>
On Wed, 13 Aug 2003, ProgHome wrote:
> 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
As I replied to Franco for his query below, I believe this query is not
equivalent to your original query for a few cases, but those might not
come up.
If you had a row in lead like
id = 2, affiliate_id = 2
And rows in affiliate_lockout like:
affiliate_locked_id=2, member_id=21101
affiliate_locked_id=2, member_id=31101
should this row in lead be shown or not?
In the original query I think it would not (because lead.affiliate_id was
IN the affiliate_lockout table where member_id=21101). In the above query
I think it will, because one of the joined tables will have the lead
information and a member_id that is not equal to 21101.
> -----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: sszabo@megazone.bigpanda.com, proghome@silesky.com, franco@akyasociados.com.ar
Subject: Re: How to optimize this query ?
In-Reply-To: <20030813112311.J53899-100000@megazone.bigpanda.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