On Tue, 20 Oct 2020 at 22:38, Mats Julian Olsen <mats@duneanalytics.com> wrote:
>
> The crux of our issue is that the query planner chooses a nested loop join for this query. Essentially making this
query(and other queries) take a very long time to complete. In contrast, by toggling `enable_nestloop` and
`enable_seqscan`off we can take the total runtime down from 16 minutes to 2 minutes.
>
> 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR
> 2) enable_nestloop=off (4 min): https://explain.depesz.com/s/buKK
> 3) enable_nestloop=off; enable_seqscan=off (2 min): https://explain.depesz.com/s/0WXx
>
> How can I get Postgres not to loop over 12M rows?
You'll likely want to look at what random_page_cost is set to. If the
planner is preferring nested loops then it may be too low. You'll
also want to see if effective_cache_size is set to something
realistic. Higher values of that will prefer nested loops like this.
You may also want to reduce max_parallel_workers_per_gather. It looks
like you're not getting your parallel workers as often as you'd like.
If the planner chooses a plan thinking it's going to get some workers
and gets none, then that plan may be inferior the one that the planner
would have chosen if it had known the workers would be unavailable.
> Let me know if there is anything I left out here that would be useful for further debugging.
select name,setting from pg_Settings where category like 'Query
Tuning%' and source <> 'default';
select version();
would be useful.
David
