Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 259AF2E0149 for ; Sat, 8 Mar 2008 00:07:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 55548-03 for ; Sat, 8 Mar 2008 00:07:04 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.188]) by postgresql.org (Postfix) with ESMTP id 846A32E0137 for ; Sat, 8 Mar 2008 00:07:02 -0400 (AST) Received: by rv-out-0910.google.com with SMTP id g13so526916rvb.43 for ; Fri, 07 Mar 2008 20:07:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=H3GD6MCfZtlzo2ohmEA2XYa1f1du74TO7vHoKMJQBI8=; b=bjAgeXdvfzFWa8zPK2OVju6H29nK4+aEGc5KfmJ9BxbDHiYrsVGeGaKfqh8QvmYOJjgvPkhQ+7EcdZvlK+iuBkTu/fTDtlfxqCr/2a+MM+nIutIsuNCFQGiQuh+WLaQOf12zPLOHaWOLWIsxP4GHzB1v3frSfe2q8OPjnvh6lYU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=bDGt/KbuqRqhuh+T7xowmyMepJO/mCwJjWVU0yLISHISNsvcDUGwII0irWmLwf9Wnaynx/YpdNQPw6bO3GPj2fCmV45ZXGb+lBZnFy9gHUmaAirOwIqrnw8pRLo0E6ueMYY5No1tcMlEbiEiwOg+fQ27Z0kj9LSELMiqlUeq79w= Received: by 10.141.171.6 with SMTP id y6mr1398385rvo.84.1204949221913; Fri, 07 Mar 2008 20:07:01 -0800 (PST) Received: by 10.114.208.18 with HTTP; Fri, 7 Mar 2008 20:07:01 -0800 (PST) Message-ID: Date: Sat, 8 Mar 2008 09:37:01 +0530 From: "petchimuthu lingam" To: pgsql-performance@postgresql.org Subject: join query performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9361_12240255.1204949221899" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/173 X-Sequence-Number: 29127 ------=_Part_9361_12240255.1204949221899 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_9361_12240255.1204949221899 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 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_campa= igns.campaign_name, test_log.test_stime, test_log.test_duration, test_log.t= est_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.s= ite_id, test_log.dtmf_values FROM user_details,group_details,site_details,t= est_log, sv_agent_map,test_campaigns,test_projects  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'  AND user_details.user_id=3Dsv= _agent_map.agent_user_id and group_details.group_id=3Dsv_agent_map.agent_gr= oup_id and site_details.site_id=3Dsv_agent_map.agent_site_id and test_log.a= gent_id=3Dsv_agent_map.agent_user_id and test_log.campaign_id=3Dtest_campai= gns.campaign_id and test_projects.project_id=3Dtest_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)


--
With Best = Regards,
Petchimuthulingam S ------=_Part_9361_12240255.1204949221899--