Postgresql – Optimisation postgres windows query

postgresql

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?

Best Answer

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;
Related Question