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: Tue, 12 Aug 2003 18:29:14 -0700 (PDT)
Message-ID: <20030812180544.L27665-100000@megazone.bigpanda.com> (raw)
In-Reply-To: <4848.200.59.66.253.1060736672.squirrel@webmail.akyasociados.com.ar>
On Tue, 12 Aug 2003, Franco Bruno Borghesi wrote:
> I *guess* this query does the same as yours (please verify).
It does not in general unfortunately. :( I see two possible
problems.
The first is that by using an inner join you're going to lose
any rows where there is no match on the right hand table which
the original doesn't.
The second is that if there were two rows in affiliate_lockout
with the different member_ids but the same affiliate_locked_id
say, (21101, 10) and (21201, 10) and you were matching a lead row
with a affiliate_id of 10, the second row would get past the
condition since it has a member_id!=21101, but the original
would drop the row because there existed some matching
affiliate_lockout row where the member_id was 21101.
> SELECT
> L.*
> FROM
> lead L
> INNER JOIN purchase P ON (L.id=P.lead_id)
> INNER JOIN affiliate_lockout A ON
> (L.affiliate_id=A.affiliate_locked_id)
> INNER JOIN member_exclusion M ON
> (P.member_id=M.member_id_to_exclude)
> WHERE
> L.exclusive IS NULL OR
> (
> L.exclusive=0 AND
> L.nb_purchases<3
> ) AND
> P.lead_id<>21101 AND
> A.member_id<>21011
>
> > Hi all
> >
> > I have to optmize this query, because it takes a while to run (about
> > 30s)
> >
> > Here are the tables (with the keys):
> > affiliate_lockout (6 rows) (member_id, affiliate_id)
> > lead (4490 rows) (id, ...)
> > member (6 rows) (id, ...)
> > member_exclusion (3 rows) (member_id, member_id_to_exclude)
> > purchase (10000 rows) (lead_id, member_id, ...)
> >
> >
> > Here is the query:
> > SELECT *
> > FROM lead
> > WHERE
> > (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND
> >
> > id NOT IN (
> >
> > SELECT lead_id
> > FROM purchase
> > WHERE member_id = 21101
> > ) AND affiliate_id NOT
> > IN (
> >
> > SELECT affiliate_locked_id
> > FROM affiliate_lockout
> > WHERE member_id = 21101
> > ) 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
> > )
> >
> >
> > I wonder the problem is with the subqueries (which are apparently very
> > slow to run, according to what I read), but I can't figure how to
> > rewrite this query without any subquery ...
> >
> > Maybe the problem comes from the index ... How would you create your
> > indexes to optimize this query ?
> >
> > Could somebody help me ?
> > Thanks
> > krystoffff
> >
> > ---------------------------(end of
> > broadcast)--------------------------- TIP 5: Have you checked our
> > extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
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: <20030812180544.L27665-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