Re: Postgres using nested loops despite setting enable_nestloop to false

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 does not control its use of memory, setting it
higher on this VM will cause the OOM killer to kill Postgresql for some
queries.
On the Postgres 9.6 server we have it way higher, at 5GB (this machine is a
monster with about 800GB of RAM).

I indeed saw too that the artificial cost for the nested join led to 2x
that amount. But that seems to be because there are actually 2 nested joins
in there: we use a cross join with a "time" table (which contains just some
28 rows) and that one always seems to need a nested loop (it is present
always). So I'm not too certain that that 2x disable_cost is from joins; it
seems to be from 2x the nested loop. And I actually 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 <pryzby(at)telsasoft(dot)com> wrote:

> 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 pg9.6 as attachments ?
>
> What is the setting of work_mem ?
>
> I see the cost is dominated by 2*disable_cost, but I wonder whether the I/O
> cost of hash joins now exceeds that. Maybe hash_mem_multiplier helps you?
>
> GroupAggregate (cost=20008853763.07..20008853776.02 rows=370 width=68)
>
>
> Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)),
>
>
>
>
> --
> Justin
>
>
>