Re: unstable query plan on pg 16,17,18 - Mailing list pgsql-performance

> On 24 Feb 2026, at 16:57, Andrei Lepikhov <lepihov@gmail.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
timeperiods 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
daysbetween 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
tiedto 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
thistable 5 day rental order has 5 rows. (this is stored on disk and maintained via trigger, because using
generate_serieswas too slow). 

There is a list of possible products/items (table_s), which stores the company's own portion of the inventory. The
otherportion 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
andare 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
rendersthe 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
intable_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
summarizedstates 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
onstock, when items are configured as "fulfilled from rental" but no rental order has been placed, or the rental order
hasnot the right amount of items, or when the required items are not returned as planned, or are returned damaged or
arelost. 
Because of resource limits, the flags are built only for quotes where the renting period includes a date between now
andnow+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
rowfor 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