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

      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
      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):

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.

  • have you thought about writing a trigger which updates this after an update automatically?
    – nbk
    May 28, 2021 at 15:43
  • Sorry, I'm not following you here, how would that be implemented?
    – dssof
    May 28, 2021 at 15:57
  • How much time are we talking about when you say "more time than you would like" ? How many rows are being updated? Also, I hope you will upgrade soon as 9.4 is very old now and no longer supported. May 29, 2021 at 10:19
  • 26776 rows are being updated, It doesn't seem like a big number of rows (but maybe me expectations are incorrect). I was hoping for less than a second, because this is triggered by an update on my app, so the user is waiting that total time. Also, the plan is to upgrade postgres as soon as we can, but it is very likely to stay in that version for a few months now.
    – dssof
    May 29, 2021 at 21:25

2 Answers 2


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.


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()
   NEW.last_updated = now(); 


$$ language 'plpgsql';
CREATE TRIGGER update_det_price_list_timestamp BEFORE UPDATE
  • Okey, now I get it. That update is triggered by an update on another related table (which contains the currency price): create table currency ( currency_id BIGINT NOT NULL price numeric(22,8) ); So, when the price change, I have to look for the det_price_list rows of that currency and update. I don't know if a trigger would work for that. Also, when updating, is it really faster to update rows one by one?
    – dssof
    May 28, 2021 at 17:09
  • if you don't change anything in pricelist, don't change it, change a lastupdated column in currency you can alwys join it and get the date
    – nbk
    May 28, 2021 at 17:18
  • That's a good point, but this is not the only update I need to do on that table. So, basically, I would like to know if there is a way of update that many rows with a decent execution time
    – dssof
    May 28, 2021 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.