slow_query_patterns

Creates, updates, deletes, gets or lists a slow_query_patterns resource.

Overview

Name	slow_query_patterns
Type	Resource
Id	clickhouse.postgres.slow_query_patterns

Fields

The following fields are returned by SELECT queries:

get

Name	Datatype	Description
aggregate	object
recent_executions	array	Recent individual executions matching the pattern. (wire: recentExecutions)

list

Name	Datatype	Description
query_id	string	Stable identifier for the query pattern (normalized SQL). (wire: queryId)
db_name	string	Database the query ran in. (wire: dbName)
app	string	Value of the Postgres application_name for executions matching this pattern.
avg_duration_us	integer	Average execution time per call, in microseconds. (wire: avgDurationUs)
call_count	integer	Number of times the pattern executed in the window. (wire: callCount)
db_operation	string	Top-level SQL operation type (for example, SELECT, INSERT, UPDATE, DELETE, UTILITY). (wire: dbOperation)
db_user	string	Database user that executed the query. (wire: dbUser)
error_count	integer	Number of executions of the pattern that raised an error. (wire: errorCount)
max_duration_us	integer	Maximum execution time of any call, in microseconds. (wire: maxDurationUs)
p_50_duration_us	integer	50th percentile execution time, in microseconds. (wire: p50DurationUs)
p_95_duration_us	integer	95th percentile execution time, in microseconds. (wire: p95DurationUs)
p_99_duration_us	integer	99th percentile execution time, in microseconds. (wire: p99DurationUs)
query_text	string	Normalized query text with literals replaced by placeholders. (wire: queryText)
total_cpu_time_us	integer	Total CPU time across all calls, in microseconds. (wire: totalCpuTimeUs)
total_duration_us	integer	Total execution time across all calls, in microseconds. (wire: totalDurationUs)
total_rows	integer	Total number of rows returned or affected across all calls. (wire: totalRows)
total_shared_blks_hit	integer	Total shared buffer blocks hit (cache hits) across all calls. (wire: totalSharedBlksHit)
total_shared_blks_read	integer	Total shared buffer blocks read from disk (cache misses) across all calls. (wire: totalSharedBlksRead)
total_wal_bytes	integer	Total WAL (write-ahead log) bytes generated across all calls. (wire: totalWalBytes)

Methods

The following methods are available for this resource:

Name	Accessible by	Required Params	Optional Params	Description
get	select	postgres_id, query_id, db_name, db_user, db_operation, organization_id	app, timestamp	This endpoint is in beta. API contract is stable, and no breaking changes are expected in the future. <br /><br /> Returns aggregate metrics for a single slow query pattern together with its most recent individual executions.
list	select	postgres_id, from_date, to_date, organization_id	db_name, db_user, db_operation, app, sort_by, sort_order, limit, offset	This endpoint is in beta. API contract is stable, and no breaking changes are expected in the future. <br /><br /> 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.

Parameters

Parameters can be passed in the WHERE clause of a query. Check the Methods section to see which parameters are required or optional for each operation.

Name	Datatype	Description
db_name	string	Database name filter.
db_operation	string	Database operation filter (for example, SELECT, INSERT, UPDATE, DELETE, UTILITY).
db_user	string	Database user filter.
from_date	string (date-time)	Inclusive start of the time window (RFC 3339 date-time).
organization_id	string	ClickHouse Cloud organization ID. Resolved from the CLICKHOUSE_ORG_ID environment variable when it is set (x-stackQL-envVar); otherwise it must be supplied on every query as WHERE organization_id = <uuid>. A WHERE value always takes precedence over the environment. (x-stackQL-envVar: CLICKHOUSE_ORG_ID)
postgres_id	string (uuid)	ID of the requested Postgres service. (wire: postgresId)
query_id	string	Stable identifier for the query pattern. (wire: queryId)
to_date	string (date-time)	Exclusive end of the time window (RFC 3339 date-time).
app	string	Application name filter.
db_name	string	Database name filter.
db_operation	string	Database operation filter (for example, SELECT, INSERT, UPDATE, DELETE, UTILITY).
db_user	string	Database user filter.
limit	integer	Maximum number of results to return.
offset	integer	Number of results to skip before returning.
sort_by	string	Field to sort results by.
sort_order	string	Sort order. One of asc or desc.
timestamp	string (date-time)	Timestamp of a specific execution (RFC 3339).

SELECT examples

get

This endpoint is in beta. API contract is stable, and no breaking changes are expected in the future. <br /><br /> Returns aggregate metrics for a single slow query pattern together with its most recent individual executions.

SELECT
aggregate,
recent_executions
FROM clickhouse.postgres.slow_query_patterns
WHERE postgres_id = '{{ postgres_id }}' -- required
AND query_id = '{{ query_id }}' -- required
AND db_name = '{{ db_name }}' -- required
AND db_user = '{{ db_user }}' -- required
AND db_operation = '{{ db_operation }}' -- required
AND organization_id = '{{ organization_id }}' -- required unless CLICKHOUSE_ORG_ID is set
AND app = '{{ app }}'
AND timestamp = '{{ timestamp }}'
;

list

This endpoint is in beta. API contract is stable, and no breaking changes are expected in the future. <br /><br /> 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.

SELECT
query_id,
db_name,
app,
avg_duration_us,
call_count,
db_operation,
db_user,
error_count,
max_duration_us,
p_50_duration_us,
p_95_duration_us,
p_99_duration_us,
query_text,
total_cpu_time_us,
total_duration_us,
total_rows,
total_shared_blks_hit,
total_shared_blks_read,
total_wal_bytes
FROM clickhouse.postgres.slow_query_patterns
WHERE postgres_id = '{{ postgres_id }}' -- required
AND from_date = '{{ from_date }}' -- required
AND to_date = '{{ to_date }}' -- required
AND organization_id = '{{ organization_id }}' -- required unless CLICKHOUSE_ORG_ID is set
AND db_name = '{{ db_name }}'
AND db_user = '{{ db_user }}'
AND db_operation = '{{ db_operation }}'
AND app = '{{ app }}'
AND sort_by = '{{ sort_by }}'
AND sort_order = '{{ sort_order }}'
AND limit = '{{ limit }}'
AND offset = '{{ offset }}'
;