Re: Postgres using nested loops despite setting enable_nestloop to false

Ok, I set all those cost parameters:
# - Planner Cost Constants -

seq_page_cost = 0.0001 # measured on an arbitrary scale
random_page_cost = 0.0002
cpu_tuple_cost = 0.00001 # same scale as above
cpu_index_tuple_cost = 0.000005 # same scale as above
cpu_operator_cost = 0.0000025 # same scale as above
parallel_tuple_cost = 0.0001 # same scale as above
parallel_setup_cost = 1.0 # same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
effective_cache_size = 2GB

It still has the nested loop on top, but the total cost is now:
GroupAggregate (cost=20000005652.88..20000005652.90 rows=370 width=68)

On Tue, Nov 17, 2020 at 5:08 PM Frits Jalvingh <jal(at)etc(dot)to> wrote:

> 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 <jal(at)etc(dot)to> 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 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
>>>
>>>
>>>