Details
-
Improvement
-
Status: Open
-
Minor
-
Resolution: Unresolved
-
3.9.7
-
None
-
MOODLE_39_STABLE
Description
Thanks brendanheywood for implementing MDL-67635. However, it seems like the task is still very slow at quering data from DB.
It takes about 30 minutes to complete the task and as I can see the following SQL is the cause:
moodle_39_uat=> \timing on
|
Timing is on.
|
moodle_39_uat=>
|
moodle_39_uat=>
|
moodle_39_uat=> SELECT p.*
|
moodle_39_uat-> FROM mdl_files p
|
moodle_39_uat-> LEFT JOIN mdl_files o ON (p.filename = o.contenthash)
|
moodle_39_uat-> WHERE p.contextid = 1 |
moodle_39_uat-> AND p.component = 'core' |
moodle_39_uat-> AND (p.filearea = 'preview' OR p.filearea = 'documentconversion') |
moodle_39_uat-> AND p.itemid = 0 |
moodle_39_uat-> AND o.id IS NULL;
|
Time: 1126423.504 ms (18:46.424) |
Only 7 rows returned.
Query explain:
moodle_39_uat=> EXPLAIN SELECT p.*
|
moodle_39_uat-> FROM mdl_files p
|
moodle_39_uat-> LEFT JOIN mdl_files o ON (p.filename = o.contenthash)
|
moodle_39_uat-> WHERE p.contextid = 1 |
moodle_39_uat-> AND p.component = 'core' |
moodle_39_uat-> AND (p.filearea = 'preview' OR p.filearea = 'documentconversion') |
moodle_39_uat-> AND p.itemid = 0 |
moodle_39_uat-> AND o.id IS NULL;
|
QUERY PLAN
|
-----------------------------------------------------------------------------------------------------------------------
|
Gather (cost=1129.53..143047.51 rows=1 width=294) |
Workers Planned: 1 |
-> Nested Loop Left Join (cost=129.53..142047.41 rows=1 width=294) |
Filter: (o.id IS NULL)
|
-> Parallel Index Scan using mdl_file_comfilconite_ix on mdl_files p (cost=0.69..55609.78 rows=7 width=294) |
Index Cond: (((component)::text = 'core'::text) AND (contextid = 1) AND (itemid = 0)) |
Filter: (((filearea)::text = 'preview'::text) OR ((filearea)::text = 'documentconversion'::text)) |
-> Bitmap Heap Scan on mdl_files o (cost=128.84..12317.07 rows=3116 width=49) |
Recheck Cond: ((p.filename)::text = (contenthash)::text)
|
-> Bitmap Index Scan on mdl_file_con_ix (cost=0.00..128.06 rows=3116 width=0) |
Index Cond: ((contenthash)::text = (p.filename)::text)
|
(11 rows) |
The mdl_files table has an extra index, but nothing is missing:
root@629cf2f9b27e:/site# php admin/cli/check_database_schema.php |
-------------------------------------------------------------------------------
|
files
|
* Unexpected index 'mdl_file_time_ix'. |
-------------------------------------------------------------------------------
|
|
|
moodle_39_uat=> \d mdl_files
|
Table "public.mdl_files" |
Column | Type | Collation | Nullable | Default
|
-----------------+------------------------+-----------+----------+---------------------------------------
|
id | bigint | | not null | nextval('mdl_files_id_seq'::regclass) |
contenthash | character varying(40) | | not null | ''::character varying |
pathnamehash | character varying(40) | | not null | ''::character varying |
contextid | bigint | | not null | |
component | character varying(100) | | not null | ''::character varying |
filearea | character varying(50) | | not null | ''::character varying |
itemid | bigint | | not null | |
filepath | character varying(255) | | not null | ''::character varying |
filename | character varying(255) | | not null | ''::character varying |
userid | bigint | | |
|
filesize | bigint | | not null | |
mimetype | character varying(100) | | | |
status | bigint | | not null | 0 |
source | text | | |
|
author | character varying(255) | | | |
license | character varying(255) | | | |
timecreated | bigint | | not null | |
timemodified | bigint | | not null | |
sortorder | bigint | | not null | 0 |
referencefileid | bigint | | |
|
Indexes:
|
"mdl_file_id_pk" PRIMARY KEY, btree (id) |
"mdl_file_pat_uix" UNIQUE, btree (pathnamehash) |
"mdl_file_comfilconite_ix" btree (component, filearea, contextid, itemid) |
"mdl_file_con2_ix" btree (contextid) |
"mdl_file_con_ix" btree (contenthash) |
"mdl_file_lic_ix" btree (license) |
"mdl_file_ref_ix" btree (referencefileid) |
"mdl_file_time_ix" btree (contenthash, filesize, timecreated, id) WHERE filesize > 0 |
"mdl_file_use_ix" btree (userid) |
Task profiling:
