Opened 2 years ago

Closed 17 months ago

Last modified 17 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 (17)

comment:1 Changed 2 years ago by Hannes Ljungberg

Description: modified (diff)

comment:2 Changed 2 years ago by Hannes Ljungberg

Description: modified (diff)

comment:3 Changed 2 years ago by Hannes Ljungberg

Description: modified (diff)

comment:4 Changed 2 years ago by Hannes Ljungberg

Description: modified (diff)

comment:5 Changed 2 years ago by Hannes Ljungberg

Description: modified (diff)

comment:6 Changed 2 years ago by Hannes Ljungberg

Description: modified (diff)

comment:7 Changed 2 years ago by Simon Charette

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

comment:8 Changed 2 years ago by Hannes Ljungberg

Owner: changed from nobody to Hannes Ljungberg

comment:9 Changed 2 years ago by Hannes Ljungberg

Description: modified (diff)
Has patch: set

comment:10 Changed 18 months ago by Mariusz Felisiak

Patch needs improvement: set

See comment.

comment:11 Changed 18 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In 10449331:

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

comment:12 Changed 17 months ago by Hannes Ljungberg

Patch needs improvement: unset

comment:13 Changed 17 months ago by Mariusz Felisiak

Patch needs improvement: set

comment:14 Changed 17 months ago by Hannes Ljungberg

Patch needs improvement: unset

comment:15 Changed 17 months ago by Mariusz Felisiak

Triage Stage: AcceptedReady for checkin

comment:16 Changed 17 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In 8c7992f:

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

comment:17 Changed 17 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In f83b4407:

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

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