Indexes are on all join fields. In the shown
example on all fields I have indexes.
Yes I vacuum the database regulary.
Andy.
----- Original Message -----
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.
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.
|