Date: Mon, 13 May 2024 15:52:42 +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: <CAApHDvo8yYvqa1+bkW_f5xHX-gmKGYfaGwH+Y_KP-=9TOuF+-g@mail.gmail.com>
Message-ID: <98c715d1-22f4-0fc1-1997-6236873c13de@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> <3efce60b-48c0-160d-0444-474b02f76739@gmx.net> <CAApHDvo8yYvqa1+bkW_f5xHX-gmKGYfaGwH+Y_KP-=9TOuF+-g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:XnasN7BEOYQ=;sLthGyqRDSZ64FGlvT2vX8lVAiV
 UW0aOubybLfNdUdzxxgIW+IAozw+0SSA+Q6txGioLZsh0R+38sfnhilnnVVT7qZjWX87H+ONQ
 2zsaBMH+Fify0Rgt4QyY8Rz2sKwjA0WjiAyBNMSRJYZD/f5antmXYr/08gucAJNOJDvBzpqtv
 8ksRLOWWGm8IADr6FAXWfeabzFWVB+GhNxqAhhGni1A5IEW5c2sOhf6Q5MyRv1GSBN4BX0e+I
 BJ1wlGy6VRW6quc47XYKsLAv059b5K442Fjb27xl33AJGg5kzZRiLr0lTfdWt2oOHu3yBXPvr
 I8iADRQMsnK83Uyose4wtSztLrvKId+7zMiOF32hOUILtl1H4gIsk2VWtLA7UKx3NYBX/LY4t
 k+3Y8AiYoFnGgiPL2gR7cPwVfU9D/SYr5uVwgXmhTRkRB8KXcIdYSo3qA7ZeKzNys85QPMi6Z
 PF8f0k6HwwFcQoRujzEReAUN+xrDO6TPUwIdoBhUsCraA0WsHj9QFBTbVm/QSrpx6u4cdNdDX
 mbepb+sMP8ydMXeRIfY9NmE72fKMfhqpVTCd2PYDbIPxjJJ5QVmmERJITZ/DO/0JF6VrjnFIX
 shjb34OKZkeV9pl/OZMm9ONSYybT0Mi4g/jX5eqgRc3Sb2VRggu0dklnfArJ/DMno4ab8dV3j
 h7h33bRE89o06neAwLwNMHE6xubOYF15hyZMS1EbI/8RBdit3yKXnfGYopCcZF4lFq8fB6F3D
 76oepIdkSPqXDjugJJHpCN1S8U2Ld/6aafCSttk6ngj9/co6OsmTP1h1K5cwE1FEnHriDiZiN
 ZqiTGYmCfhWpbTeJWdoJjwJHhp1Tv4BSn/veTuL3KA6mQ=
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/98c715d1-22f4-0fc1-1997-6236873c13de%40gmx.net>
Precedence: bulk

On Tue, 14 May 2024, David Rowley wrote:

> On Tue, 14 May 2024 at 00:41, Dimitrios Apostolou <jimis@gmx.net> wrote:
>>
>> On Sat, 11 May 2024, David Rowley wrote:
>>> It will. It's just that Sorting requires fetching everything from its =
subnode.
>>
>> Isn't it plain wrong to have a sort step in the plan than? The differen=
t
>> 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.
>
> The query does contain an ORDER BY, so if the index is not chosen to
> provide pre-sorted input, then something has to put the results in the
> correct order before the LIMIT is applied.

The last query I tried was:

SELECT DISTINCT workitem_n FROM test_runs_raw LIMIT 10;

See my message at

[1] https://www.postgresql.org/message-id/69077f15-4125-2d63-733f-21ce6eac=
4f01%40gmx.net

Will re-check things and report back with further debugging info you asked
for later today.


Dimitris