Re: Covering Indexes

On Thu, Jun 28, 2012 at 7:02 AM, Rob Wultsch <wultsch(at)gmail(dot)com> wrote:
> On Thu, Jun 28, 2012 at 8:16 AM, David E. Wheeler <david(at)justatheory(dot)com> wrote:
>> Hackers,
>>
>> Very interesting design document for SQLite 4:
>>
>>  http://www.sqlite.org/src4/doc/trunk/www/design.wiki
>>
>> I'm particularly intrigued by "covering indexes". For example:
>>
>>    CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);
>>
>> This allows the following query to do an index-only scan:
>>
>>    SELECT c, d FROM table1 WHERE a=? AND b=?;
>>
>> Now that we have index-only scans in 9.2, I'm wondering if it would make sense to add covering index support, too, where additional, unindexed columns are stored alongside indexed columns.
>>
>> And I wonder if it would work well with expressions, too?
>>
>> David
>
> IRC MS SQL also allow unindexed columns in the index.
>
> --
> Rob Wultsch
> wultsch(at)gmail(dot)com

MS SQL Server does support including non-key columns in indexes,
allowing index only scans for queries returning these columns. Their
syntax is different from that proposed in the linked SQLite document.
To the best of my experience, the advantages in SQL Server to such an
index (as opposed to just adding the columns to the index normally)
are as follows:

1- You can include extra columns in a unique index which do not
participate in the uniqueness determination.
2- The non-key columns can be of types which normally cannot be
included in a b-tree index due to lack of proper sorting operators.
3- The resulting index is smaller, because the non-key columns are
only contained in leaf nodes, not in internal nodes.
4- The insert/update overhead is lower.

Of course, an implementation in a different database system, such as
Postgres, may or may not have the same set of benefits. Number 4 in
particular seems to be dependent on the details of the b-tree
implementation. In my mind numbers 1 and 2 are the most compelling
arguments in favor of this feature. Whether the it's worth the effort
of coding the feature would depend on how well the above benefits (or
any benefits I missed) hold true, and how useful such an index
actually proved for index only scans in Postgres.