1

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?
  • 1
    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 – Evan Carroll Sep 9 '17 at 14:17
  • 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 – Misa Sep 9 '17 at 14:33
  • 1
    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. – a_horse_with_no_name Sep 9 '17 at 16:22
  • @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. – Evan Carroll Sep 9 '17 at 17:03
  • No I mean something like this: privatebin.net/… – a_horse_with_no_name Sep 9 '17 at 22:02
0

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;
  • Thank you very much for your answer! I will try it out as soon as possible. @evancarrol – Misa Sep 9 '17 at 17:02
  • @Misa not yet, it's not right. – Evan Carroll Sep 9 '17 at 17:02
  • @Misa try that. =) – Evan Carroll Sep 9 '17 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.