#30913: Add support for adding non-key columns to indexes
-------------------------------------+-------------------------------------
Reporter: Hannes Ljungberg | Owner: nobody
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: db-indexes | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Hannes Ljungberg:
Old description:
> Postgres got support for the `INCLUDE` clause in `CREATE INDEX` in
> version 11. 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 col1 = '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.
New description:
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 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#comment:5>
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/067.03a99cac0a70d0eb11442793b35fe634%40djangoproject.com.
