Date: Mon, 13 May 2024 14:41:05 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: David Rowley <dgrowleyml@gmail.com>
cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-general@lists.postgresql.org
Subject: Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan
 on huge table with 1000 partitions
In-Reply-To: <CAApHDvrtTKfh7HgAyXBd3KN0s-jxiHzW7sWdm-sFEjP6fGPCkg@mail.gmail.com>
Message-ID: <3efce60b-48c0-160d-0444-474b02f76739@gmx.net>
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>
 <CAApHDvrtTKfh7HgAyXBd3KN0s-jxiHzW7sWdm-sFEjP6fGPCkg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:WEg81dUSzAs=;KswOlTi4oO/F9IFBooESJJyWkMn
 CslJuIwjkHBaovdy4/iR8w/lZCnMrHWnGNmDPmyBbf2XTlEbwoF4RvTfp+SM0gDchUPLPJN8C
 P2MAUR+NBZmVCUhONv1VOZYNVfycjiZVeueCkUw+T0OwoQenvYvH5R1FDie87UlBKehjspdF6
 D+yhXFnhyKLgUpiid1pNKe4MNEk+MIm3Fn7FGlFu+z2HhB60FmXOQbS78jbPm3CQip1opnNx/
 IT9exn+myJkjFtpyFbmri8O7qRYukvBIIXSvbQhQJaDimAMUiI6rU/riI/k8UN4irP9Z50PzM
 jlU25Of3b3R8WgRRNlxopJEEjL2QTrgETZjS0lVFKQ0lZHD6ZoHy5e+0d866I33Jz6p5RykLT
 qUKHJN08cwaZIEnCQaCXhfUWOaJXMvnWjGKGo/Hw1twDgIyYo+LGBi/cJCqrt1kfW37wSWkTJ
 8DaZz5JPZ6D8+OpIVDOXCFQGyVLqyYjmDM58RmIGzdFZghTgJLMn42VnD/UjqsQfU9T+GwX3g
 zo1/a0lcerkU3piKclH47KtANtTHmgsWl00h7L2fdP4oID63h2Ucu6VgokjGN8lzSTCo1vdFC
 GtspBg27d+5M0eyxb0/HZrlh9+u6CgFYuzB3DGtMG+zhSzxVkIhg9r5Ks8CqOcaiwotuWrEU3
 SGPULkzMTRgIjOw3HaJQgNgHX5PAepVrarK5VMcoyOO0LbMum9epUI0aeD/9HxmWu3Knkp9Sa
 9bUpRSRGgXg0Kfa/+20MIWO+2f20g7gjaDqDvKhpwiTdlt5lrbSIaT55C6NYdOGGjbEkymhNJ
 Od2pLHW55A6by5ynvr600Yk8LOz4677uPNJiIFeoZHGhE=
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/3efce60b-48c0-160d-0444-474b02f76739%40gmx.net>
Precedence: bulk

On Sat, 11 May 2024, David Rowley wrote:

> On Sat, 11 May 2024 at 13:11, Dimitrios Apostolou <jimis@gmx.net> wrote:
>> Indeed that's an awful estimate, the table has more than 1M of unique
>> values in that column. Looking into pg_stat_user_tables, I can't see th=
e
>> partitions having been vacuum'd or analyzed at all. I think they should
>> have been auto-analyzed, since they get a ton of INSERTs
>> (no deletes/updates though) and I have the default autovacuum settings.
>> Could it be that autovacuum starts, but never
>> finishes? I can't find something in the logs.
>
> It's not the partitions getting analyzed you need to worry about for
> an ndistinct estimate on the partitioned table. It's auto-analyze or
> ANALYZE on the partitioned table itself that you should care about.
>
> If you look at [1], it says "Tuples changed in partitions and
> inheritance children do not trigger analyze on the parent table."

Thanks

>
>> In any case, even after the planner decides to execute the terrible pla=
n
>> with the parallel seqscans, why doesn't it finish right when it finds 1=
0
>> distinct values?
>
> It will. It's just that Sorting requires fetching everything from its su=
bnode.

Isn't it plain wrong to have a sort step in the plan than? The different
partitions contain different value ranges with no overlap, and the last
query I posted doesn't even contain an ORDER BY clause, just a DISTINCT
clause on an indexed column.

Even with bad estimates, even with seq scan instead of index scan, the
plan should be such that it concludes all parallel work as soon as it
finds the 10 distinct values. And this is actually achieved if I disable
parallel plans. Could it be a bug in the parallel plan generation?


Dimitris