Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

List Postgres slow query patterns

Beta
GET/v1/organizations/{organizationId}/postgres/{postgresId}/slowQueryPatterns
API playground

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.

Permission

The API key must have the control-plane:postgres-service:view permission.

Authorizations

Path parameters

  • organizationIdstringrequired

    ID of the organization that owns the Postgres service.

    format: uuid
  • postgresIdstringrequired

    ID of the requested Postgres service.

    format: uuid

Query parameters

  • from_datestringrequired

    Inclusive start of the time window (RFC 3339 date-time).

    format: date-time
  • to_datestringrequired

    Exclusive end of the time window (RFC 3339 date-time).

    format: date-time
  • db_nameoptionalstring

    Database name filter.

  • db_useroptionalstring

    Database user filter.

  • db_operationoptionalstring

    Database operation filter (for example, SELECT, INSERT, UPDATE, DELETE, UTILITY).

  • appoptionalstring

    Application name filter.

  • sort_byoptionaltotal_durationoravg_durationorcall_countortotal_blks_readortotal_cpu_timeorerror_count+7 more

    Field to sort results by.

    Default: "total_duration"
  • sort_orderoptionalascordesc

    Sort order. One of asc or desc.

    Default: "desc"
  • limitoptionalinteger

    Maximum number of results to return.

    maximum: 500, minimum: 1
    Default: 20
  • offsetoptionalinteger

    Number of results to skip before returning.

    minimum: 0
    Default: 0

Response

JSON

200

Successful response

JSON
  • statusoptionalnumber

    HTTP status code.

    Example: 200
  • requestIdoptionalstring

    Unique id assigned to every request. UUIDv4

    format: uuid
  • resultoptionalarray ofobject
    19 properties
    • queryIdstringrequired

      Stable identifier for the query pattern (normalized SQL).

    • queryTextstringrequired

      Normalized query text with literals replaced by placeholders.

    • dbNamestringrequired

      Database the query ran in.

    • dbUserstringrequired

      Database user that executed the query.

    • dbOperationstringrequired

      Top-level SQL operation type (for example, SELECT, INSERT, UPDATE, DELETE, UTILITY).

    • appstringrequired

      Value of the Postgres application_name for executions matching this pattern.

    • callCountintegerrequired

      Number of times the pattern executed in the window.

    • errorCountintegerrequired

      Number of executions of the pattern that raised an error.

    • totalDurationUsintegerrequired

      Total execution time across all calls, in microseconds.

    • avgDurationUsintegerrequired

      Average execution time per call, in microseconds.

    • maxDurationUsintegerrequired

      Maximum execution time of any call, in microseconds.

    • p50DurationUsintegerrequired

      50th percentile execution time, in microseconds.

    • p95DurationUsintegerrequired

      95th percentile execution time, in microseconds.

    • p99DurationUsintegerrequired

      99th percentile execution time, in microseconds.

    • totalRowsintegerrequired

      Total number of rows returned or affected across all calls.

    • totalSharedBlksReadintegerrequired

      Total shared buffer blocks read from disk (cache misses) across all calls.

    • totalSharedBlksHitintegerrequired

      Total shared buffer blocks hit (cache hits) across all calls.

    • totalCpuTimeUsintegerrequired

      Total CPU time across all calls, in microseconds.

    • totalWalBytesintegerrequired

      Total WAL (write-ahead log) bytes generated across all calls.

Report an issue
Navigation