Opened 3 years ago

Last modified 3 years ago

#33138 closed New feature

Tuple comparison for efficient lexicographic ordering on multiple columns — at Initial Version

Reported by: Michal Charemza Owned by: nobody
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords: QuerySet.extra
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

The below doesn't seem possible without resorting to extra

WHERE (col_a, col_b) > ('value_a', 'value_b')
ORDER BY (col_a, col_b)

While this sort of this is semantically the same as

WHERE col_a > 'value_a' OR (col_a = 'value_a' AND col_b > 'value_b')
ORDER BY (col_a, col_b)

which can be expressed using the Django ORM, PostgreSQL at least treats these differently in terms of applying indexes. Essentially, the tuple version (from my brief testing) is better in the presence of a multi-column index on col_a, col_b: it seems to avoid quite a lot of scanning.

My ultimate use case for this is cursor-based pagination, where the cursor is a tuple of 2 columns: an "almost" unique datetime, and a fully unique ID for tie-breakers.

Change History (0)

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