Opened 5 years ago

Closed 4 years ago

Last modified 9 months ago

#30913 closed New feature (fixed)

Add support for adding non-key columns to indexes

Reported by: Hannes Ljungberg Owned by: Hannes Ljungberg
Component: Migrations Version: dev
Severity: Normal Keywords: postgresql covering index include
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Hannes Ljungberg)

Postgres got support for the INCLUDE clause in CREATE INDEX. This can be used to add non-key columns to the index.

CREATE INDEX idx
    ON t1 ( col1 )
    INCLUDE ( col2 );

This allows for Index Only Scans on queries like:

SELECT col1, col2 FROM t1 WHERE t1 = 'foo';

More info:
https://www.postgresql.org/docs/current/sql-createindex.html
https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes
https://www.postgresql.org/docs/current/indexes-index-only-scans.html

The idea is to add an additional kwarg to Index to support this:

Index(
    name='some-idx',
    fields=['headline'],
    include=['pub_date']
)

One of the biggest possibilities of this feature is to add included columns to unique indexes and use them to perform Index Only Scans. This would require adding the same kwarg to UniqueConstraint. The implementation would be a lot like the condition kwargs to both Index and UniqueConstraint.

At the moment the only Django-supported database that can use this feature is Postgres but it's also supported by Microsoft SQL Server and IBM Db2 with the same syntax. Because of this I think it shouldn't be implemented as a postgres only feature but exposed on BaseDatabaseSchemaEditor to ease the adding of support when/if sqlite or mariadb/mysql get it.

Change History (18)

comment:1 by Hannes Ljungberg, 5 years ago

Description: modified (diff)

comment:2 by Hannes Ljungberg, 5 years ago

Description: modified (diff)

comment:3 by Hannes Ljungberg, 5 years ago

Description: modified (diff)

comment:4 by Hannes Ljungberg, 5 years ago

Description: modified (diff)

comment:5 by Hannes Ljungberg, 5 years ago

Description: modified (diff)

comment:6 by Hannes Ljungberg, 5 years ago

Description: modified (diff)

comment:7 by Simon Charette, 5 years ago

Component: Database layer (models, ORM)Migrations
Keywords: postgresql covering index include added; db-indexes removed
Triage Stage: UnreviewedAccepted

comment:8 by Hannes Ljungberg, 5 years ago

Owner: changed from nobody to Hannes Ljungberg

comment:9 by Hannes Ljungberg, 5 years ago

Description: modified (diff)
Has patch: set

comment:10 by Mariusz Felisiak, 5 years ago

Patch needs improvement: set

See comment.

comment:11 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

In 10449331:

Refs #30913 -- Added BaseDatabaseSchemaEditor._index_condition_sql().

comment:12 by Hannes Ljungberg, 5 years ago

Patch needs improvement: unset

comment:13 by Mariusz Felisiak, 5 years ago

Patch needs improvement: set

comment:14 by Hannes Ljungberg, 5 years ago

Patch needs improvement: unset

comment:15 by Mariusz Felisiak, 5 years ago

Triage Stage: AcceptedReady for checkin

comment:16 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: fixed
Status: assignedclosed

In 8c7992f:

Fixed #30913 -- Added support for covering indexes on PostgreSQL 11+.

comment:17 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In f83b4407:

Refs #30913 -- Added system checks for covering indexes and unique constraints support.

comment:18 by Mariusz Felisiak <felisiak.mariusz@…>, 9 months ago

In abe6c1f9:

Refs #30913 -- Corrected IndexesTests.test_index_include_pointing_to_fk().

This adjusts a test assigning an Index to Meta.constraints.

Note: See TracTickets for help on using tickets.
Back to Top