Improve snippets search SQL
This is a follow up to gitlab-foss!23952 (merged). We removed redundant counts there to improve the request time but the query itself might need improvements too.
-
Current query on staging (same in
master):SQL
SELECT COUNT(*) FROM (SELECT "snippets".* FROM "snippets" WHERE (snippets.visibility_level IN (10, 20) OR snippets.author_id = 1675774) 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 IN (10, 20) OR snippets.author_id = 1675774) AND (projects.visibility_level IN (10, 20)) AND "project_features"."snippets_access_level" IN (20, 30) 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 "project_features"."snippets_access_level" IN (20, 30, 10) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE (project_id = snippets.project_id) AND "project_authorizations"."user_id" = 1675774))) snippets WHERE "snippets"."content" ILIKE '%foo%'EXPLAIN
Aggregate (cost=13494.71..13494.72 rows=1 width=8) (actual time=9136.346..9136.346 rows=1 loops=1) Buffers: shared hit=34431 read=20888 I/O Timings: read=962.676 -> HashAggregate (cost=13492.51..13493.49 rows=98 width=1744) (actual time=9135.890..9136.298 rows=494 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, snippe ts.description_html Buffers: shared hit=34431 read=20888 I/O Timings: read=962.676 -> Append (cost=605.78..13488.83 rows=98 width=1744) (actual time=30.786..8364.451 rows=509 loops=1) Buffers: shared hit=25958 read=16632 I/O Timings: read=856.209 -> Bitmap Heap Scan on snippets (cost=605.78..6538.49 rows=92 width=1134) (actual time=30.784..4049.599 rows=451 loops=1) Recheck Cond: (((visibility_level = ANY ('{10,20}'::integer[])) OR (author_id = 1675774)) AND (project_id IS NULL)) Filter: (content ~~* '%foo%'::text) Rows Removed by Filter: 11029 Heap Blocks: exact=5012 Buffers: shared hit=3894 read=14249 I/O Timings: read=615.799 -> BitmapAnd (cost=605.78..605.78 rows=10235 width=0) (actual time=19.574..19.575 rows=0 loops=1) Buffers: shared hit=51 read=107 I/O Timings: read=12.004 -> BitmapOr (cost=162.75..162.75 rows=13978 width=0) (actual time=2.236..2.236 rows=0 loops=1) Buffers: shared hit=49 -> Bitmap Index Scan on index_snippets_on_visibility_level (cost=0.00..160.90 rows=13976 width=0) (actual time=2.220..2.220 rows=14132 loops=1) Index Cond: (visibility_level = ANY ('{10,20}'::integer[])) Buffers: shared hit=47 -> Bitmap Index Scan on index_snippets_on_author_id (cost=0.00..1.80 rows=2 width=0) (actual time=0.015..0.015 rows=0 loops= 1) Index Cond: (author_id = 1675774) Buffers: shared hit=2 -> Bitmap Index Scan on index_snippets_on_project_id (cost=0.00..442.75 rows=37195 width=0) (actual time=16.478..16.478 rows=37500 loops=1) Index Cond: (project_id IS NULL) Buffers: shared hit=2 read=107 I/O Timings: read=12.004 -> Nested Loop (cost=163.62..6881.02 rows=5 width=1134) (actual time=464.825..4168.857 rows=39 loops=1) Buffers: shared hit=19866 read=2292 I/O Timings: read=210.612 -> Nested Loop (cost=163.19..6871.58 rows=15 width=1138) (actual time=461.839..4158.875 rows=41 loops=1) Buffers: shared hit=19740 read=2254 I/O Timings: read=202.338 -> Bitmap Heap Scan on snippets snippets_1 (cost=162.77..6624.38 rows=126 width=1134) (actual time=5.848..4150.055 rows=563 loops= 1) Recheck Cond: ((visibility_level = ANY ('{10,20}'::integer[])) OR (author_id = 1675774)) Filter: (content ~~* '%foo%'::text) Rows Removed by Filter: 13411 Heap Blocks: exact=5436 Buffers: shared hit=19239 read=2208 I/O Timings: read=199.190 -> BitmapOr (cost=162.77..162.77 rows=13978 width=0) (actual time=1.477..1.477 rows=0 loops=1) Buffers: shared hit=49 -> Bitmap Index Scan on index_snippets_on_visibility_level (cost=0.00..160.90 rows=13976 width=0) (actual time=1.465.. 1.465 rows=14132 loops=1) Index Cond: (visibility_level = ANY ('{10,20}'::integer[])) Buffers: shared hit=47 -> Bitmap Index Scan on index_snippets_on_author_id (cost=0.00..1.80 rows=2 width=0) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (author_id = 1675774) Buffers: shared hit=2 -> Index Only Scan using index_projects_on_id_partial_for_visibility on projects (cost=0.42..1.95 rows=1 width=4) (actual time=0.0 11..0.012 rows=0 loops=563) Index Cond: (id = snippets_1.project_id) Heap Fetches: 191 Buffers: shared hit=501 read=46 I/O Timings: read=3.148 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.62 rows=1 width=4) (actual time=0.237..0.238 rows=1 loops=41) Index Cond: (project_id = projects.id) Filter: (snippets_access_level = ANY ('{20,30}'::integer[])) Rows Removed by Filter: 0 Buffers: shared hit=126 read=38 I/O Timings: read=8.274EXPLAIN for subquery 1
gitlabhq_production=> explain (analyze, buffers) SELECT "snippets".* gitlabhq_production-> FROM "snippets" gitlabhq_production-> WHERE (snippets.visibility_level IN (10, gitlabhq_production(> 20) gitlabhq_production(> OR snippets.author_id = 1675774) gitlabhq_production-> AND "snippets"."project_id" IS NULL; Bitmap Heap Scan on snippets (cost=613.38..6520.51 rows=10235 width=1134) (actual time=6.284..21.256 rows=11480 loops=1) Recheck Cond: (((visibility_level = ANY ('{10,20}'::integer[])) OR (author_id = 1675774)) AND (project_id IS NULL)) Heap Blocks: exact=5012 Buffers: shared hit=5170 -> BitmapAnd (cost=613.38..613.38 rows=10235 width=0) (actual time=5.545..5.545 rows=0 loops=1) Buffers: shared hit=158 -> BitmapOr (cost=167.82..167.82 rows=13978 width=0) (actual time=1.746..1.746 rows=0 loops=1) Buffers: shared hit=49 -> Bitmap Index Scan on index_snippets_on_visibility_level (cost=0.00..160.90 rows=13976 width=0) (actual time=1.733..1.733 rows=14132 loops=1 ) Index Cond: (visibility_level = ANY ('{10,20}'::integer[])) Buffers: shared hit=47 -> Bitmap Index Scan on index_snippets_on_author_id (cost=0.00..1.80 rows=2 width=0) (actual time=0.010..0.010 rows=0 loops=1) Index Cond: (author_id = 1675774) Buffers: shared hit=2 -> Bitmap Index Scan on index_snippets_on_project_id (cost=0.00..442.75 rows=37195 width=0) (actual time=2.992..2.992 rows=37500 loops=1) Index Cond: (project_id IS NULL) Buffers: shared hit=109 Planning time: 0.239 ms Execution time: 21.987 msEXPLAIN for subquery 2
gitlabhq_production=> explain (analyze, buffers) SELECT "snippets".* gitlabhq_production-> FROM "snippets" gitlabhq_production-> INNER JOIN "projects" ON "projects"."id" = "snippets"."project_id" gitlabhq_production-> INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id" gitlabhq_production-> WHERE (snippets.visibility_level IN (10, gitlabhq_production(> 20) gitlabhq_production(> OR snippets.author_id = 1675774) gitlabhq_production-> AND (projects.visibility_level IN (10, gitlabhq_production(> 20)) gitlabhq_production-> AND "project_features"."snippets_access_level" IN (20, gitlabhq_production(> 30); Nested Loop (cost=170.55..17809.22 rows=605 width=1134) (actual time=3.175..187.270 rows=810 loops=1) Buffers: shared hit=17790 read=1240 I/O Timings: read=136.371 -> Nested Loop (cost=170.12..16768.46 rows=1654 width=1138) (actual time=3.070..64.243 rows=860 loops=1) Buffers: shared hit=14998 read=589 I/O Timings: read=22.263 -> Bitmap Heap Scan on snippets (cost=169.69..6596.36 rows=13978 width=1134) (actual time=2.330..22.674 rows=13974 loops=1) Recheck Cond: ((visibility_level = ANY ('{10,20}'::integer[])) OR (author_id = 1675774)) Heap Blocks: exact=5436 Buffers: shared hit=5485 -> BitmapOr (cost=169.69..169.69 rows=13978 width=0) (actual time=1.526..1.526 rows=0 loops=1) Buffers: shared hit=49 -> Bitmap Index Scan on index_snippets_on_visibility_level (cost=0.00..160.90 rows=13976 width=0) (actual time=1.518..1.518 rows=14132 l oops=1) Index Cond: (visibility_level = ANY ('{10,20}'::integer[])) Buffers: shared hit=47 -> Bitmap Index Scan on index_snippets_on_author_id (cost=0.00..1.80 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=1) Index Cond: (author_id = 1675774) Buffers: shared hit=2 -> Index Only Scan using index_projects_on_id_partial_for_visibility on projects (cost=0.42..0.72 rows=1 width=4) (actual time=0.003..0.003 rows=0 l oops=13974) Index Cond: (id = snippets.project_id) Heap Fetches: 2095 Buffers: shared hit=9513 read=589 I/O Timings: read=22.263 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.62 rows=1 width=4) (actual time=0.142..0.142 rows=1 loops=860) Index Cond: (project_id = projects.id) Filter: (snippets_access_level = ANY ('{20,30}'::integer[])) Rows Removed by Filter: 0 Buffers: shared hit=2792 read=651 I/O Timings: read=114.108 Planning time: 1.521 ms Execution time: 187.459 msEXPLAIN for subquery 3
gitlabhq_production=> explain (analyze, buffers) SELECT "snippets".* gitlabhq_production-> FROM "snippets" gitlabhq_production-> INNER JOIN "projects" ON "projects"."id" = "snippets"."project_id" gitlabhq_production-> INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id" gitlabhq_production-> WHERE "project_features"."snippets_access_level" IN (20, gitlabhq_production(> 30, gitlabhq_production(> 10) gitlabhq_production-> AND (EXISTS gitlabhq_production(> (SELECT 1 gitlabhq_production(> FROM "project_authorizations" gitlabhq_production(> WHERE (project_id = snippets.project_id) gitlabhq_production(> AND "project_authorizations"."user_id" = 1675774)); Nested Loop (cost=5.12..68.48 rows=1 width=1134) (actual time=0.561..9.657 rows=255 loops=1) Buffers: shared hit=2827 read=5 I/O Timings: read=4.310 -> Nested Loop (cost=4.69..67.99 rows=1 width=1142) (actual time=0.540..4.641 rows=255 loops=1) Buffers: shared hit=1812 -> Nested Loop (cost=4.40..58.59 rows=27 width=8) (actual time=0.158..3.224 rows=235 loops=1) Buffers: shared hit=1100 -> HashAggregate (cost=3.97..4.24 rows=27 width=4) (actual time=0.132..0.210 rows=235 loops=1) Group Key: project_authorizations.project_id Buffers: shared hit=8 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..3.90 rows =27 width=4) (actual time=0.035..0.073 rows=235 loops=1) Index Cond: (user_id = 1675774) Heap Fetches: 2 Buffers: shared hit=8 -> Index Only Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects (cost=0.43..2.00 rows=1 width=4) (actual ti me=0.012..0.012 rows=1 loops=235) Index Cond: (id = project_authorizations.project_id) Heap Fetches: 184 Buffers: shared hit=1092 -> Index Scan using index_snippets_on_project_id on snippets (cost=0.29..0.33 rows=2 width=1134) (actual time=0.003..0.005 rows=1 loops=235) Index Cond: (project_id = projects.id) Buffers: shared hit=712 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.47 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=255) Index Cond: (project_id = projects.id) Filter: (snippets_access_level = ANY ('{20,30,10}'::integer[])) Buffers: shared hit=1015 read=5 I/O Timings: read=4.310 Planning time: 11.190 ms Execution time: 9.803 ms
-
Suggested query from the comment of @redbaron1 https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/23952#note_128840089:
just curious does it get better if we merge first 2 selects from snippets? Idea is to get rid of duplicated heap scan in non-indexed case, which does more or less same job twice
SQL
SELECT COUNT(*) FROM (SELECT "snippets".* FROM "snippets" LEFT OUTER JOIN ( "projects" INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id" AND "projects"."visibility_level" IN (10, 20) AND "project_features"."snippets_access_level" IN (20, 30) ) ON "projects"."id" = "snippets"."project_id" WHERE (snippets.visibility_level IN (10, 20) OR snippets.author_id = 443319) AND ("snippets"."project_id" IS NULL OR "projects"."id" IS NOT 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 "project_features"."snippets_access_level" IN (20, 30, 10) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE (project_id = snippets.project_id) AND "project_authorizations"."user_id" = 443319))) snippets WHERE ("snippets"."content" ILIKE '%foo%')EXPLAIN (from staging)
Aggregate (cost=8898.23..8898.24 rows=1 width=8) (actual time=5211.605..5211.606 rows=1 loops=1) Buffers: shared hit=38571 read=2970 I/O Timings: read=528.957 -> HashAggregate (cost=8895.37..8896.64 rows=127 width=1744) (actual time=5211.230..5211.565 rows=494 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, snippe ts.description_html Buffers: shared hit=38571 read=2970 I/O Timings: read=528.957 -> Append (cost=163.82..8890.61 rows=127 width=1744) (actual time=24.537..4530.016 rows=510 loops=1) Buffers: shared hit=25932 read=2880 I/O Timings: read=517.138 -> Nested Loop Left Join (cost=163.82..6947.22 rows=126 width=1134) (actual time=24.537..4332.577 rows=491 loops=1) Filter: ((snippets.project_id IS NULL) OR (projects.id IS NOT NULL)) Rows Removed by Filter: 73 Buffers: shared hit=19437 read=2761 I/O Timings: read=452.625 -> Bitmap Heap Scan on snippets (cost=162.96..6625.03 rows=126 width=1134) (actual time=24.520..4313.814 rows=564 loops=1) Recheck Cond: ((visibility_level = ANY ('{10,20}'::integer[])) OR (author_id = 443319)) Filter: (content ~~* '%foo%'::text) Rows Removed by Filter: 13421 Heap Blocks: exact=5441 Buffers: shared hit=18747 read=2750 I/O Timings: read=443.573 -> BitmapOr (cost=162.96..162.96 rows=14004 width=0) (actual time=6.450..6.450 rows=0 loops=1) Buffers: shared hit=48 read=2 I/O Timings: read=3.773 -> Bitmap Index Scan on index_snippets_on_visibility_level (cost=0.00..160.90 rows=13976 width=0) (actual time=2.516..2.516 rows=14125 loops=1) Index Cond: (visibility_level = ANY ('{10,20}'::integer[])) Buffers: shared hit=47 -> Bitmap Index Scan on index_snippets_on_author_id (cost=0.00..2.00 rows=28 width=0) (actual time=3.931..3.931 rows=28 loop s=1) Index Cond: (author_id = 443319) Buffers: shared hit=1 read=2 I/O Timings: read=3.773 -> Nested Loop (cost=0.85..2.55 rows=1 width=4) (actual time=0.029..0.029 rows=0 loops=564) Buffers: shared hit=690 read=11 I/O Timings: read=9.052 -> Index Only Scan using index_projects_on_id_partial_for_visibility on projects (cost=0.42..1.92 rows=1 width=4) (actual time=0.0 20..0.020 rows=0 loops=564) Index Cond: (id = snippets.project_id) Heap Fetches: 174 Buffers: shared hit=525 read=8 I/O Timings: read=7.047 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.62 rows=1 width=4) (actual time=0.090.. 0.091 rows=1 loops=42) Index Cond: (project_id = projects.id) Filter: (snippets_access_level = ANY ('{20,30}'::integer[])) Rows Removed by Filter: 0 Buffers: shared hit=165 read=3 I/O Timings: read=2.005 -> Nested Loop (cost=59.73..1942.12 rows=1 width=1134) (actual time=70.415..197.145 rows=19 loops=1) Buffers: shared hit=6495 read=119 I/O Timings: read=64.513 -> Nested Loop (cost=59.30..1941.64 rows=1 width=1142) (actual time=70.329..196.806 rows=19 loops=1) Buffers: shared hit=6419 read=119 I/O Timings: read=64.513 -> Nested Loop (cost=59.01..1658.23 rows=826 width=8) (actual time=2.689..41.227 rows=954 loops=1) Buffers: shared hit=3962 read=19 I/O Timings: read=26.518 -> HashAggregate (cost=58.58..66.84 rows=826 width=4) (actual time=1.068..1.583 rows=954 loops=1) Group Key: project_authorizations.project_id Buffers: shared hit=135 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (co st=0.43..56.51 rows=826 width=4) (actual time=0.104..0.852 rows=954 loops=1) Index Cond: (user_id = 443319) Heap Fetches: 36 Buffers: shared hit=135 -> Index Only Scan using projects_pkey on projects projects_1 (cost=0.43..1.92 rows=1 width=4) (actual time=0.041..0.041 row s=1 loops=954) Index Cond: (id = project_authorizations.project_id) Heap Fetches: 227 Buffers: shared hit=3827 read=19 I/O Timings: read=26.518 -> Index Scan using index_snippets_on_project_id on snippets snippets_1 (cost=0.29..0.33 rows=1 width=1134) (actual time=0.057..0. 163 rows=0 loops=954) Index Cond: (project_id = projects_1.id) Filter: (content ~~* '%foo%'::text) Rows Removed by Filter: 0 Buffers: shared hit=2457 read=100 I/O Timings: read=37.995 -> Index Scan using index_project_features_on_project_id on project_features project_features_1 (cost=0.43..0.47 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=19) Index Cond: (project_id = projects_1.id) Filter: (snippets_access_level = ANY ('{20,30,10}'::integer[])) Buffers: shared hit=76 Planning time: 41.530 ms Execution time: 5212.074 msEXPLAIN for subquery 1
gitlabhq_production=> explain (analyze, buffers) SELECT "snippets".* gitlabhq_production-> FROM "snippets" gitlabhq_production-> LEFT OUTER JOIN ( gitlabhq_production(> "projects" gitlabhq_production(> INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id" gitlabhq_production(> AND "projects"."visibility_level" IN (10, 20) gitlabhq_production(> AND "project_features"."snippets_access_level" IN (20, 30) gitlabhq_production(> ) ON "projects"."id" = "snippets"."project_id" gitlabhq_production-> WHERE (snippets.visibility_level IN (10, gitlabhq_production(> 20) gitlabhq_production(> OR snippets.author_id = 443319) gitlabhq_production-> AND ("snippets"."project_id" IS NULL OR "projects"."id" IS NOT NULL); Nested Loop Left Join (cost=170.75..25586.51 rows=13997 width=1134) (actual time=4.929..51.256 rows=12301 loops=1) Filter: ((snippets.project_id IS NULL) OR (projects.id IS NOT NULL)) Rows Removed by Filter: 1684 Buffers: shared hit=19236 read=2 I/O Timings: read=2.040 -> Bitmap Heap Scan on snippets (cost=169.90..6596.96 rows=13997 width=1134) (actual time=4.881..22.648 rows=13985 loops=1) Recheck Cond: ((visibility_level = ANY ('{10,20}'::integer[])) OR (author_id = 443319)) Heap Blocks: exact=5441 Buffers: shared hit=5489 read=2 I/O Timings: read=2.040 -> BitmapOr (cost=169.90..169.90 rows=14004 width=0) (actual time=3.661..3.662 rows=0 loops=1) Buffers: shared hit=48 read=2 I/O Timings: read=2.040 -> Bitmap Index Scan on index_snippets_on_visibility_level (cost=0.00..160.90 rows=13976 width=0) (actual time=1.522..1.522 rows=14132 loops=1 ) Index Cond: (visibility_level = ANY ('{10,20}'::integer[])) Buffers: shared hit=47 -> Bitmap Index Scan on index_snippets_on_author_id (cost=0.00..2.00 rows=28 width=0) (actual time=2.137..2.138 rows=28 loops=1) Index Cond: (author_id = 443319) Buffers: shared hit=1 read=2 I/O Timings: read=2.040 -> Nested Loop (cost=0.85..1.35 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=13985) Buffers: shared hit=13747 -> Index Only Scan using index_projects_on_id_partial_for_visibility on projects (cost=0.42..0.72 rows=1 width=4) (actual time=0.001..0.001 rows=0 l oops=13985) Index Cond: (id = snippets.project_id) Heap Fetches: 2225 Buffers: shared hit=10264 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.62 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops =870) Index Cond: (project_id = projects.id) Filter: (snippets_access_level = ANY ('{20,30}'::integer[])) Rows Removed by Filter: 0 Buffers: shared hit=3483 Planning time: 1.264 ms Execution time: 52.029 msEXPLAIN for subquery 2
gitlabhq_production=> explain (analyze, buffers) SELECT "snippets".* gitlabhq_production-> FROM "snippets" gitlabhq_production-> INNER JOIN "projects" ON "projects"."id" = "snippets"."project_id" gitlabhq_production-> INNER JOIN "project_features" ON "project_features"."project_id" = "projects"."id" gitlabhq_production-> WHERE "project_features"."snippets_access_level" IN (20, gitlabhq_production(> 30, gitlabhq_production(> 10) gitlabhq_production-> AND (EXISTS gitlabhq_production(> (SELECT 1 gitlabhq_production(> FROM "project_authorizations" gitlabhq_production(> WHERE (project_id = snippets.project_id) gitlabhq_production(> AND "project_authorizations"."user_id" = 443319)); Nested Loop (cost=59.73..1985.51 rows=5 width=1134) (actual time=6.588..43.190 rows=260 loops=1) Buffers: shared hit=6545 read=643 I/O Timings: read=25.879 -> Nested Loop (cost=59.30..1978.77 rows=14 width=1142) (actual time=6.539..40.283 rows=260 loops=1) Buffers: shared hit=5507 read=641 I/O Timings: read=23.839 -> Nested Loop (cost=59.01..1691.23 rows=826 width=8) (actual time=2.162..36.626 rows=954 loops=1) Buffers: shared hit=3354 read=638 I/O Timings: read=23.683 -> HashAggregate (cost=58.58..66.84 rows=826 width=4) (actual time=1.962..2.508 rows=954 loops=1) Group Key: project_authorizations.project_id Buffers: shared hit=119 read=16 I/O Timings: read=0.842 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..56.51 row s=826 width=4) (actual time=0.172..1.753 rows=954 loops=1) Index Cond: (user_id = 443319) Heap Fetches: 36 Buffers: shared hit=119 read=16 I/O Timings: read=0.842 -> Index Only Scan using projects_pkey on projects (cost=0.43..1.96 rows=1 width=4) (actual time=0.035..0.035 rows=1 loops=954) Index Cond: (id = project_authorizations.project_id) Heap Fetches: 239 Buffers: shared hit=3235 read=622 I/O Timings: read=22.841 -> Index Scan using index_snippets_on_project_id on snippets (cost=0.29..0.33 rows=2 width=1134) (actual time=0.003..0.003 rows=0 loops=954) Index Cond: (project_id = projects.id) Buffers: shared hit=2153 read=3 I/O Timings: read=0.156 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.47 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=260) Index Cond: (project_id = projects.id) Filter: (snippets_access_level = ANY ('{20,30,10}'::integer[])) Buffers: shared hit=1038 read=2 I/O Timings: read=2.040 Planning time: 17.046 ms Execution time: 43.345 ms
Edited by Markus Koller