HelloI suspect that the partial index is not correct.It should include both column play_positionand elapsed. may you please help me figure out what's wrong here
Not easily, but I do see the scan of an index with “partial” in its name so it is getting used. Though the fact it thinks its returning 6,000 or so rows but in actuality gets almost 500,000 (each on two partitions) is a bit worrying (though maybe not solveable…and since you do have nearly 1,000,000 rows to deal with 3 seconds doesn’t seem terrible). It doesn’t want to do 1,500 nested loops from the media_contents table onto an only 15,000 rows telemetry result. I don’t know if it would think differently if it knew the inner result is 1 million instead (you could experiment with the planner GUCs). I don’t know enough about the statistics to give concrete help on improving this other than make sure tou’ve run analyze on the table.
You may find keeping client_id on the telemetry table to be helpful if you need better performance.
All that said, I’m somewhat learning by teaching here so take this with a critical mindset.
I take it the query used to use aggregates? If not the group by in the main query is just noise. Also, as you are grouping by account_id in the CTE it will be impossible for rows to exist that the “select distinct” will get rid of.
HTH
David J.
