Postgresql update slow on a relative small/medium table

Question

I'm trying to run an update on a table with 10 million rows (I just need to update a few), but the query is taking more time than I would like.

This is the table deinifition:

CREATE TABLE det_price_list
    (
      price_list_id     BIGINT NOT NULL
        CONSTRAINT fk_det_list_precio_venta_2
        REFERENCES price_list
        ON UPDATE RESTRICT ON DELETE RESTRICT,

      det_price_list_id BIGINT NOT NULL,
      product_id        VARCHAR(25),
      account_id        BIGINT,
      price             NUMERIC(22, 8),
      currency_id       BIGINT,
      last_updated      TIMESTAMP,
      
      CONSTRAINT pk_det_price_list
      PRIMARY KEY (price_list_id, det_price_list_id),
      CONSTRAINT fk_det_list_precio_venta_1
      FOREIGN KEY (product_id, account_id) REFERENCES product
      ON UPDATE RESTRICT ON DELETE RESTRICT,
      CONSTRAINT fk_det_price_list_4
      FOREIGN KEY (currency_id, account_id) REFERENCES currency
    );

With this index:

CREATE UNIQUE INDEX indice_det_lista_item
  ON det_price_list (product_id, price_list_id);

CREATE INDEX ix_det_price_list_5
  ON det_price_list (price_list_id, account_id, product_id);

CREATE INDEX ix_det_price_list_1
  ON det_price_list (price_list_id);

CREATE INDEX ix_det_price_list_2
  ON det_price_list (product_id);

CREATE INDEX ix_det_price_list_6
  ON det_price_list (account_id, product_id, price_list_id, last_updated);

CREATE INDEX ix_det_price_list_3
  ON det_price_list (account_id);

CREATE INDEX ix_det_price_list_4
  ON det_price_list (last_updated);

CREATE INDEX det_price_list_account_id_price_list_id_currency_id_i
  ON det_price_list (account_id, price_list_id, currency_id);

And this is the query:

update det_price_list d
    set last_updated = current_timestamp
  where d.account_id = 994
  AND   price_list_id = 4338
  and d.currency_id= 2;

Also here is the query plan using EXPLAIN (ANALYZE, BUFFERS):

QUERY PLAN
Update on det_price_list d  (cost=0.56..219.89 rows=60 width=77) (actual time=1806.823..1806.823 rows=0 loops=1)
  Buffers: shared hit=1040857 read=1704 dirtied=8839
  I/O Timings: read=1.814
  ->  Index Scan using det_price_list_account_id_price_list_id_currency_id_i  on det_price_list d  (cost=0.56..219.89 rows=60 width=77) (actual time=0.034..28.386 rows=26776 loops=1)
        Index Cond: ((account_id  = 994) AND (price_list_id  = 4338) AND (currency_id = 2))
        Buffers: shared hit=2740 dirtied=621
Planning time: 0.157 ms
Execution time: 1806.873 ms

The query is using the index. I've already tried to vacumm, vacumm full, reindex and analyze but i can't reduce that time. I can't copy into another table and drop the old one because it is not a complete update and also the table is used constantly.

Answer 1

You are updating many rows on a table with many indexes. It is normal that that takes a while.

To make that faster, drop all indexes that contain last_updated and define the table with a fillfactor of less than 100. Then you can get HOT updates, which don't have to modify the indexes and are much faster.

Answer 2

Instead of updating hundreds of row at once, which will always take sometime with that much rows

make an UPDATE TRIGGER, which automatically updates the timestamp for every row, whrn an update occurs.

CREATE OR REPLACE FUNCTION update_changetimestamp_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.last_updated = now(); 
   RETURN NEW;
END;

AND

$$ language 'plpgsql';
CREATE TRIGGER update_det_price_list_timestamp BEFORE UPDATE
ON ab FOR EACH ROW EXECUTE PROCEDURE 
update_changetimestamp_column();