Note to we BIG BRAIN types in the OpenACS community...
When a SELECT like the notifications query posted by Jeff above does a sequential scan on the objects table when joining on its primary key object_id...
Try the ANALYZE command.
Sometimes you get plans like this after you do:
Sort (cost=47.76..47.77 rows=3 width=794)
Sort Key: notification_requests.user_id, notifications.type_id, notifications.notif_date
-> Hash Join (cost=6.01..47.74 rows=3 width=794)
Hash Cond: ("outer".user_id = "inner".user_id)
Join Filter: ("outer".notification_id = "inner".notification_id)
Filter: ("inner".sent_date IS NULL)
-> Nested Loop (cost=0.00..41.72 rows=3 width=778)
Join Filter: ("inner".creation_date <= "outer".notif_date)
-> Nested Loop (cost=0.00..13.63 rows=8 width=766)
-> Index Scan using notifications_object_id_idx on notifications (cost=0.00..7.76 rows=1 width=746)
-> Index Scan using notification_requests_t_o_idx on notification_requests (cost=0.00..5.85 rows=1 width=20)
Index Cond: (("outer".type_id = notification_requests.type_id) AND ("outer".object_id = notification_requests.object_id))
Filter: (interval_id = 2889)
-> Index Scan using acs_objects_pk on acs_objects (cost=0.00..3.39 rows=1 width=12)
Index Cond: ("outer".request_id = acs_objects.object_id)
-> Hash (cost=6.01..6.01 rows=1 width=16)
-> Index Scan using notification_user_map_user_idx on notification_user_map (cost=0.00..6.01 rows=1 width=16)
(17 rows)
Compare that with the plan posted by Jeff above.
Much nicer ... we'll see if the notification associate with THIS post goes a bit faster!
