Armand Pirvu wrote: > Running 9.5.2 > > I have the following update and run into a bit of a trouble . I realize the tables > involved have quite some data but here goes > > > UPDATE > tf_transaction_item_person TRANS > SET > general_ledger_code = PURCH.general_ledger_code, > general_ledger_code_desc = PURCH.general_ledger_code_desc, > update_datetime = now()::timestamp(0) > FROM > tf_purchases_person PURCH > WHERE > PURCH.general_ledger_code != '' AND > TRANS.purchased_log_id = PURCH.purchased_log_id AND > TRANS.general_ledger_code != PURCH.general_ledger_code > ; [...] > Table "tf_transaction_item_person" [...] > Indexes: > "tf_transaction_item_person_pkey" PRIMARY KEY, btree (person_transaction_item_id) > "tf_tip_idx" btree (client_id, update_datetime) > "tf_tip_isdel_idx" btree (show_id, person_transaction_item_id) You don't show EXPLAIN (ANALYZE, BUFFERS) output for the problematic query, so it is difficult to say where the time is spent. But since you say that the same query without the UPDATE also takes more than a minute, the duration for the UPDATE is not outrageous. It may well be that much of the time is spent updating the index entries for the 3.5 million affected rows. I don't know if dropping indexes for the duration of the query and recreating them afterwards would be a net win, but you should consider it. It may be that the only ways to improve performance would be general things like faster I/O, higher max_wal_size setting, and, most of all, enough RAM in the machine to contain the whole database. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
