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 DDDD4D8036 for ; Thu, 13 Oct 2005 13:58:30 -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 53481-02 for ; Thu, 13 Oct 2005 16:58:19 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id 2B2C4D818D for ; Thu, 13 Oct 2005 13:58:18 -0300 (ADT) Received: (qmail 7560 invoked from network); 13 Oct 2005 16:58:17 -0000 Received: from pcp0012204803pcs.blairblvd.tn.nash.comcast.net (HELO ?192.168.15.103?) (69.245.49.69) by 0 with SMTP; 13 Oct 2005 16:58:17 -0000 In-Reply-To: <001d01c5cf34$2df7f100$0b00a8c0@forge> References: <001d01c5cf34$2df7f100$0b00a8c0@forge> Mime-Version: 1.0 (Apple Message framework v734) X-Priority: 3 Content-Type: multipart/alternative; boundary=Apple-Mail-1-591640399 Message-Id: <282DBF35-C713-4359-97D5-E8308A5DB226@sitening.com> Cc: From: "Thomas F. O'Connell" Subject: Re: Strange join...maybe some improvements??? Date: Thu, 13 Oct 2005 11:58:15 -0500 To: Andy X-Mailer: Apple Mail (2.734) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.061 required=5 tests=[AWL=-0.026, HTML_40_50=0.086, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200510/139 X-Sequence-Number: 22992 --Apple-Mail-1-591640399 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed 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 > ) > > 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. > > I have this query: > > SELECT count(o.id) FROM orders o > INNER JOIN modell m ON m.id=o.id_modell > WHERE o.id_ag IN (SELECT id_ag FROM contactpartner cp WHERE > id_user=15) OR m.id_hersteller IN (SELECT id_ag FROM contactpartner > cp WHERE id_user=15) > > Aggregate (cost=7828.60..7828.60 rows=1 width=4) (actual > time=1145.150..1145.151 rows=1 loops=1) > -> Hash Join (cost=1689.64..7706.32 rows=48913 width=4) (actual > time=153.059..1136.457 rows=9395 loops=1) > Hash Cond: ("outer".id_modell = "inner".id) > Join Filter: ((hashed subplan) OR (hashed subplan)) > -> Seq Scan on orders o (cost=0.00..3129.17 rows=65217 > width=12) (actual time=0.031..94.444 rows=65217 loops=1) > -> Hash (cost=1218.07..1218.07 rows=66607 width=8) > (actual time=151.211..151.211 rows=0 loops=1) > -> Seq Scan on modell m (cost=0.00..1218.07 > rows=66607 width=8) (actual time=0.044..87.154 rows=66607 loops=1) > SubPlan > -> Index Scan using contactpartner_id_user_idx on > contactpartner cp (cost=0.00..6.02 rows=2 width=4) (actual > time=0.010..0.018 rows=4 loops=1) > Index Cond: (id_user = 15) > -> Index Scan using contactpartner_id_user_idx on > contactpartner cp (cost=0.00..6.02 rows=2 width=4) (actual > time=0.092..0.116 rows=4 loops=1) > Index Cond: (id_user = 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=o.id_modell > INNER JOIN contactpartner cp ON cp.id_user=15 AND (o.id_ag=cp.id_ag > OR cp.id_ag=m.id_hersteller) > > Aggregate (cost=11658.63..11658.63 rows=1 width=4) (actual > time=1691.570..1691.570 rows=1 loops=1) > -> Nested Loop (cost=7752.40..11657.27 rows=542 width=4) > (actual time=213.945..1683.515 rows=9416 loops=1) > Join Filter: (("inner".id_ag = "outer".id_ag) OR > ("outer".id_ag = "inner".id_hersteller)) > -> Index Scan using contactpartner_id_user_idx on > contactpartner cp (cost=0.00..6.02 rows=2 width=4) (actual > time=0.108..0.188 rows=4 loops=1) > Index Cond: (id_user = 15) > -> Materialize (cost=7752.40..8723.57 rows=65217 > width=12) (actual time=37.586..352.620 rows=65217 loops=4) > -> Hash Join (cost=1677.59..7368.18 rows=65217 > width=12) (actual time=150.220..1153.872 rows=65217 loops=1) > Hash Cond: ("outer".id_modell = "inner".id) > -> Seq Scan on orders o (cost=0.00..3129.17 > rows=65217 width=12) (actual time=0.034..95.133 rows=65217 loops=1) > -> Hash (cost=1218.07..1218.07 rows=66607 > width=8) (actual time=149.961..149.961 rows=0 loops=1) > -> Seq Scan on modell m > (cost=0.00..1218.07 rows=66607 width=8) (actual time=0.032..86.378 > rows=66607 loops=1) > 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. > > > Is there any way to speed up this query??? > > Regards, > Andy. --Apple-Mail-1-591640399 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1 What indexes do you have on = these tables?

And = have you ANALYZEd all three recently?

http://www.sitening.com/
11= 0 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
(
=A0 id = int4 SERIAL,
=A0 id_ag int4,
=A0 id_modell int4 ->> this is = linked to the modell.id
)
=A0
CREATE TABLE modell
(
=A0 id int4 = SERIAL,
=A0 id_hersteller int4)
=A0
CREATE TABLE contactpartner
(
=A0 id int4 SERIAL,
=A0 = id_ag int4, ->> this is linked to order.id_ag or = modell.id_hersteller
id_user int4
=
=A0).
=A0
=
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.
=A0
I have = this query:
=A0
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)
=A0
Aggregate=A0 = (cost=3D7828.60..7828.60 rows=3D1 width=3D4) (actual = time=3D1145.150..1145.151 rows=3D1 loops=3D1)
=A0 ->=A0 Hash Join=A0= (cost=3D1689.64..7706.32 rows=3D48913 width=3D4) (actual = time=3D153.059..1136.457 rows=3D9395 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0 = Hash Cond: ("outer".id_modell =3D "inner".id)
=A0=A0=A0=A0=A0=A0=A0 = Join Filter: ((hashed subplan) OR (hashed subplan))
=A0=A0=A0=A0=A0=A0=A0= ->=A0 Seq Scan on orders o=A0 (cost=3D0.00..3129.17 rows=3D65217 = width=3D12) (actual time=3D0.031..94.444 rows=3D65217 = loops=3D1)
=A0=A0=A0=A0=A0=A0=A0 ->=A0 Hash=A0 = (cost=3D1218.07..1218.07 rows=3D66607 width=3D8) (actual = time=3D151.211..151.211 rows=3D0 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 ->=A0 Seq Scan on modell m=A0 (cost=3D0.00..1218.07 = rows=3D66607 width=3D8) (actual time=3D0.044..87.154 rows=3D66607 = loops=3D1)
=A0=A0=A0=A0=A0=A0=A0 SubPlan
=A0=A0=A0=A0=A0=A0=A0=A0=A0= ->=A0 Index Scan using contactpartner_id_user_idx on contactpartner = cp=A0 (cost=3D0.00..6.02 rows=3D2 width=3D4) (actual time=3D0.010..0.018 = rows=3D4 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = Index Cond: (id_user =3D 15)
=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 = Index Scan using contactpartner_id_user_idx on contactpartner cp=A0 = (cost=3D0.00..6.02 rows=3D2 width=3D4) (actual time=3D0.092..0.116 = rows=3D4 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = Index Cond: (id_user =3D 15)
Total runtime: 1145.689 ms
=
=A0
I tried also this one:
=A0
=
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)
=
=A0
Aggregate=A0 (cost=3D11658.63..11658.63 rows=3D1 = width=3D4) (actual time=3D1691.570..1691.570 rows=3D1 loops=3D1)
=A0 = ->=A0 Nested Loop=A0 (cost=3D7752.40..11657.27 rows=3D542 width=3D4) = (actual time=3D213.945..1683.515 rows=3D9416 loops=3D1)
=A0=A0=A0=A0=A0= =A0=A0 Join Filter: (("inner".id_ag =3D "outer".id_ag) OR ("outer".id_ag = =3D "inner".id_hersteller))
=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan = using contactpartner_id_user_idx on contactpartner cp=A0 = (cost=3D0.00..6.02 rows=3D2 width=3D4) (actual time=3D0.108..0.188 = rows=3D4 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index = Cond: (id_user =3D 15)
=A0=A0=A0=A0=A0=A0=A0 ->=A0 Materialize=A0 = (cost=3D7752.40..8723.57 rows=3D65217 width=3D12) (actual = time=3D37.586..352.620 rows=3D65217 loops=3D4)
=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 ->=A0 Hash Join=A0 (cost=3D1677.59..7368.18 = rows=3D65217 width=3D12) (actual time=3D150.220..1153.872 rows=3D65217 = loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = Hash Cond: ("outer".id_modell =3D "inner".id)
=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq Scan on orders o=A0 = (cost=3D0.00..3129.17 rows=3D65217 width=3D12) (actual = time=3D0.034..95.133 rows=3D65217 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Hash=A0 (cost=3D1218.07..1218.07 = rows=3D66607 width=3D8) (actual time=3D149.961..149.961 rows=3D0 = loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 ->=A0 Seq Scan on modell m=A0 (cost=3D0.00..1218.07 = rows=3D66607 width=3D8) (actual time=3D0.032..86.378 rows=3D66607 = loops=3D1)
Total runtime: 1696.253 ms
=A0
=
>>>>>>>> but this brings me some double = information(the same o.id)=A0in the situation in which the o.id_ag and = m.id_hersteller are different, but still both in the AG_LIST.
=
=A0
=A0
Is there any way to speed up this = query???
=A0
Regards,
=
Andy.

= --Apple-Mail-1-591640399--