On Monday, July 6, 2020, Michael Lewis <mlewis@xxxxxxxxxxx> wrote:
What is your work_mem set to? Would it be possible to set it higher (for this process) to avoid spilling to disk?
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.arod@xxxxxxxxx> wrote:
Michael, David thanks for your quick replies.@MichaelI 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?
