join query performance
am 08.03.2008 06:01:53 von petchimuthu lingam------=_Part_9480_25851418.1204952513310
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
In a select query i have used the join conditions, will it affect query
performance.
Explicitly I didn't used the join command, Will it make any difference.
My Query is:
SELECT test_log.test_id, test_log.test_id, test_log.test_id,
user_details.first_name, group_details.group_name, site_details.site_name,
test_projects.project_name, test_campaigns.campaign_name,
test_log.test_stime, test_log.test_duration, test_log.test_etime,
test_log.dialed_no, test_log.tester_id, test_log.voice_recorded,
test_log.screen_recorded, test_log.agent_id, test_log.group_id,
test_log.site_id, test_log.dtmf_values FROM
user_details,group_details,site_details,test_log,
sv_agent_map,test_campaigns,test_projects WHERE
sv_agent_map.sv_user_id='347' AND sv_agent_map.sv_group_id='13' AND
sv_agent_map.sv_site_id='10' AND
user_details.user_id=sv_agent_map.agent_user_id and
group_details.group_id=sv_agent_map.agent_group_id and
site_details.site_id=sv_agent_map.agent_site_id and
test_log.agent_id=sv_agent_map.agent_user_id and
test_log.campaign_id=test_campaigns.campaign_id and
test_projects.project_id=test_log.project_id ORDER BY test_log.test_id limit
5000.
The test_log has 50 million records.
The postgres version is 7.4
The Explain Analysis Output is:
QUERY PLAN
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------
Limit (cost=206342.52..206345.46 rows=1178 width=420) (actual time=
42514.526..42525.443 rows=5000 loops=1)
-> Sort (cost=206342.52..206345.46 rows=1178 width=420) (actual time=
42514.517..42519.466 rows=5000 loops=1)
Sort Key: test_log.test_id
-> Hash Join (cost=10.22..206282.43 rows=1178 width=420) (actual
time=1.297..37852.353 rows=281603 loops=1)
Hash Cond: ("outer".agent_id = "inner".user_id)
-> Hash Join (cost=7.11..206256.15 rows=2278 width=361)
(actual time=0.923..34630.591 rows=281603 loops=1)
Hash Cond: ("outer".campaign_id = "inner".campaign_id)
-> Hash Join (cost=5.77..206209.22 rows=2281
width=272) (actual time=0.789..31832.361 rows=281603 loops=1)
Hash Cond: ("outer".agent_group_id =
"inner".group_id)
-> Hash Join (cost=4.51..206153.11 rows=6407
width=228) (actual time=0.656..28964.197 rows=281603 loops=1)
Hash Cond: ("outer".project_id =
"inner".project_id)
-> Hash Join
(cost=3.24..206055.70rows=6415 width=139) (actual time=
0.461..26168.581 rows=281603 loops=1)
Hash Cond: ("outer".agent_id =
"inner".agent_user_id)
-> Seq Scan on test_log (cost=
0.00..180692.90 rows=5013690 width=83) (actual
time=0.005..18942.968rows=5061643 loops=1)
-> Hash (cost=3.24..3.24 rows=1
width=56) (actual time=0.362..0.362 rows=0 loops=1)
-> Hash Join
(cost=2.04..3.24rows=1 width=56) (actual time=
0.256..0.325 rows=31 loops=1)
Hash Cond:
("outer".site_id = "inner".agent_site_id)
-> Seq Scan on
site_details (cost=0.00..1.13 rows=13 width=52) (actual
time=0.005..0.018rows=13 loops=1)
-> Hash (cost=
2.03..2.03 rows=1 width=12) (actual time=0.156..0.156 rows=0 loops=1)
-> Seq Scan on
sv_agent_map (cost=0.00..2.03 rows=1 width=12) (actual
time=0.031..0.113rows=31 loops=1)
Filter:
((sv_user_id = 347) AND (sv_group_id = 13) AND (sv_site_id = 10))
-> Hash (cost=1.21..1.21 rows=21
width=97) (actual time=0.145..0.145 rows=0 loops=1)
-> Seq Scan on test_projects (cost=
0.00..1.21 rows=21 width=97) (actual time=0.010..0.042 rows=21 loops=1)
-> Hash (cost=1.21..1.21 rows=21 width=52)
(actual time=0.077..0.077 rows=0 loops=1)
-> Seq Scan on group_details (cost=
0.00..1.21 rows=21 width=52) (actual time=0.010..0.039 rows=21 loops=1)
-> Hash (cost=1.27..1.27 rows=27 width=97) (actual
time=0.084..0.084 rows=0 loops=1)
-> Seq Scan on test_campaigns
(cost=0.00..1.27rows=27 width=97) (actual time=
0.011..0.043 rows=27 loops=1)
-> Hash (cost=2.89..2.89 rows=89 width=67) (actual time=
0.245..0.245 rows=0 loops=1)
-> Seq Scan on user_details (cost=0.00..2.89 rows=89
width=67) (actual time=0.019..0.154 rows=89 loops=1)
Total runtime: 42548.932 ms
(30 rows)
--
With Best Regards,
Petchimuthulingam S
------=_Part_9480_25851418.1204952513310
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
In a select query i have used the join condi=
tions, will it affect query performance.
Explicitly I didn't use=
d the join command, Will it make any difference.
My Query is:
SEL=
ECT test_log.test_id, test_log.test_id, test_log.test_id, user_detail=
s.first_name, group_details.group_name, site_details.site_name, test_projec=
ts.project_name, test_campaigns.campaign_name, test_log.test_stime, test_lo=
g.test_duration, test_log.test_etime, test_log.dialed_no, test_log.tester_i=
d, test_log.voice_recorded, test_log.screen_recorded, test_log.agent_id, te=
st_log.group_id, test_log.site_id, test_log.dtmf_values FROM user_details,g=
roup_details,site_details,test_log, sv_agent_map,test_campaigns,test_projec=
ts WHERE sv_agent_map.sv_user_id=3D'347' AND sv_agent_map.sv_=
group_id=3D'13' AND sv_agent_map.sv_site_id=3D'10' AN=
D user_details.user_id=3Dsv_agent_map.agent_user_id and group_details.group=
_id=3Dsv_agent_map.agent_group_id and site_details.site_id=3Dsv_agent_map.a=
gent_site_id and test_log.agent_id=3Dsv_agent_map.agent_user_id and test_lo=
g.campaign_id=3Dtest_campaigns.campaign_id and test_projects.project_id=3Dt=
est_log.project_id ORDER BY test_log.test_id limit 5000.
The test_log has 50 million records.
The postgres version is 7.4=
The Explain Analysis Output is:
&n=
bsp; =
; &n=
bsp; =
; &n=
bsp; =
; QUERY PLAN
-----------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
---
Limit (cost=3D206342.52..206345.46 rows=3D1178 width=3D420) (ac=
tual time=3D42514.526..42525.443 rows=3D5000 loops=3D1)
-&g=
t; Sort (cost=3D206342.52..206345.46 rows=3D1178 width=3D420) (=
actual time=3D42514.517..42519.466 rows=3D5000 loops=3D1)
Sort Key: test_log.test_id=
-> Hash Join&=
nbsp; (cost=3D10.22..206282.43 rows=3D1178 width=3D420) (actual time=3D1.29=
7..37852.353 rows=3D281603 loops=3D1)
&nbs=
p; Hash Cond: ("outer&=
quot;.agent_id =3D "inner".user_id)
&nb=
sp; -> Hash Join (cost=3D7.11..206256.15 rows=3D2278 w=
idth=3D361) (actual time=3D0.923..34630.591 rows=3D281603 loops=3D1)
&nb=
sp; =
Hash Cond: ("outer".ca=
mpaign_id =3D "inner".campaign_id)
&nb=
sp; -> Hash Join (=
cost=3D5.77..206209.22 rows=3D2281 width=3D272) (actual time=3D0.789..31832=
..361 rows=3D281603 loops=3D1)
=
&nb=
sp; Hash Cond: ("outer".agent=
_group_id =3D "inner".group_id)
&nb=
sp; =
-> Hash Join (cost=3D4.51..206153.11 rows=3D6407 widt=
h=3D228) (actual time=3D0.656..28964.197 rows=3D281603 loops=3D1)
=
&nb=
sp; =
Hash Cond: ("outer".project_=
id =3D "inner".project_id)
&nb=
sp; =
-> Hash Join (cos=
t=3D3.24..206055.70 rows=3D6415 width=3D139) (actual time=3D0.461..26168.58=
1 rows=3D281603 loops=3D1)
&nb=
sp; =
&nb=
sp; Hash Cond: ("outer".agent_id =
=3D "inner".agent_user_id)
&nb=
sp; =
&nb=
sp; -> Seq Scan on test_log (cost=3D0.00..180692.90 rows=3D5=
013690 width=3D83) (actual time=3D0.005..18942.968 rows=3D5061643 loops=3D1=
)
&nbs=
p; &=
nbsp; &nbs=
p; -> Hash (cost=3D3.24..3.24 rows=3D1 width=3D56) (ac=
tual time=3D0.362..0.362 rows=3D0 loops=3D1)
&nb=
sp; =
&nb=
sp; -> Hash Join (cost=
=3D2.04..3.24 rows=3D1 width=3D56) (actual time=3D0.256..0.325 rows=3D31 lo=
ops=3D1)
&nb=
sp; =
&nb=
sp; =
Hash Cond: ("outer".site_id =3D "inner".ag=
ent_site_id)
&nb=
sp; =
&nb=
sp; =
-> Seq Scan on site_details (cost=3D0.00..1.13 rows=3D13 wi=
dth=3D52) (actual time=3D0.005..0.018 rows=3D13 loops=3D1)
&=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; -> =
Hash (cost=3D2.03..2.03 rows=3D1 width=3D12) (actual time=3D0.156..0.=
156 rows=3D0 loops=3D1)
&nb=
sp; =
&nb=
sp; =
-> Seq Scan on sv_agent_map&n=
bsp; (cost=3D0.00..2.03 rows=3D1 width=3D12) (actual time=3D0.031..0.113 ro=
ws=3D31 loops=3D1)
=
; &n=
bsp; =
; &n=
bsp; =
; Filter: ((sv_user_id =3D 347) AND (sv_group_id =3D 13) =
AND (sv_site_id =3D 10))
&nb=
sp; =
-> Hash (cost=3D1=
..21..1.21 rows=3D21 width=3D97) (actual time=3D0.145..0.145 rows=3D0 loops=
=3D1)
=
&nb=
sp; =
-> Seq Scan on test_projects (cost=3D0.00..1.21=
rows=3D21 width=3D97) (actual time=3D0.010..0.042 rows=3D21 loops=3D1)
&nb=
sp; =
-> Hash (cost=3D1.21..1.21 rows=3D21 width=3D52) (act=
ual time=3D0.077..0.077 rows=3D0 loops=3D1)
&nbs=
p; &=
nbsp; &nbs=
p; -> Seq Scan on group_details (cost=3D0.00..1.=
21 rows=3D21 width=3D52) (actual time=3D0.010..0.039 rows=3D21 loops=3D1)
&nb=
sp; -> Hash (cost=
=3D1.27..1.27 rows=3D27 width=3D97) (actual time=3D0.084..0.084 rows=3D0 lo=
ops=3D1)
&nb=
sp; =
-> Seq Scan on test_campaigns (cost=3D0.0=
0..1.27 rows=3D27 width=3D97) (actual time=3D0.011..0.043 rows=3D27 loops=
=3D1)
&nb=
sp; -> Hash (cost=3D2.89..2.89 rows=3D89 width=3D67) (=
actual time=3D0.245..0.245 rows=3D0 loops=3D1)
&=
nbsp; &nbs=
p; -> Seq Scan on user_details (cost=3D0.0=
0..2.89 rows=3D89 width=3D67) (actual time=3D0.019..0.154 rows=3D89 loops=
=3D1)
Total runtime: 42548.932 ms
(30 rows)
l">
--
With Best Regards,
Petchimuthulingam S
------=_Part_9480_25851418.1204952513310--