Re: Long updates by primary key

Can you confirm the table is vacuumed regularly and there is little or no
bloat.

You can check table and index bloat using queries here

https://github.com/ioguix/pgsql-bloat-estimation#bloat-in-btree-indexes

Huge amount of read io makes me point to bloat or index corruption.

http://www.postgresql-blog.com/how-to-fix-postgresql-index-corruption-deal-repair-rebuild/

Does a select using the same predicate also take so long ?

If you can rule out these two queries , I guess it might help.

Also just for sanity reasons , if you can upgrade to the latest patch on
9.6 it may be of help too, as a lot of performance improvements came in
later.

On Mon, May 24, 2021, 3:55 PM Алексей Белобородов <abeloborodov88(at)gmail(dot)com>
wrote:

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