Optimising a Lead query in postgresql

Question

I'd like to select an entity based on it's name and based on alphabetical order, select the name of the next row. The name column is a varchar, is unique and has an index.

Using the Lead window function this is what I've come up with:

SELECT *
FROM (
    SELECT  
        *,
        LEAD("name", 1, '') OVER(ORDER BY name) AS next
    FROM entity    
    ORDER BY "name"
) results
WHERE "name" = 'CACTUS';

However the query performance degrades as the size of the entity table increases.

The query plan looks like:

Subquery Scan on results  (cost=0.42..31205.95 rows=1 width=299)
  Filter: ((results.""name"")::text = 'CACTUS'::text)"
  ->  WindowAgg  (cost=0.42..29002.24 rows=176297 width=299)"
        ->  Index Scan using ""IDX_2fbbd02c0f1ee2a4dda593705d"" on entity  (cost=0.42..26357.79 rows=176297 width=235)"

Is there a more efficient way of doing this?

postgresql version 11+

lead() without an order by doesn't really make sense to begin with - there is no such thing as "the next row" unless you specify an order by. But yes it is expected that this gets slower the more rows you have, because the inner query has to sort all rows in the table — user330315
– user330315, Commented Mar 5, 2019 at 15:19
Ok, I assumed it would use the order of the subquery, I've updated the window function to have an explicit ORDER BY. — shusson
– shusson, Commented Mar 5, 2019 at 15:25

user330315 · Accepted Answer · 2019-03-05 15:29:10Z

3

You could try without a window function and a co-related scalar sub-query, but I am not sure if that really is faster:

SELECT e1.name, 
       (select coalesce(max(name), '')
        from entity e2
        where e2.name > e1.name) as next
FROM entity e1
WHERE e1.name = 'CACTUS';

answered Mar 5, 2019 at 15:29

user330315

Sign up to request clarification or add additional context in comments.

7 Comments

shusson Over a year ago

Thanks, this works orders of magnitude faster for me but I had to add an ORDER BY to the sub-query and query. I also updated the question to reflect that I wanted an order :D. If there are no other answers in a couple days I'll accept this answer.

user330315 Over a year ago

@shusson: but you claimed that name is unique, so the condition where e1.name = 'CACTUS' can only return a single row - I don't see why you would need an order by for that. The use of max() and > does impose an order on the select "next" name

shusson Over a year ago

yes but I'm interested in the name of the next row based on a particular order, let's say alphabetical.

user330315 Over a year ago

@shusson: the condition e2.name > e1.name imposes an order

shusson Over a year ago

yeah ok I'll update the question to impose alphabetical order

|

Collectives™ on Stack Overflow

Optimising a Lead query in postgresql

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related