Re: [BUGS] Query planner skipping index depending on DISTINCTparameter order (2)

Hello Dilyan,

You're right - we're currently not able to use the index if it's not
consistent with the DISTINCT ordering. That is, if you have index on
(a,b) and DISTINCT ON (b,a) we fail to leverage the index.

The reason for this simple - if you look at create_distinct_paths [1],
which is where the Unique path comes from, you'll see it iterates over
all paths and compares the ordering using pathkeys_is_contained [2].

That however only ensures the path matches the expected Unique ordering
(determined by the column list in DISTINCT ON clause), we don't try to
re-shuffle the columns in any way at this point.

So this is more a missing optimization than a bug, I'd guess. But it
seems worthwhile and possibly not extremely difficult to implement, so I
may look into it - but that's PG11 at the earliest.

But, looking at the code in create_distinct_paths, ISTM you can easily
convince the planner to use the index by simply adding a matching ORDER
BY clause. That is
   SELECT DISTINCT ON(token, id) token  FROM bayes_token   ORDER BY id, token;

should be able to use the index on (id,token).


[1]
https://github.com/postgres/postgres/blob/master/src/backend/optimizer/plan/planner.c#L4725

[2]
https://github.com/postgres/postgres/blob/master/src/backend/optimizer/plan/planner.c#L4811


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