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 1vvzKi-00E6Cv-0d for pgsql-performance@arkaria.postgresql.org; Fri, 27 Feb 2026 15:00:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vvzKh-004Af7-0h for pgsql-performance@arkaria.postgresql.org; Fri, 27 Feb 2026 15:00:47 +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 1vvzKg-004Aer-1e for pgsql-performance@lists.postgresql.org; Fri, 27 Feb 2026 15:00:46 +0000 Received: from mout.gmx.net ([212.227.17.21]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vvzKb-00000001Vx3-4BB8 for pgsql-performance@postgresql.org; Fri, 27 Feb 2026 15:00:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1772204437; x=1772809237; i=atiware@gmx.net; bh=iresLqfMLDk6bebf1Lw/BK34LOPBbhd4Wzrf1umixjk=; 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=JPKVU8BtbUVrCRA88Nn0N4Ta3+mqjNfyh/X0pNVAn2hInxCS3z9JDVd0/gwHQK73 kg6zP4UfVtz+GHizrM2hrr2u441ZiDMh/1SMctc2b3SD9weioIZ1j9usXBXYKl1NF LtR2at+XwThqhBeqdUeH4Vm6DDgrxTotNL55UjwG9hxIuy82WIRttWi6TjohiMMLY qfvQepreU++MKjklcNTlEm0cQuyueWMW3XQ0HX4CyWW4RoIWA+CVxmoY3knURpCAO My/YOj2pGcA1/dy4grF6xm3+Ne4Q6J60x3oKJmYPDZjJEUb4EGE37+ZEwRgWXq1ap ebyAmkSrmpsBJQ9Tlw== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from client.hidden.invalid by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MOiDX-1wLcxH2UrX-00Rcge; Fri, 27 Feb 2026 16:00:36 +0100 From: Attila Soki Message-Id: <24F30E2F-038C-4E3D-A8AA-1C8EAAF2E547@gmx.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_291EAC60-3F15-4D15-BB55-4BF6BFE7195F" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\)) Subject: Re: unstable query plan on pg 16,17,18 Date: Fri, 27 Feb 2026 16:00:25 +0100 In-Reply-To: <6199d929-711e-4657-bcf9-7d285cbafca6@gmail.com> Cc: pgsql-performance@postgresql.org To: Andrei Lepikhov , Laurenz Albe References: <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> <1388B912-7E9A-475A-93AA-67FB99A084BC@gmx.net> <0c8b9845-a2ac-4613-8075-45dc55911143@gmail.com> <45AA83DB-A6B4-43A2-879B-290E4A7845AE@gmx.net> <4FEE2E28-5A05-4423-B49E-CA23E2B78420@gmx.net> <6199d929-711e-4657-bcf9-7d285cbafca6@gmail.com> X-Mailer: Apple Mail (2.3864.400.21) X-Provags-ID: V03:K1:wWRmkgIbL++syojASQl0htuSJMfKyR98UPf0A+taojAe6q4oQPc tt3QJ4hkUvO0OANzhfwk01wmSWT8zMEPHO3aepy+agcnYepZ0WlOTXmpmxMLV1gUWXH2fNM RczjyOTJWdvygkXRwF0q3NQcDtUpdRVhnh+2ri92FhUiDADKishcskLisen4lIff3qd04oW 9XlYl0Q6VoAozw/4cUeFg== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:anoqOmOpRl8=;FzRqWSt6QgCu/pM1/23Oh4da2Jb wtnXE8SVP9I7zyzHHHdIf74RQwsU/UqXpXZ2GRLcUeF8ZgGVouMeBfWaAq88/1+XkJpfPP+fF LLrU44HaEs98VUtWqh1crv0/oohav5GP3qoooMz9ryDAc5jKRcD9+KCxHAd6jTUGyxwLjKcRN n03ZMzpV4FrgxfYZKRDFSCmmAJsJdgI8D3c8fDnyI857V1BmakQLz+X6h3KvbT7UTJeZNtynu q7I019S3IiiqTRPg7q5+BciLmQSB/z/LhpChNbz3JyfM4YZP4TAKFRMY0Ing9cK8N/8l6HjLZ KHVP3w2jkdbw4zK3xTDmqWqHv80FTBxpsot3qu16k/XkE+ZSUiRKeNPUH9H70SJs8vJ2gVTO8 qUNyg0J2KKI6BikHNo31mBaUPOyd3Ntd0X1Wm6q4F4CUQLGIe7q0JX1aXLUKgy+UPH+l7bRvZ L7U3Ntvlzqn5sKbuc5cRwPoL8pC4cJhzUg3q2KTSosXYjp35ZeeSiVCHB6tC/PH6Apo/xVxqF 7hGvzXHUiQ56pXQ6xbMZuwSpooBT28iVqTYajW2bzNo2fvQaJONUpX6vY5fwtepQpX3K3qN5l GCpeBjz8CNbxhNLyLDW23FtKALwEf/5DnWr9DgvEU4I8+w7Lyo/98hkzRTd0rmD665cpRgulo A/qz1w7KHd2feseSwu/iKxSVWE9Q6wt9oIaaN+cjZuJxtj/YPqF0zXGT7BozoVWSL3Om96kNj yG6d1R2K5cMdrhGduem14JAXORV6uv3JzOuXOLBlJ7tORuaCMSjzb6z8b7z9wawo3aG0mZJQv gB17/L2ZnupdBxE/0L026YFn+/qOPxIahFzzYqjRMZdQeYa8/tKLjUKWNzsu+PwPaj5gnq/jD cbxtYo9dMQ/QK8RXjrD4mE6hXjmtQ3S+vml+y833+H3uVDDFqV1Nx2PT9ypSZkaUSURgQqg/5 p59DDYz4B9uAYX04a1PpTkMwKx56wfC0CHGCtH75yh53kY6yacBwcuNZ8MjOeZ/hI9yox/1zO gOsOS8luPucl/SuVwmCFv6PKc7r5xWAK5up4p+GhsYnuY0HWuCaxD2+bBVX2m3QlARcUnykjL KDcTIrAx4F6/ftD3wVu4jyFJLMv0ebNGBwtS8eA6ChimGQXkw8rgaAy5hpJ+1bqd9jky0pdJU McOOtdfgY3oL99dX6enND/PLfdGZC7QJ+xdR26P734vensdME3zztCRNNEsrk0yySwFpSwEW4 Wj2pnz6yyL+fXYOEOGK2Iu5+nw3PgstCI3GFcE9fsBKv7DerNVGDnpuI6U9p+rtdk/tplYASx ao+pqZVPUBrj12kRd2F6Z1z6rntrUpeZiCkKo0tlKVDiMYI9CgcY9NgG+REZiiMq6hTs2Io6j GAc/R9E09niYjkN/aXWwOrrxFWt1wGp17C1dWdY7xFFi7M4goiEkx5b/uOBcm7cqiIEwAcKWT glx4phhBTb6YmNuCoXSG1CoPUlA25IiZhuWuuL+ZHkdz88Z6qGb2J/GTOovV0w+5m3FN5bNiD nZ3v9KKXd35VOT+7SCNinX6zi65Tq/N5mHW9nd5bgXWeF9IO4FqniYIxWFpfnHyKWjRcXbwEV VYWmOlUnDCRwO49FXYfaOxr8hDmBCVd77u74eXFSxDcbEAzJG2QeQSSBcG1FSxvlna+jLwuaC WcM4/emqPy5uiA/LQldw9B/D1SdLEnk5m5T1NWEULO7iJteNJ8pG03T2gCr3caSfzcfc6VjbQ ZskBDV9wHFZvzS4kr+K7JIREn/jzLezKj5XRneC7OOFnEBZXqRO29Dk2Xt6ibZ8b3ArY9DC0i u6xHmf/ZU3t6ds58crjY0jS1a87zEAguzUmo1gfRK/OLQXLzSy+QpSvdhlNH5WaWs+D20DBc+ gg/fXhcNwbwimtg4X07hh8H7ci6LClk3DS9OswRBhFIv4yM9nB6+OMIJ4UXjxHZvFsH/MXzDu nMDfSlCMf1y98Ee7OAdp/YXgPYrP+re2rXnmkSlMFrEi0b4fjKuYPsvSv9pI9Tmx8hPGLm3AP tLn7DLrt9+GiPk2I+NySC2fuMeNZuXYfm2/zv4TbOUIJ0LscIEFFNt3oHaPHYhUbdyvSv2OnO BMGs3n22PjRpscbHlpW4RjnLNzAx3Ui/UZ7DHk5iKvDuVFv1H8dPAyekLAPs7YFPSnP+cFRT1 b/Ew8+m9EgyZpPQCfZoEi5omcbbmyo5DEfBrbs1OBFd15iCzCTPvDQPcUTAay1m3+qy4WR3jZ 2XRVZqVNe0Ev3SVcba4wZF/KIzs252XKzgFJMLaktpSVWftz0QZLWT47aLjb6Pl5qlKrDtd/Y ElY3rjfCcV1yfF4K8oWGyAVNvsGPbtqD1HnAelunYor+D9Z+ULcPATn4oE0TOlpeCbzGgoLQ4 YgcxU60RXRTAdZWIvkwbUo9BgsqY3Q/+8M9XaQC5jntb2JhO3tXqLnfLiElROzGvOELk25ouq wrIm4zWBOzzWnPm9lkhdjFwPY0MuiywD6xpXRwA7vsvWbxGHrGa4fr8Vntiqv2RC9qC1Fj5eF jiK1jdAfwPsFXKEn+A1W0tM7xvEhuoNsjwby2HeDTHyIoq3ybFXrPELjDs5Cd/ZGYlFbu39p8 EVWOVnSU95ILdDSFtil1c4xuzt/BhmhnGYfOc5GG7dONg+hnduaA+sg5pjzIvcYOnu43Gg4se G5Jk+nJR2HRVluIFaUvEjEMw91mnlcJom6EG+J/024gUQxtHKEVeqp5Zc2LFfvpW08qe3nQC2 nK68XiyQ3i1tfPEqr64utXkDH7fqQlr2vjmMcXGrjLbCw8ztxPnsph99WVo9/K9wqpCHjLp75 o/w/xnYEmbtjx4MmtmfoFhhtl5HOt07z9ttYf9mXi4D8hO1EoOJMAKOoWw896JTbsetDVmzcB Uvkg6345IPf6Bc47u5sSs9oH80g14foqqgYTqe/eEkwFmBmfUukunwTfJh6LXpS66uU8/3jag CuEgmmRSz0TohJF9iY6aKjjv1KKRpuX43gZpuUkXXKgKQJLRRRGisxhJgiJgNq7Ah3T3+C5ja cMAwy/hDP9kDm3X07N1roON1AxV3AH40rY9HD9LnPu5y1lz2yapeh+dTL4nQ994T2ZqH254ir DlNmfJV9TuISls5X5PJ5kCVzkHFyA5AoRObP5dAaKt6kWN821KUW/Dwn7squLzqnLR1yhMx/R bl47g+skHcX6ZluVQZ72sF0oDAWo53uM6Oxqt6Ps7o36Dnecu2HxX/s4q84CTeHAVmvj5dmPU /nge3WoKe7ZEZe3VjZsTZ48rCxgLi0UjKo5gL7t+XTE7ydGIvHaFqWihpLvpw8j/fJNv0anpW pioC+1KZlRMFTcKENgJwnByTKGtJYJm4zczg7x9xTi3ASdLZVFDFljP+u57zny0R5S//v1ABY 5XqFyjZBfr0Uq8arCXZ87eZGAVESozuYMbbBNKWPtec0D6RRGP7CIJu8N6wTqjyQ2Bts6yBGD 1SD+17Jf2KxazkQ6gWqUw4wihL1XmRcnUDpuEIVsgpwARaoQZ9EycoYilMgihUyvLQxkFfdfY 9PstSWCg47SKqD0CVHWseFoYXBCzTsNaG466PHuM4taBPvlPQxfNzlIBJTjLCvYLg6sBJJMF7 mCZrza6MOploty8sYd0P3YfUlju0LzasJVyFp81Kua9JYeHb5DpHakHWwjVWptQ52nW2HXgzR nlPUW/0fDDT5gRD3o1DtsyPmBXT/i5g45Z2fmGpNgBdOXvW1OH8dYs9gPlOx7UenoAZrKPuvW P2KXrW21dKF3I1M/8EFssiu89F6VMMe4tDQQkcKTWTXH/KfJyhgqXW+MD/fXUbNUm3wKR0hwr avc144PVbg7alg3aAo2b6kTmDp64kD91bj9BTtW47bxpBQYKkZdA8PUEfVlrmb5Pg1E2Ptw5f yCufkchvLun9jJDtVaFQc4yIiulWIvU18HmqjuGZjtGq3+HIh7+nCxR/PLVZ0mhTXCsLp9KiT x2NnVzw9DTgdbJV6Ubju3JzrXqyLkILWELlcaVwMiPrwt7rqqtyoe9X3O1UGH+7v/qeUlk1nA NsuthFhYec0ynCKCW6A5jM7j6A8PXsMRLKlKUrcZy5MaC6kIVxDhyvFzvMoyzW6FxZLlDWSiv 5Yayf0Pili/ue8wwv/7jeNNANUR0Bu/bvGiu7op9v355XaEtdC93bzch4YHxbXCKSsK7lQoNL 2mCWrPu8P6CWRrhMHzCUJAesTuDMij/aWbe13rt1X3RBhCsLT9R0PY5O7Yn7hMeq7vY1HieoN LaqDuQP80H5UT0pTfC35vX68QNi1nMKFM76id2gZ3akAkSY+11tEB2ShrdE3u6H01INq1wTKi kHTE4mDdYctvL7rblRLgEujfDvSZ6NUda9kK/zOLMtiKQFT08lgxd9PfZmf1Wy7qzm9cMfiF3 JqlO7iNdTAY/hQGUgxVbnI5PGzhgNZzrmMkjyW7qhPySBhvqNp1hJnHu5bpURRqIb3lNkSM9D QCFeDj672OvIK3UZERmwZREYEEcT8AkHyNxtHFWE+c+ztfXw0CyOZKoyBEOPaIPzz4IkQ1XKq yCQ1gTbHRUj+83/sa7x+iNQgcDBYygV/ODEvx+tCiWGU34CX+q7ywceyDMkeEapeuYQkjCJCX ootWbIExHadzywFK8x1g79owkxYvYV7wm9wyWebsr+5TiMQ0gjZ4RoSqlLLiofPR+aaDFT8yH qSen6n/L+3Gu9xQHglw6eWWJAm51UmvxccjWU2/9nM/UWOSbU7uvId13Jfa5wIvzucOhhrr9y An3FJgMhLE5VaRU1DSEnztO93eXSoJncAez8e9x3UFHG+gaprWBVT3E4ttYbq0WRotIR4nfAp Ym+f69C0JhqvO2bgAAgEbl9dVSpg4PVUWEsPPdH2+ZDHlouCOo9fRpAGhgQa0Ec808PcIBfSU iviRfG7uFa7pKQhwOUlq1ruSEHG4Z8cB1vWKMpQTfEgFqTI/uYj9o/kuM5m8V/7QSvTj8cYK8 e7MIAiRmPR8Mtv01UkQjJ8/xo25ucO9mPHRrUePiIP8VT7cgnjmc/tDUQJkmekXc8rNmGLUFi PRFvTaJXtsw3zYQG+4RKDs7ucdVesOq/tkuQ1V/n18/fALuozBQT+IhL6zLc5UUd71LdemFgH zy98sr7Ki+gZzn6AcmXC/nV9js1JhCyMFqAmfPXH7zxf3LACegPEUc0jiUzWZMLZ+R0EHNwnZ 77KNl9XTEW5hLm7vRAt5FlKdGvPu14Y5V949JrDBJK9p67Uv7YeZEPBrjePZwXloRWlfJrad0 xTDB0WauT6scP77RDP2vlhlUW23+t7tU2OKFdtRqkQAvAFu5IYJOUXZ15pE7mro1yj9KumF1G AmznA7T0121ocmqQ1gO4J18MRKgehWzA3M9ABRUfriDqpZJPzzNSBpd/eW48nElQX4rDKw9TC vFH2xEeYXmg7ccp2b0JnORxUECKyCQtD51qvPqecufezvxneRqHZ/YaI0Kpa103rDz6dDiP+G LI960ta0k1rZTww== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_291EAC60-3F15-4D15-BB55-4BF6BFE7195F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 On 27 Feb 2026, at 09:15, Andrei Lepikhov wrote: >=20 > -> Hash Right Join (cost=3D210369.25..210370.30 rows=3D8 width=3D99) > (actual time=3D150.790..150.853 rows=3D44.56 loops=3D21798) >=20 > Schema of this part of the query tree is as the following: >=20 > Hash Right Join (loops=3D21798) > =E2=94=82 > =E2=94=9C=E2=94=80 [Left/Probe] GroupAggregate (loops=3D14426) > =E2=94=82 =E2=94=94=E2=94=80 Merge Right Anti Join > =E2=94=82 =E2=94=94=E2=94=80 Merge Join > =E2=94=82 =E2=94=94=E2=94=80 Index Only Scan on table_k = gkal_2 (loops=3D14426) > =E2=94=82 > =E2=94=94=E2=94=80 [Right/Build =3D Hash] Nested Loop (loops=3D21798) > =E2=94=9C=E2=94=80 Index Scan on table_o goftr_1 (loops=3D21798) > =E2=94=82 Index Cond: goftr_1.au_id =3D gauf_1.id = > =E2=94=94=E2=94=80 Index Scan on table_k gkal_1 > Index Cond: gkal_1.oo_id =3D goftr_1.id = >=20 > So, the hash table is rebuilt each rescan based on the changed = 'gauf_1.id ' external parameter. > Without the query, it is hard to say exactly what the trigger of this = problem is. Having a reproduction, we could use planner advising = extensions and see how additional knowledge of true cardinalities = rebuilds the query plan. Sometimes, additional LATERAL restriction, = added by the planner to pull-up subplan, restricts the join search scope = badly, but I doubt if we have this type of problem here. I searched for the condition kal.dp_end_dat < current_date, then = realized that this part of the explain is misleading. Index Scan using table_k_late_spec_dp_end_dat_key on schema1.table_k kal = (cost=3D0.28..122468.46 rows=3D196053 width=3D24) (actual = time=3D0.039..0.614 rows=3D471.00 loops=3D1) Output: kal.dp_rti_id, kal.art_dp_res, kal.oo_id 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 The definiton of the index table_k_late_spec_dp_end_dat_key is: CREATE INDEX table_k_late_spec_dp_end_dat_key ON schema1.table_k USING btree (dp_end_dat) WHERE dp_st_dat IS NOT NULL AND dp_end_dat IS NOT NULL AND dp_status = IS NOT NULL AND dp_status > 0 AND oo_id IS NOT NULL AND = COALESCE(art_rtd, 0.0000) < (COALESCE(art_grt, 0.0000) + = COALESCE(art_grt_j2j, 0.0000)); This, because the where in index corresponds the where in query. so the = simplified query is: SELECT * FROM schema1.table_k AS kal WHERE dp_end_dat < current_date AND dp_st_dat IS NOT NULL AND dp_end_dat = IS NOT NULL AND dp_status IS NOT NULL AND dp_status > 0 AND oo_id IS NOT = NULL AND COALESCE(art_rtd, 0.0000) < (COALESCE(art_grt, 0.0000) + = COALESCE(art_grt_j2j, 0.0000)); The surrounding query part of the view is below, where the part with = "dp_end_dat < current_date" is in the "with late as ()": WITH late AS ( SELECT kal.dp_rti_id AS rti_id, sum(COALESCE(kal.art_dp_res, 0.0000)) AS sum_art_dp_late FROM schema1.table_k kal WHERE kal.dp_status IS NOT NULL AND kal.dp_status > 0 AND = COALESCE(kal.art_rtd, 0.0000) < (COALESCE(kal.art_grt, 0.0000) + = COALESCE(kal.art_grt_j2j, 0.0000)) AND kal.dp_st_dat IS NOT NULL AND = kal.dp_end_dat IS NOT NULL AND kal.dp_end_dat < 'now'::text::date AND = kal.oo_id IS NOT NULL AND NOT (EXISTS ( SELECT akdt_late.oo_id FROM schema1.table_k_dtg akdt_late =E2=80=94 ------ this is a = view WHERE akdt_late.dp_rti_id::text =3D kal.dp_rti_id::text AND = akdt_late.oo_id IS NOT NULL AND (akdt_late.art_prov_res > 0.0000 OR akdt_late.dp_status > 0 = AND akdt_late.art_dp_res > 0.0000) AND akdt_late.datum >=3D 'now'::text::date AND (akdt_late.a_status::text =3D ANY (ARRAY['d'::character = varying::text, 'v'::character varying::text, 'i'::character = varying::text])) AND akdt_late.ih_flag AND kal.oo_id::text =3D = akdt_late.oo_id::text )) GROUP BY kal.dp_rti_id ) SELECT akd.oo_id, akd.dp_rti_id AS rti_id, akd.datum, akd.lgaagng AS auf_lgaagng, akd.rueday_def, akd.rettag_def, COALESCE(min(COALESCE(sum_ast_per_day.sum_per_day, 0.0000)), 0.0000) = AS sum_ast_per_day, COALESCE(max(COALESCE(sum_red_per_day.sum_per_day, 0.0000)), 0.0000) = AS sum_red_per_day, CASE WHEN akd.datum > 'now'::text::date THEN = COALESCE(late.sum_art_dp_late, 0.0000) ELSE 0.0000 END AS sum_art_dp_late FROM schema1.table_k_future_dt akd =E2=80=94 ------ this is a view LEFT JOIN schema1.dd_ext ext_dd ON ext_dd.id::text =3D akd.ext::text LEFT JOIN schema1.dp_epkt ext_dd_dpe ON ext_dd_dpe.id::text =3D = ext_dd.table_d_id::text LEFT JOIN late ON late.rti_id::text =3D akd.dp_rti_id::text LEFT JOIN LATERAL ( SELECT COALESCE(sum(COALESCE(stk.anz, 0.0000)), 0.0000) AS = sum_per_day FROM schema1.al_ast stk WHERE stk.rti_id::text =3D akd.dp_rti_id::text AND stk.von <=3D = akd.datum AND stk.bis >=3D akd.datum GROUP BY akd.datum ) sum_ast_per_day ON ( EXISTS ( SELECT al_ast.rti_id FROM schema1.al_ast WHERE al_ast.rti_id::text =3D akd.dp_rti_id::text ) ) LEFT JOIN LATERAL ( SELECT COALESCE(sum(COALESCE(alred.anz, 0.0000)), 0.0000) AS = sum_per_day FROM schema1.al_red alred WHERE alred.rti_id::text =3D akd.dp_rti_id::text AND alred.von <=3D = akd.datum AND alred.bis >=3D akd.datum GROUP BY akd.datum ) sum_red_per_day ON ( EXISTS ( SELECT al_red.rti_id FROM schema1.al_red WHERE al_red.rti_id::text =3D akd.dp_rti_id::text ) ) WHERE (ext_dd.table_d_id IS NULL OR ext_dd.table_d_id::text =3D = 'schema1'::text OR NOT COALESCE(ext_dd_dpe.enabled, false)) AND akd.rti_id::text !~~ 'P%'::text AND akd.dp_rti_id::text !~~ = 'P%'::text AND (akd.art_dp_res > 0.0000 OR akd.art_prov_res > 0.0000 OR = akd.art_dp_zm > 0.0000) AND (akd.lgaagng IS NULL OR akd.lgaagng::date >=3D 'now'::text::date AND = akd.lgaagng::date <=3D ('now'::text::date + '3 mons'::interval)::date) GROUP BY akd.oo_id, akd.dp_rti_id, akd.datum, akd.lgaagng, = akd.rueday_def, akd.rettag_def, late.sum_art_dp_late "gauf" is in one of the table_k_* views, and looks like below. There are = multiple variants, they differ mostly in "where" part. SELECT gdt.datum, gkal.rti_id, gdt.au_id, gkal.oo_id, gkal.id AS kal_id, gauf.status AS a_status, goftr_1.token AS ih_flag, gdt.prov, gdt.def, gkal.dp_status, gkal.ext, gdt.rueday_def, gdt.rettag_def, gdt.rueday_prov, gdt.rettag_prov, gauf.lgaagng, gauf.lgaein, gkal.art_dp_res, gkal.art_prov_res, gkal.art_dp_zm, gkal.rti, gkal.art_dp_extern, gkal.dp_rti_id, gkal.art_dp_lga, gkal.set_fix_vkpt FROM schema1.table_a_dtg gdt LEFT JOIN schema1.table_a gauf ON gauf.id::text =3D gdt.au_id::text LEFT JOIN schema1.auf_oos goftr_1 ON goftr_1.au_id::text =3D = gauf.id::text LEFT JOIN schema1.table_k gkal ON gkal.oo_id::text =3D = goftr_1.id::text WHERE gdt.datum >=3D ('now'::text::date - '7 days'::interval)::date;; I tried to change the statistics of dp_end_dat and also all of the = fields in "where" from 10 to 1500 increased in increments of 10. One field at once then all fields together. the estimate got not better, = actual rows 471, planned rows somewhere between 180000 and 195000. then i checked the same query on pg 14, the estimate is the same as on = pg 18. ALTER TABLE scema1.table_k ALTER dp_end_dat SET STATISTICS 140; ALTER TABLE ALTER TABLE scema1.table_k ALTER dp_status SET STATISTICS 140; ALTER TABLE ALTER TABLE scema1.table_k ALTER oo_id SET STATISTICS 140; ALTER TABLE ALTER TABLE scema1.table_k ALTER art_rtd SET STATISTICS 140; ALTER TABLE ALTER TABLE scema1.table_k ALTER art_grt SET STATISTICS 140; ALTER TABLE ALTER TABLE scema1.table_k ALTER art_grt_j2j SET STATISTICS 140; ALTER TABLE ANALYZE scema1.table_k; pg14 at statistics 140: EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT * FROM schema1.table_k AS = kal WHERE dp_end_dat < current_date AND dp_st_dat IS NOT NULL AND = dp_end_dat IS NOT NULL AND dp_status IS NOT NULL AND dp_status > 0 AND = oo_id IS NOT NULL AND COALESCE(art_rtd, 0.0000) < (COALESCE(art_grt, = 0.0000) + COALESCE(art_grt_j2j, 0.0000)); Index Scan using table_k_late_spec_dp_end_dat_key on table_k kal = (cost=3D0.28..122750.89 rows=3D193091 width=3D614) (actual = time=3D0.010..0.261 rows=3D471 loops=3D1) Index Cond: (dp_end_dat < CURRENT_DATE) Buffers: shared hit=3D279 Settings: hash_mem_multiplier =3D '2.5', jit =3D 'off', = max_parallel_workers =3D '4', max_parallel_workers_per_gather =3D '4', = random_page_cost =3D '1.2', temp_buffers =3D '512MB', work_mem =3D = '768MB' Planning: Buffers: shared hit=3D1459 Planning Time: 3.101 ms Execution Time: 0.325 ms pg18 at statistics 140: EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT * FROM schema1.table_k AS = kal WHERE dp_end_dat < current_date AND dp_st_dat IS NOT NULL AND = dp_end_dat IS NOT NULL AND dp_status IS NOT NULL AND dp_status > 0 AND = oo_id IS NOT NULL AND COALESCE(art_rtd, 0.0000) < (COALESCE(art_grt, = 0.0000) + COALESCE(art_grt_j2j, 0.0000)); Index Scan using table_k_late_spec_dp_end_dat_key on table_k kal = (cost=3D0.28..122561.69 rows=3D195550 width=3D624) (actual = time=3D0.021..0.514 rows=3D471.00 loops=3D1) Index Cond: (dp_end_dat < CURRENT_DATE) Index Searches: 1 Buffers: shared hit=3D279 Settings: temp_buffers =3D '512MB', work_mem =3D '768MB', = hash_mem_multiplier =3D '2.5', jit =3D 'off', = max_parallel_workers_per_gather =3D '4', max_parallel_workers =3D '4', = random_page_cost =3D '1.2' Planning: Buffers: shared hit=3D1508 Planning Time: 3.123 ms Execution Time: 0.639 ms (9 rows) I hope I have selected the correct parts of the query, as it is not = really possible to share the entire query with all its dependencies. The first version of this query was written for PostgreSQL 8.3; since = then, it has been refactored and optimized a few times when necessary. I = will check if it is possible to reorder the query without rewriting = everything. I dont know the inner workings of analyze, is that normal that executing = analyze on unchanged data can flip the plan? Does analyze select a = random set of rows? Thanks. regards, Attila --Apple-Mail=_291EAC60-3F15-4D15-BB55-4BF6BFE7195F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 On 27 Feb 2026, at 09:15, Andrei = Lepikhov <lepihov@gmail.com> wrote:

->  Hash Right Join =  (cost=3D210369.25..210370.30 rows=3D8 width=3D99)
= (actual = time=3D150.790..150.853 rows=3D44.56 loops=3D21798)

Schema of this part of the query tree is as = the following:

Hash Right Join =  (loops=3D21798)
 =E2=94=82
 =E2=94=9C=E2=94=80 [Left/Probe] = GroupAggregate (loops=3D14426)
 =E2=94=82    =E2=94=94=E2= =94=80 Merge Right Anti Join
 =E2=94=82 =         =E2=94=94=E2=94=80 Merge = Join
 =E2=94=82 =             &n= bsp;=E2=94=94=E2=94=80 Index Only Scan on table_k gkal_2 =  (loops=3D14426)
 =E2=94=82
 =E2=94=94=E2=94=80 [Right/Build =3D = Hash] Nested Loop (loops=3D21798)
      =E2=94=9C= =E2=94=80 Index Scan on table_o goftr_1 (loops=3D21798)
      =E2=94=82=    Index Cond: goftr_1.au_id =3D gauf_1.id
      =E2=94=94= =E2=94=80 Index Scan on table_k gkal_1
          &= nbsp;Index Cond: gkal_1.oo_id =3D goftr_1.id

So, the hash table is rebuilt each rescan = based on the changed 'gauf_1.id' external parameter.
Without the query, it is hard to say = exactly what the trigger of this problem is. Having a reproduction, we = could use planner advising extensions and see how additional knowledge = of true cardinalities rebuilds the query plan. Sometimes, additional = LATERAL restriction, added by the planner to pull-up subplan, restricts = the join search scope badly, but I doubt if we have this type of problem = here.

I searched for the = condition kal.dp_end_dat < current_date, then realized that this part = of the explain is misleading.

Index Scan using = table_k_late_spec_dp_end_dat_key on schema1.table_k kal =  (cost=3D0.28..122468.46 rows=3D196053 width=3D24) (actual = time=3D0.039..0.614 rows=3D471.00 loops=3D1)
  =  Output: kal.dp_rti_id, kal.art_dp_res, kal.oo_id
  =  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

The definiton of the index = table_k_late_spec_dp_end_dat_key is:
CREATE INDEX = table_k_late_spec_dp_end_dat_key
  ON = schema1.table_k
  USING btree
  = (dp_end_dat)
  WHERE dp_st_dat IS NOT NULL AND dp_end_dat = IS NOT NULL AND dp_status IS NOT NULL AND dp_status > 0 AND oo_id IS = NOT NULL AND COALESCE(art_rtd, 0.0000) < (COALESCE(art_grt, 0.0000) + = COALESCE(art_grt_j2j, 0.0000));

This, because = the where in index corresponds the where in query. so the simplified = query is:
SELECT * FROM schema1.table_k AS kal
WHERE = dp_end_dat < current_date AND dp_st_dat IS NOT NULL AND dp_end_dat IS = NOT NULL AND dp_status IS NOT NULL AND dp_status > 0 AND oo_id IS NOT = NULL AND COALESCE(art_rtd, 0.0000) < (COALESCE(art_grt, 0.0000) + = COALESCE(art_grt_j2j, = 0.0000));


The surrounding query = part of the view is below, where the part with "dp_end_dat < = current_date" is in the "with late as ()":

WITH = late AS (
    SELECT kal.dp_rti_id AS = rti_id,
        = sum(COALESCE(kal.art_dp_res, 0.0000)) AS = sum_art_dp_late
    FROM schema1.table_k = kal
    WHERE kal.dp_status IS NOT NULL AND = kal.dp_status > 0 AND COALESCE(kal.art_rtd, 0.0000) < = (COALESCE(kal.art_grt, 0.0000) + COALESCE(kal.art_grt_j2j, 0.0000)) AND = kal.dp_st_dat IS NOT NULL AND kal.dp_end_dat IS NOT NULL AND = kal.dp_end_dat < 'now'::text::date AND kal.oo_id IS NOT = NULL
    AND NOT (EXISTS (
    =     SELECT akdt_late.oo_id
      =   FROM schema1.table_k_dtg akdt_late =E2=80=94 ------ this is a = view
        WHERE = akdt_late.dp_rti_id::text =3D kal.dp_rti_id::text AND akdt_late.oo_id IS = NOT NULL
        AND = (akdt_late.art_prov_res > 0.0000 OR akdt_late.dp_status > 0 AND = akdt_late.art_dp_res > 0.0000)
        = AND akdt_late.datum >=3D 'now'::text::date
    =     AND (akdt_late.a_status::text =3D ANY = (ARRAY['d'::character varying::text, 'v'::character varying::text, = 'i'::character varying::text]))
        = AND akdt_late.ih_flag AND kal.oo_id::text =3D = akdt_late.oo_id::text
    ))
    = GROUP BY kal.dp_rti_id
)
SELECT = akd.oo_id,
    akd.dp_rti_id AS = rti_id,
    akd.datum,
    = akd.lgaagng AS auf_lgaagng,
    = akd.rueday_def,
    akd.rettag_def,
  =   COALESCE(min(COALESCE(sum_ast_per_day.sum_per_day, 0.0000)), = 0.0000) AS sum_ast_per_day,
    = COALESCE(max(COALESCE(sum_red_per_day.sum_per_day, 0.0000)), 0.0000) AS = sum_red_per_day,
    CASE
    =     WHEN akd.datum > 'now'::text::date THEN = COALESCE(late.sum_art_dp_late, 0.0000)
      =   ELSE 0.0000
    END AS = sum_art_dp_late
FROM schema1.table_k_future_dt akd =E2=80=94 = ------ this is a view
LEFT JOIN schema1.dd_ext ext_dd ON = ext_dd.id::text =3D akd.ext::text
LEFT JOIN schema1.dp_epkt = ext_dd_dpe ON ext_dd_dpe.id::text =3D = ext_dd.table_d_id::text
LEFT JOIN late ON late.rti_id::text =3D = akd.dp_rti_id::text
LEFT JOIN LATERAL (
  =   SELECT COALESCE(sum(COALESCE(stk.anz, 0.0000)), 0.0000) AS = sum_per_day
    FROM schema1.al_ast = stk
    WHERE stk.rti_id::text =3D = akd.dp_rti_id::text AND stk.von <=3D akd.datum AND stk.bis >=3D = akd.datum
    GROUP BY akd.datum
) = sum_ast_per_day ON (
        EXISTS = (
            SELECT = al_ast.rti_id
            FROM = schema1.al_ast
            WHERE = al_ast.rti_id::text =3D akd.dp_rti_id::text
    =     )
    )
LEFT JOIN LATERAL = (
    SELECT COALESCE(sum(COALESCE(alred.anz, = 0.0000)), 0.0000) AS sum_per_day
    FROM = schema1.al_red alred
    WHERE alred.rti_id::text =3D = akd.dp_rti_id::text AND alred.von <=3D akd.datum AND alred.bis >=3D = akd.datum
    GROUP BY akd.datum
) = sum_red_per_day ON (
        EXISTS = (
            SELECT = al_red.rti_id
            FROM = schema1.al_red
            WHERE = al_red.rti_id::text =3D akd.dp_rti_id::text
    =     )
    )
WHERE = (ext_dd.table_d_id IS NULL OR ext_dd.table_d_id::text =3D = 'schema1'::text OR NOT COALESCE(ext_dd_dpe.enabled, = false))
AND akd.rti_id::text !~~ 'P%'::text AND = akd.dp_rti_id::text !~~ 'P%'::text
AND (akd.art_dp_res > = 0.0000 OR akd.art_prov_res > 0.0000 OR akd.art_dp_zm > = 0.0000)
AND (akd.lgaagng IS NULL OR akd.lgaagng::date >=3D = 'now'::text::date AND akd.lgaagng::date <=3D ('now'::text::date + '3 = mons'::interval)::date)
GROUP BY akd.oo_id, akd.dp_rti_id, = akd.datum, akd.lgaagng, akd.rueday_def, akd.rettag_def, = late.sum_art_dp_late


"gauf" = is in one of the table_k_* views, and looks like below. There are = multiple variants, they differ mostly in "where" = part.

SELECT gdt.datum,
    = gkal.rti_id,
    gdt.au_id,
    = gkal.oo_id,
    gkal.id AS kal_id,
  =   gauf.status AS a_status,
    goftr_1.token AS = ih_flag,
    gdt.prov,
    = gdt.def,
    gkal.dp_status,
    = gkal.ext,
    gdt.rueday_def,
  =   gdt.rettag_def,
    = gdt.rueday_prov,
    = gdt.rettag_prov,
    gauf.lgaagng,
  =   gauf.lgaein,
    = gkal.art_dp_res,
    = gkal.art_prov_res,
    = gkal.art_dp_zm,
    gkal.rti,
  =   gkal.art_dp_extern,
    = gkal.dp_rti_id,
    = gkal.art_dp_lga,
    = gkal.set_fix_vkpt
   FROM schema1.table_a_dtg = gdt
     LEFT JOIN schema1.table_a gauf ON = gauf.id::text =3D gdt.au_id::text
     LEFT = JOIN schema1.auf_oos goftr_1 ON goftr_1.au_id::text =3D = gauf.id::text
     LEFT JOIN schema1.table_k = gkal ON gkal.oo_id::text =3D goftr_1.id::text
  WHERE = gdt.datum >=3D ('now'::text::date - '7 = days'::interval)::date;;

I tried to = change the statistics of dp_end_dat and also all of the fields in = "where" from 10 to 1500 increased in increments of 10.
One = field at once then all fields together. the estimate got not better, = actual rows 471, planned rows somewhere between 180000 and = 195000.

then i checked the same query on pg 14, = the estimate is the same as on pg 18.

ALTER = TABLE scema1.table_k ALTER dp_end_dat SET STATISTICS = 140;
ALTER TABLE
ALTER TABLE scema1.table_k ALTER = dp_status SET STATISTICS 140;
ALTER TABLE
ALTER = TABLE scema1.table_k ALTER oo_id SET STATISTICS 140;
ALTER = TABLE
ALTER TABLE scema1.table_k ALTER art_rtd SET STATISTICS = 140;
ALTER TABLE
ALTER TABLE scema1.table_k ALTER = art_grt SET STATISTICS 140;
ALTER TABLE
ALTER TABLE = scema1.table_k ALTER art_grt_j2j SET STATISTICS 140;
ALTER = TABLE
ANALYZE scema1.table_k;

pg14 at = statistics 140:
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT * = FROM schema1.table_k AS kal WHERE dp_end_dat < current_date AND = dp_st_dat IS NOT NULL AND dp_end_dat IS NOT NULL AND dp_status IS NOT = NULL AND dp_status > 0 AND oo_id IS NOT NULL AND COALESCE(art_rtd, = 0.0000) < (COALESCE(art_grt, 0.0000) + COALESCE(art_grt_j2j, = 0.0000));


 Index Scan using = table_k_late_spec_dp_end_dat_key on table_k kal =  (cost=3D0.28..122750.89 rows=3D193091 width=3D614) (actual = time=3D0.010..0.261 rows=3D471 loops=3D1)
   Index = Cond: (dp_end_dat < CURRENT_DATE)
   Buffers: = shared hit=3D279
 Settings: hash_mem_multiplier =3D = '2.5', jit =3D 'off', max_parallel_workers =3D '4', = max_parallel_workers_per_gather =3D '4', random_page_cost =3D '1.2', = temp_buffers =3D '512MB', work_mem =3D = '768MB'
 Planning:
   Buffers: shared = hit=3D1459
 Planning Time: 3.101 = ms
 Execution Time: 0.325 = ms


 pg18 at statistics = 140:
 EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT * FROM = schema1.table_k AS kal WHERE dp_end_dat < current_date AND dp_st_dat = IS NOT NULL AND dp_end_dat IS NOT NULL AND dp_status IS NOT NULL AND = dp_status > 0 AND oo_id IS NOT NULL AND COALESCE(art_rtd, 0.0000) = < (COALESCE(art_grt, 0.0000) + COALESCE(art_grt_j2j, = 0.0000));


 Index Scan using = table_k_late_spec_dp_end_dat_key on table_k kal =  (cost=3D0.28..122561.69 rows=3D195550 width=3D624) (actual = time=3D0.021..0.514 rows=3D471.00 loops=3D1)
  =  Index Cond: (dp_end_dat < CURRENT_DATE)
  =  Index Searches: 1
   Buffers: shared = hit=3D279
 Settings: temp_buffers =3D '512MB', work_mem =3D= '768MB', hash_mem_multiplier =3D '2.5', jit =3D 'off', = max_parallel_workers_per_gather =3D '4', max_parallel_workers =3D '4', = random_page_cost =3D '1.2'
 Planning:
  =  Buffers: shared hit=3D1508
 Planning Time: 3.123 = ms
 Execution Time: 0.639 ms
(9 = rows)


I hope I have selected the = correct parts of the query, as it is not really possible to share the = entire query with all its dependencies.

The = first version of this query was written for PostgreSQL 8.3; since then, = it has been refactored and optimized a few times when necessary. I will = check if it is possible to reorder the query without rewriting = everything.

I dont know the inner workings of = analyze, is that normal that executing analyze on unchanged data can = flip the plan? Does analyze select a random set of = rows?

Thanks.

regards,
Attila



= --Apple-Mail=_291EAC60-3F15-4D15-BB55-4BF6BFE7195F--