Improve one of the slowest (on GitLab.com) queries: SELECT FROM members .. (WITH RECURSIVE "base_and_ancestors" AS ...)
Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.
This query group is in Top-5 of the slowest ones on GitLab.com by total_time according to pg_stat_statements:
(reformatted)
SELECT "members".*
FROM "members"
WHERE
"members"."type" IN (?)
AND "members"."source_type" = ?=
AND (members.id IN (
SELECT "members"."id"
FROM "members"
WHERE
"members"."type" IN (?)
AND "members"."source_id" = ?
AND "members"."source_type" = ?
AND "members"."type" IN (?)
AND "members"."requested_at" IS NULL
) OR members.id IN (
SELECT "members"."id"
FROM "members"
WHERE
"members"."type" IN (?)
AND "members"."source_type" = ?
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN (
WITH RECURSIVE "base_and_ancestors" AS (
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" IN (?) AND "namespaces"."id" = ?
UNION
SELECT "namespaces".*
FROM "namespaces", "base_and_ancestors"
WHERE "namespaces"."type" IN (?) AND "namespaces"."id" = "base_and_ancestors"."parent_id"
)
SELECT "id"
FROM "base_and_ancestors" AS "namespaces"
) AND ("members"."user_id" NOT IN (
SELECT "users"."id"
FROM "users"
INNER JOIN "members" ON "users"."id" = "members"."user_id"
WHERE
"members"."source_type" = ?
AND "members"."source_id" = ?
AND "members"."source_type" = ?
AND "members"."type" IN (?)
AND "members"."requested_at" IS NULL
)
)
)
) AND ("members"."user_id" NOT IN (
SELECT "members"."user_id"
FROM "members"
WHERE
"members"."type" IN (?)
AND "members"."source_id" = ?
AND "members"."source_type" = ?
AND "members"."type" IN (?)
AND "members"."requested_at" IS NULL
AND ("members"."user_id" IS NOT NULL)
)
)
;Example registered in logs:
Apr 12 14:41:11 patroni-04-db-gprd postgres[1262]: [22-1] 2019-04-12 14:41:11 GMT [1262]: [11-1] LOG: duration: 3427.163 ms execute <unnamed>: SELECT "members".* FROM "members" WHERE "members"."type" IN ('GroupMember') AND "members"."source_type" = 'Namespace' AND (members.id IN (SELECT "members"."id" FROM "members" WHERE "members"."type" IN ('GroupMember') AND "members"."source_id" = 4280570 AND "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."requested_at" IS NULL) OR members.id IN (SELECT "members"."id" FROM "members" WHERE "members"."type" IN ('GroupMember') AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" IN (WITH RECURSIVE "base_and_ancestors" AS (SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."id" = 2365196
Apr 12 14:41:11 patroni-04-db-gprd postgres[1262]: [22-2] UNION
Apr 12 14:41:11 patroni-04-db-gprd postgres[1262]: [22-3] SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."id" = "base_and_ancestors"."parent_id") SELECT "id" FROM "base_and_ancestors" AS "namespaces") AND ("members"."user_id" NOT IN (SELECT "users"."id" FROM "users" INNER JOIN "members" ON "users"."id" = "members"."user_id" WHERE "members"."source_type" = 'Namespace' AND "members"."source_id" = 4280570 AND "members"."source_type" = 'Namespace' AND "members"."type" IN ('GroupMember') AND "members"."requested_at" IS NULL)))) AND ("members"."user_id" NOT IN (SELECT "members"."user_id" FROM "members" WHERE "members"."type" IN ('ProjectMember') AND "members"."source_id" = 10017127 AND "members"."source_type" = 'Project' AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL AND ("members"."user_id" IS NOT NULL)))Query group profile (see the full report made on 2019-04-09):
| # | Calls | ▼ Total time | Rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | blk_read_time | blk_write_time | kcache_reads | kcache_writes | kcache_user_time_ms | kcache_system_time | Query |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 3 | 327 0.05/sec 1.00/call 0.00% |
1,315,927.27 ms 207ms/sec 4.024s/call 4.25% |
123,678 19.51/sec 378.22/call 0.03% |
597,272,881 blks 94.22K blks/sec 1.83M blks/call 5.12% |
0 blks 0.00 blks/sec 0.00 blks/call 0.00% |
190 blks 0.03 blks/sec 0.58 blks/call 0.00% |
0 blks 0.00 blks/sec 0.00 blks/call 0.00% |
0.00 ms 0s/sec 0s/call 0.00% |
0.00 ms 0s/sec 0s/call 0.00% |
0.00 bytes 0.00 bytes/sec 0.00 bytes/call 0.00% |
0.00 bytes 0.00 bytes/sec 0.00 bytes/call 0.00% |
0.00 ms 0s/sec 0s/call 0.00% |
0.00 ms 0s/sec 0s/call 0.00% |
SELECT "members".* FROM "members" WHERE "members"."type" IN (?) AND "members"."source_type" = ? AND (members.id IN (SELECT "members"."id" FROM "members" WHERE "members"."type" IN (?) AND "members"."source_id" = ? AND "members"."source_type" = ? AND "members"."type" IN (?) AND "members"."requested_at" IS NULL) OR members.id IN (SELECT "members"."id" FROM "members" WHERE "members"."type" IN (?) AND "members"."source_type" = ? AND "members"."requested_at" IS NULL AND "members"."source_id" IN (WITH RECURSIVE "base_and_ancestors" AS (SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN (?) AND "namespaces"."id" = ? UNION SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" IN (?) AND "namespaces"."id" = "base_and_ancestors"."parent_id") SELECT "id" FROM "base_and_ancestors" AS "namespaces") AND ("members"."user_id" NOT IN (SELECT "users"."id" FROM "users" INNER JOIN "members" ON "users"."id" = "members"."user_id" WHERE "members"."source_type" = ? AND "members"."source_id" = ? AND "members"."source_type" = ? AND "members"."type" IN (?) AND "members"."requested_at" IS NULL)))) AND ("members"."user_id" NOT IN (SELECT "members"."user_id" FROM "members" WHERE "members"."type" IN (?) AND "members"."source_id" = ? AND "members"."source_type" = ? AND "members"."type" IN (?) AND "members"."requested_at" IS NULL AND ("members"."user_id" IS NOT NULL))) |
Execution plan:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_members_on_source_id_and_source_type on members (cost=580.52..372484.04 rows=147081 width=168) (actual time=2055.689..4415.389 rows=3 loops=1)
Index Cond: ((source_type)::text = 'Namespace'::text)
Filter: ((NOT (hashed SubPlan 5)) AND ((type)::text = 'GroupMember'::text) AND ((hashed SubPlan 1) OR (hashed SubPlan 4)))
Rows Removed by Filter: 2055358
Buffers: shared hit=1641753
SubPlan 5
-> Index Scan using index_members_on_source_id_and_source_type on members members_4 (cost=0.43..5.46 rows=3 width=4) (actual time=0.083..0.105 rows=3 loops=1)
Index Cond: ((source_id = 10017127) AND ((source_type)::text = 'Project'::text))
Filter: ((requested_at IS NULL) AND (user_id IS NOT NULL) AND ((type)::text = 'ProjectMember'::text))
Buffers: shared hit=5
SubPlan 1
-> Index Scan using index_members_on_source_id_and_source_type on members members_1 (cost=0.43..3.46 rows=1 width=4) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: ((source_id = 4280570) AND ((source_type)::text = 'Namespace'::text))
Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text))
Buffers: shared hit=3
SubPlan 4
-> Nested Loop (cost=360.16..571.15 rows=2 width=4) (actual time=0.234..0.321 rows=3 loops=1)
Buffers: shared hit=20
-> HashAggregate (cost=352.81..353.01 rows=21 width=4) (actual time=0.146..0.146 rows=2 loops=1)
Group Key: namespaces_2.id
Buffers: shared hit=8
-> CTE Scan on base_and_ancestors namespaces_2 (cost=352.12..352.54 rows=21 width=4) (actual time=0.103..0.145 rows=2 loops=1)
Buffers: shared hit=8
CTE base_and_ancestors
-> Recursive Union (cost=0.43..352.12 rows=21 width=295) (actual time=0.102..0.143 rows=2 loops=1)
Buffers: shared hit=8
-> Index Scan using namespaces_pkey on namespaces (cost=0.43..3.45 rows=1 width=295) (actual time=0.091..0.091 rows=1 loops=1)
Index Cond: (id = 2365196)
Filter: ((type)::text = 'Group'::text)
Buffers: shared hit=4
-> Nested Loop (cost=0.43..34.83 rows=2 width=295) (actual time=0.018..0.018 rows=0 loops=2)
Buffers: shared hit=4
-> WorkTable Scan on base_and_ancestors (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.001 rows=1 loops=2)
-> Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=295) (actual time=0.015..0.015 rows=0 loops=2)
Index Cond: (id = base_and_ancestors.parent_id)
Filter: ((type)::text = 'Group'::text)
Buffers: shared hit=4
-> Index Scan using index_members_on_source_id_and_source_type on members members_3 (cost=7.35..10.38 rows=1 width=8) (actual time=0.072..0.085 rows=2 loops=2)
Index Cond: ((source_id = namespaces_2.id) AND ((source_type)::text = 'Namespace'::text))
Filter: ((requested_at IS NULL) AND (NOT (hashed SubPlan 2)) AND ((type)::text = 'GroupMember'::text))
Buffers: shared hit=12
SubPlan 2
-> Nested Loop (cost=0.86..6.92 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
Buffers: shared hit=3
-> Index Scan using index_members_on_source_id_and_source_type on members members_2 (cost=0.43..3.46 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((source_id = 4280570) AND ((source_type)::text = 'Namespace'::text))
Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text))
Buffers: shared hit=3
-> Index Only Scan using users_pkey on users (cost=0.43..3.45 rows=1 width=4) (never executed)
Index Cond: (id = members_2.user_id)
Heap Fetches: 0
Planning time: 8.151 ms
Execution time: 4415.721 ms
(53 rows)Edited by 🤖 GitLab Bot 🤖