CODEWITHSUNDEEP
sqlpostgresql
Luffydude
Luffydude

Reputation: 812

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?

Upvotes: 0

Views: 627

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522732

In fact the presence of the LEFT function in the ORDER BY clause of DENSE_RANK should preclude the chance to use any index there. I suggest the following index:

CREATE INDEX idx ON table1 (condition1, condition2, condition3);

There is no real benefit to using LEFT(condition3, 5), so I suggest calling DENSE_RANK as:

DENSE_RANK() OVER (ORDER BY condition1, condition2, condition3)

The above version should be sargable, and the index suggested should be usable.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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.

Upvotes: 1

Related Questions