Re: Postgres using nested loops despite setting enable_nestloop to false

[Frits Jalvingh <jal@xxxxxx>]

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@xxxxxx> 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@xxxxxx> 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@xxxxxxxxxxxxx> 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