Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

On Tue, 14 May 2024, David Rowley wrote:

> On Tue, 14 May 2024 at 00:41, Dimitrios Apostolou <jimis(at)gmx(dot)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 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.
>
> 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-21ce6eac4f01%40gmx.net

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

Dimitris