Reports use unoptimizable queries
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
python-oops-tools |
Confirmed
|
Undecided
|
Unassigned |
Bug Description
The report indexes produce SQL which looks like: (individual columns excluded for brevity):
oops_tools=# EXPLAIN SELECT "oops_oops"."id", "oops_infestati
-------
Limit (cost=0.
-> Nested Loop (cost=0.
-> Index Scan Backward using oops_oops_date on oops_oops (cost=0.
-> Index Scan using oops_infestatio
It appears postgres does not handle this well (http://
For ORDER BY + LIMIT situations, it would be better to do the limit within the app:
oops_tools=# EXPLAIN ANALYZE SELECT "oops_oops"."id", "oops_infestati
-------
Sort (cost=726543.
Sort Key: oops_oops.date
Sort Method: quicksort Memory: 948kB
-> Nested Loop (cost=1334.
-> Bitmap Heap Scan on oops_oops (cost=1334.
-> Bitmap Index Scan on oops_oops_prefix_id (cost=0.00..1320.24 rows=56453 width=0) (actual time=11.337..11.337 rows=12111 loops=1)
-> Index Scan using oops_infestatio
Total runtime: 1251.054 ms
Changed in python-oops-tools: | |
status: | New → Confirmed |
I don't think it's quite that easy. The latter plan is faster in this case because the most recent oops_oops rows for those prefixes are quite old, which isn't the normal situation. Better would be to convince postgres to use an oops_oops( prefix_ id, date) index, which would perform adequately in all cases.
Can we get an EXPLAIN ANALYZE from the first query? Or, better, a dump of the DB.