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 seem
Hello Tom, thanks for your help!
I understand that the "time" table cross join needs a nested loop. Indeed
that nested loop is present in all plans generated.
But it is the _second_ (topmost) nested loop that is the issue. Once the
time table has been joined it should be possible to do something e
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
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.1# same scale as above
cpu_index_tuple_cost = 0.05 # same scale as above
c
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 = false in
> the attach
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 t
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
Hi list,
We have an application that generates SQL statements that are then executed
on a postgresql database. The statements are always "bulk" type statements:
they always return a relatively large amount of data, and have only a few
not very selective filter expressions. They do contain a terrib
