Re: Group by reordering optimization

On Tue, Sep 1, 2020 at 2:09 PM Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> >* Instead of changing the order directly, now patch creates another patch with
> > modifier order of clauses. It does so for the normal sort as well as for
> > incremental sort. The whole thing is done in two steps: first it finds a
> > potentially better ordering taking into account number of groups, widths and
> > comparison costs; afterwards this information is used to produce a cost
> > estimation. This is implemented via a separate create_reordered_sort_path to
> > not introduce too many changes, I couldn't find any better place.
> >
>
> I haven't tested the patch with any queries, but I agree this seems like
> the right approach in general.

If we're creating a new sort path anyway, then perhaps we can also
change the collation -- it might be possible to "reduce" it to the "C"
collation without breaking queries.

This is admittedly pretty hard to do well. It could definitely work
out when we have to do a sort anyway -- a sort with high cardinality
abbreviated keys will be very fast (though we can't use abbreviated
keys with libc collations right now). OTOH, it would be quite
counterproductive if we were hoping to get an incremental sort that
used some available index that happens to use the default collation
(which is not the C collation in cases where this optimization is
expected to help).

--
Peter Geoghegan