Opened 3 years ago
Closed 3 years ago
#33138 closed New feature (duplicate)
Tuple comparison for efficient lexicographic ordering on multiple columns
| 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.
Change History (2)
comment:1 by , 3 years ago
| Description: | modified (diff) |
|---|
comment:2 by , 3 years ago
| Component: | Uncategorized → Database layer (models, ORM) |
|---|---|
| Resolution: | → duplicate |
| Status: | new → closed |
| Type: | Uncategorized → New feature |
Note:
See TracTickets
for help on using tickets.
Duplicate of #29527. Please feel-free to continue the discussion in #29527. We can reopen the ticket if you/someone will provide PoC.