From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Cc: proghome@silesky.com
Cc: pgsql-sql@postgresql.org
Subject: Re: How to optimize this query ?
Date: Wed, 13 Aug 2003 10:10:02 -0700 (PDT)
Message-ID: <20030813093224.V50295-100000@megazone.bigpanda.com> (raw)
In-Reply-To: <1060791455.1082.1.camel@taz.oficina>
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, franco@akyasociados.com.ar, proghome@silesky.com
Subject: Re: How to optimize this query ?
In-Reply-To: <20030813093224.V50295-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