I just figured this out. Someone had set the default_statistics_target to 5000 .... instead of 500 I think. I changed it to 500, ran analyze and planning time is much better. In case someone runs into this problem, sending this out here. Thank you all.
On Wed, Apr 6, 2022 at 7:57 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"David G. Johnston" <david.g.johnston@xxxxxxxxx> writes:
> On Wed, Apr 6, 2022 at 5:27 PM Saurabh Sehgal <saurabh.r.s@xxxxxxxxx> wrote:
>> I have the following query:
>> I don't think it is super complex. But when I run explain analyze on this
>> I get the following:
>> Planning Time: 578.068 ms
>> Execution Time: 0.113 ms
> The fundamental issue here is that you have basically 12 conditions across
> 5 tables that need to be evaluated to determine which one of the 1,680
> possible join orders is the most efficient.
A 5-way join doesn't seem particularly outrageous. But I'm wondering
if these are all plain tables or if some of them are actually complex
views. Another possibility is that the statistics target has been
cranked to the moon and the planner is spending all its time sifting
through huge statistics arrays.
It'd be interesting to see the actual schemas for the tables,
as well as EXPLAIN's output for this query. I'm wondering
exactly which PG version this is, too.
regards, tom lane
Saurabh Sehgal
E-mail: saurabh.r.s@xxxxxxxxx
Phone: 425-269-1324
LinkedIn: https://www.linkedin.com/in/saurabh-s-4367a31/
E-mail: saurabh.r.s@xxxxxxxxx
Phone: 425-269-1324
LinkedIn: https://www.linkedin.com/in/saurabh-s-4367a31/
