Thread: Strange join...maybe some improvements???

From: Thomas F. O'Connell

To: Andy

Cc: pgsql-sql@postgresql.org

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

)

 

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.