Skip to content
Snippets Groups Projects

Slow query analysis

Reviewing all queries run recently which took longer, on average, than 500ms (or so), turned up interesting results. This analysis was started using pg_stat_statements_history with a query along these lines:

select queryid,substring(query,1,200),sum(calls),sum(total_time) as total_time,min(min_time) as min_time,max(max_time) as max_time,avg(mean_time) as mean_time,sum(rows) as rows from pg_stat_statements_history where log_time >= '2017-05-11 00:00:00' group by queryid,substring(query,1,200) order by mean_time desc;

While pg_stat_statements does try to combine queries together which are structurally the same, this is not always possible. Working top-down from the results of the above query, the next step was to search for similarly structured queries and then extract out the queryids for them, to provide a more comprehensive analysis. The queryids for each statement are included in the analysis. Once the query ids had been identified, a subsequent query for each was done to extract out the query runtime statistics for that kind of query, like so:

select sum(calls) as calls,sum(total_time) as total_time,min(min_time) as min_time,max(max_time) as max_time,avg(mean_time) as mean_time,sum(rows) as rows from pg_stat_statements_history where log_time >= '2017-05-11 00:00:00' and queryid in ( 1966453112 );

Finally, the results were then sorted by "total_time." Queries which appeared to be run by hand were excluded.

select project_id max level

  • SELECT project_id, MAX(access_level) AS access_level FROM (SELECT ? AS user_id, projects.id AS project_id, ? AS access_level FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id"

  • queryids: 609277962

  • calls | 19300

  • total_time | 40667541.783

  • min_time | 1583.079

  • max_time | 19931.487

  • mean_time | 2063.86555260373

  • rows | 778676

  • "routes" in this query is related to the authorization code, likely addressed by those fixes

  • https://gitlab.com/gitlab-org/gitlab-ce/issues/30472

count ci_builds project

  • SELECT COUNT(*) FROM "ci_builds" WHERE "ci_builds"."type" IN (?) AND "ci_builds"."project_id" = ? AND "ci_builds"."status" IN (?, ?)

  • queryids: 2432173361

  • calls | 167134

  • total_time | 16495143.862

  • min_time | 0.013

  • max_time | 1833.06

  • mean_time | 91.1983079182215

  • rows | 167134

  • Recommendation for an additional partial index to address this query

  • https://gitlab.com/gitlab-org/gitlab-ce/issues/31792

events table query

  • SELECT "events".* FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND "events"."project_id" IN (?, ?, ...) ORDER BY "events"."id" DESC LIMIT ? OFFSET ?

  • queryids: 2654195867, 3666841887, 3708492907, 633539946, 173960073, 3957233608, 1726684837, 67951902, 352833001, 2662892856, 1599873525, 974218383, 762634027, 1647035797, 2865689452, 826189844, 1944174585, 3828512978, 678721649, 1547096320, 1013555886, 1154068566, 194445460, 2198615537, 3400905100, 2432971058, 1415764130, 1151997171, 925174174, 761323629, 3452072753, 2159315416, 3821844675, 2000798799, 3128995101, 1601871360, 2106652730, 3628253965, 3901952046, 3395412522, 1962630535, 4234299266, 3466431986

  • calls | 9282

  • total_time | 10352695.644

  • min_time | 0.028

  • max_time | 43630.359

  • mean_time | 2124.46774552101

  • rows | 150381

  • https://gitlab.com/gitlab-org/gitlab-ce/issues/31806

select routes.path

  • SELECT routes.path FROM "namespaces" INNER JOIN "routes" ON "routes"."source_id" = "namespaces"."id" AND "routes"."source_type" = ? INNER JOIN routes r2 ON routes.path LIKE CONCAT(r2.path, ?)

  • queryids: 1522119898, 1522119898

  • calls | 1566

  • total_time | 9498185.367

  • min_time | 1135.895

  • max_time | 51773.967

  • mean_time | 4820.60045682477

  • rows | 48113

  • "routes" and this query is related to the authorization code, likely addressed by those fixes

  • https://gitlab.com/gitlab-org/gitlab-ce/issues/30472

select ci_build status

  • SELECT projects.namespace_id, ci_builds.status, projects.shared_runners_enabled, COUNT(*) AS count FROM ci_builds JOIN projects ON projects.id = ci_builds.project_id WHERE ci_builds.

  • queryids: 1976707676

  • calls | 3057

  • total_time | 7012384.009

  • min_time | 1765.236

  • max_time | 9791.67

  • mean_time | 2293.98419203911

  • rows | 4096266

  • The costing around this query is such that a sequential scan of projects into a hash table to join against an index scan across ci_builds is cheapest. However, a nested-loop join using two index scans ends up being faster by ~10x. Interestingly, the sequential scan across the whole table hits 136,621 pages while the index scan hits 88,311 or over half of that number. Possibly the performance improvement is due to the same pages being used in the index scan over and over and therefore being more efficient. This may be related to the hosting platform itself.

  • Changing random_page_cost to 2, as discussed elsewhere, resulted in PostgreSQL choosing the nested loop join and the query time dropping to ~200ms from ~2200ms, or a savings of about 2s per query.

  • A possible alternative/additional optimization of the query would be to build an index on projects.id, namespace_id, shared_runners_enabled, which would allow an index-only scan of that index to be used with this query. Of course, there would be a cost to having such an index in terms of write performance on projects.

  • No additional issue created as the recommendation to reduce random_page_cost is likely sufficient for this query.

  • While this query is much better thanks the reduction of random_page_cost, it's still showing near the top of the slow queries analysis.

  • The index contemplated in https://gitlab.com/gitlab-org/gitlab-ce/issues/31792 across ci_builds (project_id, status) would likely also improve the performance of this query.

delete from ci_pipelines

  • DELETE FROM "ci_pipelines" WHERE "ci_pipelines"."id" = ? AND "ci_pipelines"."lock_version" IS NULL

  • queryids: 1797272874, 1992052330

  • calls | 1047

  • total_time | 6182091.701

  • min_time | 5098.052

  • max_time | 9571.768

  • mean_time | 6003.28965095994

  • rows | 1047

  • There are foreign keys referring to this table which have to be maintained. Unfortunately, not all of the referring columns are indexed, resulting in sequential scans being required across those tables. Indexes should be added to these tables to address this.

  • Issues for new indexes to be created to address this are:

  • https://gitlab.com/gitlab-org/gitlab-ce/issues/31793

  • https://gitlab.com/gitlab-org/gitlab-ce/issues/31792

count projects

  • SELECT COUNT(*) FROM "projects" WHERE "projects"."pending_delete" = ? AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_auth

  • queryids: 2074964368, 859949810, 2651495786

  • calls | 1518

  • total_time | 4736743.351

  • min_time | 26.999

  • max_time | 9431.506

  • mean_time | 2893.39891249885

  • rows | 1518

  • https://gitlab.com/gitlab-com/infrastructure/issues/1789

count users

  • SELECT COUNT(*) FROM "users" WHERE ("users"."state" IN (?)) AND ("users"."ghost" = ? OR "users"."ghost" IS NULL) AND ("users"."support_bot" = ? OR "users"."support_bot" IS NULL) AND ((id IN (SELECT

  • queryids: 3020355617, 2880503409, 3735901189, 3721880404, 4094467260, 1325480639, 242916844, 3535554324, 3709624550, 2478899854, 2940796834, 3117711339, 1387205427, 1278016840, 3776761666, 239377166, 858035358, 3270215177, 2665565636, 826886427, 292149370, 430358738, 1863700960, 199306181, 90518094, 4238114465, 1265660883, 3270215177

  • calls | 3846

  • total_time | 4379560.319

  • min_time | 0.059

  • max_time | 2437.206

  • mean_time | 1197.46135810218

  • rows | 3846

  • https://gitlab.com/gitlab-com/infrastructure/issues/1792

update tags

  • UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", ?) + ? WHERE "tags"."id" = ?

  • queryids: 2218715947

  • calls | 26313

  • total_time | 2173756.314

  • min_time | 0.013

  • max_time | 59007.686

  • mean_time | 77.2590158320857

  • rows | 26313

  • The time required for this query appears to vary wildly even though the query itself is reasonably simple. My suspicion, which can be confirmed by enabling log_lock_waits in postgresql.conf (which an issue exists for), is that multiple application processes are updating this table concurrently and are trying to update the same row, leading to locking.

  • Further, this table has a different count for about 7% of the tags (2237 out of 33975 tags) than what is in the taggings table.

  • There are a few different possible approaches to deal with this:

  • Does updating this have to take place in the same, evidently long-running, transaction as other operations? The delay due to locking is almost certainly not due to this one query but due to other operations happening in the same transaction which means that the lock is held on the row that is trying to be acquired by another process. Updating this count in an independent, short-lived, transaction would reduce the length of time a given lock is held.

  • Does this table have to be accurate immediately, or would batch updating it from the "taggings" table be acceptable? If the contents are allowed to "lag" a bit, then updating it periodically, or perhaps through a materialized view, would avoid the locking issue. If the count must be accurate then another approach is to have two tables- a "base" table which is updated periodically and has the running total and a table that new entries are inserted into, and queries to get the count would pull the base count and then add it to the count from the "recently added" table. If the tag counts are only needed on a per-tag basis, dropping the "taggings_count" column in favor of calculating the count on-the-fly from the "taggings" table would be another approach which would lead to most tags being returned very quickly, though the worst case there appears to be still on the order of 1.5s.

select events, BI query

  • SELECT "events"."id" AS t0_r0, "events"."target_type" AS t0_r1, "events"."target_id" AS t0_r2, "events"."title" AS t0_r3, "events"."data" AS t0_r4, "events"."project_id" AS t0_r5, "events"."created_a

  • queryids: 2969659987, 424370033

  • calls | 1486

  • total_time | 1876142.573

  • min_time | 0.042

  • max_time | 2655.388

  • mean_time | 1225.66207160726

  • rows | 28753

  • https://gitlab.com/gitlab-com/infrastructure/issues/1793

select labels: https://gitlab.com/gitlab-org/gitlab-ce/issues/33217

  • SELECT "labels".* FROM "labels" WHERE "labels"."template" = ? ORDER BY "labels"."title" ASC

  • queryids: 3507093519, 1661352576

  • calls | 5305

  • total_time | 1533226.873

  • min_time | 229.975

  • max_time | 702.836

  • mean_time | 288.165583057846

  • rows | 0

  • https://gitlab.com/gitlab-org/gitlab-ce/issues/31815

  • This query never returns any records.

ci_builds select

  • SELECT "ci_builds".* FROM "ci_builds" INNER JOIN "projects" ON "projects"."id" = "ci_builds"."project_id" AND "projects"."pending_delete" = ? WHERE "ci_builds"."type" IN (?) AND

  • queryids: 2111516600, 4163606102

  • calls | 41

  • total_time | 952939.082

  • min_time | 22.182

  • max_time | 26136.086

  • mean_time | 21179.0348888889

  • rows | 632

  • https://gitlab.com/gitlab-com/infrastructure/issues/1829

select users id

  • SELECT "users"."id" FROM "users" INNER JOIN "identities" ON "identities"."user_id" = "users"."id" WHERE ("identities"."provider" = ? AND "identities"."extern_uid" = ?) ORDER BY "users"."id" DESC LIM

  • queryids: 2676200259, 4221210546

  • calls | 10481

  • total_time | 819793.082

  • min_time | 59.358

  • max_time | 161.454

  • mean_time | 78.408557401543

  • rows | 2933

  • https://gitlab.com/gitlab-org/gitlab-ce/issues/31808

select ci_builds artifact size

  • SELECT SUM("ci_builds"."artifacts_size") FROM "ci_builds" WHERE "ci_builds"."type" IN (?) AND "ci_builds"."project_id" = ?
  • queryids: 1847545236
  • calls | 16962
  • total_time | 652383.237999999
  • min_time | 0.009
  • max_time | 8954.372
  • mean_time | 91.0662767683441
  • rows | 16962

check users

  • SELECT ? AS one FROM "users" WHERE ("users"."public_email" = ? AND "users"."id" != ?) LIMIT ?

  • queryids: 1943885964

  • calls | 501

  • total_time | 467005.232

  • min_time | 721.819

  • max_time | 2185.375

  • mean_time | 929.278351893758

  • rows | 0

  • https://gitlab.com/gitlab-org/gitlab-ce/issues/32287

update users

  • UPDATE "users" SET last_activity_on = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN
  • queryids: 2406223287, 1787696863
  • calls | 164
  • total_time | 452585.276
  • min_time | 42.382
  • max_time | 15643.678
  • mean_time | 1450.99288957055
  • rows | 81581

count ci_builds

  • SELECT COUNT(*) AS count FROM ci_builds WHERE ci_builds.type = ? AND ci_builds.status = ? AND ci_builds.updated_at < NOW() - INTERVAL ?
  • queryids: 619468533
  • calls | 3057
  • total_time | 424520.156
  • min_time | 109.019
  • max_time | 512.255
  • mean_time | 138.847568622672
  • rows | 3057

check merge_requests

  • SELECT ? AS one FROM "merge_requests" INNER JOIN "label_links" ON "label_links"."target_id" = "merge_requests"."id" AND "label_links"."target_type" = ? INNER JOIN "labels" ON "labels"."id" = "label_l
  • queryids: 2620442434, 1712559941, 1539653591, 1993484287, 3586979360, 1755362042, 3609087964, 3372658950, 2055173473, 2998604767, 3127070443, 3414761357, 3796734611, 2127311105, 1459766381, 846851635, 1169298825, 2557982392, 3374348295, 3244809440, 2620442434, 1485440325, 734857978, 1995039524, 1531440203, 972274717, 305095366, 4115491502, 816143262
  • calls | 936
  • total_time | 415874.146
  • min_time | 0.028
  • max_time | 3490.204
  • mean_time | 413.439812451677
  • rows | 501

update ci_builds set user_id

  • UPDATE "ci_builds" SET "user_id" = ? WHERE "ci_builds"."type" IN (?) AND "ci_builds"."user_id" = ?
  • queryids: 953108396
  • calls | 34
  • total_time | 382860.549
  • min_time | 9886.322
  • max_time | 12625.002
  • mean_time | 11178.2843192308
  • rows | 0

count projects with nonamespace

  • SELECT COUNT(*) FROM projects LEFT JOIN namespaces ON projects.namespace_id = namespaces.id WHERE namespaces.id IS NULL;
  • queryids: 3284642542
  • calls | 49
  • total_time | 107461.809
  • min_time | 1753.029
  • max_time | 5982.769
  • mean_time | 2217.50106071429
  • rows | 49

select ci_builds

  • SELECT "ci_builds"."id" FROM "ci_builds" WHERE "ci_builds"."type" IN (?) AND (NOT (("ci_builds"."artifacts_file" = ? OR "ci_builds"."artifacts_file" IS NULL))) AND (artifacts_expire_at < ?)
  • queryids: 3035261384
  • calls | 14
  • total_time | 87908.24
  • min_time | 5630.412
  • max_time | 6737.279
  • mean_time | 6279.16
  • rows | 5862

issues count query

  • SELECT COUNT(*) FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" AND "projects"."pending_delete" = ? WHERE "issues"."deleted_at" IS NULL AND

  • queryids: 1261864557, 952021065, 3026635475, 468839062, 4146786054, 1035569799, 3690754225, 3272779413, 834924458, 1897860650, 2062583205, 2095434213, 3192337648, 2656327216, 886692208, 3083828397, 3485957808

  • calls | 858

  • total_time | 85309.585

  • min_time | 0.031

  • max_time | 43956.627

  • mean_time | 579.090747678761

  • rows | 858

  • Likely improved through authorization improvements

  • https://gitlab.com/gitlab-org/gitlab-ce/issues/30472

merge_requests count query

  • SELECT COUNT(*) FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" AND "projects"."pending_delete" = ? WHERE "merge_requests"."deleted_at" IS NULL
  • queryids: 1753659975, 1399070230, 3401856445, 3533976937, 1741553508, 3411543022
  • calls | 578
  • total_time | 78007
  • min_time | 2.199
  • max_time | 22467.769
  • mean_time | 1291.12509409347
  • rows | 578

count projects pending delete

  • SELECT COUNT(*) FROM projects WHERE pending_delete=?;
  • queryids: 3198252279
  • calls | 98
  • total_time | 65610.997
  • min_time | 0.207
  • max_time | 1489.399
  • mean_time | 667.076997619048
  • rows | 98

delete namespaces

  • DELETE FROM "namespaces" WHERE "namespaces"."type" IN (?) AND "namespaces"."id" = ?
  • DELETE FROM "namespaces" WHERE "namespaces"."id" = ?
  • queryids: 3069773253, 195435075
  • calls | 197
  • total_time | 55684.337
  • min_time | 221.951
  • max_time | 467.327
  • mean_time | 284.000937954801
  • rows | 197

select users

  • SELECT "users".* FROM "users" WHERE "users"."unlock_token" = ? ORDER BY "users"."id" DESC LIMIT ?
  • queryids: 546658772
  • calls | 132
  • total_time | 55405.564
  • min_time | 307.738
  • max_time | 600.085
  • mean_time | 425.361192016807
  • rows | 0

check identities

  • SELECT ? AS one FROM "identities" WHERE ("identities"."extern_uid" = ? AND "identities"."provider" = ?) LIMIT ?
  • queryids: 3720078897
  • calls | 796
  • total_time | 53973.085
  • min_time | 12.44
  • max_time | 124.583
  • mean_time | 67.4993958106915
  • rows | 8

select project namespaces

  • SELECT "projects"."id" FROM "projects" LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."deleted_at" IS NULL WHERE "projects"."pending_delete" = ? AND (p
  • queryids: 124873900, 1662645944
  • calls | 1356
  • total_time | 52870.732
  • min_time | 2.195
  • max_time | 12300.266
  • mean_time | 32.9093381727102
  • rows | 14447

count distinct member ids

  • SELECT COUNT(DISTINCT "members"."id") FROM "members" LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id" WHERE (members.id IN (SELECT "members"."id" FROM "members" WHERE "members"
  • queryids: 2245949800, 1195357893, 4027082637, 1363941746, 611777517
  • calls | 42
  • total_time | 33532.515
  • min_time | 0.045
  • max_time | 11337.765
  • mean_time | 1264.11867361111
  • rows | 42

select members

  • SELECT "members".* FROM "members" LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id" WHERE (members.id IN (SELECT "members"."id" FROM "members" WHERE "members"."source_type" = ? AND
  • queryids: 3998214265, 226123122, 2629429066, 2970188559, 2020800148
  • calls | 42
  • total_time | 33279.625
  • min_time | 0.078
  • max_time | 4504.336
  • mean_time | 1128.55844444444
  • rows | 189

count snippets

  • SELECT COUNT(*) FROM snippets WHERE type=?;
  • queryids: 20125131
  • calls | 98
  • total_time | 31807.837
  • min_time | 203.772
  • max_time | 507.9
  • mean_time | 324.704592857143
  • rows | 98

select keys id

  • SELECT "keys"."id" FROM "keys" WHERE "keys"."type" IN (?) AND "keys"."public" = ? ORDER BY "keys"."id" DESC
  • queryids: 704874081
  • calls | 81
  • total_time | 31729.151
  • min_time | 323.267
  • max_time | 829.297
  • mean_time | 393.029289892624
  • rows | 1215

count users simple

  • SELECT COUNT(*) FROM users ;
  • queryids: 3897489339, 3897489339
  • calls | 51
  • total_time | 21715.184
  • min_time | 340.49
  • max_time | 555.016
  • mean_time | 420.553440625
  • rows | 51

select projects jids

  • SELECT id, import_jid FROM "projects" WHERE "projects"."pending_delete" = ? AND ("projects"."import_status" IN (?)) AND ("projects"."import_jid" IS NOT NULL) ORDER BY "projects"."id" ASC LIMIT ?
  • queryids: 3309825537
  • calls | 17
  • total_time | 19551.404
  • min_time | 1036.143
  • max_time | 1428.544
  • mean_time | 1150.08258823529
  • rows | 67

select project ids

  • SELECT "projects"."id" FROM "projects" WHERE "projects"."pending_delete" = ? AND (last_repository_check_at < ?) ORDER BY last_repository_check_at ASC LIMIT ?
  • queryids: 1757518593
  • calls | 16
  • total_time | 18449.195
  • min_time | 1054.081
  • max_time | 1247.673
  • mean_time | 1153.0746875
  • rows | 49920

select keys

  • SELECT "keys".* FROM "keys" WHERE "keys"."type" IN (?) AND "keys"."public" = ? ORDER BY "keys"."id" DESC
  • queryids: 3755823578
  • calls | 40
  • total_time | 15852.343
  • min_time | 325.849
  • max_time | 939.651
  • mean_time | 388.873526041667
  • rows | 600

count issues projects

  • SELECT COUNT(count_column) FROM (SELECT ? AS count_column FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" AND "projects"."pending_delete" = ? WHERE "issues"."deleted_at
  • queryids: 2325123914, 120078333, 2908170205, 2761040355
  • calls | 283
  • total_time | 14801.858
  • min_time | 0.591
  • max_time | 13501.577
  • mean_time | 299.169575313545
  • rows | 283

count projects namespaces

  • SELECT COUNT(*) FROM "projects" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."deleted_at" IS NULL WHERE "projects"."pending_delete" = ? AND (namespace_id
  • queryids: 4256678383
  • calls | 6
  • total_time | 12679.654
  • min_time | 2048.347
  • max_time | 2183.988
  • mean_time | 2100.81608333333
  • rows | 6

insert trending_projects

  • INSERT INTO trending_projects (project_id)
  • queryids: 525510026
  • calls | 1
  • total_time | 10759.775
  • min_time | 10759.775
  • max_time | 10759.775
  • mean_time | 10759.775
  • rows | 100

select issues label

  • SELECT "issues".* FROM "issues" INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id" AND "label_links"."target_type" = ? INNER JOIN "labels" ON "labels"."id" = "label_links"."label_i
  • queryids: 3376979266
  • calls | 20
  • total_time | 8083.471
  • min_time | 0.539
  • max_time | 1186.513
  • mean_time | 346.493911764706
  • rows | 18

count events

  • SELECT COUNT(DISTINCT count_column) FROM (SELECT "events"."id" AS count_column FROM "events" LEFT OUTER JOIN "projects" ON "projects"."id" = "events"."project_id" AND "projects"."pending_delete" = ?
  • queryids: 3532511722
  • calls | 8
  • total_time | 7680.268
  • min_time | 0.044
  • max_time | 1215.075
  • mean_time | 1022.3177
  • rows | 8

count projects visibility level

  • SELECT COUNT(*) FROM "projects" WHERE "projects"."visibility_level" = ? AND "projects"."archived" = ?
  • queryids: 1952615082
  • calls | 7
  • total_time | 6099.441
  • min_time | 794.481
  • max_time | 988.858
  • mean_time | 871.25475
  • rows | 7

count issues lables

  • SELECT COUNT(count_column) FROM (SELECT ? AS count_column FROM "issues" INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id" AND "label_links"."target_type" = ? INNER JOIN "labels" O
  • queryids: 1423860795
  • calls | 17
  • total_time | 5390.584
  • min_time | 0.418
  • max_time | 748.743
  • mean_time | 317.093176470588
  • rows | 17

select issues project

  • SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" AND "projects"."pending_delete" = ? WHERE "issues"."deleted_at" IS NULL AND
  • queryids: 2099428344, 3342622282, 3910244770, 1226514554, 1401568261, 2784274565, 3201896709, 342492076
  • calls | 304
  • total_time | 5160.367
  • min_time | 0.564
  • max_time | 2923.691
  • mean_time | 65.0889344268901
  • rows | 4170

select projects

  • SELECT "projects".* FROM "projects" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."deleted_at" IS NULL WHERE "projects"."pending_delete" = ? AND (namespace
  • queryids: 1966453112
  • calls | 1
  • total_time | 4202.065
  • min_time | 4202.065
  • max_time | 4202.065
  • mean_time | 4202.065
  • rows | 10

delete events

  • DELETE FROM "events" WHERE ((id IN (SELECT id FROM (SELECT "events"."id" FROM "events" WHERE (created_at < ?) LIMIT ?) ids_to_remove)))
  • queryids: 3931336604
  • calls | 2
  • total_time | 3301.532
  • min_time | 1544.78
  • max_time | 1756.752
  • mean_time | 1650.766
  • rows | 20000

update issues

  • UPDATE "issues" SET position = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? ELSE position END WHERE
  • queryids: 1830880351
  • calls | 7
  • total_time | 3074.953
  • min_time | 0.838
  • max_time | 3067.518
  • mean_time | 1023.26625
  • rows | 49

select snippets

  • SELECT "snippets".* FROM "snippets" WHERE (snippets.id IN (SELECT "snippets"."id" FROM "snippets" WHERE "snippets"."visibility_level" IN (?, ?)
  • queryids: 1846066210, 2238086631
  • calls | 6
  • total_time | 3007.352
  • min_time | 411.777
  • max_time | 753.393
  • mean_time | 501.225333333333
  • rows | 51

select lfs_objects

  • SELECT "lfs_objects".* FROM "lfs_objects" LEFT JOIN lfs_objects_projects ON lfs_objects_projects.lfs_object_id = lfs_objects.id WHERE "lfs_objects_projects"."id" IS NULL
  • queryids: 1733297314
  • calls | 1
  • total_time | 2972.429
  • min_time | 2972.429
  • max_time | 2972.429
  • mean_time | 2972.429
  • rows | 1

count issues labels

  • SELECT COUNT(*) AS count_all, "issues"."id" AS issues_id FROM "issues" INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id" AND "label_links"."target_type" = ? INNER JOIN "labels" ON
  • queryids: 1488239209, 3794019832, 3953217434
  • calls | 4
  • total_time | 2915.669
  • min_time | 688.257
  • max_time | 782.677
  • mean_time | 728.91725
  • rows | 57

count snippets visibility

  • SELECT COUNT(*) FROM "snippets" WHERE (snippets.id IN (SELECT "snippets"."id" FROM "snippets" WHERE "snippets"."visibility_level" IN (?, ?)
  • queryids: 1861355156, 732887323
  • calls | 6
  • total_time | 2613.409
  • min_time | 405.065
  • max_time | 464.125
  • mean_time | 435.568166666667
  • rows | 6

count projects count_column

  • SELECT COUNT(count_column) FROM (SELECT ? AS count_column FROM "projects" WHERE "projects"."pending_delete" = ? AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authori
  • queryids: 999690877
  • calls | 2
  • total_time | 2359.542
  • min_time | 1174.577
  • max_time | 1184.965
  • mean_time | 1179.771
  • rows | 2

count issues state

  • SELECT COUNT(*) AS count_all, "issues"."state" AS issues_state FROM "issues" INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id" AND "label_links"."target_type" = ? INNER JOIN "label
  • queryids: 3438414827
  • calls | 3
  • total_time | 2151.642
  • min_time | 687.027
  • max_time | 748.983
  • mean_time | 725.15625
  • rows | 5

select projects namespaces

  • SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = ? AND (projects.id IN (SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project
  • queryids: 3766550127
  • calls | 1
  • total_time | 1377.576
  • min_time | 1377.576
  • max_time | 1377.576
  • mean_time | 1377.576
  • rows | 20

select projects mirror

  • SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = ? AND "projects"."mirror" = ? AND ("projects"."import_status" IN (?)) AND (mirror_last_update_at < ?) ORDER BY "projects"."id"
  • queryids: 3629645559
  • calls | 1
  • total_time | 1277.944
  • min_time | 1277.944
  • max_time | 1277.944
  • mean_time | 1277.944
  • rows | 4

select issues labels

  • SELECT "issues"."id", labels.title FROM "issues" INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id" AND "label_links"."target_type" = ? INNER JOIN "labels" ON "labels"."id" = "label
  • queryids: 1215481825
  • calls | 1
  • total_time | 770.933
  • min_time | 770.933
  • max_time | 770.933
  • mean_time | 770.933
  • rows | 42

select namespaces

  • SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."deleted_at" IS NULL AND (lower(path) = ? OR lower(name) = ?) ORDER BY "namespaces"."id" DESC LIMIT ?
  • queryids: 1904248845
  • calls | 1
  • total_time | 671.459
  • min_time | 671.459
  • max_time | 671.459
  • mean_time | 671.459
  • rows | 1

UPDATED - 2017-06-01

monitoring query (?)

  • SELECT COUNT(*) FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind in ('r','v','m');

  • queryids: 1119963169

  • calls | 45242015

  • total_time | 1436771.64000097

  • min_time | 0.007

  • max_time | 31.671

  • mean_time | 0.031

  • rows | 45242015

  • Issue for this query: https://gitlab.com/gitlab-com/infrastructure/issues/1930

Individual issues will be created for each of these, where there is action which can be taken to improve the query's performance (either through changes to the query structure or the addition of indexes or restructuring of the data into a more performant schema).

Edited by username-removed-1169139