]> git.ipfire.org Git - thirdparty/sqlalchemy/sqlalchemy.git/commit
Remove ORDER BY pk from subqueryload, selectinload
authorMike Bayer <mike_mp@zzzcomputing.com>
Mon, 30 Mar 2020 15:04:24 +0000 (11:04 -0400)
committerMike Bayer <mike_mp@zzzcomputing.com>
Mon, 30 Mar 2020 15:44:07 +0000 (11:44 -0400)
commit3e22b7655a3aca24c993fc70e0caa1cb11205704
tree08c1cdcda940eaf5de18d1e12f8e88eab2ee66fb
parentfea89ef47080a581d79aafc1dc649bf21e165793
Remove ORDER BY pk from subqueryload, selectinload

Modified the queries used by subqueryload and selectinload to no longer
ORDER BY the primary key of the parent entity;  this ordering was there to
allow the rows as they come in to be copied into lists directly with a
minimal level of Python-side collation.   However, these ORDER BY clauses
can negatively impact the performance of the query as in many scenarios
these columns are derived from a subquery or are otherwise not actual
primary key columns such that SQL planners cannot make use of indexes. The
Python-side collation uses the native itertools.group_by() to collate the
incoming rows, and has been modified to allow multiple
row-groups-per-parent to be assembled together using list.extend(), which
should still allow for relatively fast Python-side performance.  There will
still be an ORDER BY present for a relationship that includes an explicit
order_by parameter, however this is the only ORDER BY that will be added to
the query for both kinds of loading.

Fixes: #5162
Change-Id: I8befd1303c1af7cc24cbf005f39bc01c8b2745f3
(cherry picked from commit f86ee556add28afd4de31c10fce56b00a0014a4e)
12 files changed:
doc/build/changelog/unreleased_13/5162.rst [new file with mode: 0644]
doc/build/orm/tutorial.rst
lib/sqlalchemy/orm/strategies.py
test/orm/inheritance/test_poly_loading.py
test/orm/inheritance/test_polymorphic_rel.py
test/orm/inheritance/test_single.py
test/orm/test_ac_relationships.py
test/orm/test_deferred.py
test/orm/test_of_type.py
test/orm/test_relationships.py
test/orm/test_selectin_relations.py
test/orm/test_subquery_relations.py