Performance Insights -Query Review- :: Week 36 // Query 1
Query
SELECT
COUNT(*)
FROM
projects
WHERE (EXISTS (
SELECT
1
FROM
project_authorizations
WHERE
project_authorizations.user_id = 282798
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (10, 20))
Plan:
Aggregate (cost=38341739.13..38341739.14 rows=1 width=8) (actual time=4149.801..4149.801 rows=1 loops=1)
-> Seq Scan on projects (cost=0.00..38327158.60 rows=5832213 width=0) (actual time=0.048..4048.527 rows=1406381 loops=1)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[])))
Rows Removed by Filter: 8851510
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..3.58 rows=1 width=0) (never executed)
Index Cond: ((user_id = 282798) AND (project_id = projects.id))
Heap Fetches: 0
SubPlan 2
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.56..37.84 rows=95 width=4) (actual time=0.025..0.025 rows=0 loops=1)
Index Cond: (user_id = 282798)
Heap Fetches: 0
Planning time: 0.338 ms
Execution time: 4149.858 ms
(14 rows)
Statistics
Observation range | [2019-09-04 05:53 - 2019-09-05 06:25 ] |
Frequency (calls per minute) | 5.1 |
Average execution time (ms) | 3827 |
Total database time during range (s) | ~28945 |
Analysis
This plan does a seq scan over projects
. For this case, the use of EXISTS
does not fit very well here. The (project_authorizations.project_id = projects.id)
in the filter of the "inner" query turns it into a "correlated query", and using "left join lateral" should help:
SELECT COUNT(*) FROM projects left join lateral
(SELECT 1 FROM project_authorizations WHERE
project_authorizations.user_id = 282798
AND (project_authorizations.project_id = projects.id)
) foo on true
where projects.visibility_level IN (10,20)
;