-
Improvement
-
Resolution: Fixed
-
Minor
-
2.2
-
MOODLE_22_STABLE
-
MOODLE_23_STABLE
-
w20_
MDL-32659_m23_navcourses -
See the discussion at http://moodle.org/mod/forum/discuss.php?d=201214#p878277 for more context. Here are some stats for the moodle database I'm working with:
mdl_course records: approx. 4000
mdl_context records: approx. 300,000
mdl_course_categories: approx. 200
All but a couple of queries on a basic page load (http://moodle_site/login/index.php, for example) are of the sub-millisecond variety. The following query, however, takes about 200ms every time (even worse when things aren't in the database and/or os cache):
moodle=# EXPLAIN ANALYZE SELECT c.id, c.sortorder, c.visible, c.fullname, c.shortname, c.category, cat.path AS categorypath , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
|
FROM mdl_course c
|
LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
|
LEFT JOIN mdl_course_categories cat ON cat.id=c.category
|
WHERE c.id <> 1
|
ORDER BY c.sortorder;
|
|
|
QUERY PLAN
|
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|
Sort (cost=2681.31..2691.70 rows=4157 width=210) (actual time=195.667..196.797 rows=4157 loops=1)
|
Sort Key: c.sortorder
|
Sort Method: quicksort Memory: 1657kB
|
-> Hash Left Join (cost=922.22..2431.45 rows=4157 width=210) (actual time=9.922..190.237 rows=4157 loops=1)
|
Hash Cond: (c.category = cat.id)
|
-> Merge Right Join (cost=902.84..2354.91 rows=4157 width=142) (actual time=9.605..187.454 rows=4157 loops=1)
|
Merge Cond: (ctx.instanceid = c.id)
|
-> Index Scan using mdl_cont_ins_ix on mdl_context ctx (cost=0.00..32361.58 rows=4074 width=47) (actual time=0.024..172.762 rows=4158 loops=1)
|
Filter: (contextlevel = 50)
|
-> Sort (cost=902.84..913.23 rows=4157 width=95) (actual time=9.565..10.645 rows=4157 loops=1)
|
Sort Key: c.id
|
Sort Method: quicksort Memory: 866kB
|
-> Seq Scan on mdl_course c (cost=0.00..652.98 rows=4157 width=95) (actual time=0.005..4.394 rows=4157 loops=1)
|
Filter: (id <> 1)
|
-> Hash (cost=16.39..16.39 rows=239 width=76) (actual time=0.302..0.302 rows=239 loops=1)
|
Buckets: 1024 Batches: 1 Memory Usage: 27kB
|
-> Seq Scan on mdl_course_categories cat (cost=0.00..16.39 rows=239 width=76) (actual time=0.003..0.164 rows=239 loops=1)
|
Total runtime: 197.911 ms
|
(18 rows)
|
Is there a way to make this query more efficient or to cache it so that it doesn't make quite as large a hit as it does now?
- is blocked by
-
-
- Closed
-