Performance Insights -Query Review- :: Week 36 // Query 3
Query
SELECT snippets.* FROM
(SELECT snippets.* FROM snippets
WHERE snippets.visibility_level = 20
AND snippets.project_id IS NULL
UNION
SELECT snippets.* FROM snippets
INNER JOIN projects ON projects.id = snippets.project_id
INNER JOIN project_features ON project_features.project_id = projects.id
WHERE snippets.visibility_level = 20
AND (projects.visibility_level IN (20))
AND project_features.snippets_access_level IN (20, 30)
)
snippets
ORDER BY created_at DESC LIMIT 20 OFFSET 0;
Plan:
Limit (cost=179035.72..179035.77 rows=20 width=1744) (actual time=4394.065..4394.083 rows=20 loops=1)
-> Sort (cost=179035.72..179133.67 rows=39180 width=1744) (actual time=4394.063..4394.072 rows=20 loops=1)
Sort Key: snippets.created_at DESC
Sort Method: top-N heapsort Memory: 50kB
-> HashAggregate (cost=177209.56..177601.36 rows=39180 width=1744) (actual time=4311.967..4361.869 rows=46433 loops=1)
Group Key: snippets.id, snippets.title, snippets.content, snippets.author_id, snippets.project_id, snippets.created_at, snippets.updated_at, snippets.file_name, snippets.type, snippets.visibility_level, snippets.title_html, snippets.content_html, snippets.cached_markdown_version, snippets.description, snippets.description_html
-> Append (cost=0.42..175740.31 rows=39180 width=1744) (actual time=0.025..284.737 rows=46433 loops=1)
-> Index Scan using index_snippets_on_visibility_level on snippets (cost=0.42..34631.99 rows=34185 width=1084) (actual time=0.025..105.241 rows=42528 loops=1)
Index Cond: (visibility_level = 20)
Filter: (project_id IS NULL)
Rows Removed by Filter: 6778
-> Nested Loop (cost=1.28..140716.52 rows=4995 width=1084) (actual time=0.116..173.926 rows=3905 loops=1)
-> Nested Loop (cost=0.85..136725.49 rows=6243 width=1088) (actual time=0.090..143.089 rows=4059 loops=1)
-> Index Scan using index_snippets_on_visibility_level on snippets snippets_1 (cost=0.42..34631.99 rows=49306 width=1084) (actual time=0.021..86.099 rows=49306 loops=1)
Index Cond: (visibility_level = 20)
-> Index Scan using index_projects_on_id_partial_for_visibility on projects (cost=0.43..2.06 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=49306)
Index Cond: (id = snippets_1.project_id)
Filter: (visibility_level = 20)
Rows Removed by Filter: 0
-> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.63 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=4059)
Index Cond: (project_id = projects.id)
Filter: (snippets_access_level = ANY ('{20,30}'::integer[]))
Rows Removed by Filter: 0
Planning time: 1.151 ms
Execution time: 4400.076 ms
(25 rows)
Statistics
Observation range | [2019-09-04 05:53 - 2019-09-05 06:25 ] |
Frequency (calls per minute) | 1.2 |
Average execution time (ms) | 4417 |
Total database time during range (s) | ~8273 |
Analysis
The vast mayority of the time is used by aggregate the product of the UNION
. The use of UNION enforces uniqueness between both relations. If we can force the two relations to have non-overlaping results, we can use UNION ALL
to avoid check for duplicated:
SELECT snippets.* FROM
(SELECT snippets.* FROM snippets
WHERE snippets.visibility_level = 20
AND snippets.project_id IS NULL
UNION ALL
SELECT snippets.* FROM snippets
INNER JOIN projects ON projects.id = snippets.project_id
INNER JOIN project_features ON project_features.project_id = projects.id
WHERE snippets.visibility_level = 20
***AND snippets.project_id IS NOT NULL***
AND (projects.visibility_level IN (20))
AND project_features.snippets_access_level IN (20, 30)
)
snippets
ORDER BY created_at DESC LIMIT 20 OFFSET 0;