To my knowledge it does not allow that level of parallelism. If that's what you are trying to do the best approach is to go with Postgres-XC or GridSQL, but Postgres-XC while more complex is also more flexible.
http://postgres-xc.sourceforge.net/ is the project page.
In this particular case the CTE could be replaced with a normal subselect (a derived table):
SELECT
tbl_notifications.module_name,
tbl_notifications.message,
tbl_notifications.staff_username,
tbl_notifications.date_time
FROM
(
SELECT
tbl_modules.module_code
FROM
tbl_student
INNER JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
WHERE
tbl_student.student_number = '1002'
) AS CTE
INNER JOIN tbl_notifications ON cte.module_code = tbl_notifications.module_code
ORDER BY
tbl_notifications.date_time DESC
;
And that query would work both in SQL Server and MySQL (in any major SQL product, in fact).
Moreover, nesting is unnecessary here at all, and so you could have the same result with the following:
SELECT
tbl_notifications.module_name,
tbl_notifications.message,
tbl_notifications.staff_username,
tbl_notifications.date_time
FROM
tbl_student
INNER JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
INNER JOIN tbl_notifications ON tbl_modules.module_code = tbl_notifications.module_code
WHERE
tbl_student.student_number = '1002'
ORDER BY
tbl_notifications.date_time DESC
;
And by the way, please consider using short table aliases in your queries. Here is what the same query looks like with short aliases:
SELECT
n.module_name,
n.message,
n.staff_username,
n.date_time
FROM
tbl_student AS s
INNER JOIN tbl_modules AS m ON s.registration_year = m.registration_year
INNER JOIN tbl_notifications AS n ON m.module_code = n.module_code
WHERE
s.student_number = '1002'
ORDER BY
n.date_time DESC
;
It may be subjective sometimes, but I do believe that in this case readability improves greatly.
Best Answer
Postgres currently does not use parallelism when executing a single query. The entire execution engine is single threaded for each query (you can of course still execute multiple queries in parallel). This lack of "intra query parallelism" is one of the great drawbacks of running Postgres or MySQL. Basically, it makes those engines unfit for purpose on large datasets.
To get multi-threaded Postgres execution, you either need to split up the query into multiple queries yourself or run a Postgres compatible variant like Amazon Redshift or Greenplum (the former has added intra query parallelism to the code base, the latter does the splitting for you).
Whenever you feel the need to run a parallel query, first ask yourself: Could I add indexes that would make this fast enough without parallelism? If that answer is "Yes", pursue that option first. Very often though, you need brute force. And when it comes to brute force, vanilla Postgres is unfortunately not a particularly good database engine.