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

On Tue, 14 May 2024 at 00:28, Dimitrios Apostolou <jimis(at)gmx(dot)net> wrote:
>
> On Sat, 11 May 2024, David Rowley wrote:
>
> > On Sat, 11 May 2024 at 13:33, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> I do kind of wonder why it's producing both a hashagg and a Unique
> >> step --- seems like it should do one or the other.
> >
> > It still needs to make the duplicate groups from parallel workers unique.
>
> Range partitioning of the table guarantees that, since the ranges are not
> overlapping.

That assumes the Append won't ever use > 1 worker per subnode, but
that's not the case for your plan as the subnodes are "Parallel".
That means all the workers could be working on the same subnode which
could result in one group being split between 2 or more workers.

Parallel Append can also run in a way that the Append child nodes will
only get 1 worker each. However, even if that were the case for your
plan, we have no code that would skip the final aggregate phase when
the DISTINCT / GROUP contains all of the partition key columns.

David