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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Using unique btree indexes for pathkeys with one extra column
Date: 2019-07-15 23:52:19
Message-ID: 32730.1563234739@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Thomas Munro <thomas(dot)munro(at)gmail(dot)com> writes:
> 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).

Seems like you also have to insist that a is NOT NULL.

regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2019-07-15 23:56:37 Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)
Previous Message Jerry Sievers 2019-07-15 23:48:05 SegFault on 9.6.14