> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-postgresql-tls-support.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Get a Postgres slow query pattern with recent executions
> **This endpoint is in beta.** API contract is stable, and no breaking changes are expected in the future.
Returns aggregate metrics for a single slow query pattern together with its most recent individual executions.
Beta
**This endpoint is in beta.** API contract is stable, and no breaking changes are expected in the future.
Returns aggregate metrics for a single slow query pattern together with its most recent individual executions.
## OpenAPI
````yaml _specs/cloud-openapi.json GET /v1/organizations/{organizationId}/postgres/{postgresId}/slowQueryPatterns/{queryId}
openapi: 3.1.2
info:
title: OpenAPI spec for ClickHouse Cloud
version: '1.0'
contact:
name: ClickHouse Support
url: >-
https://clickhouse.com/docs/en/cloud/manage/openapi?referrer=openapi-910487
email: support@clickhouse.com
servers:
- url: https://api.clickhouse.cloud
security:
- basicAuth: []
tags:
- name: Organization
- name: User management
- name: Billing
- name: Role Management
- name: Service
- name: Backup
- name: OpenAPI
- name: Prometheus
- name: ClickPipes
- name: ClickStack
- name: Postgres
paths:
/v1/organizations/{organizationId}/postgres/{postgresId}/slowQueryPatterns/{queryId}:
get:
tags:
- Postgres
summary: Get a Postgres slow query pattern with recent executions
description: >-
**This endpoint is in beta.** API contract is stable, and no breaking
changes are expected in the future.
Returns aggregate
metrics for a single slow query pattern together with its most recent
individual executions.
operationId: slowQueryPatternGet
parameters:
- in: path
name: organizationId
description: ID of the organization that owns the Postgres service.
required: true
schema:
type: string
format: uuid
- in: path
name: postgresId
description: ID of the requested Postgres service.
required: true
schema:
type: string
format: uuid
- in: path
name: queryId
description: Stable identifier for the query pattern.
required: true
schema:
type: string
- in: query
name: db_name
description: Database name filter.
schema:
type: string
required: true
- in: query
name: db_user
description: Database user filter.
schema:
type: string
required: true
- in: query
name: db_operation
description: >-
Database operation filter (for example, SELECT, INSERT, UPDATE,
DELETE, UTILITY).
schema:
type: string
required: true
- in: query
name: app
description: Application name filter.
schema:
type: string
- in: query
name: timestamp
description: Timestamp of a specific execution (RFC 3339).
schema:
type: string
format: date-time
responses:
'200':
description: Successful response
content:
application/json:
schema:
type: object
properties:
status:
type: number
description: HTTP status code.
example: 200
requestId:
type: string
description: Unique id assigned to every request. UUIDv4
format: uuid
result:
$ref: '#/components/schemas/PostgresSlowQueryPatternDetail'
'400':
description: >-
The request cannot be processed due to a client error. Please verify
your request parameters and try again.
content:
application/json:
schema:
type: object
properties:
status:
type: number
description: HTTP status code.
example: 400
error:
type: string
description: Detailed error description.
requestId:
type: string
description: Unique id assigned to every request. UUIDv4
format: uuid
'500':
description: >-
An internal server error has occurred. If this issue persists,
please contact ClickHouse Cloud support for assistance.
content:
application/json:
schema:
type: object
properties:
status:
type: integer
description: HTTP status code.
example: 500
error:
type: string
description: Detailed error description.
requestId:
type: string
description: Unique id assigned to every request. UUIDv4
format: uuid
components:
schemas:
PostgresSlowQueryPatternDetail:
properties:
aggregate:
$ref: '#/components/schemas/PostgresSlowQueryPattern'
recentExecutions:
type: array
description: Recent individual executions matching the pattern.
items:
$ref: '#/components/schemas/PostgresQueryExecution'
required:
- recentExecutions
PostgresSlowQueryPattern:
properties:
queryId:
description: Stable identifier for the query pattern (normalized SQL).
type: string
queryText:
description: Normalized query text with literals replaced by placeholders.
type: string
dbName:
description: Database the query ran in.
type: string
dbUser:
description: Database user that executed the query.
type: string
dbOperation:
description: >-
Top-level SQL operation type (for example, SELECT, INSERT, UPDATE,
DELETE, UTILITY).
type: string
app:
description: >-
Value of the Postgres `application_name` for executions matching
this pattern.
type: string
callCount:
description: Number of times the pattern executed in the window.
type: integer
errorCount:
description: Number of executions of the pattern that raised an error.
type: integer
totalDurationUs:
description: Total execution time across all calls, in microseconds.
type: integer
avgDurationUs:
description: Average execution time per call, in microseconds.
type: integer
maxDurationUs:
description: Maximum execution time of any call, in microseconds.
type: integer
p50DurationUs:
description: 50th percentile execution time, in microseconds.
type: integer
p95DurationUs:
description: 95th percentile execution time, in microseconds.
type: integer
p99DurationUs:
description: 99th percentile execution time, in microseconds.
type: integer
totalRows:
description: Total number of rows returned or affected across all calls.
type: integer
totalSharedBlksRead:
description: >-
Total shared buffer blocks read from disk (cache misses) across all
calls.
type: integer
totalSharedBlksHit:
description: Total shared buffer blocks hit (cache hits) across all calls.
type: integer
totalCpuTimeUs:
description: Total CPU time across all calls, in microseconds.
type: integer
totalWalBytes:
description: Total WAL (write-ahead log) bytes generated across all calls.
type: integer
required:
- queryId
- queryText
- dbName
- dbUser
- dbOperation
- app
- callCount
- errorCount
- totalDurationUs
- avgDurationUs
- maxDurationUs
- p50DurationUs
- p95DurationUs
- p99DurationUs
- totalRows
- totalSharedBlksRead
- totalSharedBlksHit
- totalCpuTimeUs
- totalWalBytes
PostgresQueryExecution:
properties:
timestamp:
description: Execution timestamp (RFC 3339).
type: string
format: date-time
queryId:
description: Stable identifier for the query pattern.
type: string
dbName:
description: Database the query ran in.
type: string
dbUser:
description: Database user that executed the query.
type: string
dbOperation:
description: Top-level SQL operation type.
type: string
app:
description: Value of the Postgres `application_name` for this execution.
type: string
queryText:
description: Normalized query text for this execution.
type: string
pid:
description: Postgres backend process ID that executed the query.
type: string
durationUs:
description: Execution duration in microseconds.
type: integer
rows:
description: Rows returned or affected.
type: integer
sharedBlksHit:
description: Shared buffer blocks hit.
type: integer
sharedBlksRead:
description: Shared buffer blocks read from disk.
type: integer
sharedBlksWritten:
description: Shared buffer blocks written.
type: integer
sharedBlksDirtied:
description: Shared buffer blocks dirtied.
type: integer
sharedBlkReadTimeUs:
description: Time spent reading shared blocks, in microseconds.
type: integer
sharedBlkWriteTimeUs:
description: Time spent writing shared blocks, in microseconds.
type: integer
localBlksHit:
description: Local buffer blocks hit (temp tables).
type: integer
localBlksRead:
description: Local buffer blocks read (temp tables).
type: integer
localBlksWritten:
description: Local buffer blocks written (temp tables).
type: integer
localBlksDirtied:
description: Local buffer blocks dirtied (temp tables).
type: integer
tempBlksRead:
description: Temp blocks read (spills to disk).
type: integer
tempBlksWritten:
description: Temp blocks written (spills to disk).
type: integer
tempBlkReadTimeUs:
description: Time spent reading temp blocks, in microseconds.
type: integer
tempBlkWriteTimeUs:
description: Time spent writing temp blocks, in microseconds.
type: integer
walRecords:
description: Number of WAL records produced.
type: integer
walBytes:
description: Number of WAL bytes produced.
type: integer
walFpi:
description: Number of WAL full-page images produced.
type: integer
cpuUserTimeUs:
description: CPU time spent in user mode, in microseconds.
type: integer
cpuSysTimeUs:
description: CPU time spent in kernel mode, in microseconds.
type: integer
jitFunctions:
description: Number of JIT-compiled functions.
type: integer
jitGenerationTimeUs:
description: JIT generation time, in microseconds.
type: integer
jitInliningTimeUs:
description: JIT inlining time, in microseconds.
type: integer
jitOptimizationTimeUs:
description: JIT optimization time, in microseconds.
type: integer
jitEmissionTimeUs:
description: JIT emission time, in microseconds.
type: integer
jitDeformTimeUs:
description: JIT deform time, in microseconds.
type: integer
parallelWorkersPlanned:
description: Parallel workers planned for this execution.
type: integer
parallelWorkersLaunched:
description: Parallel workers actually launched for this execution.
type: integer
errMessage:
description: Error message if the execution raised an error.
type: string
errSqlstate:
description: Postgres SQLSTATE code if the execution raised an error.
type: string
errElevel:
description: Postgres error severity level if the execution raised an error.
type: integer
serverRole:
description: >-
Role of the server that executed the query (for example, primary or
standby).
type: string
traceId:
description: OpenTelemetry trace ID associated with the execution.
type: string
spanId:
description: OpenTelemetry span ID associated with the execution.
type: string
required:
- timestamp
- queryId
- dbName
- dbUser
- dbOperation
- app
- queryText
- pid
- durationUs
- rows
- sharedBlksHit
- sharedBlksRead
- sharedBlksWritten
- sharedBlksDirtied
- sharedBlkReadTimeUs
- sharedBlkWriteTimeUs
- localBlksHit
- localBlksRead
- localBlksWritten
- localBlksDirtied
- tempBlksRead
- tempBlksWritten
- tempBlkReadTimeUs
- tempBlkWriteTimeUs
- walRecords
- walBytes
- walFpi
- cpuUserTimeUs
- cpuSysTimeUs
- jitFunctions
- jitGenerationTimeUs
- jitInliningTimeUs
- jitOptimizationTimeUs
- jitEmissionTimeUs
- jitDeformTimeUs
- parallelWorkersPlanned
- parallelWorkersLaunched
- serverRole
securitySchemes:
basicAuth:
type: http
scheme: basic
description: >-
Use key ID and key secret obtained in ClickHouse Cloud console:
https://clickhouse.com/docs/cloud/manage/openapi
````