An error occurred while fetching the assigned iteration of the selected issue.
Get query results from staging
I'd need some EXPLAIN analyse results for the queries in this snippet: https://gitlab.com/snippets/1677663
These are the queries that need to be run on staging:
In these examples I'm limiting the queries with ID 2, I'm assuming that exists on staging?
Subgroups + count
EXPLAIN ANALYSE
SELECT namespaces.*,
(SELECT COUNT(*) AS preloaded_project_count
FROM projects WHERE projects.namespace_id = namespaces.id),
(SELECT COUNT(*) AS preloaded_subgroup_count
FROM namespaces children
WHERE children.parent_id = namespaces.id),
(SELECT COUNT(*) AS preloaded_member_count
FROM members
WHERE members.source_type = 'Namespace'
AND members.source_id = namespaces.id
AND members.requested_at IS NULL)
FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."parent_id" = 2 ORDER BY "namespaces"."id" ASC LIMIT 20 OFFSET 0;All descendant groups matching filter and their ancestors
EXPLAIN ANALYSE
WITH RECURSIVE "base_and_ancestors" AS (
WITH RECURSIVE "base_and_descendants" AS (
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."id" = 2
UNION
SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."parent_id" = "base_and_descendants"."id"
)
SELECT "namespaces".* FROM "base_and_descendants" AS "namespaces" WHERE "namespaces"."id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" IN ('Group') ORDER BY "namespaces"."id" DESC) AND ("namespaces"."id" != 2) AND ("namespaces"."name" ILIKE '%sub%' OR "namespaces"."path" ILIKE '%sub%')
UNION
SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."id" = "base_and_ancestors"."parent_id"
)
SELECT namespaces.*,
(SELECT COUNT(*) AS preloaded_project_count
FROM projects
WHERE projects.namespace_id = namespaces.id
AND projects.archived IS NOT true),
(SELECT COUNT(*) AS preloaded_subgroup_count
FROM namespaces children
WHERE children.parent_id = namespaces.id),
(SELECT COUNT(*) AS preloaded_member_count
FROM members
WHERE members.source_type = 'Namespace'
AND members.source_id = namespaces.id
AND members.requested_at IS NULL)
FROM "base_and_ancestors" AS "namespaces"
WHERE (
"namespaces"."id" NOT IN
(WITH RECURSIVE "base_and_ancestors" AS (
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."id" = 2
UNION
SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."id" = "base_and_ancestors"."parent_id") SELECT "namespaces"."id" FROM "base_and_ancestors" AS "namespaces")
)
ORDER BY "namespaces"."id" ASC LIMIT 20 OFFSET 0All projects nested in a group matching a filter
EXPLAIN ANALYSE
SELECT "projects".* FROM "projects"
WHERE (
EXISTS (
SELECT 1 FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0,10,20))
AND "projects"."archived" = 'f'
AND (projects.id IN (
SELECT "projects"."id"
FROM "projects"
WHERE (("projects"."path" ILIKE '%sub%' OR "projects"."name" ILIKE '%sub%')
OR "projects"."description" ILIKE '%sub%')
UNION
SELECT "projects"."id" FROM "projects" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE ("namespaces"."name" ILIKE '%sub%')))
AND "projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS (
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."id" = 2
UNION
SELECT "namespaces".* FROM "namespaces", "base_and_descendants"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."parent_id" = "base_and_descendants"."id")
SELECT id FROM "base_and_descendants" AS "namespaces" WHERE "namespaces"."id" IN (
SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" IN ('Group')
)
)
ORDER BY "projects"."id" ASC LIMIT 20 OFFSET 0Activity
assigned to @ilyaf
first one:
gitlabhq_production=# EXPLAIN ANALYSE gitlabhq_production-# SELECT namespaces.*, gitlabhq_production-# (SELECT COUNT(*) AS preloaded_project_count gitlabhq_production(# FROM projects WHERE projects.namespace_id = namespaces.id), gitlabhq_production-# (SELECT COUNT(*) AS preloaded_subgroup_count gitlabhq_production(# FROM namespaces children gitlabhq_production(# WHERE children.parent_id = namespaces.id), gitlabhq_production-# (SELECT COUNT(*) AS preloaded_member_count gitlabhq_production(# FROM members gitlabhq_production(# WHERE members.source_type = 'Namespace' gitlabhq_production(# AND members.source_id = namespaces.id gitlabhq_production(# AND members.requested_at IS NULL) gitlabhq_production-# FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."parent_id" = 2 ORDER BY "namespaces"."id" ASC LIMIT 20 OFFSET 0; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.43..29.75 rows=1 width=336) (actual time=1.716..1.716 rows=0 loops=1) -> Index Scan using index_namespaces_on_parent_id_and_id on namespaces (cost=0.43..29.75 rows=1 width=336) (actual time=1.714..1.714 rows=0 loops=1) Index Cond: (parent_id = 2) Filter: ((type)::text = 'Group'::text) SubPlan 1 -> Aggregate (cost=4.77..4.78 rows=1 width=8) (never executed) -> Index Only Scan using index_projects_on_namespace_id on projects (cost=0.43..4.73 rows=17 width=0) (never executed) Index Cond: (namespace_id = namespaces.id) Heap Fetches: 0 SubPlan 2 -> Aggregate (cost=4.59..4.60 rows=1 width=8) (never executed) -> Index Only Scan using index_namespaces_on_parent_id_and_id on namespaces children (cost=0.43..4.57 rows=8 width=0) (never executed) Index Cond: (parent_id = namespaces.id) Heap Fetches: 0 SubPlan 3 -> Aggregate (cost=4.45..4.46 rows=1 width=8) (never executed) -> Index Scan using index_members_on_source_id_and_source_type on members (cost=0.43..4.45 rows=1 width=0) (never executed) Index Cond: ((source_id = namespaces.id) AND ((source_type)::text = 'Namespace'::text)) Filter: (requested_at IS NULL) Planning time: 3.379 ms Execution time: 1.839 ms (21 rows)second one (w/o query for brevity):
QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=15633.19..15907.12 rows=6 width=373) (actual time=0.689..0.689 rows=0 loops=1) CTE base_and_ancestors -> Recursive Union (cost=14534.13..15180.20 rows=11 width=349) (actual time=0.662..0.662 rows=0 loops=1) CTE base_and_descendants -> Recursive Union (cost=0.43..1789.94 rows=11 width=312) (actual time=0.659..0.659 rows=0 loops=1) -> Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..4.45 rows=1 width=312) (actual time=0.655..0.655 rows=0 loops=1) Index Cond: (id = 2) Filter: ((type)::text = 'Group'::text) Rows Removed by Filter: 1 -> Nested Loop (cost=0.43..178.53 rows=1 width=312) (actual time=0.003..0.003 rows=0 loops=1) -> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=0 loops=1) -> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..17.82 rows=1 width=312) (never executed) Index Cond: (parent_id = base_and_descendants.id) Filter: ((type)::text = 'Group'::text) -> Nested Loop Semi Join (cost=12744.19..12942.30 rows=1 width=349) (actual time=0.661..0.661 rows=0 loops=1) Join Filter: (namespaces_3.id = namespaces_4.id) -> CTE Scan on base_and_descendants namespaces_3 (cost=0.00..0.30 rows=1 width=349) (actual time=0.659..0.659 rows=0 loops=1) Filter: ((id <> 2) AND (((name)::text ~~* '%sub%'::text) OR ((path)::text ~~* '%sub%'::text))) -> Sort (cost=12744.19..12763.97 rows=7912 width=4) (never executed) Sort Key: namespaces_4.id DESC -> Bitmap Heap Scan on namespaces namespaces_4 (cost=245.75..12231.90 rows=7912 width=4) (never executed) Recheck Cond: ((type)::text = 'Group'::text) -> Bitmap Index Scan on index_namespaces_on_type (cost=0.00..243.77 rows=7912 width=0) (never executed) Index Cond: ((type)::text = 'Group'::text) -> Nested Loop (cost=0.43..44.78 rows=1 width=312) (actual time=0.001..0.001 rows=0 loops=1) -> WorkTable Scan on base_and_ancestors (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=0 loops=1) -> Index Scan using namespaces_pkey on namespaces namespaces_5 (cost=0.43..4.45 rows=1 width=312) (never executed) Index Cond: (id = base_and_ancestors.parent_id) Filter: ((type)::text = 'Group'::text) -> Result (cost=452.99..726.92 rows=6 width=373) (actual time=0.687..0.687 rows=0 loops=1) -> Sort (cost=452.99..453.01 rows=6 width=349) (actual time=0.686..0.686 rows=0 loops=1) Sort Key: namespaces.id Sort Method: quicksort Memory: 25kB -> CTE Scan on base_and_ancestors namespaces (cost=452.67..452.91 rows=6 width=349) (actual time=0.664..0.664 rows=0 loops=1) Filter: (NOT (hashed SubPlan 7)) SubPlan 7 -> CTE Scan on base_and_ancestors namespaces_8 (cost=452.42..452.64 rows=11 width=4) (never executed) CTE base_and_ancestors -> Recursive Union (cost=0.43..452.42 rows=11 width=312) (never executed) -> Index Scan using namespaces_pkey on namespaces namespaces_6 (cost=0.43..4.45 rows=1 width=312) (never executed) Index Cond: (id = 2) Filter: ((type)::text = 'Group'::text) -> Nested Loop (cost=0.43..44.78 rows=1 width=312) (never executed) -> WorkTable Scan on base_and_ancestors base_and_ancestors_1 (cost=0.00..0.20 rows=10 width=4) (never executed) -> Index Scan using namespaces_pkey on namespaces namespaces_7 (cost=0.43..4.45 rows=1 width=312) (never executed) Index Cond: (id = base_and_ancestors_1.parent_id) Filter: ((type)::text = 'Group'::text) SubPlan 3 -> Aggregate (cost=36.57..36.58 rows=1 width=8) (never executed) -> Index Scan using index_projects_on_namespace_id on projects (cost=0.43..36.53 rows=17 width=0) (never executed) Index Cond: (namespace_id = namespaces.id) Filter: (archived IS NOT TRUE) SubPlan 4 -> Aggregate (cost=4.59..4.60 rows=1 width=8) (never executed) -> Index Only Scan using index_namespaces_on_parent_id_and_id on namespaces children (cost=0.43..4.57 rows=8 width=0) (never executed) Index Cond: (parent_id = namespaces.id) Heap Fetches: 0 SubPlan 5 -> Aggregate (cost=4.45..4.46 rows=1 width=8) (never executed) -> Index Scan using index_members_on_source_id_and_source_type on members (cost=0.43..4.45 rows=1 width=0) (never executed) Index Cond: ((source_id = namespaces.id) AND ((source_type)::text = 'Namespace'::text)) Filter: (requested_at IS NULL) Planning time: 2.001 ms Execution time: 1.244 ms (64 rows)Third one:
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------ Limit (cost=296971.57..296971.62 rows=20 width=594) (actual time=0.034..0.034 rows=0 loops=1) -> Sort (cost=296971.57..296971.79 rows=89 width=594) (actual time=0.032..0.032 rows=0 loops=1) Sort Key: projects.id Sort Method: quicksort Memory: 25kB -> Hash Join (cost=293898.78..296969.20 rows=89 width=594) (actual time=0.026..0.026 rows=0 loops=1) Hash Cond: (projects_1.id = projects.id) -> HashAggregate (cost=290820.16..292112.93 rows=129277 width=4) (never executed) Group Key: projects_1.id -> Append (cost=359.20..290496.97 rows=129277 width=4) (never executed) -> Bitmap Heap Scan on projects projects_1 (cost=359.20..39103.89 rows=25355 width=4) (never executed) Recheck Cond: (((path)::text ~~* '%sub%'::text) OR ((name)::text ~~* '%sub%'::text) OR (description ~~* '%sub%'::text)) -> BitmapOr (cost=359.20..359.20 rows=25358 width=0) (never executed) -> Bitmap Index Scan on index_projects_on_path_trigram (cost=0.00..241.61 rows=25014 width=0) (never executed) Index Cond: ((path)::text ~~* '%sub%'::text) -> Bitmap Index Scan on index_projects_on_name_trigram (cost=0.00..37.86 rows=248 width=0) (never executed) Index Cond: ((name)::text ~~* '%sub%'::text) -> Bitmap Index Scan on index_projects_on_description_trigram (cost=0.00..60.72 rows=96 width=0) (never executed) Index Cond: (description ~~* '%sub%'::text) -> Hash Join (cost=45904.50..250100.31 rows=103922 width=4) (never executed) Hash Cond: (projects_2.namespace_id = namespaces.id) -> Seq Scan on projects projects_2 (cost=0.00..193413.79 rows=2598079 width=8) (never executed) -> Hash (cost=45113.30..45113.30 rows=63296 width=4) (never executed) -> Bitmap Heap Scan on namespaces (cost=546.55..45113.30 rows=63296 width=4) (never executed) Recheck Cond: ((name)::text ~~* '%sub%'::text) -> Bitmap Index Scan on index_namespaces_on_name_trigram (cost=0.00..530.72 rows=63296 width=0) (never executed) Index Cond: ((name)::text ~~* '%sub%'::text) -> Hash (cost=3076.41..3076.41 rows=177 width=594) (actual time=0.017..0.017 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Nested Loop (cost=1839.76..3076.41 rows=177 width=594) (actual time=0.017..0.017 rows=0 loops=1) -> HashAggregate (cost=1839.33..1839.44 rows=11 width=4) (actual time=0.016..0.016 rows=0 loops=1) Group Key: namespaces_1.id -> Nested Loop Semi Join (cost=1790.37..1839.19 rows=11 width=4) (actual time=0.015..0.015 rows=0 loops=1) CTE base_and_descendants -> Recursive Union (cost=0.43..1789.94 rows=11 width=312) (actual time=0.013..0.013 rows=0 loops=1) -> Index Scan using namespaces_pkey on namespaces namespaces_3 (cost=0.43..4.45 rows=1 width=312) (actual time=0.011..0.011 rows=0 loops= 1) Index Cond: (id = 2) Filter: ((type)::text = 'Group'::text) Rows Removed by Filter: 1 -> Nested Loop (cost=0.43..178.53 rows=1 width=312) (actual time=0.002..0.002 rows=0 loops=1) -> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.002 rows=0 loops=1) -> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_4 (cost=0.43..17.82 rows=1 width=312) (never exec uted) Index Cond: (parent_id = base_and_descendants.id) Filter: ((type)::text = 'Group'::text) -> CTE Scan on base_and_descendants namespaces_1 (cost=0.00..0.22 rows=11 width=4) (actual time=0.015..0.015 rows=0 loops=1) -> Index Scan using namespaces_pkey on namespaces namespaces_2 (cost=0.43..4.45 rows=1 width=4) (never executed) Index Cond: (id = namespaces_1.id) Filter: ((type)::text = 'Group'::text) -> Index Scan using index_projects_on_namespace_id on projects (cost=0.43..112.29 rows=16 width=594) (never executed) Index Cond: (namespace_id = namespaces_1.id) Filter: ((NOT archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[])))) SubPlan 1 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..4.45 rows=1 width=0) ( never executed) Index Cond: ((user_id = 1) 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.4 3..5.73 rows=74 width=4) (never executed) Index Cond: (user_id = 1) Heap Fetches: 0 Planning time: 6.445 ms Execution time: 0.966 ms (60 rows)added requires production access label
changed milestone to %WoW ending 2017-10-03
@yorickpeterse WDYT about these? They're related to https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/14055
I don't think there are any matches for the specific queries here, should we generate them? We're never fetching more than 20 rows though.
@reprazent These plans are fairly useless because large portions of them are never executed. The best way to test these would be to make sure there's enough data so all parts of the plans are executed.
changed milestone to %WoW
Please register or sign in to reply