join query performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]




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.70 rows=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.968 rows=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.24 rows=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.018 rows=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.113 rows=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.27 rows=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

[Index of Archives]     [Postgresql General]     [Postgresql Admin]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Backpacking]     [Postgresql Jobs]

  Powered by Linux