Re: Query planner skipping index depending on DISTINCT parameter 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