Re: unstable query plan on pg 16,17,18

> On 24 Feb 2026, at 16:57, Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
>
> On 24/2/26 16:50, Attila Soki wrote:
>> Now with join_collapse_limit=7 works for me and I am not able to flip the plan. makes that sense?
>
> It is almost a game of chance. But if it solves your problem - why not ;)?
>
>> should I still test with increased statistic on table_k.dp_end_dat as Laurenz suggested?
>
> Yes, it may provide us additional info for developing.
ok i try that and come with more info.
Should I set join_collapse_limit back to default for this test?

>
>> I could now share some general infos about the query, if you still interested.
> Yes, we still have a problem. So, any additional info that can let us build a repro is appreciated.

The query involves many tables, views, functions and data. Maybe we can build something simpler to reproduce the problem.

I still checking whether it is okay to share the SQL. Until then, here are some general details.
It is basically a per order report on the availability of the inventory for a rental service.
Striped down, it builds an in memory calendar and calculates some sums for overlapping time periods, where some of the time periods has no end. (delayed return: late)

There are projects (table_e), the project has several orders (table_a). The order has a renting period, measured in days between start and end.
The period is not limited but somewhere between 1 - 365, usually between 5-15 days.
An order can have multiple quotes (auf_oos), but only one of them is active at a time.
A quote lists multiple items (table_k). Usually 10-500 items per quote, where an item can have one or more accessories tied to it.
This list is the customer's order.

There is also a helper table that stores one row for each day of the order: (table_a_dtg) table_a.id and a date. In this table 5 day rental order has 5 rows. (this is stored on disk and maintained via trigger, because using generate_series was too slow).

There is a list of possible products/items (table_s), which stores the company's own portion of the inventory. The other portion of the inventory can be temporarily held (rented long-term) and is stored in (al_ast).
Another portion of the inventory may come from other suppliers as needed. These stock items are rented for the order and are tied to it. The rent-order is located in al_zm, and the rent-order's item list is in al_zm_kal.

In addition, there are items that were not returned on time, were lost, or are temporarily blocked due to repairs. This renders the stock level of a future day-x not predictable.

The items required for a quote are manually allocated from one of the three possible sources. This allocation is stored in table_k.
The info that the rented items are fully or partly returned is also stored in table_k.

The primary function of this query is to list one row per quote, with multiple flags indicating the various possible summarized states of the quote. It also computes the total volume and weight of the order.
Some of the flags indicates various working phases or possible conflict states.
For example when inventory levels are insufficient, configured as "fulfilled from stock" but there is not enough items on stock, when items are configured as "fulfilled from rental" but no rental order has been placed, or the rental order has not the right amount of items, or when the required items are not returned as planned, or are returned damaged or are lost.
Because of resource limits, the flags are built only for quotes where the renting period includes a date between now and now+3 months.

To achieve this, the query builds a stock availability report for all items of the quotes on the fly for each item/day.

gauf_1 is table_a in a view is where an item stored in table_k will be combined with the rental period and so gets one row for each day for the rental period. This row is representing the required stock quantity per day for that and order.
These rows building the base of this query and will be combined to build the daily overall demand and state.
the view is simple:

select
...
from table_a_dtg gdt -- one row per order_rental_date, eg. 5 rows for a 5 days rental
left join table_a gauf on gauf.id=gdt.au_id -- one row per order
left join auf_oos goftr on goftr.au_id=gauf.id -- one row per quote
left join table_k gkal on gkal.oo_id=gofrt.id -- one row per quote item
where gdt.datum >= ('now'::text::date - '7 days'::interval)::date;

eg. a 3 day rental order with 2x1 items gets 6 rows
order1, item1, day1, 1 pcs
order1, item1, day2, 1 pcs
order1, item1, day3, 1 pcs
order1, item2, day1, 1 pcs
order1, item2, day2, 1 pcs
order1, item2, day3, 1 pcs

I can provide more details on other parts of the query too, if that helps.

regards,
Attila