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 1pS3k4-00055j-Hw for pgsql-general@arkaria.postgresql.org; Tue, 14 Feb 2023 22:25:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pS3k3-0006BN-Cd for pgsql-general@arkaria.postgresql.org; Tue, 14 Feb 2023 22:25:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pS3k3-0006BE-1u for pgsql-general@lists.postgresql.org; Tue, 14 Feb 2023 22:25:39 +0000 Received: from mail-pg1-x532.google.com ([2607:f8b0:4864:20::532]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pS3k0-0004E9-Px for pgsql-general@lists.postgresql.org; Tue, 14 Feb 2023 22:25:38 +0000 Received: by mail-pg1-x532.google.com with SMTP id c29so7146349pgm.5 for ; Tue, 14 Feb 2023 14:25:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=L/2+LYZLOiynZpCMIrjLoyFLSMpsIb13iLs93O3xQL0=; b=MqxOmGq3yUPFOWsHJ7GAgj9lYFdQN3aMhgjfAyWPtJQBeu+K7x9562PM33i9L71BnO we/qNUi+q9pJ0taP0Epc0QIIINSLKgtpz1VBFC2h/e1bfagANMPNNv8r08A0KBt5Ouv/ vdxQ/3uXXFcYy2w8Jok34oSPzOmqFS7fXzhgdBKzqY8f+fJ3vOsYyylM/3BwvLbXBRwf RgQ7EjsWXNKibxUpBS5vblhrjtJPwWErdEzqbyNQBEq/FEDgZ0Auh5d9UkRu4j/nq87X rHRwEKV0GWsM1J53Apsb+tXp9QaPkrOsed/Xpwreat2kCIlpXj6y0lD74pzS0x+/9ioh LrYQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references: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=L/2+LYZLOiynZpCMIrjLoyFLSMpsIb13iLs93O3xQL0=; b=wCBn4JuoolPXyeOlB2u9rExd1KUWbiSH29xFaAD9AGSMOa3sZcxotGUqEpUfEQA4mH leJpcOLI8UKxLSOO79ng+nNiAMbtZtmDJzEHM3TzRvTHLYfMQ+xk6RWL+oQpTv27MNjH 4rw+AppiG799WHsz8lmW4ik6wPdG6UmdGtVgtcGe3kSb66EJ2Bm/awgJ0MhVst/kOY/x B14VDbfLht8hyg0clOf3hyn9vmGqw9ZhgbWniExHa1TBJ5+YK7Q6xRumYQjFk0k0W23P aSef4gJLS9uij+KdZqOQhV5MVZlY1rtKk03Dr54ijK83k4Vpg30gxrChM0vcjFz3WuGJ bPMg== X-Gm-Message-State: AO0yUKVn7slnD9O0Tb40ggTZ8TUSZOgVJeOgyDzAveeWrKHy4OQmMkw5 J3VRXVMEZ/tQMUE1Xf37raZ48r/cCDs= X-Google-Smtp-Source: AK7set9SRKK/EqS3Jlv99Cnzva/3md7uJjof2xS9EH96qcgZH8QhEhOoRhabNu4okeoNPTVATgixXg== X-Received: by 2002:a62:4e57:0:b0:577:272f:fdb with SMTP id c84-20020a624e57000000b00577272f0fdbmr2758236pfb.29.1676413534441; Tue, 14 Feb 2023 14:25:34 -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 t13-20020a62ea0d000000b0057fec210d33sm10260156pfh.152.2023.02.14.14.25.33 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 14 Feb 2023 14:25:33 -0800 (PST) Message-ID: <8e622f19-bc95-eac3-188d-b03734f547f6@gmail.com> Date: Tue, 14 Feb 2023 15:25: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: pgsql-general@lists.postgresql.org References: From: Rob Sargent In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/14/23 15:04, Ben Chrobot wrote: > Hello, > > Long time listener, first time caller. > > We have a large table (~470 million rows) with integer primary key id > (not null) on a Postgres 14.5 cluster. A third-party tool is > attempting to perform a SELECT-based full table copy in preparation > for log-based sync with a query like the following: > > SELECT "id", "other_column_a", "other_column_b", "created_at", > "updated_at" > FROM "public"."my_large_table" > WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?)) > ORDER  BY "id" LIMIT 50000; > > The lower bound increments by batch size (50k) while the upper bound > is always the `max(id)`, in our case around 575,000,000. > > The query plan produced is very slow as the index condition does > basically nothing: > >                         QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >  Limit  (cost=0.57..21901.46 rows=50000 width=417) (actual > time=1708920.675..1709198.995 rows=50000 loops=1) >    ->  Index Scan using my_large_table_pkey on > my_large_table  (cost=0.57..135230792.97 rows=308733624 width=417) > (actual time=1708920.673..1709195.926 rows=50000 loops=1) >          Index Cond: (id <= 575187488) >          Filter: ((id > 193208795) OR (id IS NULL)) >          Rows Removed by Filter: 157784540 >  Planning Time: 0.186 ms >  Execution Time: 1709200.618 ms > (7 rows) > Time: 1709231.721 ms (28:29.232) > > 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? > > We have provided the vendor with the same query without the `id is > null` showing that it's significantly faster (see below). They have > informed us that addressing the null check on a not null PK is on > their roadmap to address but no timeline. > > explain analyze > SELECT "id", "other_column_a", "other_column_b", "created_at", > "updated_at" > FROM "public"."my_large_table" > WHERE (("id" > ?)) AND (("id" <= ?)) > ORDER  BY "id" LIMIT 50000; > > QUERY PLAN > --------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Limit (cost=0.57..13930.19 rows=50000 width=416) (actual > time=2.118..400.937 rows=50000 loops=1) > -> Index Scan using my_large_table_pkey on my_large_table > (cost=0.57..85429173.84 rows=306645829 width=416) (actual > time=2.117..398.325 rows=50000 loops=1) > Index Cond: ((id > 193208795) AND (id <= 575187488)) > Planning Time: 0.166 ms > Execution Time: 402.376 ms > > We have tried leading the planner to water with this view but it did > not change the slow query plan: > > create view my_fast_large_table as > select * > from my_large_table > where id is not null; > > Any other tricks to try here? > > Thank you, > Ben Chrobot When will id be null in a primary key?