Opened 3 years ago
Last modified 3 years ago
#33138 closed New feature
Tuple comparison for efficient lexicographic ordering on multiple columns — at Version 1
| 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 (last modified by )
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 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.
Note:
See TracTickets
for help on using tickets.