Re: Explain out put

On Tuesday, July 20, 2021, Yambu <hyambu(at)gmail(dot)com> wrote:

> Hello
>
> I 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
>
> https://explain.depesz.com/s/tfho
>

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.