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 1s6Vdn-0001k0-J3 for pgsql-general@arkaria.postgresql.org; Mon, 13 May 2024 13:22:56 +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 1s6Vdm-001FcT-PY for pgsql-general@arkaria.postgresql.org; Mon, 13 May 2024 13:22:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s6Vdm-001FcK-Dm for pgsql-general@lists.postgresql.org; Mon, 13 May 2024 13:22:54 +0000 Received: from mail-lj1-x232.google.com ([2a00:1450:4864:20::232]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s6Vdj-0000oi-J5 for pgsql-general@lists.postgresql.org; Mon, 13 May 2024 13:22:54 +0000 Received: by mail-lj1-x232.google.com with SMTP id 38308e7fff4ca-2e3e18c240fso46618891fa.0 for ; Mon, 13 May 2024 06:22:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1715606571; x=1716211371; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=dQygzUm02m8EuqyqHfju0KYoIGLS6E3BuONO6YlhRFI=; b=MYflmwahqtcx56Vn6p4ftrmI2rLOQg/kkMxFj9fcefdb8lmSP8c8hqw/aN3MmIG6Zq FLi8z5Rg9itlJ92f0x/oKuA7u+LpcPsLRL/sh9kxpCD8GNjQy7ZWZzwGCwzxzdMgqbVn M8LAqWwsc38pjXv3mVXQ4oQ/7yrcDHE331qw5cI/wnH+pvCBA3y1Rj3YLpchi6motqtZ MhBzIUKPqkGcPYDpns1V7p/yypicj8vxRRvOfjKymjXD8bGDkcKyRjth9b4W4U/phzmg /pwCGZSmdLOGKxapTtH1HSY5Nvmd82qoBjZ0NMO9Yci7eutx60GrtRD0LXEBY7s9UC3F WpIw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715606571; x=1716211371; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=dQygzUm02m8EuqyqHfju0KYoIGLS6E3BuONO6YlhRFI=; b=iqzUW50JXLtZeF6pbbORpf8hD4TOw+yeGFChJmuwHm/jwEQw5m4lzgQrRdQOrQNjHO RzRG8r8tuJtsv5+TeUHcW3ZDkf9gFndxa/BzqyuIenEfhFhlUbKchPISY/BYBTf7ATaQ szFJa6lq+On3LpyF+jhch5UgUIHDka845U7lkwqZkke7tKIkWADMqNedEKPyBv08aF4O WLDEdUDkslihAbmnwo8x4QQbQlycf77fUVMM2sfK2StdmjFrKRnOl07Ef0knEvibLYTx W+t09eOJomg/rZyMUVWSAde8fLeG9Ws73R6A+Bwo1fLVwwW+/hLS4gtJtd0Y4S6hKeMx 5HHQ== X-Forwarded-Encrypted: i=1; AJvYcCXYBDj4EnTTMB4UN796+WqbmiJKqW6Ui9hcHToSQzqm8kdSqnM9WluynVhEjHLZDDAgyo1TU4iZfbe9Z3kSRFy9m6Z5xY1RRArHYvcxNJFd3nj+ X-Gm-Message-State: AOJu0YwJQE4vwEcQOmPUsbzfHD7C/DYeHklbj9jGVR1RwTq/rHG7Jxqb 4nP3jIDWXMcXysBjl8lNeY/i1VVHzU30Rb4TGUHQJuFVnxWUmlHjdiQCTwHb7Tq0jY9sD4jJWat 7KVA4moyQQMn8aKhG/GIGtxXfJ9U= X-Google-Smtp-Source: AGHT+IGbXht2dsBPzKWOmaV0wdX4POixetHnYO5SAbVjhtu55V5azE+YDISsVb6zyHvrzIRzo4X7aW0vD+YI4Y4RR7w= X-Received: by 2002:a2e:2e12:0:b0:2e1:f338:d228 with SMTP id 38308e7fff4ca-2e51fe540d1mr83056511fa.20.1715606571224; Mon, 13 May 2024 06:22:51 -0700 (PDT) MIME-Version: 1.0 References: <7886a68f-b466-2131-1747-f69f0fb71a37@gmx.net> <69077f15-4125-2d63-733f-21ce6eac4f01@gmx.net> <559b0e40-63e6-fa9a-6b03-d1eba10f30f8@gmx.net> <1629463.1715372568@sss.pgh.pa.us> <6a2f3906-3d7a-6924-7403-8f77d57a18e4@gmx.net> <1685688.1715391218@sss.pgh.pa.us> <2e3947bd-b01c-37c8-00e1-d8e925e79597@gmx.net> In-Reply-To: <2e3947bd-b01c-37c8-00e1-d8e925e79597@gmx.net> From: David Rowley Date: Tue, 14 May 2024 01:22:38 +1200 Message-ID: Subject: Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions To: Dimitrios Apostolou Cc: Tom Lane , pgsql-general@lists.postgresql.org Content-Type: text/plain; charset="UTF-8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 14 May 2024 at 00:28, Dimitrios Apostolou wrote: > > On Sat, 11 May 2024, David Rowley wrote: > > > On Sat, 11 May 2024 at 13:33, Tom Lane wrote: > >> I do kind of wonder why it's producing both a hashagg and a Unique > >> step --- seems like it should do one or the other. > > > > It still needs to make the duplicate groups from parallel workers unique. > > Range partitioning of the table guarantees that, since the ranges are not > overlapping. That assumes the Append won't ever use > 1 worker per subnode, but that's not the case for your plan as the subnodes are "Parallel". That means all the workers could be working on the same subnode which could result in one group being split between 2 or more workers. Parallel Append can also run in a way that the Append child nodes will only get 1 worker each. However, even if that were the case for your plan, we have no code that would skip the final aggregate phase when the DISTINCT / GROUP contains all of the partition key columns. David