Forum OpenACS Development: Re: slow notifications query on openacs.org

Collapse
Posted by Dave Bauer on
Here is the query

 select notification_id,
                   notif_subject,
                   notif_text,
                   notif_html,
                   user_id,
                   type_id,
                   delivery_method_id,
                   response_id,
                   notif_date,
                   notif_user
            from notifications inner join notification_requests using (type_id,\
 object_id)
              inner join acs_objects on (notification_requests.request_id = acs\
_objects.object_id)
              left outer join notification_user_map using (notification_id, use\
r_id)
            where sent_date is null
              and creation_date <= notif_date
              and interval_id = '2889'
          order by user_id, type_id, notif_date;

and the plan

-----------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=17740.19..17742.12 rows=771 width=1371)
   Sort Key: notification_requests.user_id, notifications.type_id, notifications.notif_date
   ->  Nested Loop Left Join  (cost=3397.39..17435.77 rows=771 width=1371)
         Filter: ("inner".sent_date IS NULL)
         ->  Hash Join  (cost=3397.39..13169.02 rows=771 width=1371)
               Hash Cond: ("outer".object_id = "inner".request_id)
               Join Filter: ("outer".creation_date <= "inner".notif_date)
               ->  Seq Scan on acs_objects  (cost=0.00..6083.49 rows=220849 width=12)
               ->  Hash  (cost=2994.61..2994.61 rows=2312 width=1375)
                     ->  Merge Join  (cost=2105.18..2994.61 rows=2312 width=1375)
                           Merge Cond: (("outer".type_id = "inner".type_id) AND ("outer".object_id = "inner".object_id))
                           ->  Index Scan using notification_requests_type_obj_ on notification_requests  (cost=0.00..767.84 rows=16037 width=20)
                                 Filter: (interval_id = 2889)
                           ->  Sort  (cost=2105.18..2113.16 rows=3192 width=1367)
                                 Sort Key: notifications.type_id, notifications.object_id
                                 ->  Seq Scan on notifications  (cost=0.00..589.92 rows=3192 width=1367)
         ->  Index Scan using notification_user_map_n_u_idx on notification_user_map  (cost=0.00..5.52 rows=1 width=16)
               Index Cond: (("outer".notification_id = notification_user_map.notification_id) AND ("outer".user_id = notification_user_map.user_id))