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))