Re: zabbix on postgresql - very slow delete of events

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

"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно
когда я так делаю ещё раз?"