pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange join...maybe some improvements???
3+ messages / 2 participants
[nested] [flat]
* Strange join...maybe some improvements???
@ 2005-10-12 13:52 Andy <frum@ar-sd.net>
2005-10-13 16:58 ` Re: Strange join...maybe some improvements??? Thomas F. O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 3+ messages in thread
From: Andy @ 2005-10-12 13:52 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Strange join...maybe some improvements???
2005-10-12 13:52 Strange join...maybe some improvements??? Andy <frum@ar-sd.net>
@ 2005-10-13 16:58 ` Thomas F. O'Connell <tfo@sitening.com>
2005-10-13 18:28 ` Re: Strange join...maybe some improvements??? Andy <frum@ar-sd.net>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas F. O'Connell @ 2005-10-13 16:58 UTC (permalink / raw)
To: Andy <frum@ar-sd.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Strange join...maybe some improvements???
2005-10-12 13:52 Strange join...maybe some improvements??? Andy <frum@ar-sd.net>
2005-10-13 16:58 ` Re: Strange join...maybe some improvements??? Thomas F. O'Connell <tfo@sitening.com>
@ 2005-10-13 18:28 ` Andy <frum@ar-sd.net>
0 siblings, 0 replies; 3+ messages in thread
From: Andy @ 2005-10-13 18:28 UTC (permalink / raw)
To: Thomas F. O'Connell <tfo@sitening.com>; +Cc: pgsql-sql
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 -----
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-13 18:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-12 13:52 Strange join...maybe some improvements??? Andy <frum@ar-sd.net>
2005-10-13 16:58 ` Thomas F. O'Connell <tfo@sitening.com>
2005-10-13 18:28 ` Andy <frum@ar-sd.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox