Indexing for dense_rank query in postgresql

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?

Answer

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.

Advertisement

Answer