#30913: Add support for adding non-key columns to indexes
-------------------------------------+-------------------------------------
Reporter: Hannes | Owner: nobody
Ljungberg |
Type: New | Status: assigned
feature |
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords: db-indexes
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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/10/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 if 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 adds support.
I have a proof of concept I'm working on and will create a PR soon if
accepted.
--
Ticket URL: <https://code.djangoproject.com/ticket/30913>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.b1eb2d600a2c59708a9da610780fc2b2%40djangoproject.com.
