Everything here works fine - but after a handful of product iterations & production adjustments, a query that handles a "task queue" across a few tables looks a bit ugly.
My concern is that the sort needs to happen 3x --
in the subselect for 1000 items
in the partition for row numbering
in the final sort
Unless you can discard the 5 and 1000 limits you are going to be stuck computing rank three times in order to compute and filter them.
Without understanding how the data is ultimately used its difficult to suggest meaningful alternatives. Views and/or functions can hide some of the complexity behind meaningful names but changing away from your choice of "window" and "partition" as relation aliases will give most of the same effect.
The query is a solid reflection of the data model. If the only concern is stylistic I'd say you should move on.
Given the first limit 1000 a reasonable work_mem setting should (without any evidence...) suffice to make the actual time spent sorting immaterial. Lower level sorting is retained where possible so the upper levels don't have as much to move around. The lakck of the provider id in the final sort was noticed though...
David J.
