Date: Sat, 11 May 2024 03:10:50 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: pgsql-general@lists.postgresql.org
Subject: Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan
 on huge table with 1000 partitions
In-Reply-To: <1629463.1715372568@sss.pgh.pa.us>
Message-ID: <6a2f3906-3d7a-6924-7403-8f77d57a18e4@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>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:RQ2rb0MNo3c=;EiJTYYC+jtz1/3s9xKH4Z6W3eSe
 iFlCWjD+GqpD7+uNQ+qV1nW/jGxzvU2Ri63Ujxut8tdjdglCOS9bXY25CVa0G7aJt3sazSDbb
 pxw+oFplW6DYhaLW6Cfl52GiMF4JW4PLg5hXfCoNnf2/Ti3ow+4OfWXYGNtKbmWZ+mcSChG6K
 5xmgNj6WasHcRzm0+vcbGVWkzdQGdKSaVamvg9Mq7Vw5SUW0aQJxYDYoW+Kyvk2IHP4AVGxXj
 KdU3b+iI5Gvkc79tGywAbjAWnATZxWaIddLlrOhBFG5MLJ+p0UiZwM6pUNWmvVZ+Lnf2H8ZdL
 xktT1lT7NlDEzRM77tIjY3Ci89tx5jCDvn7x+gtDZTo31zxx792OFAFUXrEvxzEww2ijVE8y/
 XuzVcumzO/3uVKcrxfLpKaLp3exkBJOGOAThEQK+0N+ztXtkOURlMqiSVkNmux93IdgW4RD9d
 0DuT2Jg6Pg3IOrWw2zJydu8hYMftQwUu9YDTxo76p8hSFJnRCUk9oSu9evCfYSV5fJHPe7Kim
 D9sBb3xoTyROPHU4LqT0CYcC2h7qHtVjEfkKtc8OwiklXJNHGKID243cFDkeuCZf4NlEouyOG
 V9JvBFEPzJ+osXTcy13w4Z0tnaaJdAzScspoCR2HnuUFr2WJ2JOjjrsjJaM2KBDj8QRSPwagS
 EyvkIajr49y7I8Ktr2lTWTK/RJhtbe33D49nmIWwjA15pjjhXe4vpTeF49CdqD++3pl56FwA2
 S7YSxxheM7sfs0qugLXG7fNNLmPMv3iGM/BpL9N/HIWtQq1QzPLwvJOtUcbIQMGgY8FvW/HJr
 MlzPcu5Je6KZxtAm7ZAGhGFoyOxtZxpJ4TugFycWkE1JI=
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/6a2f3906-3d7a-6924-7403-8f77d57a18e4%40gmx.net>
Precedence: bulk

On Fri, 10 May 2024, Tom Lane wrote:

> Dimitrios Apostolou <jimis@gmx.net> writes:
>> Further digging into this simple query, if I force the non-parallel pla=
n
>> by setting max_parallel_workers_per_gather TO 0, I see that the query
>> planner comes up with a cost much higher:
>
>>   Limit  (cost=3D363.84..1134528847.47 rows=3D10 width=3D4)
>>     ->  Unique  (cost=3D363.84..22690570036.41 rows=3D200 width=3D4)
>>           ->  Append  (cost=3D363.84..22527480551.58 rows=3D65235793929=
 width=3D4)
>> ...
>
>> The total cost on the 1st line (cost=3D363.84..1134528847.47) has a muc=
h
>> higher upper limit than the total cost when
>> max_parallel_workers_per_gather is 4 (cost=3D853891608.79..853891608.99=
).
>> This explains the planner's choice. But I wonder why the cost estimatio=
n
>> is so far away from reality.
>
> I'd say the blame lies with that (probably-default) estimate of
> just 200 distinct rows.  That means the planner expects to have
> to read about 5% (10/200) of the tables to get the result, and
> that's making fast-start plans look bad.

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 the
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.

In any case, even after the planner decides to execute the terrible plan
with the parallel seqscans, why doesn't it finish right when it finds 10
distinct values?

>
> Possibly an explicit ANALYZE on the partitioned table would help.

Thanks, I'll save the ANALYZE as the last step; I feel it's a good
opportunity to figure out more details about how postgres works. Plus I
expect ANALYZE to last a couple of days, so I should first find quiet time
for that. :-)

Dimitris