Long updates by primary key

From: Алексей Белобородов <abeloborodov88(at)gmail(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Long updates by primary key
Date: 2021-05-24 10:19:02
Message-ID: CAOeBAj5Ny3S7D=7D5wFz3b381pRwWyiR_GMChOMMo4kiFoh4sw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

We use postgres 9.6.
Sometimes we have long updates by transaction id like this:

UPDATE transactions SET lasterror = NULL::json WHERE id =
'd926d582-8a94-4674-a400-ad2f02571c0e';
Plan in pgBadger:

Update on public.transactions (cost=0.57..8.59 rows=1 width=2373)
(actual rows=0 loops=1)
Buffers: shared hit=629177 read=399611 dirtied=353681 written=4702
I/O Timings: read=4189592.228 write=242.336
-> Index Scan using transactions_pkey on public.transactions
(cost=0.57..8.59 rows=1 width=2373) (actual rows=1 loops=1)
Output: id, companyid, idinouterservice, date, status,
category, contragentname, contragentinn, contragentbankaccountnumber,
contragentbankname, contragentbankbic, currency, amount,
bankaccountid, bankaccountnumber, paymentpurpose, contragentkpp,
amountall, executestamp, registerstamp, docmodule, doctype, docnumber,
accid, bankbik, corraccid, agreerules, sendtype, opertype, urgenttype,
ndstypeid, corrtype, uin, kbk, okato, ground, tax1, tax2, tax3,
taxdocnum, taxdocdate, taxtype, stat, contragentshortname,
infavorites, outerservicestatus, authorid, notify_needed,
notify_email, notify_text, paymenttype, nds, contragentid,
contragentbankid, ifnscontragentid, dateref, transferbankaccountid,
ibsoid, currencytransferamount, groupname, contragentbankswift,
currencyoperationcode, contragentcountry, contragentcity,
contragentaddress, contragentbankcorrespondentid,
contragentbankcorrespondentaccountnumber,
contragentbankcorrespondentname, contragentbankcorrespondentswift,
mailtobankid, mailtobanksended, payuntil, inquiryid,
comissionbankaccountid, comissionaccid, paymentreasondocs,
inquirydate, inquirydocnumber, moddate, declineinfo, transferkind,
amountnat, absid, dboid, contragentbankcorraccount,
corraccisbankclient, corraccisourbank, accibsoid, companyname,
companyinn, companykpp, lasteditwithrootfirmware, children, parentid,
ishidden, signstatus, contragentcurrency,
amountforcredittocurrentaccount, NULL::json, favoritename,
importedfrom, payrollid, parentdboid, rrn, approval_code, chargestype,
startbankaccountbalance, timeref, formnumber, additionalinfo,
corramount, createstamp, signercert, dboadditionalinfo, signdate,
communalpaymentinfo, taxtransactionid, mergefield, bankproductid,
contragentclientid, addinfavoritesdate, favoritefromid, terminalname,
cardpan, pcdate, bankname, banknameplace, contragentbanknameplace,
bankcorraccount, comissionbankaccountnumber, taxthirdpersonname,
creditlineamount, currfundtransfernotificationid,
displaycontragentname, notify_phone,
contragentbankaccountnumberforprint, comissionchildren,
comissionparentid, smallcurrdealreason, delayeddue, regularpaymentid,
skipcurrencycontrol, dbupdatetime, ctid
Index Cond: (transactions.id =
'd926d582-8a94-4674-a400-ad2f02571c0e'::uuid)
Buffers: shared hit=5 read=1
I/O Timings: read=0.018

This query has been executed for 1h10m8s. Index scan node shows actual rows
= 1 but then we have actual rows=0 with big counts in Buffers sections.
Field id is the primary key of table transactions. Any idea why the update
of one row is so long?
Row with this id really exists.

About transactions size
SELECT reltuples, relpages FROM pg_class WHERE relname = 'transactions';
172354000, 31646472

More examples:
update transactions set signstatus = 'Signed', signercert=null, signdate =
'2021-05-21 10:54:47.7720000' where id
='d926d582-8a94-4674-a400-ad2f02571c0e';
Plan is

Update on public.transactions (cost=0.57..8.59 rows=1 width=2563)
(actual rows=0 loops=1)
Buffers: shared hit=70 read=28 dirtied=15 written=7
I/O Timings: read=20.999 write=0.303
-> Index Scan using transactions_pkey on public.transactions
(cost=0.57..8.59 rows=1 width=2563) (actual rows=1 loops=1)
Output: id, companyid, idinouterservice, date, status,
category, contragentname, contragentinn, contragentbankaccountnumber,
contragentbankname, contragentbankbic, currency, amount,
bankaccountid, bankaccountnumber, paymentpurpose, contragentkpp,
amountall, executestamp, registerstamp, docmodule, doctype, docnumber,
accid, bankbik, corraccid, agreerules, sendtype, opertype, urgenttype,
ndstypeid, corrtype, uin, kbk, okato, ground, tax1, tax2, tax3,
taxdocnum, taxdocdate, taxtype, stat, contragentshortname,
infavorites, outerservicestatus, authorid, notify_needed,
notify_email, notify_text, paymenttype, nds, contragentid,
contragentbankid, ifnscontragentid, dateref, transferbankaccountid,
ibsoid, currencytransferamount, groupname, contragentbankswift,
currencyoperationcode, contragentcountry, contragentcity,
contragentaddress, contragentbankcorrespondentid,
contragentbankcorrespondentaccountnumber,
contragentbankcorrespondentname, contragentbankcorrespondentswift,
mailtobankid, mailtobanksended, payuntil, inquiryid,
comissionbankaccountid, comissionaccid, paymentreasondocs,
inquirydate, inquirydocnumber, moddate, declineinfo, transferkind,
amountnat, absid, dboid, contragentbankcorraccount,
corraccisbankclient, corraccisourbank, accibsoid, companyname,
companyinn, companykpp, lasteditwithrootfirmware, children, parentid,
ishidden, 'Signed'::text, contragentcurrency,
amountforcredittocurrentaccount, lasterror, favoritename,
importedfrom, payrollid, parentdboid, rrn, approval_code, chargestype,
startbankaccountbalance, timeref, formnumber, additionalinfo,
corramount, createstamp, NULL::text, dboadditionalinfo, '2021-05-21
10:54:47.772'::timestamp without time zone, communalpaymentinfo,
taxtransactionid, mergefield, bankproductid, contragentclientid,
addinfavoritesdate, favoritefromid, terminalname, cardpan, pcdate,
bankname, banknameplace, contragentbanknameplace, bankcorraccount,
comissionbankaccountnumber, taxthirdpersonname, creditlineamount,
currfundtransfernotificationid, displaycontragentname, notify_phone,
contragentbankaccountnumberforprint, comissionchildren,
comissionparentid, smallcurrdealreason, delayeddue, regularpaymentid,
skipcurrencycontrol, dbupdatetime, ctid
Index Cond: (transactions.id =
'd926d582-8a94-4674-a400-ad2f02571c0e'::uuid)
Buffers: shared hit=7

This query has been executed for 1h10m8s too.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Vijaykumar Jain 2021-05-24 10:52:31 Re: Long updates by primary key
Previous Message Yambu 2021-05-24 04:42:50 Re: Index creation