Re: [BUGS] Query planner skipping index depending on DISTINCTparameter 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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs