#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 )
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 , 5 years ago
Description: | modified (diff) |
---|
comment:2 by , 5 years ago
Description: | modified (diff) |
---|
comment:3 by , 5 years ago
Description: | modified (diff) |
---|
comment:4 by , 5 years ago
Description: | modified (diff) |
---|
comment:5 by , 5 years ago
Description: | modified (diff) |
---|
comment:6 by , 5 years ago
Description: | modified (diff) |
---|
comment:7 by , 5 years ago
Component: | Database layer (models, ORM) → Migrations |
---|---|
Keywords: | postgresql covering index include added; db-indexes removed |
Triage Stage: | Unreviewed → Accepted |
comment:8 by , 5 years ago
Owner: | changed from | to
---|
comment:9 by , 5 years ago
Description: | modified (diff) |
---|---|
Has patch: | set |
comment:12 by , 4 years ago
Patch needs improvement: | unset |
---|
comment:13 by , 4 years ago
Patch needs improvement: | set |
---|
comment:14 by , 4 years ago
Patch needs improvement: | unset |
---|
comment:15 by , 4 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
PR: https://github.com/django/django/pull/11991