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 1vucUs-00GAIm-25 for pgsql-performance@arkaria.postgresql.org; Mon, 23 Feb 2026 20:25:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vucUr-00F4cA-1A for pgsql-performance@arkaria.postgresql.org; Mon, 23 Feb 2026 20:25:37 +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 1vucUr-00F4c0-00 for pgsql-performance@lists.postgresql.org; Mon, 23 Feb 2026 20:25:37 +0000 Received: from mail-wm1-x32e.google.com ([2a00:1450:4864:20::32e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vucUn-00000000qmv-3PIB for pgsql-performance@postgresql.org; Mon, 23 Feb 2026 20:25:35 +0000 Received: by mail-wm1-x32e.google.com with SMTP id 5b1f17b1804b1-48373a4bca3so28687875e9.0 for ; Mon, 23 Feb 2026 12:25:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1771878333; x=1772483133; darn=postgresql.org; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id:from :to:cc:subject:date:message-id:reply-to; bh=F+4nKS84GJ75Uf9VYBgNxZ8wzmH4bP9MkoaydqU7rLs=; b=dcTZjUnUz0X+7TFHXDFUVLIs4p42Hi3AzGvbkpCVB44JKrlePMmPc1UuSBEuodvv2a LQrOsdpLuzp3WUm5bmpuMmOjTTGOnKnH8dPePCwCX25pQ70IKQl/Ncfud+Id9kXIiQlj B4NQNgZ3JLSzU33ZlLwLpnh5xrLvSGKLSz/VA8R3ahD4r5Oikdip4sVKH1fhFF+3q1Jz dqBVKw0eag3Wqa66HsBhOiuQegFyDDS66/67uVDqXnjaguLlYuBjq5I4U+S8r/lbwSf5 S6QihyIrhoJcN2InrQjYa0AqKgFZz/dnbDMoG/4MELnj0HKX4jFJZPNvRNNy6bCsiKBf aGcw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1771878333; x=1772483133; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=F+4nKS84GJ75Uf9VYBgNxZ8wzmH4bP9MkoaydqU7rLs=; b=Zdj7YIqdHM0AWiPyyZeZpsLlo9Kb7sPZrwoU7AJ3M64ndphDSSYSDrOwLeCnebsrhS Vl0V0istiuCPjg25xLSfokY8cYOH2TeQiMXimYG7mg+BVhl5j3VFN6P93aKPcwTxZG2h CWRb9Ne2tRzEFN724RQSoU2I+Bv8mwc1bYRf+3+Fa1kAlrV1cG88DUZREr7YHU61uZNk N6Bk/vyREQnvU2rcGv5JY3jWMoCTK7WuiJOtxMDYLd7sziNXRCzNRKvgae1o05kh7/jc Ql8AYesEoiNJHmc3OfHde/IwfPd6dia780HepPazjLsQS61Gyu2AvSaqG6l/XZ1W6iMz pfYg== X-Forwarded-Encrypted: i=1; AJvYcCXhfb5f5FyybiKcsyK56/WoF7xT4m3BsdK/CaqSyJH3sXv62VYBCL4FpsUay6I01HE1DPtxqAsJZRSAQs8rereycw==@postgresql.org X-Gm-Message-State: AOJu0YylY4xzghJvIY3zqcCKEc6JKGSprGNLG0edPhikIkcs8GHsKDUa jlD+iQbWZsd5QJp/SEO45WZM29WxNNai55BJ5OTEY2BUy5fD6o4D0Dhz X-Gm-Gg: AZuq6aJxQLba89G9/gAZbJ2I3f/+p5olCmh48Fhs3KFlrlHgqjq5KQ+8lYMVWNOAacC TumA1RmZc3UgaZtoQxOdpHaZ6rXyrS00LGCZtqSAaGGeIJK3vPTpwWWbLMBrior51avuUtpeohZ Es59O+K9gXk9zqDULP15/mqVWhrAxca7NjZA1fwV4o4gk5zsvguxVBSD1ZEHeqRGRZM4LdmKQG4 onGs+chgjDrYXgfLcz0Wb1TW7wgiRV43zmii/thta/9EsJd0HX7cMWTHlPGCc18xj0smgiNzMfO D7U5yGAYvS6KGlK+WzAhac46dvAhe6fTQftjR4Xs4rkMOLAnKuKjb/1GXOgIx2D02rHhB64ZFOx ET1MV53g/yYPj3SFHMtO5ZsNzllxXI7uhncJgZnGj/6C2zRZElmBS2E9JbtrpSmvty9Y+CjBjbq uJY+UNwtRElE7f1pONwJ500S+yTtUZ+oRA3cdN07Puwr0F7haDOIPoxUodIcgloVgY/j1KPge5M Q== X-Received: by 2002:a05:600c:a03:b0:483:71f7:2796 with SMTP id 5b1f17b1804b1-483a95c7116mr162411005e9.10.1771878332769; Mon, 23 Feb 2026 12:25:32 -0800 (PST) Received: from [192.168.0.86] (84.123.225.106.dyn.user.ono.com. [84.123.225.106]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-43970c00e8bsm18081990f8f.15.2026.02.23.12.25.32 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 23 Feb 2026 12:25:32 -0800 (PST) Message-ID: <5bace3e5-3d6b-4f81-92a9-9ff4e9e5de53@gmail.com> Date: Mon, 23 Feb 2026 21:25:31 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: unstable query plan on pg 16,17,18 To: Attila Soki , pgsql-performance@postgresql.org References: <64ddfe83-317c-4cf1-b04f-cb74d9f40629@gmail.com> <529E2365-6C3F-4BDA-9625-312F3A023C5B@gmx.net> Content-Language: en-US From: Andrei Lepikhov In-Reply-To: <529E2365-6C3F-4BDA-9625-312F3A023C5B@gmx.net> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 23/2/26 18:03, Attila Soki wrote: >> On 23 Feb 2026, at 16:54, Andrei Lepikhov wrote: > see my previous answer: > https://www.postgresql.org/message-id/1695A676-062B-47C5- > B302-91E2357DC874%40gmx.net 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 Thanks. But I meant your 'good' plan built by the PG14. I think a new feature invented later has added some problems. Current conjecture is the following. As I see, the main problem is with Right Hash Join: -> Hash Right Join (cost=210369.25..210370.30 rows=8 width=99) Its inner side (Hash table) is rebuilt multiple times (around 1k) due to an external parameter (gauf_1.id) in the subtree. It looks like a disaster, and before I thought we don't build hash tables over parameterised query trees at all. So, let me discover a little more, but your PG14 explain could add more details here. -- regards, Andrei Lepikhov, pgEdge