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

On 09/17/2017 02:30 AM, Dilyan Palauzov wrote:
> Hello Tomas,
>
> thanks for your answer.
>
> While adding an ORDER BY uses the index, it does not help, as the
> user has to take care of the order of provided columns. Whether the
> care is is taken in DISTINCT ON or in ORDER BY is secondary.
>
> 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.

> My proposal to reflect this:
>
> diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
> --- a/doc/src/sgml/ref/select.sgml
> +++ b/doc/src/sgml/ref/select.sgml
> @@ -1069,7 +1069,10 @@ SELECT DISTINCT ON (location) location, time, report
>     <para>
>      Currently, <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>,
>      <literal>FOR SHARE</> and <literal>FOR KEY SHARE</> cannot be
> -    specified with <literal>DISTINCT</literal>.
> +    specified with <literal>DISTINCT</literal>.  Contrary to
> +    <literal>SELECT</>, which reorders its parameters to find a best,
> +    matching index, DISTINCT ON constructs an expression, e.g. from the
> +    provided rows, and checks then if an index can serve the expression.
>     </para>
>    </refsect2>
>
I don't think we want to change the docs like this. Notice that the
SELECT documentation does not mention indexes at all, and I'm pretty
sure we don't want to start doing that. The docs are user-level, deal
only explaining properties of the output relation, and not with
implementation-level details like index optimizations.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services