Re: Using unique btree indexes for pathkeys with one extra column

On Mon, 15 Jul 2019 at 12:59, Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> In the category "doing more tricks with our existing btrees", which
> includes all that difficult stuff like skip scans and incremental
> sort, here's an easier planner-only one: if you have a unique index
> on (a) possibly "including" (b) and you have a pathkey (a, b), you can
> use an index [only] scan. That is, if the index is unique, and you
> want exactly one extra column in index order, then you don't need any
> extra sorting to get (a, b) in order. (If the index is not unique, or
> there is more than one extra trailing column in the pathkey, you need
> the incremental sort patch[1] to use this index). This was brought to
> my attention by a guru from a different RDBMS complaining about stupid
> stuff that PostgreSQL does and I was triggered to write this message
> as a kind of TODO note...
>
> [1] https://commitfest.postgresql.org/24/1124/

This is one of the problems I've wanted to solve in the various times
I've mentioned the word "UniqueKeys" on this mailing list.

Probably my most detailed explanation is in
https://www.postgresql.org/message-id/CAKJS1f86FgODuUnHiQ25RKeuES4qTqeNxm1QbqJWrBoZxVGLiQ%40mail.gmail.com

Without detecting the UniqueKeys through joins then the optimisation
you mention is limited to just single rel queries, since a join may
duplicate the "a" column and make it so the sort on "b" is no longer
redundant. In my view, limiting this to just single relation queries
is just too restrictive to bother writing any code for, so I think do
to as you mention we need the full-blown thing I mention in the link
above. i.e tagging a list of UniqueKeys onto RelOptInfo and checking
which ones are still applicable after joins and tagging those onto
join RelOptInfos too. PathKey redundancy could then take into account
that list of UniqueKeys the RelOptInfo level. At the top-level plan,
you can do smarts for ORDER BY / GROUP BY / DISTINCT.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services