Loading...

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: