#33138: Tuple comparison for efficient lexicographic ordering on multiple
columns
--------------------------------+--------------------------------------
Reporter: michalc | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.2
Severity: Normal | Resolution:
Keywords: QuerySet.extra | 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 michalc:
Old description:
> The below doesn't seem possible without resorting to `extra`
>
> {{{
> #!div style="font-size: 80%"
> {{{#!sql
> 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
>
> {{{
> #!div style="font-size: 80%"
> {{{#!sql
> 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.
New description:
The below doesn't seem possible without resorting to `extra`
{{{
#!div style="font-size: 80%"
{{{#!sql
WHERE (col_a, col_b) > ('value_a', 'value_b')
ORDER BY (col_a, col_b)
}}}
}}}
While this is semantically the same as
{{{
#!div style="font-size: 80%"
{{{#!sql
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33138#comment:1>
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.c76a793aa5f71d1b145028a637f59d0b%40djangoproject.com.
