First, you need an index on id. That is probably there because it should be a primary key.
Then, Postgres would probably use an index on (condition1, condition2, left(condition3, 5)) for the dense_rank(). You might want to test to be sure.
So been trying to run a query on a big table but it's taking more than a day to finish, when I put explain at the beginning and went through the result, it did not appear as using an index
The query was here on this question Updating a table to create unique ids in from a substring in PostgreSQL
update table1 t1
set target_id = tt1.seqnum
from (select t1.*,
dense_rank() over (order by condition1, condition2, left(condition3, 5)) as seqnum
from table1 t1
) tt1
where tt1.id = t1.id;
Would a 3 column index make this query use an index? OR any other way to optimise this?
First, you need an index on id. That is probably there because it should be a primary key.
Then, Postgres would probably use an index on (condition1, condition2, left(condition3, 5)) for the dense_rank(). You might want to test to be sure.