Forum OpenACS Q&A: Re: upgrade (was: having problems. Refrain from using it until Sept 25th)

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!