Postgres: Optimize Query-CodePudding

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

CodePudding user response：

I would rewrite the query to

SELECT sj.*
FROM subjects sj 
WHERE EXISTS (SELECT 1
              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)
                AND lmc.id IS NOT NULL)
AND sj.school_id = 1
UNION
SELECT sj.*
FROM subjects sj 
WHERE EXISTS (SELECT 1
              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
                AND tc.id IS NOT NULL)
AND sj.school_id = 1;

That way, PostgreSQL can use a semi-join and may be faster. If you don't mind duplicate result rows, use UNION ALL instead of UNION for better performance.

More performance may be gained with appropriate indexes, but it requires EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) output to assess that.