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 6B9E5D71E9 for ; Wed, 12 Oct 2005 10:52:32 -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 10296-09 for ; Wed, 12 Oct 2005 13:52:25 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id E108CD7D63 for ; Wed, 12 Oct 2005 10:52:26 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 65BE021D29 for ; Wed, 12 Oct 2005 16:36:16 +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 19903-07 for ; Wed, 12 Oct 2005 16:36:15 +0300 (EEST) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 1285821D27 for ; Wed, 12 Oct 2005 16:36:15 +0300 (EEST) Message-ID: <001d01c5cf34$2df7f100$0b00a8c0@forge> From: "Andy" To: Subject: Strange join...maybe some improvements??? Date: Wed, 12 Oct 2005 16:52:23 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001A_01C5CF4D.516AC3B0" 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.236 required=5 tests=[AWL=0.099, FORGED_RCVD_HELO=0.05, HTML_40_50=0.086, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200510/104 X-Sequence-Number: 22957 This is a multi-part message in MIME format. ------=_NextPart_000_001A_01C5CF4D.516AC3B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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_001A_01C5CF4D.516AC3B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I have 3 tables:
CREATE TABLE orders
(
  id = int4=20 SERIAL,
  id_ag int4,
  id_modell int4 ->> this is = linked=20 to the modell.id
)
 
CREATE = TABLE=20 modell
(
  id int4 SERIAL,
  id_hersteller = int4)
 
CREATE TABLE = contactpartner
(
  id int4=20 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=20 user(AG_LIST). Then I have to selectselect/count all the data's from the = order=20 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 WHERE=20 id_user=3D15) OR m.id_hersteller IN (SELECT id_ag FROM contactpartner cp = WHERE=20 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 = Join =20 (cost=3D1689.64..7706.32 rows=3D48913 width=3D4) (actual = time=3D153.059..1136.457=20 rows=3D9395 loops=3D1)
        = Hash Cond:=20 ("outer".id_modell =3D = "inner".id)
       =20 Join Filter: ((hashed subplan) OR (hashed=20 subplan))
        ->  Seq = Scan on=20 orders o  (cost=3D0.00..3129.17 rows=3D65217 width=3D12) (actual=20 time=3D0.031..94.444 rows=3D65217=20 loops=3D1)
        ->  = Hash =20 (cost=3D1218.07..1218.07 rows=3D66607 width=3D8) (actual = time=3D151.211..151.211 rows=3D0=20 loops=3D1)
          = ;   =20 ->  Seq Scan on modell m  (cost=3D0.00..1218.07 = rows=3D66607 width=3D8)=20 (actual time=3D0.044..87.154 rows=3D66607=20 loops=3D1)
       =20 SubPlan
          = -> =20 Index Scan using contactpartner_id_user_idx on contactpartner cp =20 (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)
          = ->  Index=20 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 Loop =20 (cost=3D7752.40..11657.27 rows=3D542 width=3D4) (actual = time=3D213.945..1683.515=20 rows=3D9416 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) (actual=20 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 width=3D12)=20 (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 width=3D8)=20 (actual time=3D0.032..86.378 rows=3D66607 loops=3D1)
Total runtime: = 1696.253=20 ms
 
>>>>>>>> but this brings me some double=20 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_001A_01C5CF4D.516AC3B0--