X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 728ACD1C4C8 for ; Wed, 13 Aug 2003 16:13:44 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 35322-06 for ; Wed, 13 Aug 2003 13:12:07 -0300 (ADT) Received: from lucifer.akyasociados.com.ar (unknown [200.69.203.237]) by svr1.postgresql.org (Postfix) with ESMTP id 15672D1B56B for ; Wed, 13 Aug 2003 13:13:32 -0300 (ADT) Received: from taz.oficina (taz.oficina [192.168.1.174]) (authenticated bits=0) by lucifer.akyasociados.com.ar (8.12.9/8.12.9) with ESMTP id h7DGFv4d055735; Wed, 13 Aug 2003 13:15:58 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Subject: Re: How to optimize this query ? From: Franco Bruno Borghesi To: proghome@silesky.com, pgsql-sql@postgresql.org In-Reply-To: <20030812180544.L27665-100000@megazone.bigpanda.com> References: <20030812180544.L27665-100000@megazone.bigpanda.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-MQfU+HY2ZJi0VwARkFu1" Organization: AK y Asociados S.R.L. Message-Id: <1060791455.1082.1.camel@taz.oficina> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 13 Aug 2003 13:17:35 -0300 X-MailScanner-Information: Please contact the ISP for more information X-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/141 X-Sequence-Number: 14757 --=-MQfU+HY2ZJi0VwARkFu1 Content-Type: multipart/alternative; boundary="=-T9f2q3MVTj4Xm6sLXgQm" --=-T9f2q3MVTj4Xm6sLXgQm Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Maybe its better now. Anyway, what I think is that joining will perform better than using IN. Am I wrong? SELECT L.* FROM lead L LEFT JOIN purchase P ON (L.id=3DP.lead_id) LEFT JOIN member_exclusion M ON (P.member_id=3DM.member_id_to_exclude) LEFT JOIN ( SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout WHERE member_id=3D21101 ) A ON (L.affiliate_id=3DA.affiliated_locled_id) WHERE L.exclusive IS NULL OR ( L.exclusive=3D0 AND L.nb_purchases<3 ) AND (P.lead_id IS NULL OR P.lead_id<>21101) AND (M.member_id IS NULL) AND (A.member_id IS NULL) On Tue, 2003-08-12 at 22:29, Stephan Szabo wrote:=20 > On Tue, 12 Aug 2003, Franco Bruno Borghesi wrote: >=20 > > I *guess* this query does the same as yours (please verify). >=20 > It does not in general unfortunately. :( I see two possible > problems. >=20 > 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. >=20 > 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!=3D21101, but the original > would drop the row because there existed some matching > affiliate_lockout row where the member_id was 21101. >=20 > > SELECT > > L.* > > FROM > > lead L > > INNER JOIN purchase P ON (L.id=3DP.lead_id) > > INNER JOIN affiliate_lockout A ON > > (L.affiliate_id=3DA.affiliate_locked_id) > > INNER JOIN member_exclusion M ON > > (P.member_id=3DM.member_id_to_exclude) > > WHERE > > L.exclusive IS NULL OR > > ( > > L.exclusive=3D0 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 =3D 0 AND nb_purchases < 3)) AND > > > > > > id NOT IN ( > > > > > > SELECT lead_id > > > FROM purchase > > > WHERE member_id =3D 21101 > > > ) AND affiliate_id NOT > > > IN ( > > > > > > SELECT affiliate_locked_id > > > FROM affiliate_lockout > > > WHERE member_id =3D 21101 > > > ) AND id NOT > > > IN ( > > > > > > SELECT lead_id > > > FROM purchase > > > INNER JOIN member_exclusion > > > WHERE purchase.member_id =3D member_exclusion.member_id_to_exclude AND > > > purchase.member_id =3D 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 > > >=20 >=20 >=20 >=20 >=20 >=20 --=-T9f2q3MVTj4Xm6sLXgQm Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Maybe its better now. Anyway, what I think is that joining will perform bet= ter than using IN. Am I wrong?

SELECT
   L.*
FROM
      lead L
      LEFT JOIN purchase P ON (L.id=3DP.lead_id)       LEFT JOIN member_exclusion M ON (P.member_id= =3DM.member_id_to_exclude)
      LEFT JOIN (
       SELECT DISTINCT affiliate_locked_id FROM af= filiate_lockout WHERE member_id=3D21101
    ) A ON (L.affiliate_id=3DA.affiliated_locled_id)
WHERE
        L.exclusive IS NULL OR
        (
            &nb= sp;   L.exclusive=3D0 AND
            &nb= sp;   L.nb_purchases<3
        ) AND
        (P.lead_id IS NULL OR P.lead_id&= lt;>21101) AND
  (M.member_id IS NULL) AND
        (A.member_id IS NULL)


On Tue, 2003-08-12 at 22:29, Stephan Szabo wrote:=20 
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!=3D21101, 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=3DP.lead_id)
> 	INNER JOIN affiliate_lockout A ON
> (L.affiliate_id=3DA.affiliate_locked_id)
> 	INNER JOIN member_exclusion M ON
> (P.member_id=3DM.member_id_to_exclude)
> WHERE
> 	L.exclusive IS NULL OR
> 	(
> 		L.exclusive=3D0 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 (ab=
out
> > 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 =3D 0 AND nb_purchases < 3)) =
AND
> >
> > id NOT IN (
> >
> > SELECT lead_id
> > FROM purchase
> > WHERE member_id =3D 21101
> > ) AND affiliate_id NOT
> > IN (
> >
> > SELECT affiliate_locked_id
> > FROM affiliate_lockout
> > WHERE member_id =3D 21101
> > ) AND id NOT
> > IN (
> >
> > SELECT lead_id
> > FROM purchase
> > INNER JOIN member_exclusion
> > WHERE purchase.member_id =3D member_exclusion.member_id_to_exclud=
e AND
> > purchase.member_id =3D 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 y=
our
> > 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
>
--=-T9f2q3MVTj4Xm6sLXgQm-- --=-MQfU+HY2ZJi0VwARkFu1 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA/OmSf21dVnhLsBV0RAvzRAJ4xmfolyYtxkioQu++dbzSsNlGOSACfezVD sE2cMy3WE/yUpSdQX2JzcvA= =GXyo -----END PGP SIGNATURE----- --=-MQfU+HY2ZJi0VwARkFu1--