Error when executing adhoc-task \tool_dataprivacy\task\process_data_request_task

[mputzi]

moodle 3.10.3+
moodleoverflow v3.10-r1 (2020111300)

Steps to reproduce:

1. User requested his data.
2. Admin accepted data request.

AdHoc-Task \tool_dataprivacy\task\process_data_request_task started (and replicated to number of max allowed concurrent AdHoc-Tasks).
Tasks fails at moodleoverflow-plugin
-> mysqld-process at 100% CPU (3 times) -> server is slowed down

There is no way of stopping or deleting this adhoc-Task from graphical moodle interface.

output when starting via cli:

sudo -u www-data php /var/www/moodle/admin/cli/adhoc_task.php --execute --force
Server Time: Tue, 06 Apr 2021 11:30:37 +0200

Execute adhoc task: tool_dataprivacy\task\process_data_request_task
... started 11:30:37. Current memory use 12.9MB.
Pre-processing request...
  Laden von 550 Komponenten (Dienstag, 6. April 2021, 11:30)
    antivirus_clamav wird verarbeitet (1/550) (Dienstag, 6. April 2021, 11:30)
    availability_completion wird verarbeitet (2/550) (Dienstag, 6. April 2021, 11:30)
    availability_date wird verarbeitet (3/550) (Dienstag, 6. April 2021, 11:30)
    availability_grade wird verarbeitet (4/550) (Dienstag, 6. April 2021, 11:30)
    availability_group wird verarbeitet (5/550) (Dienstag, 6. April 2021, 11:30)
    availability_grouping wird verarbeitet (6/550) (Dienstag, 6. April 2021, 11:30)
    availability_profile wird verarbeitet (7/550) (Dienstag, 6. April 2021, 11:30)
    availability_xp wird verarbeitet (8/550) (Dienstag, 6. April 2021, 11:30)
    qtype_algebra wird verarbeitet (9/550) (Dienstag, 6. April 2021, 11:30)
    qtype_calculated wird verarbeitet (10/550) (Dienstag, 6. April 2021, 11:30)
    qtype_calculatedmulti wird verarbeitet (11/550) (Dienstag, 6. April 2021, 11:30)
    qtype_calculatedsimple wird verarbeitet (12/550) (Dienstag, 6. April 2021, 11:30)
    qtype_ddimageortext wird verarbeitet (13/550) (Dienstag, 6. April 2021, 11:30)
    qtype_ddmarker wird verarbeitet (14/550) (Dienstag, 6. April 2021, 11:30)
    qtype_ddmatch wird verarbeitet (15/550) (Dienstag, 6. April 2021, 11:30)
    qtype_ddwtos wird verarbeitet (16/550) (Dienstag, 6. April 2021, 11:30)
    qtype_description wird verarbeitet (17/550) (Dienstag, 6. April 2021, 11:30)
    qtype_essay wird verarbeitet (18/550) (Dienstag, 6. April 2021, 11:30)
    qtype_formulas wird verarbeitet (19/550) (Dienstag, 6. April 2021, 11:30)
    qtype_gapfill wird verarbeitet (20/550) (Dienstag, 6. April 2021, 11:30)
    qtype_gapselect wird verarbeitet (21/550) (Dienstag, 6. April 2021, 11:30)
    qtype_geogebra wird verarbeitet (22/550) (Dienstag, 6. April 2021, 11:30)
    qtype_match wird verarbeitet (23/550) (Dienstag, 6. April 2021, 11:30)
    qtype_matrix wird verarbeitet (24/550) (Dienstag, 6. April 2021, 11:30)
    qtype_missingtype wird verarbeitet (25/550) (Dienstag, 6. April 2021, 11:30)
    qtype_multianswer wird verarbeitet (26/550) (Dienstag, 6. April 2021, 11:30)
    qtype_multichoice wird verarbeitet (27/550) (Dienstag, 6. April 2021, 11:30)
    qtype_numerical wird verarbeitet (28/550) (Dienstag, 6. April 2021, 11:30)
    qtype_random wird verarbeitet (29/550) (Dienstag, 6. April 2021, 11:30)
    qtype_randomsamatch wird verarbeitet (30/550) (Dienstag, 6. April 2021, 11:30)
    qtype_shortanswer wird verarbeitet (31/550) (Dienstag, 6. April 2021, 11:30)
    qtype_truefalse wird verarbeitet (32/550) (Dienstag, 6. April 2021, 11:30)
    qtype_wordselect wird verarbeitet (33/550) (Dienstag, 6. April 2021, 11:30)
    mod_assign wird verarbeitet (34/550) (Dienstag, 6. April 2021, 11:30)
    mod_assignment wird verarbeitet (35/550) (Dienstag, 6. April 2021, 11:30)
    mod_attendance wird verarbeitet (36/550) (Dienstag, 6. April 2021, 11:30)
    mod_bigbluebuttonbn wird verarbeitet (37/550) (Dienstag, 6. April 2021, 11:30)
    mod_book wird verarbeitet (38/550) (Dienstag, 6. April 2021, 11:30)
    mod_chat wird verarbeitet (39/550) (Dienstag, 6. April 2021, 11:30)
    mod_checklist wird verarbeitet (40/550) (Dienstag, 6. April 2021, 11:30)
    mod_choice wird verarbeitet (41/550) (Dienstag, 6. April 2021, 11:30)
    mod_data wird verarbeitet (42/550) (Dienstag, 6. April 2021, 11:30)
    mod_debate wird verarbeitet (43/550) (Dienstag, 6. April 2021, 11:30)
    mod_evoting wird verarbeitet (44/550) (Dienstag, 6. April 2021, 11:30)
    mod_feedback wird verarbeitet (45/550) (Dienstag, 6. April 2021, 11:30)
    mod_folder wird verarbeitet (46/550) (Dienstag, 6. April 2021, 11:30)
    mod_forum wird verarbeitet (47/550) (Dienstag, 6. April 2021, 11:30)
    mod_game wird verarbeitet (48/550) (Dienstag, 6. April 2021, 11:30)
    mod_glossary wird verarbeitet (49/550) (Dienstag, 6. April 2021, 11:30)
    mod_groupmembers wird verarbeitet (50/550) (Dienstag, 6. April 2021, 11:30)
    mod_h5pactivity wird verarbeitet (51/550) (Dienstag, 6. April 2021, 11:30)
    mod_hotquestion wird verarbeitet (52/550) (Dienstag, 6. April 2021, 11:30)
    mod_hvp wird verarbeitet (53/550) (Dienstag, 6. April 2021, 11:30)
    mod_imscp wird verarbeitet (54/550) (Dienstag, 6. April 2021, 11:30)
    mod_jitsi wird verarbeitet (55/550) (Dienstag, 6. April 2021, 11:30)
    mod_label wird verarbeitet (56/550) (Dienstag, 6. April 2021, 11:30)
    mod_lesson wird verarbeitet (57/550) (Dienstag, 6. April 2021, 11:30)
    mod_lti wird verarbeitet (58/550) (Dienstag, 6. April 2021, 11:30)
    mod_moodleoverflow wird verarbeitet (59/550) (Dienstag, 6. April 2021, 11:30)

nothing more.
The process is not finishing and is not being terminated or killed automatically after max execution time for AdHoc-Tasks.

Please help.

Regards,
Martin.

[mputzi]

Even after killing the process of the adhoc task the mysqld process stays at 100% CPU load.
Restarting mysqld recovers the database process.

[justusdieckmann]

Hey Martin,

are you using MySQL or MariaDB and which version are you using? I would be surprised if it is important, but what php version are you using?

Justus

[roessling]

In the same vein, I get this error (luckily, sent by email) when exporting data for a privacy inspection. I am running the weekly stable build of Moodle (currently, this is 3.10.3+ (Build: 20210330)) and the latest release of the plugin (v3.10-r1 - 2020111300), using PostgreSQL (11.11 under Debian) and PHP 7.3.27-1.

Beim Aufruf von mod_moodleoverflow\privacy\provider::get_contexts_for_userid ist ein Fehler aufgetreten.
Das Plugin mod_moodleoverflow hat die Verarbeitung der Daten nicht beendet. Unten finden Sie Informationen für den Pluginentwickler.

Fehler beim Lesen der Datenbank

#0 /opt/moodle/moodle-git-stable/lib/dml/pgsql_native_moodle_database.php(329): moodle_database->query_end(false)
#1 /opt/moodle/moodle-git-stable/lib/dml/pgsql_native_moodle_database.php(826): pgsql_native_moodle_database->query_end(false)
#2 /opt/moodle/moodle-git-stable/privacy/classes/local/request/contextlist.php(71): pgsql_native_moodle_database->get_recordset_sql('DECLARE crs88 N...', Array)
#3 /opt/moodle/moodle-git-stable/mod/moodleoverflow/classes/privacy/provider.php(183): core_privacy\local\request\contextlist->add_from_sql('SELECT c.id\n ...', Array)
#4 /opt/moodle/moodle-git-stable/lib/moodlelib.php(8149): mod_moodleoverflow\privacy\provider::get_contexts_for_userid(11226)
#5 /opt/moodle/moodle-git-stable/privacy/classes/manager.php(578): component_class_callback('mod_moodleoverf...', 'get_contexts_fo...', Array)
#6 /opt/moodle/moodle-git-stable/privacy/classes/manager.php(611): core_privacy\manager::component_class_callback('mod_moodleoverf...', 'core_privacy\lo...', 'get_contexts_fo...', Array)
#7 /opt/moodle/moodle-git-stable/privacy/classes/manager.php(238): core_privacy\manager->handled_component_class_callback('mod_moodleoverf...', 'core_privacy\lo...', 'get_contexts_fo...', Array)
#8 /opt/moodle/moodle-git-stable/admin/tool/dataprivacy/classes/task/process_data_request_task.php(94): core_privacy\manager->get_contexts_for_userid(11226)
#9 /opt/moodle/moodle-git-stable/lib/cronlib.php(341): tool_dataprivacy\task\process_data_request_task->execute()
#10 /opt/moodle/moodle-git-stable/lib/cronlib.php(198): cron_run_inner_adhoc_task(Object(tool_dataprivacy\task\process_data_request_task))
#11 /opt/moodle/moodle-git-stable/lib/cronlib.php(76): cron_run_adhoc_tasks(1617722341)
#12 /opt/moodle/moodle-git-stable/admin/cli/cron.php(178): cron_run()
#13 {main}

[mputzi]

Hey Martin,

are you using MySQL or MariaDB and which version are you using? I would be surprised if it is important, but what php version are you using?

Justus

We are using MariaDB:
mysql Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

For moodle we use php7.4-fpm.
php-Version:
PHP 7.4.3 (cli) (built: Oct 6 2020 15:47:56) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
with Zend OPcache v7.4.3, Copyright (c), by Zend Technologies

[justusdieckmann]

Thank you both for your information. Sadly, I couldn't reproduce the problem. Could you (temporarily) replace Lines 182 and 183 in mod/moodleoverflow/classes/privacy/provider.php with the code below, create a new data request, run the adhoc task and post the new log? It should then contain details about the database error.

        global $DB;
        $DB->set_debug(true);
        try {
            $contextlist = new \core_privacy\local\request\contextlist();
            $contextlist->add_from_sql($sql, $params);
        } finally {
            $DB->set_debug(false);
        }

[roessling]

Hi,
I did that, but still get exactly the same mail as above. The most relevant part seems to be this line:
Beim Aufruf von mod_moodleoverflow\privacy\provider::get_contexts_for_userid ist ein Fehler aufgetreten.
Das Plugin mod_moodleoverflow konnte die Verarbeitung der Daten nicht beenden. Folgende Informationen könnten den Entwickler/innen helfen:

Fehler beim Lesen der Datenbank

#0 /opt/moodle/moodle-git-stable/lib/dml/pgsql_native_moodle_database.php(329): moodle_database->query_end(false)
#1 /opt/moodle/moodle-git-stable/lib/dml/pgsql_native_moodle_database.php(826): pgsql_native_moodle_database->query_end(false)
#2 /opt/moodle/moodle-git-stable/privacy/classes/local/request/contextlist.php(71): pgsql_native_moodle_database->get_recordset_sql('DECLARE crs88 N...', Array)
#3 /opt/moodle/moodle-git-stable/mod/moodleoverflow/classes/privacy/provider.php(186): core_privacy\local\request\contextlist->add_from_sql('SELECT c.id\n ...', Array)
#4 /opt/moodle/moodle-git-stable/lib/moodlelib.php(8149): mod_moodleoverflow\privacy\provider::get_contexts_for_userid(12237)
#5 /opt/moodle/moodle-git-stable/privacy/classes/manager.php(578): component_class_callback('mod_moodleoverf...', 'get_contexts_fo...', Array)
#6 /opt/moodle/moodle-git-stable/privacy/classes/manager.php(611): core_privacy\manager::component_class_callback('mod_moodleoverf...', 'core_privacy\lo...', 'get_contexts_fo...', Array)
#7 /opt/moodle/moodle-git-stable/privacy/classes/manager.php(238): core_privacy\manager->handled_component_class_callback('mod_moodleoverf...', 'core_privacy\lo...', 'get_contexts_fo...', Array)
#8 /opt/moodle/moodle-git-stable/admin/tool/dataprivacy/classes/task/process_data_request_task.php(94): core_privacy\manager->get_contexts_for_userid(12237)
#9 /opt/moodle/moodle-git-stable/lib/cronlib.php(341): tool_dataprivacy\task\process_data_request_task->execute()
#10 /opt/moodle/moodle-git-stable/lib/cronlib.php(198): cron_run_inner_adhoc_task(Object(tool_dataprivacy\task\process_data_request_task))
#11 /opt/moodle/moodle-git-stable/lib/cronlib.php(76): cron_run_adhoc_tasks(1617863041)
#12 /opt/moodle/moodle-git-stable/admin/cli/cron.php(178): cron_run()
#13 {main}

[justusdieckmann]

Hi @roessling

Yes, I think so too, that's why I try to get more information about the database access in get_contexts_for_userid. Could you look into the complete adhoc log (via Settings > Server > Tasks > Task logs ) for that specific task execution and see if there is some debug output in there? (I assume the email only contains the exception, and not the complete log, right?).

[mputzi]

Thank you both for your information. Sadly, I couldn't reproduce the problem. Could you (temporarily) replace Lines 182 and 183 in mod/moodleoverflow/classes/privacy/provider.php with the code below, create a new data request, run the adhoc task and post the new log? It should then contain details about the database error.

        global $DB;
        $DB->set_debug(true);
        try {
            $contextlist = new \core_privacy\local\request\contextlist();
            $contextlist->add_from_sql($sql, $params);
        } finally {
            $DB->set_debug(false);
        }

Okay, I changed the lines as you said.
This is the result:

/var/www/moodle/admin/cli$ sudo -u www-data php adhoc_task.php -e -i -f
Server Time: Thu, 08 Apr 2021 13:54:45 +0200

Execute adhoc task: tool_dataprivacy\task\process_data_request_task
... started 13:54:45. Current memory use 12.9MB.
Pre-processing request...
  Laden von 550 Komponenten (Donnerstag, 8. April 2021, 13:54)
    antivirus_clamav wird verarbeitet (1/550) (Donnerstag, 8. April 2021, 13:54)
    availability_completion wird verarbeitet (2/550) (Donnerstag, 8. April 2021, 13:54)
    availability_date wird verarbeitet (3/550) (Donnerstag, 8. April 2021, 13:54)
    availability_grade wird verarbeitet (4/550) (Donnerstag, 8. April 2021, 13:54)
    availability_group wird verarbeitet (5/550) (Donnerstag, 8. April 2021, 13:54)
    availability_grouping wird verarbeitet (6/550) (Donnerstag, 8. April 2021, 13:54)
    availability_profile wird verarbeitet (7/550) (Donnerstag, 8. April 2021, 13:54)
    availability_xp wird verarbeitet (8/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_algebra wird verarbeitet (9/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_calculated wird verarbeitet (10/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_calculatedmulti wird verarbeitet (11/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_calculatedsimple wird verarbeitet (12/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_ddimageortext wird verarbeitet (13/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_ddmarker wird verarbeitet (14/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_ddmatch wird verarbeitet (15/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_ddwtos wird verarbeitet (16/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_description wird verarbeitet (17/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_essay wird verarbeitet (18/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_formulas wird verarbeitet (19/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_gapfill wird verarbeitet (20/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_gapselect wird verarbeitet (21/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_geogebra wird verarbeitet (22/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_match wird verarbeitet (23/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_matrix wird verarbeitet (24/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_missingtype wird verarbeitet (25/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_multianswer wird verarbeitet (26/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_multichoice wird verarbeitet (27/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_numerical wird verarbeitet (28/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_random wird verarbeitet (29/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_randomsamatch wird verarbeitet (30/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_shortanswer wird verarbeitet (31/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_truefalse wird verarbeitet (32/550) (Donnerstag, 8. April 2021, 13:54)
    qtype_wordselect wird verarbeitet (33/550) (Donnerstag, 8. April 2021, 13:54)
    mod_assign wird verarbeitet (34/550) (Donnerstag, 8. April 2021, 13:54)
    mod_assignment wird verarbeitet (35/550) (Donnerstag, 8. April 2021, 13:54)
    mod_attendance wird verarbeitet (36/550) (Donnerstag, 8. April 2021, 13:54)
    mod_bigbluebuttonbn wird verarbeitet (37/550) (Donnerstag, 8. April 2021, 13:54)
    mod_book wird verarbeitet (38/550) (Donnerstag, 8. April 2021, 13:54)
    mod_chat wird verarbeitet (39/550) (Donnerstag, 8. April 2021, 13:54)
    mod_checklist wird verarbeitet (40/550) (Donnerstag, 8. April 2021, 13:54)
    mod_choice wird verarbeitet (41/550) (Donnerstag, 8. April 2021, 13:54)
    mod_data wird verarbeitet (42/550) (Donnerstag, 8. April 2021, 13:54)
    mod_debate wird verarbeitet (43/550) (Donnerstag, 8. April 2021, 13:54)
    mod_evoting wird verarbeitet (44/550) (Donnerstag, 8. April 2021, 13:54)
    mod_feedback wird verarbeitet (45/550) (Donnerstag, 8. April 2021, 13:54)
    mod_folder wird verarbeitet (46/550) (Donnerstag, 8. April 2021, 13:54)
    mod_forum wird verarbeitet (47/550) (Donnerstag, 8. April 2021, 13:54)
    mod_game wird verarbeitet (48/550) (Donnerstag, 8. April 2021, 13:54)
    mod_glossary wird verarbeitet (49/550) (Donnerstag, 8. April 2021, 13:54)
    mod_groupmembers wird verarbeitet (50/550) (Donnerstag, 8. April 2021, 13:54)
    mod_h5pactivity wird verarbeitet (51/550) (Donnerstag, 8. April 2021, 13:54)
    mod_hotquestion wird verarbeitet (52/550) (Donnerstag, 8. April 2021, 13:54)
    mod_hvp wird verarbeitet (53/550) (Donnerstag, 8. April 2021, 13:54)
    mod_imscp wird verarbeitet (54/550) (Donnerstag, 8. April 2021, 13:54)
    mod_jitsi wird verarbeitet (55/550) (Donnerstag, 8. April 2021, 13:54)
    mod_label wird verarbeitet (56/550) (Donnerstag, 8. April 2021, 13:54)
    mod_lesson wird verarbeitet (57/550) (Donnerstag, 8. April 2021, 13:54)
    mod_lti wird verarbeitet (58/550) (Donnerstag, 8. April 2021, 13:54)
    mod_moodleoverflow wird verarbeitet (59/550) (Donnerstag, 8. April 2021, 13:54)
--------------------------------

                  SELECT ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked
                    FROM mdl_context ctx
                    JOIN (SELECT c.id
                FROM mdl_context c
                INNER JOIN mdl_course_modules cm ON cm.id = c.instanceid AND c.contextlevel = ?
                INNER JOIN mdl_modules m ON m.id = cm.module AND m.name = ?
                INNER JOIN mdl_moodleoverflow mof ON mof.id = cm.instance
                LEFT JOIN mdl_moodleoverflow_discussions d ON d.moodleoverflow = mof.id
                LEFT JOIN mdl_moodleoverflow_posts p ON p.discussion = d.id
                LEFT JOIN mdl_moodleoverflow_read r ON r.moodleoverflowid = mof.id
                LEFT JOIN mdl_moodleoverflow_subscriptions s ON s.moodleoverflow = mof.id
                LEFT JOIN mdl_moodleoverflow_discuss_subs ds ON ds.moodleoverflow = mof.id
                LEFT JOIN mdl_moodleoverflow_ratings ra ON ra.moodleoverflowid = mof.id
                LEFT JOIN mdl_moodleoverflow_tracking track ON track.moodleoverflowid = mof.id
                LEFT JOIN mdl_moodleoverflow_grades g ON g.moodleoverflowid = mof.id
                WHERE (
                    d.userid = ? OR
                    d.usermodified = ? OR
                    p.userid = ? OR
                    r.userid = ? OR
                    s.userid = ? OR
                    ds.userid = ? OR
                    ra.userid = ? OR
                    track.userid = ? OR
                    g.userid = ?
                )
         ) target ON ctx.id = target.id
[array (
  0 => 70,
  1 => 'moodleoverflow',
  2 => 411,
  3 => 411,
  4 => 411,
  5 => 411,
  6 => 411,
  7 => 411,
  8 => 411,
  9 => 411,
  10 => 411,
)]
--------------------------------

The request is indeed from the user with ID 411.

[justusdieckmann]

Thanks @mputzi , and sorry for the delay.
I still wasn't able to reproduce the bug. @roessling Do you still encounter the problem? If so, could you look at #75 (comment)? Your output might contain more information, since it seems that postgres actually responds, instead of locking up like mariadb.

[roessling]

I think the problem will only occur if the person who requests the information who has also written something in a moodleoverflow. As these are used only very sparingly in my Moodle instance (and we only get a negligible number of privacy requests), the problem has not reappeared - but that does not necessarily mean it's "gone".
I also made the requests change (and the DB continues to run), but there was no further output than the one above.

When I run the query from above manually (inserting all values into the placeholders, and running it via "Moodle Adminer"), I get "No rows" (which may be correct, see above), but no error.

[NinaHerrmann]

@justusdieckmann did you checked with a person writing a post? If that is the case, I suppose we are not really able to reproduce the error.

[geichelberger]

We encountered the same error, and the same SQL query seemed involved. We try to together more data.

moodle 3.11.13
moodleoverflow v4.1-r1

[ziegenberg]

The query runs for a very long time. Obtaining the query execution plan gives the following answer. Looks expensive.

EXPLAIN: -> Filter: ((d.userid = 92701) or (d.usermodified = 92701) or (p.userid = 92701) or (r.userid = 92701) or (s.userid = 92701) or (ds.userid = 92701) or (ra.userid = 92701) or (track.userid = 92701) or (g.userid = 92701))  (cost=89138417259074.34 rows=885770895536109)
    -> Left hash join (g.moodleoverflowid = mof.id)  (cost=89138417259074.34 rows=885770895536109)
        -> Left hash join (track.moodleoverflowid = mof.id)  (cost=44311163039719.12 rows=442885447768055)
            -> Left hash join (ra.moodleoverflowid = mof.id)  (cost=452056420016.56 rows=4519239262939)
                -> Nested loop left join  (cost=729920271.62 rows=3352551382)
                    -> Nested loop left join  (cost=32589595.21 rows=160922461)
                        -> Left hash join (r.moodleoverflowid = mof.id)  (cost=109318.51 rows=1092535)
                            -> Nested loop left join  (cost=319.92 rows=240)
                                -> Nested loop left join  (cost=91.90 rows=80)
                                    -> Nested loop inner join  (cost=63.94 rows=4)
                                        -> Nested loop inner join  (cost=62.54 rows=4)
                                            -> Nested loop inner join  (cost=61.15 rows=4)
                                                -> Inner hash join (no condition)  (cost=10.85 rows=75)
                                                    -> Index scan on mof using mdl_mood_cou_ix  (cost=10.50 rows=75)
                                                    -> Hash
                                                        -> Covering index lookup on m using mdl_modu_nam_ix (name='moodleoverflow')  (cost=0.35 rows=1)
                                                -> Filter: (cm.module = m.id)  (cost=0.48 rows=0.05)
                                                    -> Index lookup on cm using mdl_courmodu_ins_ix (instance=mof.id)  (cost=0.48 rows=2)
                                            -> Filter: (c.contextlevel = 70)  (cost=0.28 rows=1)
                                                -> Single-row covering index lookup on c using mdl_cont_conins_uix (contextlevel=70, instanceid=cm.id)  (cost=0.28 rows=1)
                                        -> Single-row index lookup on ctx using PRIMARY (id=c.id)  (cost=0.28 rows=1)
                                    -> Index lookup on d using mdl_mooddisc_moo_ix (moodleoverflow=mof.id)  (cost=5.51 rows=20)
                                -> Index lookup on p using mdl_moodpost_dis_ix (discussion=d.id)  (cost=2.56 rows=3)
                            -> Hash
                                -> Index scan on r using mdl_moodread_usemoo_ix  (cost=2.16 rows=4554)
                        -> Index lookup on s using mdl_moodsubs_moo_ix (moodleoverflow=mof.id)  (cost=15.00 rows=147)
                    -> Index lookup on ds using mdl_mooddiscsubs_moo_ix (moodleoverflow=mof.id)  (cost=2.25 rows=21)
                -> Hash
                    -> Index scan on ra using mdl_moodrati_usemoo_ix  (cost=0.04 rows=1348)
            -> Hash
                -> Index scan on track using mdl_moodtrac_usemoo_ix  (cost=0.01 rows=98)
        -> Hash
            -> Table scan on g  (cost=0.00 rows=2)

[NinaHerrmann]

Sounds reasonable, does anybody has resources to optimize the query?

[NinaHerrmann]

ah is SpaceCafe related to you?