Uploaded image for project: 'OpenNMS'
  1. OpenNMS
  2. NMS-4217

deleting old notifications is slow

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.8.6, 1.9.3
    • Component/s: Database
    • Security Level: Default (Default Security Scheme)
    • Labels:
      None
    • Environment:
      Operating System: All
      Platform: PC
    • Bugzilla Id:
      4199

      Description

      When attempting to cascade delete old events that are tied to notifications, the notifications table takes an inordinately long time to clear out:

      opennms=# explain analyze delete from events where eventid = any (array (select eventid from events where nodeid = '12' limit 100));
      QUERY PLAN
      --------------------------------------------------------------------------------------------------------------------------
      Bitmap Heap Scan on events (cost=171.42..211.44 rows=10 width=6) (actual time=5.207..5.442 rows=100 loops=1)
      Recheck Cond: (eventid = ANY ($0))
      InitPlan
      -> Limit (cost=0.00..128.78 rows=100 width=4) (actual time=0.356..4.307 rows=100 loops=1)
      -> Seq Scan on events (cost=0.00..218780.86 rows=169884 width=4) (actual time=0.353..4.159 rows=100 loops=1)
      Filter: (nodeid = 12)
      -> Bitmap Index Scan on pk_eventid (cost=0.00..42.63 rows=10 width=0) (actual time=5.180..5.180 rows=100 loops=1)
      Index Cond: (eventid = ANY ($0))
      Trigger for constraint fk_eventid3 on events: time=7.529 calls=100
      Trigger for constraint fk_eventidak2 on events: time=3.833 calls=100
      Trigger for constraint fk_eventid1 on events: time=3.520 calls=100
      Trigger for constraint fk_eventid2 on events: time=3.436 calls=100
      Trigger for constraint fk_notifid2 on notifications: time=34572.905 calls=100
      Total runtime: 34599.222 ms
      (14 rows)

      This is because notifications does not have an index on the usersnotified column.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              ranger Benjamin Reed
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: