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

deleting old notifications is slow

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 1.8.6, 1.9.3
    • Database
    • Security Level: Default (Default Security Scheme)
    • None
    • Operating System: All
      Platform: PC
    • 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

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

            Dates

              Created:
              Updated:
              Resolved: