Query Performance Investigation - Query ID -6386890822646777000 (users and project_authorizations tables)

Description

This query is frequently reported as top 15 in total time taken during the monitoring period. The queries were taken from this report https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/snippets/2090600#postgres-checkup_K003


SELECT "users".*
FROM "users" INNER JOIN "project_authorizations" ON "users"."id" = "project_authorizations"."user_id"
WHERE "project_authorizations"."project_id" = $1 
/*application:web,correlation_id:1UUb7KNTS51*/

Requested Data points

This is the first query in the table https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/snippets/2090600#k003-top-15-queries-by-total_time

  • Calls 97,179 50.89/sec

image

https://log.gprd.gitlab.net/goto/5a05bdd7b2c0c30e801ded04c328b40a

Sample json

{
  "_index": "pubsub-postgres-inf-gprd-000322",
  "_type": "_doc",
  "_id": "z2pZUXgB-4Ga6iRB9iDw",
  "_version": 1,
  "_score": null,
  "_source": {
    "@timestamp": "2021-03-20T20:35:52.602Z",
    "json": {
      "sql_state_code": "00000",
      "transaction_id": "0",
      "application": "web",
      "environment": "gprd",
      "tier": "db",
      "session_id": "605657ef.cefc",
      "session_line_num": "532",
      "error_severity": "LOG",
      "database_name": "gitlabhq_production",
      "shard": "default",
      "application_name": "",
      "endpoint_id": "/api/:version/projects/:id/merge_requests/:merge_request_iid/changes",
      "stage": "main",
      "fqdn": "patroni-04-db-gprd.c.gitlab-production.internal",
      "sql": "/*application:web,correlation_id:01F18NJ1V1V3AKQKE3PVKATCN4,endpoint_id:/api/:version/projects/:id/merge_requests/:merge_request_iid/changes*/ SELECT \"users\".* FROM \"users\" INNER JOIN \"project_authorizations\" ON \"users\".\"id\" = \"project_authorizations\".\"user_id\" WHERE \"project_authorizations\".\"project_id\" = $1",
      "process_id": "52988",
      "session_start_time": "2021-03-20 20:15:43 GMT",
      "correlation_id": "01F18NJ1V1V3AKQKE3PVKATCN4",
      "connection_from": "127.0.0.1:46140",
      "tag": "postgres.postgres_csv",
      "hostname": "patroni-04-db-gprd",
      "type": "patroni",
      "virtual_transaction_id": "116/29671860",
      "duration_s": "1.1548889999999998",
      "time": "2021-03-20T20:34:53.630Z",
      "command_tag": "SELECT",
      "user_name": "gitlab"
    },
    "ecs": {
      "version": "1.5.0"
    },
    "host": {
      "name": "pubsubbeat-pubsub-postgres-inf-gprd-b4b494569-xdnlq"
    },
    "type": "pubsubbeat-pubsub-postgres-inf-gprd-b4b494569-xdnlq",
    "message_id": "2111473107734474",
    "publish_time": "2021-03-20T20:35:52.565Z"
  },
  "fields": {
    "publish_time": [
      "2021-03-20T20:35:52.565Z"
    ],
    "@timestamp": [
      "2021-03-20T20:35:52.602Z"
    ],
    "json.time": [
      "2021-03-20T20:34:53.630Z"
    ]
  },
  "highlight": {
    "json.sql": [
      "/*application:web,correlation_id:01F18NJ1V1V3AKQKE3PVKATCN4,endpoint_id:/api/:version/projects/:id/merge_requests/:merge_request_iid/changes*/ @kibana-highlighted-field@SELECT@/kibana-highlighted-field@ \"@kibana-highlighted-field@users@/kibana-highlighted-field@\".* @kibana-highlighted-field@FROM@/kibana-highlighted-field@ \"@kibana-highlighted-field@users@/kibana-highlighted-field@\" @kibana-highlighted-field@INNER@/kibana-highlighted-field@ @kibana-highlighted-field@JOIN@/kibana-highlighted-field@ \"@kibana-highlighted-field@project_authorizations@/kibana-highlighted-field@\" @kibana-highlighted-field@ON@/kibana-highlighted-field@ \"@kibana-highlighted-field@users@/kibana-highlighted-field@\".\"@kibana-highlighted-field@id@/kibana-highlighted-field@\" = \"@kibana-highlighted-field@project_authorizations@/kibana-highlighted-field@\".\"@kibana-highlighted-field@user_id@/kibana-highlighted-field@\" @kibana-highlighted-field@WHERE@/kibana-highlighted-field@ \"@kibana-highlighted-field@project_authorizations@/kibana-highlighted-field@\".\"@kibana-highlighted-field@project_id@/kibana-highlighted-field@\" = $@kibana-highlighted-field@1@/kibana-highlighted-field@"
    ]
  },
  "sort": [
    1616272493630
  ]
}