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

On Tue, 14 May 2024 at 01:52, Dimitrios Apostolou <jimis(at)gmx(dot)net> wrote:
>
> On Tue, 14 May 2024, David Rowley wrote:
> > 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;

I was looking at the original query. In that case, we have 2 ways to
remove duplicate rows with DISTINCT, "Hash Aggregate" and "Sort" ->
"Unique". Both of these will consume all of their input rows before
outputting any rows.

DISTINCT with LIMIT is a special case that we don't have a good
operator for. In theory, we could have some "Hash Distinct" node type
that was less eager to consume all of its input rows. When invoked
"Hash Distinct" could consume input rows until it found one that
didn't exist in the hash table. I've no idea how that would work when
we exceed work_mem. However, most queries with a LIMIT will have an
ORDER BY, so such a node likely wouldn't get much use.

David