Re: Query planner skipping index depending on DISTINCT parameter order (2)

Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
> On 09/17/2017 02:30 AM, Dilyan Palauzov wrote:
>> The behaviour behind DISTINCT and indexes surprises me, as the
>> query planner does reorder the columns for SELECT to determine the
>> most suitable index.

> Well, I agree it's somewhat reasonable optimization. The thing is, the
> planner/optimizer does not start with all features on day 1, it gets
> improved over time. And no one implemented this bit yet.

For the DISTINCT ON case, the user-visible semantics are actually pretty
tightly tied to ORDER BY, so that it would not be very reasonable to
consider any other orderings than the given column order anyway.

For plain DISTINCT, yeah we could consider other orderings ... but
we're rather unlikely to find an index that matches all the output
columns, regardless of what order they're in. So it's just not that
exciting.

IOW, somebody might get around to this someday, but don't hold your
breath; there's lots of higher-value fruit to be reaching for.

regards, tom lane