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 1s6wr5-002eLO-Jn for pgsql-general@arkaria.postgresql.org; Tue, 14 May 2024 18:26:29 +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 1s6wr5-001094-HW for pgsql-general@arkaria.postgresql.org; Tue, 14 May 2024 18:26:27 +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 1s6wr5-00108v-65 for pgsql-general@lists.postgresql.org; Tue, 14 May 2024 18:26:27 +0000 Received: from mout.gmx.net ([212.227.17.22]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s6wr1-000EMx-RX for pgsql-general@lists.postgresql.org; Tue, 14 May 2024 18:26:26 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1715711182; x=1716315982; i=jimis@gmx.net; bh=/tKVh1eWAh7mMU0Vex+/MaFAiAQSHBOkuj/KaI7bSKk=; h=X-UI-Sender-Class:Date:From:To:cc:Subject:In-Reply-To:Message-ID: References:MIME-Version:Content-Type:Content-Transfer-Encoding:cc: content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=Ep9+3Y5s83FPxRaqCSMKkc4Zz8Us87z68Y84EzIyurSWyoufw6GQsiiKCyz7dmiQ NY3tjHpH5Pq4k7LlBYU21AvwL63+HKQpBFff6coSpYWO2YvJETnQkZ8aOXCSJEI5D ZEmDL68Rra9oOuQJZ9GAURF1KEaa1N/Z20iJOJG96kDp179Dqsr37u+/NAaUt67oF TG406pbF3Y24ttudw6733+IAYKNCmNnuRzb9l1/fQtAsrvqGWet3BR5ISZwENtQr6 XIu5jH176FnvQj72F4Wf5KTxU8lPaINfkx00jDUYzo85P5gksz3eKpvS2vTycEbSz 7EIRcfFMoeKGEflkUw== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.35] ([185.55.106.54]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1Mn2WF-1sp2yn1I5n-00k7u9; Tue, 14 May 2024 20:26:22 +0200 Date: Tue, 14 May 2024 20:26:20 +0200 (CEST) From: Dimitrios Apostolou To: David Rowley cc: Tom Lane , pgsql-general@lists.postgresql.org Subject: Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions In-Reply-To: <46f9dc8a-ff83-d086-f726-9df0a0d39c97@gmx.net> Message-ID: 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> <3efce60b-48c0-160d-0444-474b02f76739@gmx.net> <46f9dc8a-ff83-d086-f726-9df0a0d39c97@gmx.net> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset=US-ASCII X-Provags-ID: V03:K1:wFTO8+6jb97+X/pr2oHn+twTNoJD2/bNA27//1I3B541pY+Rhbz u5ujzEXlLoKrq6CHAOgwt+YBq4TZQOvwGFJA05kCoIZuJOSsYVzDt6LZtWBWh81RGWrmdk1 Y68fkGBB/4PmGnbxti17M0YO/NvGrsJgRuqJh71mSMK0O60AAjKLKZUsFs2pIK56mvv6Ks3 lB1/uK1o4U/JN7aLN/X0Q== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:nEbB1xsWRgw=;YzJKh0+sXkHjr0/UIW8TS1r5sdn YU/BZJHl/lxLBhqntFsP6HLowoWSvZ83o8yve88gT3kwWGXggrtJWG0bsdr1/Ydg85S6r2m/Q 7hLwuYSN8ULm2q+zze3U3HKxAhuQXtCAgdn5IxJi0/6N3nLyAkM3Jji5bbAuIRsLkDUgKkOWD KxDH6NuqlrOQ43WTpJkXhjpMT92W9tA84W3B661n+IibCQLZutsNgzMWol5qtk4OVre9VMIG3 Dg+815M1uOH5KOZsOcYj7o/9zGpUuBd7Q9mv4QUs2VJtOtY5wrwIZIae3EEcM+3Mqbz3qqauw XyUr4cwoBvvrUi7bxB/QXvodNIRGQiJ7JgI2N8vakDu2Xl+9B04Ocu9JyXo2f3fCEYHOyTRCB T+S70UIt0FQejmb2j/lGhO/9wWfCVfFGyD52ebYyKBNMJQQVNHUMkOp3Ouv12wep6qOWIzvHZ aFcFWF13XiMjtNcI+xmSNUtxGp6iDnxV1koe7SFR/eLsqSTjx6qgXuQnDdQMpBssqZiEMlGIN SmVjrltYB8krs6JwU/W+/+1rh4LaI87sR9f8HiTQnRqZ5Bi5A1QVJiAnPWHENgqJCEn1xqM8R xtZYKHtug7GfmiLozhKRvC5xBxPmBUHb9qDp0gVjPU444NMVoADeclBwlj61E7NrKjNmoDs+f o59yHN+Q/C3AbCIyLEfFhz22cbGfaC9OhOk3uaS/9Ki8ptBlk4qHVvHOvaoegOwrP4E37myBP 4prvQzMT3WHp23ypmtQSTUU9ep+RUkSnsoGYT/TIJVNjz3jayJ/5AEZUcf6CF0inQGGDJDZkP clL84CnknukuujC44s+eWoulQ0FaPsSOzqIIYZ57KymnY= Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I have forgotten to mention that I have enable_partitionwise_aggregate=3Do= n in the global settings since the beginning. According to the docs: > Enables or disables the query planner's use of partitionwise grouping or > aggregation, which allows grouping or aggregation on partitioned tables > to be performed separately for each partition. Reading that, I'd expect to see a separate DISTINCT->LIMIT 10 on every partition, and then it would be up to independent plans to decide whether each partition follows a parallel or a serial plan. Not sure if this plan was checked but rejected because of cost. Dimitris