Forum OpenACS Development: slow notifications query on openacs.org

There is one query in notifications that is slow to run on openacs.org. I tested the same query on the same data on cvs.openacs.org and it runs much more quickly.

Both are running FC4 with PG 8.0.7.

They both have 1G or ram and about 18Mb free ram. cvs.openacs.org is using 500M or 1000M of swap. openacs.org us using 24M of 2000M of swap.

on openacs.org nsd is currently at 300M with PG using 17M of ram. No other signifigant use of ram.

on cvs.openacs.org nsd is currently at 500M with PG using around 16M of ram. Nothing else to speak of RAM-wise.

Both have the same settings in postgresql.conf, the defaults for everything.

On cvs.openacs.org the query runs the first time in 6ms according to explain analyze, and .05 ms for future runs of explain analyze.

On openacs.org the quer takes 16 seconds, and is the same if you run it again.

Before vacuum openacs.org was taking 30 seconds. (the machine is vacuumed --full --analyze at midnight every day as well) and the query plan was doing a sequential scan on acs_objects. After it does an index scan and the plan is identical on both boxes.

Any ideas where else to look?

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

Ok its doing a sequential scan on acs_objects, the exact same query on two different machines.