> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# List Postgres slow query patterns
> **This endpoint is in beta.** API contract is stable, and no breaking changes are expected in the future.
Returns aggregate metrics for the slowest query patterns observed on a Postgres service during the given time window. Use this to discover which queries dominate total execution time, CPU, I/O, or WAL generation.
Beta
**This endpoint is in beta.** API contract is stable, and no breaking changes are expected in the future.
Returns aggregate metrics for the slowest query patterns observed on a Postgres service during the given time window. Use this to discover which queries dominate total execution time, CPU, I/O, or WAL generation.
## OpenAPI
````yaml _specs/cloud-openapi.json GET /v1/organizations/{organizationId}/postgres/{postgresId}/slowQueryPatterns
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:
get:
tags:
- Postgres
summary: List Postgres slow query patterns
description: >-
**This endpoint is in beta.** API contract is stable, and no breaking
changes are expected in the future.
Returns aggregate
metrics for the slowest query patterns observed on a Postgres service
during the given time window. Use this to discover which queries
dominate total execution time, CPU, I/O, or WAL generation.
operationId: slowQueryPatternsGetList
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: query
name: from_date
description: Inclusive start of the time window (RFC 3339 date-time).
schema:
type: string
format: date-time
required: true
- in: query
name: to_date
description: Exclusive end of the time window (RFC 3339 date-time).
schema:
type: string
format: date-time
required: true
- in: query
name: db_name
description: Database name filter.
schema:
type: string
- in: query
name: db_user
description: Database user filter.
schema:
type: string
- in: query
name: db_operation
description: >-
Database operation filter (for example, SELECT, INSERT, UPDATE,
DELETE, UTILITY).
schema:
type: string
- in: query
name: app
description: Application name filter.
schema:
type: string
- in: query
name: sort_by
description: Field to sort results by.
schema:
type: string
enum:
- total_duration
- avg_duration
- call_count
- total_blks_read
- total_cpu_time
- error_count
- max_duration
- p50_duration
- p95_duration
- p99_duration
- total_rows
- total_shared_blks_hit
- total_wal_bytes
default: total_duration
- in: query
name: sort_order
description: Sort order. One of `asc` or `desc`.
schema:
type: string
enum:
- asc
- desc
default: desc
- in: query
name: limit
description: Maximum number of results to return.
schema:
type: integer
minimum: 1
maximum: 500
default: 20
- in: query
name: offset
description: Number of results to skip before returning.
schema:
type: integer
minimum: 0
default: 0
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:
type: array
items:
$ref: '#/components/schemas/PostgresSlowQueryPattern'
'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:
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
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
````