Optimisation postgres windows query

Question

I need some guidance with this query being too slow.

SELECT DISTINCT ON (id)
 id,
 views - lead(views)
 OVER (PARTITION BY id
 ORDER BY update_date DESC) vdiff
FROM videoupdate;

With 10 million+ rows it takes ~30 seconds. I have created a multicolumn index that reduced the original time from 1 minute. I want to see difference between views for each row partitioned by id. Some thoughts I had:

• After table update create TABLE AS with the query and select from it.
• Move old data to backup and shrink table.
• Look up data warehouse?
• Change database schema?

• Evan Carroll: I'm not sure what that returns because it's undefined. You're using DISTINCT ON without and ORDER BY? I'm assuming it's ordered by update _date DESC
• Misa: I figured I didn't need to add ORDER BY when I have it in the window function. It should be ordered by update_date, yes. I believe I tested this. Nonetheless, the query still takes too long. @EvanCarroll
• user1822: You could try to remove the distinct on and use a row_number() over the same window as the lead() function and use that row number to get the distinct ID.
• Evan Carroll: @a_horse_with_no_name I don't think that will work, because he needs the lead() and WHERE runs before the window function. So the distinct on is still the best bet.
• user1822: No I mean something like this: privatebin.net/…

Answer 1

Following @a_horse_with_no_name's suggestion again, because he's really smart though super, super-resilient to using the Post Your Answer functionality.

SELECT DISTINCT ON(id),
 id,
 views - lead(views) OVER (PARTITION BY id ORDER BY update_date DESC) AS vdiff
FROM (
 SELECT id,
 views,
 update_desc,
 row_number() OVER (PARTITION BY id ORDER BY update_date DESC) AS rn
 FROM videoupdate
) AS t
WHERE rn <=2
ORDER BY id, update_desc DESC;

• Misa: Thank you very much for your answer! I will try it out as soon as possible. @evancarrol
• Evan Carroll: @Misa not yet, it's not right.
• Evan Carroll: @Misa try that. =)