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 1vuZLb-00Dx8j-1d for pgsql-performance@arkaria.postgresql.org; Mon, 23 Feb 2026 17:03:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vuZLa-00ELiu-13 for pgsql-performance@arkaria.postgresql.org; Mon, 23 Feb 2026 17:03:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vuZLZ-00ELil-3A for pgsql-performance@lists.postgresql.org; Mon, 23 Feb 2026 17:03:50 +0000 Received: from mout.gmx.net ([212.227.17.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vuZLW-00000000tfq-2u1l for pgsql-performance@postgresql.org; Mon, 23 Feb 2026 17:03:49 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1771866225; x=1772471025; i=atiware@gmx.net; bh=9hHoWE4V8ACoM6Pv/bufrj4SkttaTW3JOqBov24aYXo=; h=X-UI-Sender-Class:From:Content-Type:Mime-Version:Subject:Date: References:To:In-Reply-To:Message-Id:cc:content-transfer-encoding: content-type:date:from:message-id:mime-version:reply-to:subject: to; b=Yz0HnzMaPWonpkn9H8hHQTMkl7nDuWoily+YWO9m0yedDBF6paFqWfOgdooD11MO eroY+N62w14Q+jmcI1seZVig/w3FPZFBhj0KGYX8zsFsIX76EYhQ79K7MiwyIYweh qVzNnDR1V7nlqObR1tki1MSmTcuD0HdN3Hbou5ozjxEdJ6wnaHpL76GBLVOYdu9Ou zby1RHh8XL0JXo9y4buPDHrpo9pHhaBxrfSmEpf6acF8OdLxfC1mBkvJm8acrcT+f RvwrWikxJWeSZF51lTRnz6iPCvMRd/d5kRdpYNHii9GG7AC9eWHpIdCvUdKzNY6jR 9y/y85x9Dqr6Wbk+QQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from smtpclient.apple ([178.197.223.46]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1M89L1-1vr0yI1ArT-00GagQ; Mon, 23 Feb 2026 18:03:45 +0100 From: Attila Soki Content-Type: multipart/alternative; boundary="Apple-Mail=_397D6208-FAA4-4B4A-9752-570294930A49" 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 18:03:33 +0100 References: <64ddfe83-317c-4cf1-b04f-cb74d9f40629@gmail.com> To: Andrei Lepikhov , pgsql-performance@postgresql.org In-Reply-To: <64ddfe83-317c-4cf1-b04f-cb74d9f40629@gmail.com> Message-Id: <529E2365-6C3F-4BDA-9625-312F3A023C5B@gmx.net> X-Mailer: Apple Mail (2.3864.400.21) X-Provags-ID: V03:K1:4H4e5fOed72WK8GUQDLUXn1No/KtmOesZsH/iWOtbo/eXik8JcH 1asTeynVbug/wUniuRN88VLchllJbMA/7Gvk6Bz4R0BDx9IuJfmuKWvuv4inEZuXF9cjDu/ U8J7mMKI9bfpa1fnmIbFo0Tht2brfmWimo78m5o/qxkQzT0nYbzAX7nmMTklEHtMBMqvweh EmbNRdmBZveS7FFZOD+8Q== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:5ckdu5svjlc=;ruTUn91wr1IJ6JGr20N4iGJuRpT 5t3NrMqky7JNbaPDUhPzy7q2lGWkDZSbOarzKNc51ht8mkG6BZeUjtNkBgz9hMdQ1SqtaLsTp RNsxnH58mV5ySNR+mkzjxu/uNeYXzFG1KCNBCXcOKcFv2vopAjKZ6jIbRnsiIH098ztSKdVLw Y2iQsIP+UoFofIC9qlycHmpcrsX19efZBEFCd79l+xryRcXBwoCehWg3fOLt4euey0FpwP9k3 D1LCBkBk8dbopG4mCQcXOUwJQdvjgfDvm5lr+qhvrHnj9yXWBqm89t+S3cLSGu90kstw0lY0a q0YlhNV7BpxDJw//yRpA8pYlFZcndNIjBjx3UlQ5mnt4bpQyl/ftV8fpbqBoourEF2MR0bh82 lXHXCz1No+96W3ZtrcUw+7xttWdkVqkxnZVH7sSi8IV8HLgKTIwDsWjqDJv7rV152T5iq5aC2 1A0th/rRLTQFb3C2QMuu/7U+qWu5DJDjRs+NbCZ4ODiQf+LzAjDW4P6vS6BkfO5ogufRqtoAp xsfQHQqZPq5Vk+o6kAUNawMmJFmUGEaIqSyUgeDR662pxQaUXHptnzaeTQcbWl/t6DVD2OESd uu8jefJEaO98MfJP0bdoQbl4P4MB8vm3Z1kFmNhifj60Er0Xi6sWFrfN7/o/OmOcTESeitazu +cbdnzd9exJ65un4p+oZWzCST6yihspgQPM6ezgFyT+4plhBEDkUoXSyn0D0JeTXBzOJjVpMh aFZFr4X5tXqhInX7WECzYeQ+gV0vLWVZMXQa5NE/8/V1Qd3DdcYcqgkRhRSTAExfHctgGZY5T /WkVE81xTGVBp8SO0O+0t0ITKn08jFrBt/A8q+OUaRSgWdzSldFv7MaEbn2+fsDKh4654PHHe OTY47zrpi548NunwDM3s/QUw/3OJhqP4enUVS4D6Bx7TPAPNCo1dSdYlPBQ97K9RuBT3YPzwD qs2r4P4lraUf1mOF5dGAQJer4H1f0VJ0Ec3LyMYv4sgc/ItRdTj3SVeknsY+qkmy499Ri95GS Fvw4PPg3V/7KOYCwt2k5GrDEcI0KV364e0TaLmtBdBE6mepG32dCOubFDT5Ku60zBTGr56Ijv GzLjNqItXPL7GlkWp0fHumUQ8Om2icdupmOYM0e4WfoOlhYVdQTYyXRw3+Qk6O9Ebx5qDdWMp 1W8cL39sn7OY2k/BOw9XTmGJ1Myb9/Cjf34LfzvIwg/NMQXH7ThL3els/awRI0IaitKX1lxfy xDq+DnUGhxEHhqdD/8uW/ShFMPLOPUocNPF9cTphpLtoEJ5VaITjFUT8lIPXH8KEeX2Z5LIQy 5N0FM3pEvrGBRehwua5SHqMxR906noTcJJk6MvQZmOY/kYSkz/Za376yvZMYS+RrAJ1k+SG3V naNRqQznC3rFITNO6QevKFQKBsPhy/AvWuYBsRsUiAvolsmKJky50TZzR4+0lZmdBM3CRL2yV 2BpO0f156OwPNZCMe6roMFl5gkxPYLLk6jUGzs+HPNCesBuhbpEoxmuHwB0PLGAIJE+GL+eK1 409JYuyzPR7EFpPu6enePxt6hR+WpPSvRIf0J2DRPB0Gc7pD4V5jty1ypad/B0dTGiDZTXf6r axiZuRV9W9c/MFnVHJ0Kb1mR1z5etREXi2mIYJYRL/sfr7eQa8QeUEKrvQ8VdBwLZWC06VpYp I/ZCjNf8c5ugbdcuUYWAYF37CEd5O6Xl3A9Zmodl0+Tk5O/GB/ha/ajhr3JckxGdMIm4B2C4Q S0hlL7RO75WGQLjunGSUiee0kfB4G8iBZb5cyvKu8LfuGcdhFuTLA8xZQ58+h258a+L+77Q0G EK551yYh8xCrsHimwcng9eN8ATqGJL9cSo0nkyczdBNdS2+3BNJ1oKteyXx6Ll6FYY+qtGyN8 dsf4LUWpi4S6j/jCohjT+9s3HYjUyR7qGAWOJSQJuoDJrn4UsCP0MnwB3/+XRr1cS8nt8a7or bIdEXXh2h43WMRebyCzr5QndsUeehTcF9+jXGN2Hf/oaZFBETJtmpsIPG+7LVBnMa5oJIIxD+ LnlR5aD+MqadDGDeLOKHHjFS8QKOfXVMDnlwocE1fQXFndVisL8SbLAG/5pNlbN0+yNquJHtU r8EFr75tqKj7teHpDlrCYlwfTnMA2y/jq2HhOKdU03mNT1aVr5fiDWhSQU3mD2iVOsei0bfIm 27Y7HFr/r+q8KrtNfOzlyjWZy7xsigpAsNX3F75LqqkmiyrjASrBum9e4EjojKjYP2cFZnaSz h5YxDsBzADLPw9orWEe46ro3Z8YC1WIhGyaROsSr/08SqZq84wasUCSDz3WHecVaYFgeXLIcP OkFcNK3lUZz42aYjqwutrPJ0kK8ljn0d5oyd7qgamBaEb4j23cuGO9z2q8NEYVUo37KqkotPS 1UeuD0i6eTpbBwmrbgAFpKqvAHt/gNaXxBAcsEGiSm1ZaobxxeWLRE9LuuQsYjEnyNjkCb8Yg Ic7hCCsX9+9ggWSlLDy8Hrya5eL2xQfQcuI7g3dZitNfqtZiTqMC6aCjW1kmDMmkJQL/tMd+1 TSGQs2OQrNSHIAr+B/3NVLH8IfIqrt0qQkbiOV5bMG28fgCWtX7LGYFKamhpm7mEhTwxQUCmv TiUl2OBIZIyfwEl832AfArogMIoAsWK3kKT5EsSJABBtdvk2sFbjIDlMW9ykxnJiFphSgnNzT OJieMNblyQHDPjHCIwOofUGiF0TL0NxA5dEzYcs5FIocWirFCyUixebw6vix8GSPyjsnO+aSx +nbMH4aQK7n4I0ZCOy02UKjjyWZHoXtBgKQD6A/q0iZlv8ET5RVW1+6+Yx5h31M2+EUkBdFoD C4ASwN1uwgKwo/ZT/5yQUPx2zmYG/VxnhrzvtPgBqxIw/WVS4HB7NejEl9ekg9NN+nxVGrBTO kIWBKKw3ggmzsSTAUjUFItcLWn1KczziM9hAhdCG4dg4f3nqjqqNEdRisXHQxfWyYoQaMQAb1 Og1w5fRq19SpDkgRpF9fbWyBe4u3NAshCXt7+WQWVaEVbTD5ZOpGOBnq1hSEermZgCiAtrDUo 91h7tRNFfMPlsW/OtbhupxgYAGU4ljqd39ArDjL/jh8BkwHDZ8xJU3FwiGU1KcbGMFYlCD7m6 z+5BnJ2evBhiGea6cw39lq8uiXAhT7GBCoxfCBScZ5lWBPnpd2e2Z4siN7diyXVhOciNxZba/ ul9tzzNEEoHi43AIjNI8FHSOspCHRKU4am3L7uTMJq8EtZ4K6KSAed08QA2ArX+WZUgYvhTTS Av7nLeHdHLbf+aqrFAYk9OSygtM+ld2vbKpJavGw75xEpaIaAyIMQnQC7sukmpUPoiOLZul+N YQtyG5kQcGglwh2eelGASTmtUv8fUqwkUOv6nrJO+/t0f2HsrgpK35xu3Q+01CuWk0LVAFKhb MEdNRNwfFoABH6R4Nav5kWEp//0yIfZsyP7vyU2+30zf+JP0d8H/E+MJSdfCmnj0xyDkKTFBE BBgoSYyWzHegDNbmPSt0QvEwo9KIJIpNqX4Z9dlsywS7fawFHh5n8bcHFFjuWizPZnNa4rbCE cHm9tKT8C3tfvr+HzVc0SXkWg+pb3glx+3E1TUMdz80tMDsKRe9aCRyf2kZ1RzD94zOf0wjPf 1b+a3jZt0kgH+mhsposh+EgghRPtKgTEykwTNTcByxlZJe9Ju7eVwc3e96k6LhjqaVlzTeDve OXMOglYayKd8s5taOZbEnqonsgPCjkbRcAv+TIEzNb/iSmzKzQrXmI+T+notcLi3FqKDiT0e9 eoEL8MbRTqpFULXuQ0wNBzqq+Tpr1/yQjF0Wanxnxc5BRYyUeQdKWMVKAVLLb7oqKIE+J8pn7 xvuKq5EfuLZQVWrFiEBOws5DtWjWBZHoRDVuhwQlNHJ2Jz3Nw+kNZLSAvpCnepI/6gQ1AOCJj JjhfKaG/CxAsZBcgk31QbDGlZoh4Z2mlPD6AGvZcWSDDuV/LKc/Oecc9snUYImlR+vg2qxNjk taST07WdnUfzqUZa6r2V6u3XABxN/ijTSJPP6ytPbVAchntLct0g2OLYnk12g0tEeaLXnrYFK iXdRMmN5mqKF21CSGeC6NK+ZETnM/SwzLoJjWHba8i4RobteeUtjwpaTUWtHHVbpPtckdvbHf tH7SWH5us31kvaqCKmHFeE/XjNVBLZVqRjTRY8w3Fo02KH5L/egpeubtVkZo8wKQDlA83NCLK ZreFc7GWDg1YA8AnCxMI0L0/6BZcAvz+p3ynxejwEyuxB94+Z4Gr22sdNAPDkO1+JZYB/4ApC 2ZlrZNew38S9OQJjNk1MF3XhiIz+Myk5+h0f0T3q3Q/RsP6viejPq6aJsrmp/exuxAEi4/0kt 0BAXwamItfBFSnPNKtaTZG50IjRwkr3niVVL4aQUez7SVoX1DQWLPopTkPucEseDoAqiebuSA z7ioKtIEc2V917I7i+43WQaMVPtSDwVcJUlvmLqINxGWIzvC5J9w92VnV9iTbJcorSyMQNH4T v/s/3fP4CqfuhayRSOxMXfgOpMsurRqdc4Atgq8vsqv0zKVQ388LbSeMYs5DaoASOqXVc/y1V R0bWW5S2JSlzoUyjEx0vBOnEJSBOXWgRIl7fDEMl98t/M0xNGIb2xsfNSjqF7zzHDpeGXIRWQ LOXZqqzap0ptQI9J9qq8GC+XjoEIsGsrqxKeA2Rgx686PBYLv1D01eH+V2+024UOncQsgelEj cibRlRFwUANMn8Zxt6H7SmGnQ30vaVJC5OckdhtLt08hbxklRN2/Rtez5AXB85evdqo6uF0+h Ch0Jcio+i9NbYKq36pxVl8oQtvWWYzdX0TNoEmAbC0h4aZLzyuCx2RXfg6jfdz65+O4g9ANxe 7S0ttiR4WwEUD3txpTUm/XnR5wupnbF+AAEod7Uijkpotm3Gr1ax28zPn7RYGeB3NRhGd1MWD vcZraigfcE16FvyNIWhIPcrfGiMYmd8x6f/gGZ1oQPHw8AhRg+7Flv4aQYnop7F1IWwmfC2AD p7t6W13vzDVKXwFubRN/FYTEBgQT1QKS+JrVitUrgNrL+9/FmbF916tniPMgorimFT7ll6ltk DjodsElKg0jmcatlfRXAPZRADmuU53+j10i46MjV+l7J53rcjoVz3iZz8maRPjYlb7DXUc36z eOn7KzLMxReCsSQaveccvYEqJPikPYnz14V71UB8oa4cScmVWrW9U+eHOjKJxsPo8Oap0AiBz ttot5pPOPUtSQrqrGFSQSONKQW9kIwcW4n+my8fZ8KDW2m+oV3kOpn2tDHYoA27vmg8V+cRwv YpFId1Dznheh5T2VyVli2XOIojQA8jnhJcymkIjbiqzg+4V35Ht/LuSJCiIUUDiMpQs6smOjS bGMlAL3NLoIn8Uh2kJjDwUKklZwuGzkGj8O0V6zhKDr5sM1FMse7jM4eU75B0/NAkiasFMgdM 0A6QJtFp+SB004x2rIuzcMWLOWkt+uHr2FK2qJtn2vesbUp6kUp+3VQY4cvRWgfvPo List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_397D6208-FAA4-4B4A-9752-570294930A49 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On 23 Feb 2026, at 16:54, Andrei Lepikhov wrote: >=20 > On 23/2/26 10:41, Laurenz Albe 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? >=20 > Since PG16, the optimiser changed a lot. So, there are plenty of = possibilities that might happen - table statistics update, for example. = So, we need at least EXPLAIN ANALYSE for 'good' and 'bad' cases to begin = a discussion. Hi Andrei, see my previous answer: = https://www.postgresql.org/message-id/1695A676-062B-47C5-B302-91E2357DC874= %40gmx.net but here are the plans again: 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/hQvM plan-wrong: https://explain.depesz.com/s/uLvl regards, Attila --Apple-Mail=_397D6208-FAA4-4B4A-9752-570294930A49 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
On = 23 Feb 2026, at 16:54, Andrei Lepikhov <lepihov@gmail.com> = wrote:

On 23/2/26 = 10:41, Laurenz Albe 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?

Since PG16, the optimiser changed a lot. So, = there are plenty of possibilities that might happen - table statistics = update, for example. So, we need at least EXPLAIN ANALYSE for 'good' and = 'bad' cases to begin a = discussion.

Hi = Andrei,

see my previous answer:

but here are = the plans again:
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


regards,
Attila

= --Apple-Mail=_397D6208-FAA4-4B4A-9752-570294930A49--