Re: improvements/feedback sought for a working query that looks a bit ugly and might be inefficient

On Tuesday, May 16, 2017, jonathan vanasco <postgres(at)2xlp(dot)com> wrote:

>
> 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.

This is a far cry from 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.