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.94.2) (envelope-from ) id 1tuXkI-009f0v-Cc for pgsql-bugs@arkaria.postgresql.org; Tue, 18 Mar 2025 14:16:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tuXkG-00FiCz-PP for pgsql-bugs@arkaria.postgresql.org; Tue, 18 Mar 2025 14:16:40 +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.94.2) (envelope-from ) id 1tuXkG-00FiAT-Ga for pgsql-bugs@lists.postgresql.org; Tue, 18 Mar 2025 14:16:40 +0000 Received: from mail-wm1-x32b.google.com ([2a00:1450:4864:20::32b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tuXkE-003WJB-2D for pgsql-bugs@lists.postgresql.org; Tue, 18 Mar 2025 14:16:39 +0000 Received: by mail-wm1-x32b.google.com with SMTP id 5b1f17b1804b1-43bb6b0b898so33724015e9.1 for ; Tue, 18 Mar 2025 07:16:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1742307397; x=1742912197; darn=lists.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=5Bk7A+iIIYU/ju6fnHsxZiajXUJPYT1zHGLp/F2+MSA=; b=G+KCtupKki5MLrOGyB1/66V/TlNNJ0srC6akJ+20bWZV81g2nkMW+WCEuV8JyhNRRU HaqEn70SuBTetzQ4XkD4P1VvftfOICmKciaM2gkLk4PRCTNGBQQNgQmk6k4eLRTJ/6Yo GfQVPiSpqH4XsLE6YVmwdZKtjHvuFOkHgvhtEeG2v3vUGMa9fep/LOS4W/ggbNIYHixH 3z+TiYbilsGA8yCt2DZlk+VlMs2bxopiO5Py0M1INNwnTsA0g6jrn/W/wOIykWaMpiPZ iaV0sgc0AwB+UbGmdN32R4bRHITEVx5pVdJH5j8aqRIBBBrxF/2sTOYWh2r/mN1AddXj cReA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742307397; x=1742912197; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=5Bk7A+iIIYU/ju6fnHsxZiajXUJPYT1zHGLp/F2+MSA=; b=fGOrZWqMH4lx+Tja3apUjDz2/UY2WRihuO5tGBiNuy+oCn8cF5JnymGnPKT3BZL/5V k/Y88SkMGGNI4Xi36lInwPKGdFQPfg54aGzdSIUxrwmsV+3skrsFUJsKIQ1EX2wDdgog JULKiqgYNuaY+rWdfLmvQHaAjxHYZKhKrGhpFguSVxYnfC7reQ+17AVaYDh+oG9bWIE7 6i4vOYC545y7/rChPdk6qDh/ckh+2jOYRyz4KTGrFo634cn2ycdeMUmmEu9XwRn1yOYT 8yALCrIsMKPlxAgJoTXi/yCNiPsZ5Ya8dES7BLqlxJrscN958JgOourZYHew2qxpLA3q JAbw== X-Forwarded-Encrypted: i=1; AJvYcCVMxvBI7ySIRCS5WB4G7uBqX4dHr9pATghmFzVoBN88zUEW6PXXhWVCdm8tuO4Kt5A/ohhmSq7sFZ+P@lists.postgresql.org X-Gm-Message-State: AOJu0YxuOxUBd5eAW0Oy1ytOHyo3HW7iu54aLTYOStKy2RY2nY1loZlc rzAZ2iWnx6nn239c/fOJ/jLzJL2rFfc3ACLqQBJF/zAzhOLoWYN+ X-Gm-Gg: ASbGnctand6+DQE5n6sG2kNUttb7qMi2Rx5P9LomU/NGw6F2CKRb/YZfoEj4I+O+ziY t7i7GNMVKa3VlB0RG2UWxY46+pC1aZgXVXNQ/CSHB67XnY6+ZZJP37FOzBeWG6r5GCqzrx9EVXR 5csPMBYWyg7ZyWTi5CgUgJXOGK+5dYB32jti7LodtKnVSgt9XbAob7lBVab5Z219SVnzCZO3kgW Z1K48PGcSRx+whAvk/sZXM1wVlxmSHOgOCwBCoqZ0bRzh59zRRfdmQn/ejg8l1bng/iMRTEls/F FnPYn0c0/bC60S6y3Ub0Rm5ZEgIAqwUFpgKpZoVFAqatLI7jBU4h9Hr8bNBoWlv2tHFDbJ9WaQ4 MYiI= X-Google-Smtp-Source: AGHT+IEVkml5uv7Q1CJF7FB6Gkhcmd8BD5DhrcJZAczSlaOq9aszSLKJmrMmZsGgeJzFfHs+yo5ntQ== X-Received: by 2002:a05:6000:18a6:b0:391:23e6:f08c with SMTP id ffacd0b85a97d-3996b4a1ef1mr2856993f8f.47.1742307397264; Tue, 18 Mar 2025 07:16:37 -0700 (PDT) Received: from [192.168.0.89] (84.123.232.230.dyn.user.ono.com. [84.123.232.230]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-395cb318a96sm18549002f8f.69.2025.03.18.07.16.36 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 18 Mar 2025 07:16:36 -0700 (PDT) Message-ID: <91d297ae-71ca-4406-a404-f8daa0b0c4a5@gmail.com> Date: Tue, 18 Mar 2025 15:16:36 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: BUG #18854: PostgreSQL chooses a suboptimal execution plan when using a specific WHERE filter To: yonisade83@gmail.com, pgsql-bugs@lists.postgresql.org References: <18854-f5bc660117569bdf@postgresql.org> Content-Language: en-AU From: Andrei Lepikhov In-Reply-To: <18854-f5bc660117569bdf@postgresql.org> 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 3/18/25 12:34, PG Bug reporting form wrote: > Query has a specific WHERE filter for tiny lookup table "t8" (19 rows) which > generates an execution plan which has two "Nested Loop" steps instead of > "Hash Join" steps which makes the query finish after 90 seconds (and spiking > the server CPU when being ran by dozens of sessions simultaneously) instead > of ~3 seconds (when commenting out that specific WHERE filter clause I > mentioned below): May you provide EXPLAIN ANALYZE VERBOSE in standard text output format? -- regards, Andrei Lepikhov