Re: slow delete

2023-08-16 Thread Jeff Janes
On Tue, Aug 15, 2023 at 4:23 PM Les wrote: { > > "Trigger Name": "RI_ConstraintTrigger_a_75463", > > "Constraint Name": "fk_pfq_src_product_file", > > "Relation": "product_file", > > "Time": 11179.429, > > "Calls": 90 > > }, > ... > The one with fk_pfft_product looks like this, it has about 500

Re: slow delete

2023-08-15 Thread Les
Tom Lane ezt írta (időpont: 2023. aug. 15., K, 22:37): > Les writes: > > It seems that two foreign key constraints use 10.395 seconds out of the > > total 11.24 seconds. But I don't see why it takes that much? > > Probably because you don't have an index on the referencing column. > You can get

Re: slow delete

2023-08-15 Thread Tom Lane
Les writes: > It seems that two foreign key constraints use 10.395 seconds out of the > total 11.24 seconds. But I don't see why it takes that much? Probably because you don't have an index on the referencing column. You can get away with that, if you don't care about the speed of deletes from th

slow delete

2023-08-15 Thread Les
I have created a table called _td with about 43 000 rows. I have tried to use this as a primary key id list to delete records from my product.product_file table, but I could not do it. It uses 100% of one CPU and it takes forever. Then I changed the query to delete 100 records only, and measure the

Re: zabbix on postgresql - very slow delete of events

2019-08-13 Thread Kristian Ejvind
tian.ejv...@resurs.se> Webb: www.resursbank.se<http://www.resursbank.se> From: Maxim Boguk Date: Wednesday, 24 July 2019 at 19:17 To: Kristian Ejvind Cc: "pgsql-performance@lists.postgresql.org" Subject: Re: zabbix on postgresql - very slow delete of events On Wed, Jul 2

Re: zabbix on postgresql - very slow delete of events

2019-07-24 Thread Maxim Boguk
On Wed, Jul 24, 2019 at 6:12 PM Kristian Ejvind wrote: > Hi Maxim > > > > Thanks for your advice, and let me start with your second email, which > I'll copy here: > > > > = > > Hi Kristian, > > > > After comparing structure of zabbix tables with same in my zabbix > installation I found one ve

Re: zabbix on postgresql - very slow delete of events

2019-07-24 Thread Kristian Ejvind
rg" Subject: Re: zabbix on postgresql - very slow delete of events Hi Kristian, If you look for explain analyze results for delete, you will see that 99% of time query spent on the foreign key triggers checks. In the same time the database have indexes on foreign key side in place. I reco

Re: zabbix on postgresql - very slow delete of events

2019-07-24 Thread Maxim Boguk
> > > 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

Re: zabbix on postgresql - very slow delete of events

2019-07-24 Thread Maxim Boguk
Hi Kristian, If you look for explain analyze results for delete, you will see that 99% of time query spent on the foreign key triggers checks. In the same time the database have indexes on foreign key side in place. I recommend try this: \timing on BEGIN; delete from zabbix.events where eventid

Re: zabbix on postgresql - very slow delete of events

2019-07-24 Thread Kristian Ejvind
Hi. Well, the events table has both a primary key and foreign keys referencing it, which is not possible on a partitioned table in postgresql 10. How did you work around this issue? On the other hand, if we can get the deletion of rows from the events table run at normal speed, I can't imagine

Re: zabbix on postgresql - very slow delete of events

2019-07-23 Thread Kenneth Marshall
On Tue, Jul 23, 2019 at 01:41:53PM +, Kristian Ejvind wrote: > Thanks Kenneth. In fact we've already partitioned the largest history* and > trends* tables > and that has been running fine for a year. Performance was vastly improved. > But since you > can't have a unique index on a partitioned

Re: zabbix on postgresql - very slow delete of events

2019-07-23 Thread Kristian Ejvind
Thanks Kenneth. In fact we've already partitioned the largest history* and trends* tables and that has been running fine for a year. Performance was vastly improved. But since you can't have a unique index on a partitioned table in postgres 10, we haven't worked on that. Regards Kristian ?On

Re: zabbix on postgresql - very slow delete of events

2019-07-23 Thread Kenneth Marshall
On Tue, Jul 23, 2019 at 08:07:55AM +, Kristian Ejvind wrote: > Hi > > This will be a rather lengthy post, just to give the full (I hope) picture. > We're using Zabbix for monitoring and I'm having problems > understanding why the deletion of rows in the events table is so slow. > > Zabbix: 4

zabbix on postgresql - very slow delete of events

2019-07-23 Thread Kristian Ejvind
more than one day to finish, so events keep stacking up in the table. A typical slow delete statement, from the postgres log: postgresql-10-20190717-031404.log:2019-07-17 03:37:43 CEST [80965]: [4-1] user=zabbix,db=zabbix_34,app=[unknown],client=[local]: LOG: duration: 27298798.930 ms stat

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Rodrigo Rosenfeld Rosas
Em 05-12-2017 16:15, Rodrigo Rosenfeld Rosas escreveu: Em 05-12-2017 15:49, Alvaro Herrera escreveu: Rodrigo Rosenfeld Rosas wrote: Em 05-12-2017 15:25, Tom Lane escreveu: Normally this is because you lack indexes on the referencing columns, so the query that scans the table to find the refer

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Rodrigo Rosenfeld Rosas
Em 05-12-2017 15:49, Alvaro Herrera escreveu: Rodrigo Rosenfeld Rosas wrote: Em 05-12-2017 15:25, Tom Lane escreveu: Normally this is because you lack indexes on the referencing columns, so the query that scans the table to find the referencing rows is a seqscan. Actually though ... the weird

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Alvaro Herrera
Rodrigo Rosenfeld Rosas wrote: > Em 05-12-2017 15:25, Tom Lane escreveu: > > > Normally this is because you lack indexes on the referencing columns, so > > > the query that scans the table to find the referencing rows is a > > > seqscan. > > Actually though ... the weird thing about this is that I

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Rodrigo Rosenfeld Rosas
Em 05-12-2017 15:25, Tom Lane escreveu: Alvaro Herrera writes: Rodrigo Rosenfeld Rosas wrote: explain analyze delete from field_values where transaction_id=226; QUERY PLAN --

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Tom Lane
Alvaro Herrera writes: > Rodrigo Rosenfeld Rosas wrote: >> explain analyze delete from field_values where transaction_id=226; >> QUERY PLAN >> ---

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Rodrigo Rosenfeld Rosas
Em 05-12-2017 14:43, Alvaro Herrera escreveu: Rodrigo Rosenfeld Rosas wrote: explain analyze delete from field_values where transaction_id=226; QUERY PLAN

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Alvaro Herrera
Rodrigo Rosenfeld Rosas wrote: > explain analyze delete from field_values where transaction_id=226; > QUERY PLAN > --- >  Delete on field_valu

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Rodrigo Rosenfeld Rosas
Em 05-12-2017 14:27, Tom Lane escreveu: Rodrigo Rosenfeld Rosas writes: Hi, I think something changed recently in my development environment as I don't recall deletes being so slow before. I've created a new dump and restored to a new database, ran VACUUM FULL ANALYSE and a simple delete takes

Re: Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Tom Lane
Rodrigo Rosenfeld Rosas writes: > Hi, I think something changed recently in my development environment as > I don't recall deletes being so slow before. > I've created a new dump and restored to a new database, ran VACUUM FULL > ANALYSE and a simple delete takes forever as you can see here: The

Extremely slow DELETE with cascade foreign keys

2017-12-05 Thread Rodrigo Rosenfeld Rosas
Hi, I think something changed recently in my development environment as I don't recall deletes being so slow before. I've created a new dump and restored to a new database, ran VACUUM FULL ANALYSE and a simple delete takes forever as you can see here: explain analyze delete from field_values