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.
Best Answer
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.
AND