Re: Is postgres able to share sorts required by common partition window functions?

On Monday, July 6, 2020, Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> Did you say you have an index on c1?
> [...]
> I don't know the data, but I assume there may be many rows with the same
> c1 value, so then you would likely benefit from getting that distinct set
> first like below as your FROM table.
>

> Re-reading the original email I see both the answer to your question and
the data being queried.
> David J.

Thanks David. I meant it as a rhetorical question, since yes of course
there was an index. I also didn't trust the example to be true to real data
in terms of c1 values distribution.

On Tue, Jul 7, 2020 at 9:01 AM Sebastien Arod <sebastien(dot)arod(at)gmail(dot)com>
wrote:

> Michael, David thanks for your quick replies.
>
> *(at)Michael*
> I initially dismissed writing this query using joins or subselects because
> the real query has about 80 columns and I was afraid that having 80
> joins/subselect would cause issues with postgresql including planner that
> would fallback to GEQO.
> I'll test it anyway with real data and see how it behaves.
>

Contrived and overly simplified examples often lead to uninformed, bad
advice. I would not attempt joins, unless the number of distinct c1 values
is relatively small perhaps. It might go fine though, and depending on your
query and the statistics on the table, perhaps join_collapse_limit = 1
would be prudent to constrain the planner to your desired plan and not
introduce the chance for the genetic optimizer to get involved.

Sort Method: external merge Disk: 52912kB
Sort Method: external merge Disk: 46168kB

What is your work_mem set to? Would it be possible to set it higher (for
this process) to avoid spilling to disk?