PostgreSQL update takes too long-CodePudding

I have a query update with limit 1000 on ny select query:

UPDATE id SET satus = 201 WHERE uniquecode in (ids..)

I already have index for my query but it took so long

CREATE UNIQUE INDEX id_idx ON public.id USING btree (id);


  ->  Index Scan using id_idx on id (cost=0.56..4280.29 rows=500 width=811) (actual time=0.758..11.244 rows=500 loops=1)
Index Cond: ((id)::numeric = ANY (ids..)
Planning Time: 3.087 ms
Execution Time: 1543.567 ms

i need execute below 1000 ms

CodePudding user response：

It seems to me that the index you want is on the uniquecode column, rather than the id column:

CREATE UNIQUE INDEX uq_idx ON public.id USING btree (uniquecode);

Note that if id be the primary key of your table, then it already should have a btree index on it.

CodePudding user response：

You didn't show the complete execution plan, nor did you show the actual query, but if the scraps of information you show are accurate, the time is not spent in the index scan, but in the update itself.

Probably there are lots of indexes on the table and/or long running triggers.