Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pS3uh-0005j7-FR for pgsql-general@arkaria.postgresql.org; Tue, 14 Feb 2023 22:36:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pS3uf-00053r-KY for pgsql-general@arkaria.postgresql.org; Tue, 14 Feb 2023 22:36:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pS3uf-00053i-8Q for pgsql-general@lists.postgresql.org; Tue, 14 Feb 2023 22:36:37 +0000 Received: from mail-pj1-x102e.google.com ([2607:f8b0:4864:20::102e]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pS3uc-0002qE-Ot for pgsql-general@lists.postgresql.org; Tue, 14 Feb 2023 22:36:36 +0000 Received: by mail-pj1-x102e.google.com with SMTP id oa11-20020a17090b1bcb00b002341a2656e5so208288pjb.1 for ; Tue, 14 Feb 2023 14:36:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=R+RudepPj58OlbNYAWnAEhpgiK6kniaPlMFHc/kHdLg=; b=WqNWv4Fce6SUXxswuDO4KcbivHmQaYfSkVrnn7faluIUndCaG/+qPFbtV8WJlov8lh xMVXr8/uwJSncpNOAGIpC3nAzznnaqEkumohuHICjwSdEFd9cVPpoujYXaNJ1I42qE/N mb5cujfLUlo3yr748skSBW7dvo+dVh3i9RqC2f4XGlE+XWQpIMT0qobNGppMk7E73JEk MqGVhOSK0zSMwUVFQ+fZmrezUsb1oBesDWRGvCbLoxS6FbUQmNPITst86egvi1pbwqHi QD0d6fCbMrR5yWSJVmMwt1QqfD+22UXbvKwUx11lot5ppkxKKSvmiOhkcZqOsnirmYKp 0Jxg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=R+RudepPj58OlbNYAWnAEhpgiK6kniaPlMFHc/kHdLg=; b=AYP5e67hkQaLcAuw1eVsainfFaW4EEg1dBi7nymiRmLauGfts02Sa6f78Iqw6oi7/C 7YjaitpylGJiKPZM3HF1HTvLypMZ9ix1xLBSE+mzrtFhFTw/OjtvuoUI6tfl4SgbtDjd wV3BVd4gpUxaNjDFKzLwqVITpr6QJBFE7mua5/hgf0ilFjyibkolhomX5tz+xIoruXM5 cQyW5B/ynelElo4yBl1G5COv4UMelONhnZNUYj1M9zBSk4NV4TCDfflIqhyLciDc0YOz q1ClmQRLjC9h89N53JHdhJCEoAp3hJDDQ4n4rOv+SI1y0jZ9gUHkIWZHY9sbHxKhrLuw 8myw== X-Gm-Message-State: AO0yUKXXdDdbghYCOLVRCVpH7yY9C1LU/nkTQvs0AA/VOuUee6+Dj3Rd RuPROMJ0NDGzIg4JqzKsVYE= X-Google-Smtp-Source: AK7set9EBHW1s8FShBuClpoS8h/88Qe1H8C5Pli4qoTHQV1KhXaqIRBFg52/KQ7tKcrHLkvsGRXozg== X-Received: by 2002:a17:902:ec8d:b0:19a:aa9c:c654 with SMTP id x13-20020a170902ec8d00b0019aaa9cc654mr237718plg.32.1676414193771; Tue, 14 Feb 2023 14:36:33 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:996f:7caf:dfa2:fac5? ([2601:681:5a00:f790:996f:7caf:dfa2:fac5]) by smtp.gmail.com with ESMTPSA id jw7-20020a170903278700b0019a593e45f1sm7041649plb.261.2023.02.14.14.36.32 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 14 Feb 2023 14:36:33 -0800 (PST) Content-Type: multipart/alternative; boundary="------------TTAOPDpDvR7dGjoBfrdS17I0" Message-ID: <8e744026-7547-5d4d-b76c-ca152881ccce@gmail.com> Date: Tue, 14 Feb 2023 15:36:32 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.7.1 Subject: Re: Query plan for "id IS NULL" on PK Content-Language: en-CA To: "David G. Johnston" Cc: pgsql-general@lists.postgresql.org References: <8e622f19-bc95-eac3-188d-b03734f547f6@gmail.com> From: Rob Sargent In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------TTAOPDpDvR7dGjoBfrdS17I0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 2/14/23 15:30, David G. Johnston wrote: > On Tue, Feb 14, 2023 at 3:25 PM Rob Sargent wrote: > > > When will id be null in a primary key? > > > The OP seems to be aware of this... > > "We cannot change the query being executed. Is there any way we can > make the query planner ignore `OR (id IS NULL)` (as that will never be > the case for the PK) and use both `id` clauses in the index condition?" > > David J. > Yes, agreed. But if the query is supposed to be generic and re-used in a situation where id could be null, wouldn't the null id records be fetched every time? --------------TTAOPDpDvR7dGjoBfrdS17I0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 2/14/23 15:30, David G. Johnston wrote:
On Tue, Feb 14, 2023 at 3:25 PM Rob Sargent <robjsargent@gmail.com> wrote:

When will id be null in a primary key?


The OP seems to be aware of this...

"We cannot change the query being executed. Is there any way we can make the query planner ignore `OR (id IS NULL)` (as that will never be the case for the PK) and use both `id` clauses in the index condition?"

David J.

Yes, agreed.
But if the query is supposed to be generic and re-used in a situation where id could be null, wouldn't the null id records be fetched every time? 


--------------TTAOPDpDvR7dGjoBfrdS17I0--