]> 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:43:00 +0000 (11:43 -0400)
commitd3cc4cdcf10db1f50fb2806358532821bc6ed796
treee0b47b4c1df0c4e1907f0c9a02a0aaf4ebb8a7e1
parentffcc16b114f14459f1b90b10e126fc31afef682a
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
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