Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: unstable query plan on pg 16,17,18
From: Attila Soki <atiware@gmx.net>
In-Reply-To: <0c8b9845-a2ac-4613-8075-45dc55911143@gmail.com>
Date: Tue, 24 Feb 2026 17:48:15 +0100
Cc: pgsql-performance@postgresql.org,
 Laurenz Albe <laurenz.albe@cybertec.at>
Content-Transfer-Encoding: quoted-printable
Message-Id: <45AA83DB-A6B4-43A2-879B-290E4A7845AE@gmx.net>
References: <ECA94B5A-E5B4-45F7-B1B4-9BF754083164@gmx.net>
 <e94e4d006975792d72e667bbd0499f9ddec4ed58.camel@cybertec.at>
 <64ddfe83-317c-4cf1-b04f-cb74d9f40629@gmail.com>
 <529E2365-6C3F-4BDA-9625-312F3A023C5B@gmx.net>
 <5bace3e5-3d6b-4f81-92a9-9ff4e9e5de53@gmail.com>
 <1C445A2F-3256-4F04-B55D-9850581FF39A@gmx.net>
 <0dd76506-053e-4a30-9f95-9dc324e8e1fb@gmail.com>
 <FF816253-DD7D-4AC2-8CD1-8A5ACB1D1113@gmx.net>
 <1388B912-7E9A-475A-93AA-67FB99A084BC@gmx.net>
 <0c8b9845-a2ac-4613-8075-45dc55911143@gmail.com>
To: Andrei Lepikhov <lepihov@gmail.com>
UI-OutboundReport: notjunk:1;M01:P0:r7QGHVf3IbA=;Imjqk5dnraFXMU9ZJ5Q/LSLvQ16
 syaqADmCy5gB4n6CnMYTSqhI+tdPALqwnhuorlq4OdhMQSc/kingZNLaCFSQd+FQqt8ebwkrK
 wdO/30RyclRd4uwaWLhuWCVozUZYEBPc3M2xaDGJNLE4BQ5vE27lpig5vLhCg+n06Cgw1YlSM
 0MOH3z8cGc8QMBhon5hl0TDhRnChbiUDa91iL6LXYmLCvCtw0toHnoTKrGkVf5uXMnoXdvELl
 8NbJ9BFR4toR1KZQtt2/Gvvfr+puZlT260jKClrlJQxRVmi7Kmvt8+Abq9zsqAz/X4v91YKhv
 9eLqZq7I1giPvadM0WLs4JTRgNzRQcotfl4ppdI49+9lTzTlSl6N7tplrN597rbEvGrt1Ggzi
 08+k545icM5DS4xGR4w+++XxSo/Vvw9pOP02nyd+JXDs8Cpzq6yMRekcl68Mlzz0nF/w3wqwT
 jLePYgBgRVfaCrWyQ51QsKOfwiiI2n0S+qdozDsStzmDl4W0eNv8loQ2v10EN3t/18GWvzk7J
 AFoH1dyhzDSZKtpkA/dP0E+nihW2WeOK62NiXIvuWcHno/m94XGU3q2hwCoqP3ewfUPd+DN2F
 9xCfdk5nDtJDoqMGunNtcxJ4af4BgCuUuLroycrz8aAuc/Cqky1sfSKHmyv5W1fyx2xV9mnTu
 iWiJ144sUYmvUO2kBm7PzuFvq3d1BiezSIa3rr8kLbved98dT9FVgLzqp9p13bAKpCmymtoEE
 t+IazFKNT9kownjlUwiTB74XYi1F+Npr8QmQIzVinK6ff3wYl+KvpL5QqeWNyRAo6aqNB0D38
 KEZi9HECL3oxgSugZ97uiY6sjYqF0KWlKzcrQlabWyilteSPVIiTc1jZ0SNIbA6spNlFDSmRj
 ITiTmpuZ4TImfwhhG0S6JF7PBcXL2MKX4NloQ+BpMNRG4Z/XoWii3gsQTfpr1LmTQ+amK5Kwb
 f/nqlpUrwT0w7r5egXqnEIhJhNCBQ7YXUXfDaxOGcA07M1kUydTbo3NwWmOqCoo0nWuumuRcG
 NQaVDVdoEQLdaplU4GNtOOHJElIkMYHkngPNd66y+qzgtgKM/8s+4vuYm/MBCskxI8fSv5lOO
 /Sa5vk1TGTEbJd2fzcRbXJ0MvZPvzC/CfXSznN8bhFFtK+oBVvPJGegYulc5bJDKS6DeAaHTE
 jD1P4O5KyJcTUsfov0ZomwvAOTPW0wwxUMocMb3X3mJdplln1tB5MhVw3Iiuo2V8W1NE9qNj7
 Dxty4iuKJEjGtnT6r2O80pcn7YP/Mtdxq2K0YKDkzRTMGglnca59oMdhIhfW4sFfnWckDPgsx
 RBtxAMG3MQ3dhLrwO69xzlCoAi1YcT4G+8cwgafie4o3HUradMu3AbtJH7DgpMZUqVGoQZkzU
 Hgk9s80InBIBPtjtXB8As2bh1LSn8jwLWKmq0W/M8Nf24M5PNWwLcpHzjKhPsgQSKywwCOeui
 H+pYEStm7hyPS/N6+NOasZFx5DD+xsHKXcV09WSAOFUkQxQdOMorho7dNE1HFrENtxND317+t
 Mx9ISbmwuBHL4tSObEoM8oxuaC8M2V4NlH9rCwNrWNhuSlV1oXD8N5CXTbN7L1hmFHwid2Ta3
 rTjllwq6O/uwHT+t5kWMxdYBGuN8Rp7tVkpM82MPD8cW71MvWue4ck/hhlhgXk+7yXHlElu+B
 wyfcbvdZQmmBYKuLt+Fkyy88r/KfSZx4Ty0dw3OwjRHo7uv/3Yh0qkucM56rVrLIINz8D/EEC
 KdT0cTRkl/wKJlLoISQAZxHCDt2/Bb6dmF0pyFvqQGz9dr5LUclOQWjty8/jusVU4skC09//o
 152gkFGxnidSP499Unz+OfaHmLlAG6HtZEjudb2gj+tg6lj+a1lJiz7EhtGkGwh4ShCm0akbV
 WveXvMzgKgwtI46vCmlDO+n42cqiMibZIYPHjELXHbGtBHZV/77hVg2yvo7UZs9q0V//1Erzv
 5op7Bm6lcZd/OrJC4uOtMJ0b5xO6vi6/lmrmAm0df1j/jXmPLDBLFv3dx4I+zXgWCEURRNyHD
 59Foqrj2kvqvTli8KNXyuNDRcT6lKhuNDobX3qpvnUXdAAMz/lk3Inz0DBzs17dqPlkZ07hsi
 1OcN6zQELIWhgC31VYZJ7GBta9MZR5RDmHs7v5QxHCH2SIOs+rKirAj2YkbkQ65r2kACihMm1
 rVV8kYkAQh56wboOzMt/xivzF6Guic2KxXL61C1ZzvbknrUndcVbQsZuytqPTAeHaLMej6LGw
 VZ6kMXddCdygFxXGwerRUsZ/v/Qw3BTghAn/yqmwC1V0YDJRYcefh7Tyz/5hJZUEzJnzdoO3D
 iOkdBSLgjuQHj3aUDPIonh7gldaU1H47jnxl6ie5TeNRh1Yg2WE1klkQ9MVef1VaArAx9hUI7
 tROfzt412FGXi8torbnUE/u7NKoOzPeyirQDdb0SPG/kWD8fi5WVPkIpF3dL1o7RN6D9T4GRp
 cF8gUR5iE8HfPrIfBEW0U2pGQUi6sK3gIci44etn8JKdW/SzpqkSE2nRnTB+Sm4G9GNwt2Vup
 XX+o+Jh1e13+KbUYevqF6HN0rMWEp2zhp1qpFTTnYZbn58QTpIF1iUPrEZWduZ2zS+/sp5Jd9
 cPgisW/9HQr+RgIl6G0M6558Jp756ayIeSNgaIVRIBvNW0YCEq/HI1i7ZhSkRlqCJXLfZ+MKJ
 twA64F7/XcE+ZpDHxYgZSeqh/oiJJL2Sha224r5YIKHgUBil9MiXNoafnu1r+gb4EdFgnA1np
 bW/xNsjSu1betx48pMbMfDq+P+RhBj9Ll98Ft2VZ2fS6qCVhEGHJXcVgDZA/tYbdXvETt03BI
 q13310qBJsCUnI9tw19iO47GyX4olIEjAKYjeXIBU7BXG+4+BvSdSXgDAFWmaRoKJYnBLsygf
 6D8TDkg0FiyIgVtepD0P6FyZuj93hI3zYUwgt8OmJZMwfHBrHRnxAHyDStkC5pZfL+zae6yn/
 Tz43LNn7GEDBq984P84ZZ2xhf2UhMqYHrk2WS/8ScdHNtYT5302XrCiqQgHRZMuYfyZaLaS8/
 IMrK/r+Cto0dsF3h/OF9Fc6tWWPTCrhX9WtuFnvC3rzmjZkRwYhP3/EIXRfPxwoN56eGGSz45
 qiZl70HD1iuiijXSZD+1SC13HV9iClL7g5ahtegM5i4WT/orsY6nUumjbYfdZBvzxrcWz5gnS
 DpbqhZPwx+VjXZuN4xlkZlAfxOeMmfld1NFNLF0KMhfKxOlBxtD12Bh7LrMLmaIshwKsiVGPX
 AHenuc62Od78dESfLQHhVub7hdp310/dVwRVxyG7R8DBt+286z7lcANgpicx9GEiNdZrcddAW
 or5GF+agGU9n8iArvOeBNNQa6ysW6Et1vJBLu2BiXZ3F/c0BhwNdBHqu3GJEVvdW0fzFH+HJM
 VcV/Wh1G8q4YhDGVHGGnlQI7uYnqkt3sX9BPfgAhOy//NM2EfSb8ex5SolQMOzr9XX7XpB1RF
 b88gzosJxvwXkly8Wcvd0hesxQoERAVFzIh5t1QzWjfL2+tR6igA1TX5/8ATQrXgW1YAM0ywM
 bIwNacRrRXAfK0w2Ab51yxxHugBbfm40+6ZwUHQnITp/0WmmyNUQpG7N9o8ErimlOscG1ntpD
 MBpwSmQlx6gdBHSjGO1/890hGCPSh8FNBDHihcISHvajE0QCVRwDSL8vXlXyyQUB8eeWOcYyB
 yNzcqi4/Nw5hOUNFknAYzHdTglOS5Dm5EQOnszrE5tLNnMljE1kcKcVRHtTR93javHy/YwRxw
 ZJltTIWHnLOz9lOaB6ga+lWP512AHR9VnCYD3a3WV986v9kWZi6C0TnEa8RLxih/rlGACXDVF
 SB9dItOvAz/Vw5zfiQCHt3om9fpejXXKF8kmniibJxCjeDmmapfL7vTcyD0vd+k5NuACrVwm4
 wIBisWuAhNL71VWL24FUHOv630ccG9wliHY84VhRdvvutC8aHlw10mQ/RreSjJq+omFBe+teM
 N71f/PbUBHWqEc7PG585L9vAAgmE5eiRZYPZr/7Ii0tf7XmCRWsNXoFtYocEGWN7pqnnMslja
 Y6OkFUC445vnXh6BM5PDV+3rPOI9jFEAHKsCR9a44cUaCbs0OvTJffva5vcBI//40olMMzucU
 K9ZVk8A+cHppf4++wKYGaWe3HG/aKVp9ey1JDpgIBu9YAg5TxBkujrx9gAWkHEtSjwbtHjJ+p
 7XK4R1e0EmWDI8oicLoHIVR1NuW3aIpQ5aN01sdzCCid6g4amgQau/uCVo3eLKCcRMPPmEMxZ
 7P3c6FBkcjEaG7t7dJDEkggi8zakckTpVOurnWC2bSzkvJkkbiXuXV5oJueaYTmS210jhOvh7
 rgW+JeVmFg/LmBvLAYTuHsdqpMVOnN8lFfwH8UprTNZKCSW3Iy7rl9KDakz3HFIayKU87AkM8
 ZO+7NhGEKz2tZGZ7Yp2bKUx5f2PscQFbGATZpYFivXcSczG5ILQt0YxaYbcvRRM6RFghHpQ9e
 jLct0g3h5NzUHKUHHXTMivpbMlxZsp6Lm9eg/alEGLnYX/DvN0BM31SGd43lfqdMUF/zzQmPs
 WH3drL/OzOiXsBfB+a3DQr1tS5lc8lrcf4ShwOFsLw9qCEP4AahQ+CxipaLi5Kei2zYqjPXR6
 yjd4lKfsW2Qh71Rtp+h5bp6rGm5RVAJqeyzXGh1cc+SZ5e1eATXLTC80HaRiwu38hmW8HDFvW
 MRZaUG0zynOmpmMBzar3W8dY9bUhOW1eUXcWcgJykI4Bhg3yndg7O13RtCN31DNj0TupUD7wG
 k8+ROvXbH1b1UUxQ8JW3bU8mg0vTm/SJQDQ4YyFGy6u5fI65iMMviihdFQyAvQC8iQMFAFud/
 UWjGggHiPe3zi+YChmakSdJG7M2sQZBj7Mbpsds1cThkQTqEZjKJ+ff6sxW/fSHOP5dJB6H51
 aeXknm8Fppj37xsIg933HVbvXcHzpr1uZ871zEHiMvnR4RWxkFHI+lP5RhmjHjfGMDMosZ89C
 WNZzFItHFzNCBZuiaN1VROuZRILYTT3KmH3n4a4Nv7h4kAkvPY7uipEH/twNt2NjrGu7iHDdl
 1AhxKXJvjOa7CwVOfVGeUF7vSxSaFYxxYS7vX4x2pcyQmWSf8JqS2e5XwSQRNbASHDe2tqZSz
 DR2JsXn7fMDRWRIE4=
Archived-At: 
 <https://www.postgresql.org/message-id/45AA83DB-A6B4-43A2-879B-290E4A7845AE%40gmx.net>
Precedence: bulk

> On 24 Feb 2026, at 16:57, Andrei Lepikhov <lepihov@gmail.com> wrote:
>=20
> On 24/2/26 16:50, Attila Soki wrote:
>> Now with join_collapse_limit=3D7 works for me and I am not able to =
flip the plan. makes that sense?
>=20
> It is almost a game of chance. But if it solves your problem - why not =
;)?
>=20
>> should I still test with increased statistic on table_k.dp_end_dat as =
Laurenz suggested?
>=20
> 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?

>=20
>> 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=3Dgdt.au_id -- one row per order
left join auf_oos goftr on goftr.au_id=3Dgauf.id -- one row per quote
left join table_k gkal on gkal.oo_id=3Dgofrt.id -- one row per quote =
item
where gdt.datum >=3D ('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