From: Justin Pryzby <pryzby@telsasoft.com>
To: Michael Lewis <mlewis@entrata.com>
Cc: Jiří Fejfar <jurafejfar@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: performance of analytical query
Date: Fri, 12 Nov 2021 12:33:50 -0600
Message-ID: <20211112183350.GJ17618@telsasoft.com> (raw)
In-Reply-To: <CAHOFxGoe_PAYV3BmKBTZXqQTHuoMT1fM0+25Mr0Gcy8FDDsofw@mail.gmail.com>
References: <CA+8wVNV98Z-RDM4R+gm-ds17LVcOODc-NB1Ov9dTFQjuHa+1_Q@mail.gmail.com>
<20211112024151.GF17618@telsasoft.com>
<CAHOFxGoe_PAYV3BmKBTZXqQTHuoMT1fM0+25Mr0Gcy8FDDsofw@mail.gmail.com>
On Fri, Nov 12, 2021 at 10:55:53AM -0700, Michael Lewis wrote:
> On Thu, Nov 11, 2021 at 7:42 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> > BTW, we disable nested loops for the our analytic report queries. I have
> > never
> > been able to avoid pathological plans any other way.
>
> Curious, do you see any problems from that? Are there certain nodes that
> really are best suited to a nested loop like a lateral subquery?
When I first disabled it years ago, I did it for the entire database, and it
caused issues with a more interactive, non-analytic query, on a non-partitioned
table.
So my second attempt was to disable nested loops only during report queries,
and I have not looked back. For our report queries on partitioned tables, the
overhead of hashing a handful of rows is of no significance. Any query that
finishes in 1sec would be exceptionally fast.
BTW, Jiří's inquiry caused me to look at the source of one of our historic
mis-estimates, and to realize that it's resolved in pg14:
https://www.postgresql.org/message-id/20211112173102.GI17618%40telsasoft.com
I doubt that's enough to avoid catastrophic nested loop plans in every case
(especially CTEs on top of CTEs).
There was a discussion about discouraging nested loop plans that weren't
provably "safe" (due to returning at most one row, due to a unique index).
https://www.postgresql.org/message-id/CA%2BTgmoYtWXNpj6D92XxUfjT_YFmi2dWq1XXM9EY-CRcr2qmqbg%40mail.g...
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, mlewis@entrata.com, jurafejfar@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: performance of analytical query
In-Reply-To: <20211112183350.GJ17618@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox