INCLUDE clause on indexes

[awinograd]

Problem

Postgres has the ability to INCLUDE non-key columns in an index which allows the columns to be returned by the index scan without having to lookup on the table.

https://www.postgresql.org/docs/11/sql-createindex.html
https://www.postgresql.org/docs/11/sql-createindex.html#:~:text=EXISTS%20is%20specified.-,INCLUDE,-The%20optional%20INCLUDE

INCLUDE
The optional INCLUDE clause specifies a list of columns which will be included in the index as non-key columns. A non-key column cannot be used in an index scan search qualification, and it is disregarded for purposes of any uniqueness or exclusion constraint enforced by the index. However, an index-only scan can return the contents of non-key columns without having to visit the index's table, since they are available directly from the index entry. Thus, addition of non-key columns allows index-only scans to be used for queries that otherwise could not use them.

It's wise to be conservative about adding non-key columns to an index, especially wide columns. If an index tuple exceeds the maximum size allowed for the index type, data insertion will fail. In any case, non-key columns duplicate data from the index's table and bloat the size of the index, thus potentially slowing searches.

Columns listed in the INCLUDE clause don't need appropriate operator classes; the clause can include columns whose data types don't have operator classes defined for a given access method.

Expressions are not supported as included columns since they cannot be used in index-only scans.

Currently, only the B-tree index access method supports this feature. In B-tree indexes, the values of columns listed in the INCLUDE clause are included in leaf tuples which correspond to heap tuples, but are not included in upper-level index entries used for tree navigation.

[...]

To create a unique B-tree index on the column title with included columns director and rating in the table films:

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

Currently, prisma migrate dev (or introspection) thinks the non-key columns in the INCLUDE clause are key columns that are part of the index. Instead it should exclude non-key columns when checking for schema drift

Suggested solution

Adding support for INCLUDE clauses in indexes would allow use of this postgres feature. Potential syntax:

@@index([email], include: [id])

Alternatives

Alternative is to omit INCLUDE clauses from the database indexes. Since it affects only db performance, it's not critical for most applications.

Additional context

Original reported as a bug in #8547 which has a minimal repro to test this functionality https://github.com/awinograd/prisma-include-clause-bug

[tomhoule]

Merging this issue with #14699 which is the same, but on MS SQL Server.

INCLUDE (column [ ,... n ] )

Specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique.

Column names can't be repeated in the INCLUDE list and can't be used simultaneously as both key and non-key columns. Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table. For more information, see Create Indexes with Included Columns.

All data types are allowed except text, ntext, and image. Starting with SQL Server 2012 (11.x) and Azure SQL Database, if any one of the specified non-key columns are varchar(max), nvarchar(max), or varbinary(max) data types, the index can be built or rebuilt using the ONLINE option.

Computed columns that are deterministic and either precise or imprecise can be included columns. Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be included in non-key columns as long as the computed column data types is allowable as an included column. For more information, see Indexes on Computed Columns.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver16#include-column---n--

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver16#i-create-an-index-with-included-non-key-columns

[janpio]

CockroachDB has its own variant with COVERING and STORING: https://www.cockroachlabs.com/docs/stable/indexes.html#storing-columns (Per https://www.cockroachlabs.com/docs/v22.1/create-index INCLUDE works as well though)

[toddtarsi]

Hello, this is a very unfortunate issue. We have to write a custom index for performance and now it tries to drop it every migration. Can anyone help us understand what to do next here? We don't really know what else to do here other than like delete that line out of every migration? Does anyone have a hack or workaround for this?

[extremegf]

A hint: If you add 'WHERE something IS NOT NULL' to the index, prisma leaves it alone during migration diffing.