Re: How to optimize this query ? - Mailing list pgsql-sql

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
>