Queries on large primary tables with limit/offset clauses are slow
|Reported by:||henrybaxter||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||master|
|Severity:||Normal||Keywords:||database, admin, slow, query|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
When django's db layer needs to select_related() on a large initial table with a number of joins and it has a limit or offset clause, it generates slow queries.
SELECT * FROM big_table
INNER JOIN t1 ON big_table.t1_id
INNER JOIN tn ON big_table.tn_id
This query will join the whole table (even if it has a 1 million records) and THEN do the limit. This is WAY faster:
SELECT * FROM (SELECT * FROM big_table LIMIT 10 OFFSET 20)
This type of query change does not work when there is a WHERE clause that references a product of the JOIN though. If the WHERE clause only references the primary table, then it still works great.
I have submitted a patch to help demonstrate the problem - it only works when there are no WHERE clauses at all (which is almost pointless), but it works for my particular purpose (pagination in the admin interface with no filters or searching)
I would love to hear from another developer how to integrate this functionality (or if it can actually be done without changes to the django codebase)