Opened 3 years ago

Closed 7 weeks ago

#20888 closed New feature (fixed)

Allow defining order of column for indexes

Reported by: anonymous Owned by: Akshesh Doshi
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: index, order, db-indexes 1.11
Cc: aksheshdoshi@… 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 Tim Graham)

Hi,

Currently the index_together tuples do not support index ordering using + / -.

If I have the following table:

class Messages:
   user_id,
   rating,
   last_update,

   index_together=[
     ['user_id', 'rating',  'last_update',] # sorted ASC by default
  ]

So for MySQL, the following query cannot make use of the index:

Messages.objects.filter(user_id=xxx).order_by('+rating','-last_update')

Pardon me if it actually can make use of the index.

I know there are workarounds, such as negating values to make a DESC ordering become ASC ordering.
But it is just nice to have such a feature.
Btw, what is the reason for not having this feature?

Attachments (1)

20888-draft.diff (1.7 KB) - added by Tim Graham 2 months ago.

Download all attachments as: .zip

Change History (22)

comment:1 Changed 3 years ago by wim@…

Hi anonymous, thanks for taking the time to fill in a ticket. Just to answer your question: most features are not implemented because people did not have time to write the code for it. :) So if you are willing to write a pull request and contribute such a feature could land in Django.

Unfortunately I do not understand MySQl enough to mark this ticket as accepted.

comment:2 Changed 3 years ago by Anssi Kääriäinen

Triage Stage: UnreviewedAccepted

To me this seems acceptable. Of course, supporting this only on MySQL isn't good, I believe most backends support index ordering.

comment:3 Changed 3 years ago by Daniel Boeve

Owner: changed from nobody to Daniel Boeve
Status: newassigned

comment:4 Changed 3 years ago by Tim Graham

Easy pickings: unset

comment:5 Changed 3 years ago by anonymous

Easy pickings: set
Owner: Daniel Boeve deleted
Status: assignednew

comment:6 Changed 3 years ago by Tim Graham

Easy pickings: unset

comment:7 Changed 9 months ago by Akshesh Doshi

Cc: aksheshdoshi@… added

comment:8 Changed 9 months ago by Asif Saifuddin Auvi

Version: 1.5master

comment:9 Changed 7 months ago by Tim Graham

Description: modified (diff)
Keywords: db-indexes added

comment:10 Changed 4 months ago by Akshesh Doshi

Owner: set to Akshesh Doshi
Status: newassigned

comment:11 Changed 4 months ago by Akshesh Doshi

Has patch: set

PR: https://github.com/django/django/pull/6982

Although requested for alter_index_together, I have implemented the feature with the new class based indexes which would allow users to do the same thing.

The patch contains implementation for all databases except MySQL which doesn't support the feature itself according to their official docs. I felt this to be a bit weird as originally the feature had been requested specifically for MySQL. Please let me know if I missed something in this context.

The patch doesn't add the feature for unique_together as I couldn't understand/don't know what that means. I can try to add it for unique_together as well if somebody could explain that.

Last edited 4 months ago by Akshesh Doshi (previous) (diff)

comment:12 Changed 4 months ago by Tim Graham

Summary: Index ordering in index_together and unique_togetherIndex ordering in index_together

I'll retitle the ticket and we can open a new one if there's more to be done with respect to unique_together.

comment:13 Changed 4 months ago by Tim Graham <timograham@…>

In f842d10:

Refs #20888 -- Added index order introspection.

comment:14 Changed 4 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 311a8e8:

Fixed #20888 -- Added support for column order in class-based indexes.

comment:15 Changed 4 months ago by Akshesh Doshi

Summary: Index ordering in index_togetherAllow defining order of column for indexes

Since technically we haven't fixed the ticket described by the earlier summary, but in the end it's the feature that matters so won't reopen this and renaming it instead.

comment:16 Changed 2 months ago by Tim Graham

Has patch: unset
Keywords: 1.11 added
Resolution: fixed
Status: closednew

We need to revisit this as on PostgreSQL 9.6, "column am.amcanorder does not exist".

According to a post on the PostgreSQL mailing list: "Using pg_get_indexdef() would leave you much less vulnerable to cross- version differences in the system catalogs.

comment:17 Changed 2 months ago by Tim Graham

I drafted a patch based on a commit for pgjdbc but it's not entirely working yet and has issues on some older versions of PostgreSQL. I didn't spend too much time debugging.

Changed 2 months ago by Tim Graham

Attachment: 20888-draft.diff added

comment:18 Changed 8 weeks ago by François Freitag

Has patch: set

PR

PostgreSQL 9.6 gets rid of most of pg_am columns[0]. In particular, amcanorder disappears and should be replaced with calls to pg_index_column_has_property(). PostgreSQL versions < 9.6 do not provide this method, hence we are forced to rely on a brittle query.

Since only B-tree indexes support ordering[1], this is the only type of index to consider. This solution relies on the access method implementation[2] to determine whether ASC or DESC ordering is in use.

Let's hope that a new view is added to information_schema[3] in the future.

[0] https://www.postgresql.org/docs/9.6/static/release-9-6.html
[1] https://www.postgresql.org/docs/current/static/indexes-ordering.html
[2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/pg_index.h;h=ee97c5dec836ad82c5b75440fcba51d4c106fc33;hb=HEAD#l99
[3] http://stackoverflow.com/questions/18121103/how-to-get-the-index-column-orderasc-desc-nulls-first-from-postgresql#comment64529591_18128104

Last edited 8 weeks ago by Tim Graham (previous) (diff)

comment:19 Changed 8 weeks ago by Tim Graham

Triage Stage: AcceptedReady for checkin

comment:20 Changed 7 weeks ago by Tim Graham <timograham@…>

In 86bb9a05:

Refs #20888 -- Fixed index ordering introspection on PostgreSQL 9.6.

comment:21 Changed 7 weeks ago by Tim Graham

Resolution: fixed
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top