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?