Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vucl2-00GLkt-2M for pgsql-performance@arkaria.postgresql.org; Mon, 23 Feb 2026 20:42:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vucl1-00FIiN-1M for pgsql-performance@arkaria.postgresql.org; Mon, 23 Feb 2026 20:42:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vucl0-00FIiE-2l for pgsql-performance@lists.postgresql.org; Mon, 23 Feb 2026 20:42:19 +0000 Received: from mout.gmx.net ([212.227.17.20]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vuckx-00000000qvf-08OT for pgsql-performance@postgresql.org; Mon, 23 Feb 2026 20:42:17 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1771879334; x=1772484134; i=atiware@gmx.net; bh=WlsrN6VYYBY3vEDNHFJ5wDdANQiRRUpQfjNdUTrOaKY=; h=X-UI-Sender-Class:From:Message-Id:Content-Type:Mime-Version: Subject:Date:In-Reply-To:Cc:To:References:cc: content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=rATHgq9HsUKZcApuHScf2nJ9F+5c2lY2W+6DGMNUgm+Yh1OuZQ8Wveao3DT+YwgH ljBf7SSe+SDKhEoPG50qgN9MGsM/7i2ZcY6J1FIFO5psqwd8YROkI8hgs2HQ2a5rE 6RRlgjMWlGDsh58APo0ajmkPbtr01HkX0d2e6pqS+97HWiyUU+4CSPLj2BQnTf95/ QbZvGZQ4aj9zBuTfPZTVAyEv5s+18j8gWW2HcbCMjOrYC6T7ZBBLMAU7KKpAerUR0 gpMS0WgKH7/jtxxmV5N5UawbD1/nV4qByIZTmyQIvD8a+Odr3tIYUDH8+mK8SObTr uLOVZSUyetOS5KyDYA== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from smtpclient.apple ([144.2.66.138]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MMobU-1wBCEb3CsO-00TQFO; Mon, 23 Feb 2026 21:42:13 +0100 From: Attila Soki Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_74684E40-F976-4749-80C8-D08C7F87ECBA" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\)) Subject: Re: unstable query plan on pg 16,17,18 Date: Mon, 23 Feb 2026 21:42:03 +0100 In-Reply-To: Cc: pgsql-performance@postgresql.org To: Laurenz Albe References: <1695A676-062B-47C5-B302-91E2357DC874@gmx.net> X-Mailer: Apple Mail (2.3864.400.21) X-Provags-ID: V03:K1:VAbHNq9Z/OhGPpD1yQ+PDmrJc7V2kgIz4pInacVymdUb2ne3Kw6 /jpLsVjAKV4YENYhaCjY8WUDX3t3MJ4DOoqIZmedusm+WmaX+6GFKyw5IEeUIvc5bK9lbKF 7FLRlgZqzCuN3/+p6Kdq0yFb16QQ9k/wlwuB7hO4YSWstoyMw6qiSAxnlqCpM1ox5sX/DBq ijJG6Gbaz8a+L4/juK27Q== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:2BcHjCsq7rI=;KsRKM6wt/6wP0PA2K8oe7GYhimy 9pN5Ia1nNk+tmipNoYlIbuBqV0yGGt6Kxh5UcAopZr2Yh3ybBZl0VVNbGsSRt0fQYdjn8HB9l sa+4GIirLvPGfd6/xVJWHRdZoDNUOFUtYLhOkkPimZ14UQWhG6ID2jWq7l6iMkQxLihmqKjUP nZwwvpg1BXjj8Fhs7ywQcftcC34K7+xipJON2CLmNFXvdE9fOw+3t3tmqVdoAh8DfbqISA9OM X442jRWjuES5DcLbZMOHa5fmGfV7TLV41qLrWVUk5RSHOBicdG9fWQyPYWBfrW5x/rCMmnwKy 7YQCvfuAvpD9e5Q/AsZ3K7kNbBXrI9+T4NN8fDrvMK9GyEJSkgmnp11L1jP4IFWzVr7Lobkm0 shRIYByhMW9s/e3F8cM4SWHlUr9GEkrs5/J0ZsXM6ygusiQoHMR0QB7ByfOaBo2/kFWb1E8wU gRSUbcEiJgqLpHb5hYJMlaEnbsLv3jUoxCBqLNQlc0w5d1SQZyxU03mtEl4//KGFa1wdfX+HK 4vKEPS3xIKYLneI1Lgnf5nHM1Dzcc+xZhdfJINInHY8N/LIDjHR5/tcDXawINoOP/DFb4NvXC uqPwtP4RUN5YhZO1iSlKO8uA28ecfnAVCnlU+/xzsmRQnqL8qZvw+g3HQHQEr3BroKvzVLXdX CTsrMsF8R/1ahdi0qedEQxtD0wHyjEzisdabCm8/eE5kHF5dvOSbt0qTRUh1zgOQv9FA7hced XUOLLhBkNGx+e1FvBE6W0H5S7RUi90rnlpVDYPA6txadUghsInSk0uKFDWm5OSFz2LdchET2I Ad5Z9799EImzGzH1JMt9Z19lkBYHt7oyu/PrrxHbeFI8qdNgkdtlKVJKqyqQh0G3llcX1amMg 6tNAQskTKIgjWfCdxv5GNZIqirq4p+qzc2yx/fLJ2g2zXR6HdzEFGLbuo0Tbcpz0C4oRCPJt/ ISgRRJ1wllsOI/kDpr9BUQBEvOB31FDudVYFWnt/gDTI/0Cz1U820sPgPjmTtegxxfXbT/QSH 6Mo+mqT/pTDz6BeJR7NojqmCjOEiv4x4keaNwIjGnGER9U+Hg8cY/eTjxRGcwyPjYkJmiHGtn wiaKShH4K+FMfqE7ZeEcf268XGR48tiRymj8BNHZUE2LboQP8PV7HyW37A1+Wity3u4LFFtkE ud7oA1k0nefWrqYMqg9D4ca0NEDbNI8b3742CiTuZjpZGdc9vBTfZ7evTdVEZ99HjslEIKZVI 4m9WmM3V+TSHD8g3zCu5hmI1Cnz9skLPMQ7AdoIjWh1vdOSX0xC1hZ6LDMlCfJ3aCGCewVxqA P1CTZ4Vvir7JMmDxiyEAqDPIt4TCnF852ZEfNiOyXz+CSUK0RatPlxJEb6bOB0egv3x+9TLex GaPG9TmHr4lOVmy6V3F3Y+Lif/Twfcl034+3xLg460fFwFD8D25r1WPBKZX9NJ4GcKEKcl197 ri5zM3paXOdbIcuPvfQTAPKdqVk86aPkiyLh0ZeRf8x2lu2GyoX49UUijMsQRW8HQSEM0BlSo tZXDfiul51fOZ/+oQCuMV5tQgEDog5FHFH1haYEUd8XuDgmWqoCSJ9ilvtCgjUOVrg4qyVRNj 6fpALt3noG23xlhqXwrY3pqc0LBekqz8HYNfNp9J0iAXBmSbkdNj0vC8USc/Kyf7GyN1q0qMy 1jlZhsnLIJ912fFibjtpt7ElFNCedtpMfwi6sTo1+jXlbxv1FQLtaLIertSxSEW1q/tJ4XZZ7 574WdwSD+dNlXGYYhVcQ1+AO5oLo/W7pWHSu0e+eYsAgka1T2gC5Un+18TqDY1JLoH/WDALxF U0tpXuA1ps9yiNSkAkDpp8oxF6zpKRkDgd4dcPmSEusHwiNpe/zXvFLOy2LEx1SEI4Tr1DQdp na/Te1Z6PUBQhPYbQmZvsOEmlcMTL/ohmmr9Ft7sZzw8nJv7OSjoOwQf3y10QzbHTvHqZWfFj t5apJW8pzUTVvri1AMc5VS/3s32sWsrtzpWcKIOn9/cA1HmUIrJyKTirKh/S7y4Js9ma9Jb30 pi7NB7HHZEiaD3OqwHgdVXMSZOHwaibnCILuoJqpPy7dLET3B3Y5dDT+greebTf6g5C3YwhU+ LaGN/9E7bOtjePieyxOYwOrEd2n6LyFnLOGIK8MV86+Lz1vUeROkrDXEQg3mIy/Fn+T2umMv/ kbNV/vBy0TqaeF7e6dx8Htkvy7okOwbZYoazSNYB4mOpSSHzghC6QoE5ziFy8e7FWccNdaj6F wDecc/1UYU0iudoEPUN5CX+7V7Jd10JZNq54QX5HkuDGUYQhOmklo6cgIB5NOY+J19awNgc2E Tlfn2727OwXiyeinSre5D0LRmhYen/7vNPZni8W2iZa+mtX2WlGXfbNcATtrFwufk1mcfn0Rf Oaj7/yUHjxF166ZcN+RdYpbCG2SnnSuPGI8jsv4tjxritmNJu/K0wKlKhU2yzAkLyS+hRhoSX ZayaOdlZrg/1ewd7pWTlAUNhoW5WyhdJqF3oohavJ3XYlPUmuSwik2AgmCxyUmjv4uFMk5cG6 yEYS6g5z41jul0VNz2n6FIBtvUCc6apOiRtXNtvyMgY9k8Q7kG8vmgUAHOHYvFToAaOQpOh7Y 4S65hTfzMboRifLBXduonOShuRgqaxRk0x+RTLSWVwM03Nv5gJZLIpOQ/z23C3EciIFNLYu3p 0ct3apffLkNmBelsjDen1Jpu72OmCSZIdvVa7fPEvp5B6YlUnE5sWTD/UXGaY4lmrT/awh81g ZuYQZp/ZXtc18rBdKpNhtW/SjHwJYtQbGJsY4riSKmjzc0t42+yqnApVeOdwaDzXjOlsF5UK+ qPmdzYKCKVJh9oz4881AT7H0fRgi8vj90zg8c1wmruBKrjDexaumUnk70SULB75clCbxIcaTa 8q4DrEc/eVvvyqBcHKrJfOM4Hqy9YWiB+fqaFkYCsID4EPVwLk9VvsENDa5ioQjEuLcaW3imE X7PNLtmH6vwf6Z2ypXbQQO0wX+F9vqwgp0NPSSixQGbz/dOJNYW6U8ji8n5sr0uTioleb/zjG 0D77/ST53h4e8sxzsME/oWaJXVWMJRdg31zIOQz1IY5rgJBNwZ9IbXp69Xm+o1JP/jvJBJJEJ yvCW7DQcbYNhg5axCWkLydHiQzg+z1IkumYfAzDLEDEijkONSq4OCYwV+9dYIHcWXu2fspHyp BtwOKWgQ8Bu6AQVA9GRqfJPP3l3g+P+c2ILgiuOYuAB0TdZ6uA9vt+NLqxhGOSAanLINRE8NI hAh5nplWq9Px+YKPNuEYJpVOlczn9dToMxvqme8oeYjt6fPy5ujA5wVDLFEug9xh0ij5Bb7+L 1vypi7eJgYpN5I4QZ2ClwbV11PaLTvSZJJKHt3iFgEN87ccdfBdzEQuu2Pqec4huWQWSl9xnc mqwm0VREuGF7j2vHyRYiYLxw+TYYFmv8NZX0KB2ZI//v207oiV87FZB49agx2j+Cjpway2pw6 Dp9PSua1NdsGH4oTbtJt8XGLQWENKryjL6vnE2F3+ZKibQJFG/I6pLWwpr4OQxcsYYmIFthqz TQkCZoh0yte0gMq7mZMrDzsRmKGH4fFZx0cc0/Y4A1bWCR6X53B5J3ZmQGG9J8Dz0luwONT7c qj3GswzN9JcQo56ZWkLoiwSsQF7MHZpzQrjHpFjj5w8kl3TDcmCTTFAn2pcSt3Hq7I3puelAa 1CW6ozUoxWoJGhLNsxS3lhBKJoyoFdsw/ZeM+yInsbtPs3LLR3c1IM8HBff2+Wj2j4ePE6opO N1qhuhYUIDkKd40ha+/N68qQ/CHdXFhtKlpHP/+NTsRddzCFWLVg0PEA8KjsmHvRW8ZQhJzrQ 5h+UN2yNnF+0PJGtyqaWCmRxJoVH7D3WyT3izoFToouTWYZxIAmjVAvdNDkuocPYiK7bqQP9B VoabZh0PO7iAhRyIJwRAwfNFpBeJ6TI/pKZPy+rfxD0ew9fNEPWNovvHk9fCcyeMYsmXDoWD/ 1g3XGPQmuGtxO4TTXHixtH5VGydgs8vWCjaBsLLf7S4HtNIPvPG31oia+pHaDUSkpsqiYQkFS Wit4mNDUFBGf3t6EZ+zywmAVOlajkmfaObVymJdinoMCpSKtKXn7gaa0I7d1CYNcot3l3iV5q Mi/+KRGPSHLypQlXPmg1MvnzS+qWgdCiwWRECCLB5Gq9N5BwT5CVHy/kvLD/xyjlnXNff9xyE KLzDA+WshaPj1BlLyMB5R4/A2a3vRWIEC3/L+ujG+uFw14bMcpEyQZez9gTwlZDUb6oml/xeN CBHgnvkDyPnRsdRmDuvTPwITfRApllaTFxxq1n0uEeyUqHTpun+wbIkMNODixLuQd7/gAJ2Xq xu6yXfEO5/tC1MJACzU15ziLzntyL3/eQo3mQeqhlGHafG2dFaQ38AxBuoG9t3ZJGfQdBLpZn DhICP5+B5JeHb6Che3EmU+cMjWuD9OgpbW51yEgp+lkgHrABMNrnDMWgIbEq2M+xBw7K2cE+h oCb0vOehKla6+LPzj5/vQuedrIilA+5emkzzaYFSSjFb2A71LSxf8yasuCNjMUfR4w5T71CwD h3XPRsxhOqe5fNqa8HGaAnOJ5MJtXW2zI0C+pEBvOqPISyme7EcMy5PehyWkC/IZb0083S99E pNsl6G3ipSv7Xx9k9il/NjRLFdKYh1MO0De5K1IP5yS28d+d7SbZXMjN8w0cDkFt3xdIgIeWg J+fZ77PMWhZh3+JDm0O5zhKrKLD7Dz4byBT3/QMLU98av7YS30ZOEHJo0prYBRvbEo0g9b80A OuV7sBkbaWsxsPzelbO+UCdUAVkSvUzr6KU7qkRAsG6EyX/2SMHh4X0y9jZ6UGrjptWOgrz8o 98yeGDfoD9TsC/D1QEZMpOrN53Q9UH0G/EljcEMlRaLZRpLguAnQAHOI3ESHrN5wO8sbE9bBa 61eXlofsXE2Mt628fRFD/9Dzf+kT5NWtF7Nve4nh/GfXICcCeq6VL6fUuKS8xmjvXbIaqO19w pRfhywAHKp1P+R55mFMXwtGEZNrYQ4p12+2tv8nVc07MS83pATfKUc9TxWh0pMC6NV4+Y26/d rkLgqiVuQh1WtZm/FVRVMLLxHGAQrqRlUX8PA1ErIB6tkC5RyX5LKyM2tWiLQ0d7KwNf+FWsk vtyLnSHVTs1Od+vrNEPVm+u+s+MNvz59YL3Qx4YLESWlXgZjhl9Bqia/r5lcMp1wzMymHCSE2 e3erYLhdUh6Nc1Z5/HZ5TzOhutYH+R/eqjuP9Qcmul1Rv5hL+fat8TQnbS7sjWzELXUqIHtIH y3J9FbWOaMvItKdTSk1ofUBxTtWCIvR2syE8P+gYzFAsURoXWOONnOamQvLNqWDkQdDHQci4b q93aw4bF94H8m//ZPPHHYfZ3lgJPvzGncCt8O6NLgs9rroKPEqaLCeZNsKUoSH/bVRkKgoCN1 jaYhr1EIUVtbPfqEZQoMLZ+5RHsxBSCz9TMq6rvCvx9ZM= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_74684E40-F976-4749-80C8-D08C7F87ECBA Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 23 Feb 2026, at 20:59, Laurenz Albe = wrote: >=20 > On Mon, 2026-02-23 at 16:10 +0100, Attila Soki wrote: >>> On 23 Feb 2026, at 10:41, Laurenz Albe = wrote: >>>=20 >>> On Mon, 2026-02-23 at 10:37 +0100, Attila Soki wrote: >>>> When upgrading from PostgreSQL 14.4, I noticed that one of my = somewhat complex >>>> analytical queries sometimes gets an inefficient plan under = PostgreSQL 16, 17, and 18. >>>> Under 14.4, the query runs with a stable plan and completes in 19 = to 22 seconds. >>>> In newer versions, the plan seems to be unstable, sometimes the = query completes >>>> in 17 to 20 seconds, sometimes it runs for 5 to 18 minutes with the = inefficient plan. >>>> This also happens even if the data is not significantly changed. >>>=20 >>> This is very likely owing to a bad estimate. >>>=20 >>> Could you turn on "track_io_timing" and send us the EXPLAIN = (ANALYZE, BUFFERS) output >>> for both the good and the bad plan? >>=20 >> Thank you for your reply. Here are the two explains. >> In order to be able to publish the plans here, I have obfuscated the = table and field names, but this is reversible, so I can provide more = info if needed. >>=20 >> plan-ok: >> https://explain.depesz.com/s/hQvM >>=20 >> plan-wrong: >> https://explain.depesz.com/s/uLvl >=20 > Thanks. >=20 > The difference in the plans is under the "Subquery Scan on odg", = starting with > plan node 50 (everything under the "Sort"). I suspect that the = mis-estimate > that is at the root of the problem is here: >=20 > -> Index Scan using table_k_late_spec_dp_end_dat_key on = schema1.table_k kal (... rows=3D196053 ...) (... rows=3D471.00 ...) > Index Cond: (kal.dp_end_dat < ('now'::cstring)::date) > Index Searches: 1 > Buffers: shared hit=3D230 read=3D49 > I/O Timings: shared read=3D0.142 >=20 > PostgreSQL overestimates the row count by a factor of over 400. > Try to fix that estimate and see if that gets PostgreSQL to do the = right thing. >=20 > Perhaps a simple ANALYZE on the table can do the trick. In the examples I used table_k to flip the plan with vacuumed -Upostgres -vZ -t schema1.tbl_used_in_query db1 in the explain output schema1.tbl_used_in_query is table_k > The right side of the comparison looks awkward, as if you wrote = 'now'::text::date > My experiments show that PostgreSQL v18 estimates well even with such = a weird > condition, but perhaps if you write "current_date" instead, you'd get = better results. I didn't realize that made a difference. I will replace all occurrences. = It also looks more clean with current_date. >=20 > I'd play just with a query like >=20 > EXPLAIN (ANALYZE) > SELECT * FROM schema1.table_k AS kal > WHERE dp_end_dat < current_date; >=20 > until I get a good estimate. I will try to set custom statistics for dp_end_dat and the fields used = by the table_k_late_spec_dp_end_dat_key index. Let=E2=80=99s see if that helps. I am on UTC+1. I will try all of this tomorrow and get back to you with = the results later. Thank you regards, Attila --Apple-Mail=_74684E40-F976-4749-80C8-D08C7F87ECBA Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On = 23 Feb 2026, at 20:59, Laurenz Albe <laurenz.albe@cybertec.at> = wrote:

On Mon, = 2026-02-23 at 16:10 +0100, Attila Soki wrote:
On 23 Feb 2026, at 10:41, Laurenz Albe = <laurenz.albe@cybertec.at> wrote:

On Mon, 2026-02-23 at = 10:37 +0100, Attila Soki wrote:
When = upgrading from PostgreSQL 14.4, I noticed that one of my somewhat = complex
analytical queries sometimes gets an inefficient plan under = PostgreSQL 16, 17, and 18.
Under 14.4, the query runs with a stable = plan and completes in 19 to 22 seconds.
In newer versions, the plan = seems to be unstable, sometimes the query completes
in 17 to 20 = seconds, sometimes it runs for 5 to 18 minutes with the inefficient = plan.
This also happens even if the data is not significantly = changed.

This is very likely owing to a bad = estimate.

Could you turn on "track_io_timing" and send us the = EXPLAIN (ANALYZE, BUFFERS) output
for both the good and the bad = plan?

Thank you for your reply. Here are the two = explains.
In order to be able to publish the plans here, I have = obfuscated the table and field names, but this is reversible, so I can = provide more info if needed.

plan-ok:
https://explain.depesz.com/s/hQ= vM

plan-wrong:
https://explain.depesz.com/s/uL= vl

Thanks.

The difference in the plans is under the = "Subquery Scan on odg", starting with
plan node 50 (everything under the "Sort"). =  I suspect that the mis-estimate
that is at the root of the problem is = here:

->  Index Scan using = table_k_late_spec_dp_end_dat_key on schema1.table_k kal  (... = rows=3D196053 ...) (... rows=3D471.00 ...)
     Index Cond: = (kal.dp_end_dat < ('now'::cstring)::date)
     Index = Searches: 1
     Buffers: = shared hit=3D230 read=3D49
     I/O Timings: = shared read=3D0.142

PostgreSQL overestimates the row count by a = factor of over 400.
Try to fix that estimate and see if that = gets PostgreSQL to do the right thing.

Perhaps a simple ANALYZE on the table can = do the trick.

In = the examples I used table_k to flip the plan with
vacuumed = -Upostgres -vZ -t schema1.tbl_used_in_query db1
in the explain = output schema1.tbl_used_in_query is = table_k

The right side of the comparison looks = awkward, as if you wrote 'now'::text::date
My experiments show that PostgreSQL v18 = estimates well even with such a weird
condition, but perhaps if you write = "current_date" instead, you'd get better results.

I didn't realize that made a = difference. I will replace all occurrences. It also looks more clean = with current_date.


I'd play just with a query like

 EXPLAIN (ANALYZE)
 SELECT * FROM schema1.table_k AS = kal
 WHERE dp_end_dat < = current_date;

until I get a good estimate.

I will try to set custom = statistics for dp_end_dat and the fields used by = the table_k_late_spec_dp_end_dat_key index.
Let=E2=80=99s = see if that helps.

I am on UTC+1. I will try = all of this tomorrow and get back to you with the results = later.

Thank = you

regards,
Attila


= --Apple-Mail=_74684E40-F976-4749-80C8-D08C7F87ECBA--