> On 24 Feb 2026, at 16:57, Andrei Lepikhov <[email protected]> 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
