X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7968FD7F63 for ; Thu, 13 Oct 2005 15:29:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 73282-08 for ; Thu, 13 Oct 2005 18:29:09 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id B45EED84AC for ; Thu, 13 Oct 2005 15:29:07 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 4C9F321E04; Thu, 13 Oct 2005 21:12:46 +0300 (EEST) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30608-07; Thu, 13 Oct 2005 21:12:45 +0300 (EEST) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 13DD221DE9; Thu, 13 Oct 2005 21:12:45 +0300 (EEST) Message-ID: <00a401c5d023$fac45210$0b00a8c0@forge> From: "Andy" To: "Thomas F. O'Connell" Cc: References: <001d01c5cf34$2df7f100$0b00a8c0@forge> <282DBF35-C713-4359-97D5-E8308A5DB226@sitening.com> Subject: Re: Strange join...maybe some improvements??? Date: Thu, 13 Oct 2005 21:28:57 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00A1_01C5D03D.1E53AD70" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.235 required=5 tests=[AWL=0.089, FORGED_RCVD_HELO=0.05, HTML_50_60=0.095, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200510/143 X-Sequence-Number: 22996 This is a multi-part message in MIME format. ------=_NextPart_000_00A1_01C5D03D.1E53AD70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Indexes are on all join fields. In the shown example on all fields I = have indexes.=20 Yes I vacuum the database regulary.=20 Andy. ----- Original Message -----=20 From: Thomas F. O'Connell=20 To: Andy=20 Cc: pgsql-sql@postgresql.org=20 Sent: Thursday, October 13, 2005 7:58 PM Subject: Re: [SQL] Strange join...maybe some improvements??? What indexes do you have on these tables? And have you ANALYZEd all three recently? -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Open Source Solutions. Optimized Web Development. http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Oct 12, 2005, at 8:52 AM, Andy wrote: I have 3 tables: CREATE TABLE orders ( id int4 SERIAL, id_ag int4, id_modell int4 ->> this is linked to the modell.id )=20 CREATE TABLE modell ( id int4 SERIAL, id_hersteller int4) CREATE TABLE contactpartner ( id int4 SERIAL, id_ag int4, ->> this is linked to order.id_ag or = modell.id_hersteller id_user int4 ). I get a list of id_ag from the contactpartner which belongs to a = user(AG_LIST). Then I have to selectselect/count all the data's from the = order table that have the order.id_ag in the AG LIST or which have the = modell.id_hersteller in the AG_LIST.=20 I have this query: SELECT count(o.id) FROM orders o INNER JOIN modell m ON m.id=3Do.id_modell WHERE o.id_ag IN (SELECT id_ag FROM contactpartner cp WHERE = id_user=3D15) OR m.id_hersteller IN (SELECT id_ag FROM contactpartner cp = WHERE id_user=3D15) Aggregate (cost=3D7828.60..7828.60 rows=3D1 width=3D4) (actual = time=3D1145.150..1145.151 rows=3D1 loops=3D1) -> Hash Join (cost=3D1689.64..7706.32 rows=3D48913 width=3D4) = (actual time=3D153.059..1136.457 rows=3D9395 loops=3D1) Hash Cond: ("outer".id_modell =3D "inner".id) Join Filter: ((hashed subplan) OR (hashed subplan)) -> Seq Scan on orders o (cost=3D0.00..3129.17 rows=3D65217 = width=3D12) (actual time=3D0.031..94.444 rows=3D65217 loops=3D1) -> Hash (cost=3D1218.07..1218.07 rows=3D66607 width=3D8) = (actual time=3D151.211..151.211 rows=3D0 loops=3D1) -> Seq Scan on modell m (cost=3D0.00..1218.07 = rows=3D66607 width=3D8) (actual time=3D0.044..87.154 rows=3D66607 = loops=3D1) SubPlan -> Index Scan using contactpartner_id_user_idx on = contactpartner cp (cost=3D0.00..6.02 rows=3D2 width=3D4) (actual = time=3D0.010..0.018 rows=3D4 loops=3D1) Index Cond: (id_user =3D 15) -> Index Scan using contactpartner_id_user_idx on = contactpartner cp (cost=3D0.00..6.02 rows=3D2 width=3D4) (actual = time=3D0.092..0.116 rows=3D4 loops=3D1) Index Cond: (id_user =3D 15) Total runtime: 1145.689 ms I tried also this one: SELECT count(o.id) FROM orders o INNER JOIN modell m ON m.id=3Do.id_modell INNER JOIN contactpartner cp ON cp.id_user=3D15 AND = (o.id_ag=3Dcp.id_ag OR cp.id_ag=3Dm.id_hersteller) Aggregate (cost=3D11658.63..11658.63 rows=3D1 width=3D4) (actual = time=3D1691.570..1691.570 rows=3D1 loops=3D1) -> Nested Loop (cost=3D7752.40..11657.27 rows=3D542 width=3D4) = (actual time=3D213.945..1683.515 rows=3D9416 loops=3D1) Join Filter: (("inner".id_ag =3D "outer".id_ag) OR = ("outer".id_ag =3D "inner".id_hersteller)) -> Index Scan using contactpartner_id_user_idx on = contactpartner cp (cost=3D0.00..6.02 rows=3D2 width=3D4) (actual = time=3D0.108..0.188 rows=3D4 loops=3D1) Index Cond: (id_user =3D 15) -> Materialize (cost=3D7752.40..8723.57 rows=3D65217 = width=3D12) (actual time=3D37.586..352.620 rows=3D65217 loops=3D4) -> Hash Join (cost=3D1677.59..7368.18 rows=3D65217 = width=3D12) (actual time=3D150.220..1153.872 rows=3D65217 loops=3D1) Hash Cond: ("outer".id_modell =3D "inner".id) -> Seq Scan on orders o (cost=3D0.00..3129.17 = rows=3D65217 width=3D12) (actual time=3D0.034..95.133 rows=3D65217 = loops=3D1) -> Hash (cost=3D1218.07..1218.07 rows=3D66607 = width=3D8) (actual time=3D149.961..149.961 rows=3D0 loops=3D1) -> Seq Scan on modell m = (cost=3D0.00..1218.07 rows=3D66607 width=3D8) (actual = time=3D0.032..86.378 rows=3D66607 loops=3D1) Total runtime: 1696.253 ms >>>>>>>> but this brings me some double information(the same o.id) = in the situation in which the o.id_ag and m.id_hersteller are different, = but still both in the AG_LIST.=20 Is there any way to speed up this query??? Regards,=20 Andy. ------=_NextPart_000_00A1_01C5D03D.1E53AD70 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Indexes are on all join fields. In the = shown=20 example on all fields I have indexes.
 
Yes I vacuum the database regulary. =
 
Andy.
 
----- Original Message -----
From:=20 Thomas F. = O'Connell
To: Andy
Sent: Thursday, October 13, = 2005 7:58=20 PM
Subject: Re: [SQL] Strange = join...maybe=20 some improvements???

What indexes do you have on these tables?

And have you ANALYZEd all three recently?

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Open Source Solutions. Optimized Web Development.

110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Oct 12, 2005, at 8:52 AM, Andy wrote:
I have 3 tables:
CREATE TABLE orders
(
  = id int4=20 SERIAL,
  id_ag int4,
  id_modell int4 ->> = this is=20 linked to the modell.id
)
 
CREATE = TABLE=20 modell
(
  id int4 SERIAL,
  id_hersteller=20 int4)
 
CREATE TABLE = contactpartner
(
  id=20 int4 SERIAL,
  id_ag int4, ->> this is linked to = order.id_ag=20 or modell.id_hersteller
id_user int4
 ).
 
I get a list of id_ag from the contactpartner which belongs to = a=20 user(AG_LIST). Then I have to selectselect/count all the data's from = the=20 order table that have the order.id_ag in the AG LIST or which have = the=20 modell.id_hersteller in the AG_LIST.
 
I have this query:
 
SELECT count(o.id) FROM orders o
INNER JOIN modell m ON=20 m.id=3Do.id_modell
WHERE o.id_ag IN (SELECT id_ag FROM = contactpartner cp=20 WHERE id_user=3D15) OR m.id_hersteller IN (SELECT id_ag FROM = contactpartner cp=20 WHERE id_user=3D15)
 
Aggregate  (cost=3D7828.60..7828.60 rows=3D1 width=3D4) = (actual=20 time=3D1145.150..1145.151 rows=3D1 loops=3D1)
  ->  = Hash=20 Join  (cost=3D1689.64..7706.32 rows=3D48913 width=3D4) (actual=20 time=3D153.059..1136.457 rows=3D9395=20 loops=3D1)
        Hash Cond:=20 ("outer".id_modell =3D=20 "inner".id)
        Join = Filter:=20 ((hashed subplan) OR (hashed=20 subplan))
        ->  = Seq Scan=20 on orders o  (cost=3D0.00..3129.17 rows=3D65217 width=3D12) = (actual=20 time=3D0.031..94.444 rows=3D65217=20 loops=3D1)
        ->  = Hash  (cost=3D1218.07..1218.07 rows=3D66607 width=3D8) (actual=20 time=3D151.211..151.211 rows=3D0=20 = loops=3D1)
          = ;   =20 ->  Seq Scan on modell m  (cost=3D0.00..1218.07 = rows=3D66607=20 width=3D8) (actual time=3D0.044..87.154 rows=3D66607=20 loops=3D1)
       =20 SubPlan
         =20 ->  Index Scan using contactpartner_id_user_idx on = contactpartner=20 cp  (cost=3D0.00..6.02 rows=3D2 width=3D4) (actual = time=3D0.010..0.018 rows=3D4=20 = loops=3D1)
          = ;     =20 Index Cond: (id_user =3D=20 15)
          = -> =20 Index Scan using contactpartner_id_user_idx on contactpartner = cp =20 (cost=3D0.00..6.02 rows=3D2 width=3D4) (actual time=3D0.092..0.116 = rows=3D4=20 = loops=3D1)
          = ;     =20 Index Cond: (id_user =3D 15)
Total runtime: 1145.689 ms
 
I tried also this one:
 
SELECT count(o.id) FROM orders o
INNER JOIN modell m ON=20 m.id=3Do.id_modell
INNER JOIN contactpartner cp ON = cp.id_user=3D15 AND=20 (o.id_ag=3Dcp.id_ag OR cp.id_ag=3Dm.id_hersteller)
 
Aggregate  (cost=3D11658.63..11658.63 rows=3D1 width=3D4) = (actual=20 time=3D1691.570..1691.570 rows=3D1 loops=3D1)
  ->  = Nested=20 Loop  (cost=3D7752.40..11657.27 rows=3D542 width=3D4) (actual=20 time=3D213.945..1683.515 rows=3D9416=20 loops=3D1)
        Join = Filter:=20 (("inner".id_ag =3D "outer".id_ag) OR ("outer".id_ag =3D=20 = "inner".id_hersteller))
       =20 ->  Index Scan using contactpartner_id_user_idx on = contactpartner=20 cp  (cost=3D0.00..6.02 rows=3D2 width=3D4) (actual = time=3D0.108..0.188 rows=3D4=20 = loops=3D1)
          = ;   =20 Index Cond: (id_user =3D = 15)
       =20 ->  Materialize  (cost=3D7752.40..8723.57 rows=3D65217 = width=3D12)=20 (actual time=3D37.586..352.620 rows=3D65217=20 = loops=3D4)
          = ;   =20 ->  Hash Join  (cost=3D1677.59..7368.18 rows=3D65217 = width=3D12)=20 (actual time=3D150.220..1153.872 rows=3D65217=20 = loops=3D1)
          = ;         =20 Hash Cond: ("outer".id_modell =3D=20 = "inner".id)
         &nbs= p;         =20 ->  Seq Scan on orders o  (cost=3D0.00..3129.17 = rows=3D65217=20 width=3D12) (actual time=3D0.034..95.133 rows=3D65217=20 = loops=3D1)
          = ;         =20 ->  Hash  (cost=3D1218.07..1218.07 rows=3D66607 = width=3D8) (actual=20 time=3D149.961..149.961 rows=3D0=20 = loops=3D1)
          = ;            =    =20 ->  Seq Scan on modell m  (cost=3D0.00..1218.07 = rows=3D66607=20 width=3D8) (actual time=3D0.032..86.378 rows=3D66607 = loops=3D1)
Total runtime:=20 1696.253 ms
 
>>>>>>>> but this brings me some double = information(the same o.id) in the situation in which the = o.id_ag and=20 m.id_hersteller are different, but still both in the AG_LIST.
 
 
Is there any way to speed up this query???
 
Regards,
=
Andy.

------=_NextPart_000_00A1_01C5D03D.1E53AD70--