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 07:15 PM, Tom Lane wrote:
>> 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.

> Tied in what sense? In the docs we explicitly say this:

> https://www.postgresql.org/docs/10/static/sql-select.html#sql-distinct

> SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of
> each set of rows where the given expressions evaluate to equal. The
> DISTINCT ON expressions are interpreted using the same rules as for
> ORDER BY (see above). Note that the “first row” of each set is
> unpredictable unless ORDER BY is used to ensure that the desired row
> appears first.

Right, so the behavior is undefined unless you have an ORDER BY clause
that includes the DISTINCT ON columns plus some more columns. That's
pretty tightly tied in my book.

> So if an ORDER BY is not specified, why couldn't we pick an arbitrary
> ordering matching based on available indexes?

The case is not of any real-world use, and so I'm unwilling to expend
the large amount of coding effort that would be needed to make the
planner behave this way.

regards, tom lane