I found out that setting:
set join_collapse_limit = 14;
set from_collapse_limit = 14;
In addition to disabling the nested loops does produce a viable plan, with
only the nested loop to generate the tijd table cross join as a basic part
down low... The original values for those were 12. It does
t data); I have
attached the plan that is made there too.
All databases that make a plan without the second nested loops also finish
the query within a reasonable time period (16 seconds on the .9.6 server).
On the 13 server with the nested loops plan the process times out after 2
hours.
As far
Frits Jalvingh writes:
> I have attached both plans, both made with set enable_nestloop = false in
> the attachments.
The reason why you're getting a nested loop is that the planner has no
other choice. The "tijd" table has no join conditions that would be
amenable to hash- or merge-joining it t
On Tue, Nov 17, 2020 at 04:58:45PM +0100, Frits Jalvingh wrote:
> Hi Justin, thanks for your help!
> I have attached both plans, both made with set enable_nestloop = false in
> the attachments.
> On the Postgresql 13 server work_mem is 64MB. It cannot really be higher
> there because Postgresql doe
nested loop on top, but the total cost is now:
GroupAggregate (cost=2005652.88..2005652.90 rows=370 width=68)
On Tue, Nov 17, 2020 at 5:08 PM Frits Jalvingh wrote:
> Ah, sorry, I forgot. I set "hash_mem_multiplier = 2", and after that to
> 20. It did had no effects on
Ah, sorry, I forgot. I set "hash_mem_multiplier = 2", and after that to 20.
It did had no effects on the nested loops.
On Tue, Nov 17, 2020 at 4:58 PM Frits Jalvingh wrote:
> Hi Justin, thanks for your help!
> I have attached both plans, both made with set enable_nestloop
tually wondered whether that
would be a cause of the issue, because as far as costs are concerned that
second nested loops only _increases_ the cost by 2 times...
Regards,
Frits
On Tue, Nov 17, 2020 at 3:20 PM Justin Pryzby wrote:
> On Tue, Nov 17, 2020 at 02:47:55PM +0100, Frits Jalvingh wro
On Tue, Nov 17, 2020 at 02:47:55PM +0100, Frits Jalvingh wrote:
> But lately while migrating to Postgres 13 (from 9.6) we found that Postgres
> does not (always) obey the enable_nestloop = false setting anymore: some
>
> The execution plan on Postgres 13.1:
Could you send the plans under pg13 and
th that at all (they are never
used by the planner because the conditions are never selective enough).
One problem we have with these queries is that Postgresql's planner often
badly underestimates the number of rows returned by query steps. It then
uses nested loops for merging parts because
On 02/02/2018 10:02 AM, Johan Fredriksson wrote:
> tor 2018-02-01 klockan 20:34 + skrev Johan Fredriksson:
>>> Johan Fredriksson writes:
Bad plan: https://explain.depesz.com/s/avtZ
Good plan: https://explain.depesz.com/s/SJSt
Any suggestions on how to make the planner make bet
tor 2018-02-01 klockan 20:34 + skrev Johan Fredriksson:
> > Johan Fredriksson writes:
> > > Bad plan: https://explain.depesz.com/s/avtZ
> > > Good plan: https://explain.depesz.com/s/SJSt
> > > Any suggestions on how to make the planner make better decisions
> > > for
> > > this query?
> >
> >
> Johan Fredriksson writes:
> > Bad plan: https://explain.depesz.com/s/avtZ
> > Good plan: https://explain.depesz.com/s/SJSt
> > Any suggestions on how to make the planner make better decisions for
> > this query?
>
> Core of the problem looks to be the misestimation here:
>
>Index Only
Johan Fredriksson writes:
> Bad plan: https://explain.depesz.com/s/avtZ
> Good plan: https://explain.depesz.com/s/SJSt
> Any suggestions on how to make the planner make better decisions for
> this query?
Core of the problem looks to be the misestimation here:
Index Only Scan using shredd
Hello!
I brought this issue up about two years ago but without getting any
real explanation or solution. The problem is that PostgreSQL does
really bad plans using nested loops. With "enable_nestloop = 0" the
same query is run about 20 times faster.
The sugested solution I got back t
Kumar, Virendra wrote:
> Can somebody help me avoid nested loops in below query:
> --
> ap_poc_db=# explain (analyze,buffers)
> ap_poc_db-# select site_id, account_id FROM ap.site_exposure se
> ap_poc_db-# WHERE se.portfolio_id=-1191836
> ap_poc_db-# AND EXI
Can somebody help me avoid nested loops in below query:
--
ap_poc_db=# explain (analyze,buffers)
ap_poc_db-# select site_id, account_id FROM ap.site_exposure se
ap_poc_db-# WHERE se.portfolio_id=-1191836
ap_poc_db-# AND EXISTS (select 1 from ap.catevent_flood_sc_split sp
where
16 matches
Mail list logo
