Nov 082014
 

I noticed the server load was high. I saw that several instances of search by date where still running.

Looking at the query in question:

freshports.org=# explain
freshports.org-#         SELECT count(DISTINCT CL.id) AS count                                                                                
freshports.org-#            FROM commit_log_ports CLP, commit_log CL JOIN commit_log_branches CLB ON CL.id = CLB.commit_log_id                 
freshports.org-#                                                     JOIN system_branch SB ON SB.branch_name = 'head' AND SB.id = CLB.branch_id
freshports.org-#           WHERE CL.commit_date BETWEEN '2014/11/07'::timestamptz  + SystemTimeAdjust()                                        
freshports.org-#                                    AND '2014/11/07'::timestamptz  + SystemTimeAdjust() + '1 Day'                              
freshports.org-#          AND CLP.commit_log_id = CL.id;
                                                                                                                      QUERY PLAN                                                                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=684.40..684.41 rows=1 width=4)
   ->  Merge Join  (cost=43.20..684.40 rows=1 width=4)
         Merge Cond: (clb.commit_log_id = cl.id)
         ->  Nested Loop  (cost=2.55..3125771.74 rows=4875 width=8)
               Join Filter: (clb.commit_log_id = clp.commit_log_id)
               ->  Index Scan using commit_log_ports_pkey on commit_log_ports clp  (cost=0.00..544853.97 rows=630110 width=4)
               ->  Materialize  (cost=2.55..618.00 rows=273 width=4)
                     ->  Hash Join  (cost=2.55..616.64 rows=273 width=4)
                           Hash Cond: (clb.branch_id = sb.id)
                           ->  Seq Scan on commit_log_branches clb  (cost=0.00..485.26 rows=33626 width=8)
                           ->  Hash  (cost=2.54..2.54 rows=1 width=4)
                                 ->  Seq Scan on system_branch sb  (cost=0.00..2.54 rows=1 width=4)
                                       Filter: (branch_name = 'head'::text)
         ->  Sort  (cost=40.61..40.63 rows=9 width=4)
               Sort Key: cl.id
               ->  Index Scan using commit_log_commit_date on commit_log cl  (cost=0.01..40.47 rows=9 width=4)
                     Index Cond: ((commit_date >= ('2014-11-07 00:00:00+00'::timestamp with time zone + '00:00:00'::interval)) AND (commit_date <= (('2014-11-07 00:00:00+00'::timestamp with time zone + '00:00:00'::interval) + '1 day'::interval)))
(17 rows)

freshports.org=# 

The analysis for this query is available at http://explain.depesz.com/s/uCT

I redesigned the query to make use of explicit JOINs:

freshports.org=# explain
freshports.org-# SELECT count(DISTINCT CL.id) AS count
freshports.org-#   FROM commit_log CL JOIN commit_log_ports    CLP ON CLP.commit_log_id = CL.id
freshports.org-#                                                  AND CL.commit_date BETWEEN '2014/11/07'::timestamptz  + SystemTimeAdjust()
freshports.org-#                                                                         AND '2014/11/07'::timestamptz  + SystemTimeAdjust() + '1 Day'
freshports.org-#                      JOIN commit_log_branches CLB ON CL.id             = CLB.commit_log_id
freshports.org-#                      JOIN system_branch       SB  ON SB.branch_name    = 'head'
freshports.org-#                                                  AND SB.id             = CLB.branch_id;
                                                                                                                            QUERY PLAN                                                                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=692.70..692.71 rows=1 width=4)
   ->  Nested Loop  (cost=40.58..692.70 rows=1 width=4)
         ->  Nested Loop  (cost=40.58..652.23 rows=1 width=8)
               ->  Hash Join  (cost=40.58..651.95 rows=1 width=12)
                     Hash Cond: (clb.commit_log_id = cl.id)
                     ->  Seq Scan on commit_log_branches clb  (cost=0.00..485.26 rows=33626 width=8)
                     ->  Hash  (cost=40.47..40.47 rows=9 width=4)
                           ->  Index Scan using commit_log_commit_date on commit_log cl  (cost=0.01..40.47 rows=9 width=4)
                                 Index Cond: ((commit_date >= ('2014-11-07 00:00:00+00'::timestamp with time zone + '00:00:00'::interval)) AND (commit_date <= (('2014-11-07 00:00:00+00'::timestamp with time zone + '00:00:00'::interval) + '1 day'::interval)))
               ->  Index Scan using system_branch_pkey on system_branch sb  (cost=0.00..0.27 rows=1 width=4)
                     Index Cond: (id = clb.branch_id)
                     Filter: (branch_name = 'head'::text)
         ->  Index Scan using commit_log_ports_pkey on commit_log_ports clp  (cost=0.00..40.25 rows=18 width=4)
               Index Cond: (commit_log_id = cl.id)
(14 rows)

freshports.org=# 

The analysis for that query is at http://explain.depesz.com/s/p3wn.

I’ll let readers describe what happened here…

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive