Postgres: Optimize Query
I have a situation where I need to show a list of subjects to which a learning material or a test has been published to. The query below works but it takes about ~8s. Is there a better way I can optimize this query? Thank you.
SELECT sj.*, sj.id AS id FROM subjects sj
WHERE (
(SELECT COUNT(lmc.id) FROM learning_materials_codes lmc
INNER JOIN students s ON lmc.student_id = s.id
INNER JOIN learning_materials lm ON lmc.learning_material_id = lm.id
WHERE lmc.student_id = 1 AND sj.id = ANY(lm.subject_ids)) > 0
OR
(SELECT COUNT(tc.id) FROM test_codes tc
INNER JOIN students s ON tc.student_id = s.id
INNER JOIN tests t ON tc.test_id = t.id
WHERE tc.student_id = 1 AND t.subject_id = sj.id) > 0
)
AND sj.school_id = 1
Table structure below
subjects
~~~~~~~~~~~
id
name
school_id
...
students
~~~~~~~~~~
id
f_name
l_name
school_id
...
tests
~~~~~~~~~
id
title
subject_id
...
test_codes
~~~~~~~~~~
test_id
student_id
code
...
learning_materials
~~~~~~~~~~~~~~~~~
id
title
subject_ids []
...
learning_material_codes
~~~~~~~~~~~~~~~~~~~~~~~~
learning_material_id
student_id
code
...
Note: Each time a learning material or a test is published an access code is generated for students and that data is kept in learning_material_codes
or test_codes
table