Re: [PERFORM] update from performance question

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