On Mon, 15 Jul 2019 at 12:59, Thomas Munro <thomas.mu...@gmail.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
