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 1vtROl-007tdQ-0E for pgsql-hackers@arkaria.postgresql.org; Fri, 20 Feb 2026 14:22:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vtROj-008Com-23 for pgsql-hackers@arkaria.postgresql.org; Fri, 20 Feb 2026 14:22:25 +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 1vtROj-008Coe-16 for pgsql-hackers@lists.postgresql.org; Fri, 20 Feb 2026 14:22:25 +0000 Received: from mail-wr1-x433.google.com ([2a00:1450:4864:20::433]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vtROg-00000000NJs-24w2 for pgsql-hackers@lists.postgresql.org; Fri, 20 Feb 2026 14:22:24 +0000 Received: by mail-wr1-x433.google.com with SMTP id ffacd0b85a97d-436e87589e8so2148320f8f.3 for ; Fri, 20 Feb 2026 06:22:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1771597342; x=1772202142; 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=rfcKkaMe0u90Yl0mGOciXJCuZ6C+LSyhd17abKZ2EWc=; b=CVHsOCgHJa+94iaxC9znADdCbWeiTIkT9wmFL+GqXBSkmsMcdgTVXJZs43AK7KQtgR DdLiK17250rnkipFLeAP9YyKCcxBEgBe6IMOoxbFj5EMg/itNf63lwo7pJqPKmOGD8QD uaKai+txCKlZ1rN+1mI9bZM12qmm4RS2lidBVZCweeBUBQMekRHnBpHQyPcnIcU8pwIX WRk0+UZAcMhFt8VuBmo/6tcsm65dUJWw+V5rDTfFvuNI/jxLQOuoZARRTnF+TUIWkI5Z 0erHj0m0X4jn4PU2Yl+xigq0DNurvzIqQ7NpQtkmVB1DVY/5NWqEQqHr7gwz2EuHh6E/ uhJg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1771597342; x=1772202142; 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=rfcKkaMe0u90Yl0mGOciXJCuZ6C+LSyhd17abKZ2EWc=; b=e2wXHE76IIiWzEhRmp3KfltRxGKVegCPO0eFBtOAh0lIfcOIxzvbZJ4eZ0N9MS7FGS yAIKjDBPPkzot3EGy0ojpEdwAfdrTpkSzEfjiTl5yNjzdU8DSJXQgZCW6OLQaaXfu7eR QDnjvyX1WHm4HXEfb1elJQZi0OrNrLcpwrFY/RiqmgXGHy25cXWgEvcwAt812P0FkIim tiadCP5UTSwxni8UkiEcekzmu7HYNBhJ9y3sqJaBA/1Rtybuu/5YCawJo9WNMHOMWrcp r+SjJJLknG2WTNcvUrBaQeSBdOnkbMHTwvj8r0oA3wWYXZiV4/0AFk+aJ15XGi0/+I11 iq6A== X-Forwarded-Encrypted: i=1; AJvYcCU0LXGS2/HviZe8szYhFvaOkt9LFseVYyHnUnx+jYTDl6OAjAYq4KZ9ZRX9of5PkxgS2cEUbBOQiuEtDlsX@lists.postgresql.org X-Gm-Message-State: AOJu0YyaOGranA+YQ7rSUWgK+a8Jin+iW+U2vOFfDeUVJEf8oAb4SlSN TfgGC4v1Wb4IB/hehN/hph4l0sg9fKHu2bYWlL807kz2QI3VMK3hURZ8M3cokL2Fnw0= X-Gm-Gg: AZuq6aIz5XlaQC/XwgR0tetCWxuGTDttBUIlR8hHMd48NgjzhVfxi5BsdAVislR9LDh sQ/4O7Zp66rfr3oNcz68q14ouQdlPEMzFi3gec0reR9z7aVcpacBQO4FMO/OD98xX7fNaIe6WHJ at4yQQiB7wx+oPt4uFOeWKeEMvrsoHW7cen8MKKMlaTHi9PSs112YPncNw93aTF+n1SAOrJ+m6z 6Y7B08ax8znJye4bjCfXLhR8vLZgtghQIEhOBBRXRNd7lgxtgcMx3ESLA+InWx5vEM/VVNEamOV prFrhWjYJdit1YyJ5Lh0E391PHj5TW/R5ANw1W4yuQRd/j+pbYbT+kvt6+VUyVT2KEoWhHP6rnK 80bZ5LTzUIuU5R1oDEkWdT1lnkCzaZkW4Br3dRO8MO4r9ELWrKX9bf8MKeIzM22ySnS9CTxNdrT jkVazJY+f1XSZVzRfb3u4= X-Received: by 2002:a05:6000:601:b0:432:5d73:79a1 with SMTP id ffacd0b85a97d-4396f188996mr84223f8f.55.1771597342010; Fri, 20 Feb 2026 06:22:22 -0800 (PST) Received: from [172.31.5.233] ([165.225.27.16]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-43796ac8d46sm57379522f8f.32.2026.02.20.06.22.21 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 20 Feb 2026 06:22:21 -0800 (PST) Message-ID: <6b3aebb5-f26c-4f50-abd6-e733d452af26@gmail.com> Date: Fri, 20 Feb 2026 15:22:20 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Reduce planning time for large NOT IN lists containing NULL To: Ilia Evdokimov , PostgreSQL Developers References: <52596e08-0902-475b-ad0f-265ec6918f19@gmail.com> <7791ec74-369d-48c7-969c-cf4ad18024f2@tantorlabs.com> Content-Language: en-US From: David Geier In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 20.02.2026 14:28, Ilia Evdokimov wrote: > On 2/20/26 16:25, Ilia Evdokimov wrote: > >> I've moved the NULL check higher, placing it immediately after >> DatumGetArrayTypeP(). This allows us to detect the presence of NULL >> elements before decontructing the array. >> > Sorry, I forgot to attach the patch in my previous mail. Attaching it now. Cool that the array code had that functionality already. The patch looks good to me and now no longer regresses other cases. The speedup will be less pronounced once the hash-based [NOT] IN code is merged [1] but will still save considerable amounts of cycles. It seems like we don't have a regression test which has a NULL value in the NOT IN list. Maybe you can find a good place to add that one? -- David Geier [1] https://www.postgresql.org/message-id/flat/7db341e0-fbc6-4ec5-922c-11fdafe7be12%40tantorlabs.com