> > > All these queries execute well below 1 ms, using indexes. > > > > Let's delete one row. See explain results here: > https://explain.depesz.com/s/aycf . 5 seconds to delete a single row, > wow! > > This shows that it is the foreign key constraints on event_recovery and > alerts that take a lot of time. > > But why? I far as I can see, the delete is fully CPU bound during > execution. > > > > Deleting the corresponding row directly from event_recovery or alerts > executes in less than 0.1 ms. > > > > Any ideas? > > > > I've observed that alerts and event_recovery tables both have more than > one foreign key that references events, if that matters. > > > Hi Kristian,
After comparing structure of zabbix tables with same in my zabbix installation I found one very weird difference. Why type of events.eventid had been changed from default bigint to numeric? I suspect that the difference between events.eventid (numeric) type and event_recovery.*_eventid (bigint) types might lead to inability of use index during foreign key checks. Anyway it will be clearly visible on the pg_stat_xact_user_tables results (I now expect to see 3 sequential scan on event_recovery and may be on some other tables as well). Kind Regards, Maxim -- Maxim Boguk Senior Postgresql DBA https://dataegret.com/ Phone RU: +7 985 433 0000 Phone UA: +380 99 143 0000 Phone AU: +61 45 218 5678 LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b Skype: maxim.boguk "Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"
