Consultative Support

PostgreSQL: database time and unused indexes

Built on pg_stat_statements, described in the PostgreSQL documentation. The second query finds indexes that cost writes and storage but are never read.

-- PostgreSQL 14+ with pg_stat_statements: where database time actually goes
SELECT
    queryid,
    calls,
    ROUND(total_exec_time::numeric / 1000, 1)                         AS total_s,
    ROUND(mean_exec_time::numeric, 2)                                 AS mean_ms,
    ROUND(100 * total_exec_time / SUM(total_exec_time) OVER (), 1)   AS pct_of_db_time,
    shared_blks_read,
    LEFT(query, 100)                                                  AS query_head
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- Indexes that cost storage and write amplification but are never read
SELECT
    s.schemaname,
    s.relname                                         AS table_name,
    s.indexrelname                                    AS index_name,
    pg_size_pretty(pg_relation_size(s.indexrelid))    AS index_size
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i
    ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;

MySQL and MariaDB: statement digests

The performance_schema digest table ranks statement shapes by total latency and shows where full scans happen; see the MySQL 8.4 reference.

-- MySQL 8.0 / 8.4 performance_schema: top statement digests by total latency
SELECT
    schema_name,
    count_star                                        AS executions,
    ROUND(sum_timer_wait / 1e12, 1)                   AS total_s,
    ROUND(avg_timer_wait / 1e9, 2)                    AS avg_ms,
    sum_rows_examined,
    sum_no_index_used,
    LEFT(digest_text, 100)                            AS digest_head
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 15;

SQL Server: Query Store

Query Store keeps runtime history per plan, so a regression can be traced to the exact plan change; see Microsoft Learn.

-- SQL Server 2016+ Query Store: heaviest queries by total duration (microseconds stored)
SELECT TOP (15)
    q.query_id,
    SUM(rs.count_executions)                                       AS executions,
    ROUND(SUM(rs.avg_duration * rs.count_executions) / 1e6, 1)     AS total_s,
    ROUND(SUM(rs.avg_logical_io_reads * rs.count_executions), 0)   AS logical_reads,
    LEFT(qt.query_sql_text, 100)                                   AS query_head
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS p
    ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q
    ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_s DESC;

Oracle: top SQL without extra licences

We baseline from V$SQLSTATS and confirm Diagnostics Pack entitlement before using Active Session History or AWR.

-- Oracle 19c / 23ai: top SQL by elapsed time from V$SQLSTATS (microseconds)
SELECT *
FROM (
    SELECT
        sql_id,
        executions,
        ROUND(elapsed_time / 1e6, 1)                  AS elapsed_s,
        ROUND(cpu_time / 1e6, 1)                      AS cpu_s,
        buffer_gets,
        SUBSTR(sql_text, 1, 100)                      AS sql_head
    FROM v$sqlstats
    ORDER BY elapsed_time DESC
)
WHERE ROWNUM <= 15;

Db2 for LUW: package cache

The monitoring table functions rank statements by execution time across all members of the instance.

-- Db2 11.5 / 12.1 for LUW: package cache statements by execution time (ms)
SELECT
    num_executions,
    stmt_exec_time                                    AS exec_ms,
    rows_read,
    SUBSTR(stmt_text, 1, 100)                         AS stmt_head
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2)) AS t
ORDER BY stmt_exec_time DESC
FETCH FIRST 15 ROWS ONLY;

MongoDB: index usage and cache pressure

Unused indexes slow every write. $indexStats shows usage since the last restart, so we read it alongside uptime.

// MongoDB 6.0+ (mongosh): index usage since restart, unused indexes first
db.getCollectionNames().forEach(function (c) {
  db.getCollection(c).aggregate([{ $indexStats: {} }]).forEach(function (ix) {
    if (ix.name !== "_id_") {
      print(c + "." + ix.name + "  ops=" + ix.accesses.ops + "  since=" + ix.accesses.since);
    }
  });
});

// WiredTiger cache pressure: bytes in cache against the configured maximum
const wt = db.serverStatus().wiredTiger.cache;
print(wt["bytes currently in the cache"] + " / " + wt["maximum bytes configured"]);

ClickHouse: heaviest query shapes

ClickHouse consultative support is delivered with our sister company ChistaDATA; system.query_log is where every baseline starts.

-- ClickHouse 24.x / 25.x: heaviest query shapes over the last 24 hours
SELECT
    normalized_query_hash,
    count()                                           AS executions,
    round(sum(query_duration_ms) / 1000, 1)           AS total_s,
    formatReadableSize(sum(read_bytes))               AS read,
    formatReadableSize(max(memory_usage))             AS peak_memory,
    substring(any(query), 1, 100)                     AS query_head
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 DAY
GROUP BY normalized_query_hash
ORDER BY total_s DESC
LIMIT 15;

Redis and Valkey: command cost and key size

Two commands usually explain a Redis latency or memory problem: which commands dominate the event loop, and which keys hold the memory.

# Redis 7.x / Valkey 8.x: which commands consume the event loop, and which keys hold the memory
export REDISCLI_AUTH="${REDIS_PASSWORD}"
redis-cli -h "${REDIS_HOST}" INFO commandstats | sort -t= -k3 -rn | head -n 15
redis-cli -h "${REDIS_HOST}" --bigkeys        # sampled scan; run against a replica in production

FinOps: reserved capacity coverage

Coverage below the steady-state baseline means paying on-demand rates for capacity that runs all year. We pair this view with measured utilization before recommending any commitment. Ongoing cost work sits with our cloud database FinOps practice.

# Reserved instance coverage for Amazon RDS by instance type: the first FinOps question we ask
aws ce get-reservation-coverage \
  --time-period Start="${PERIOD_START}",End="${PERIOD_END}" \
  --group-by Type=DIMENSION,Key=INSTANCE_TYPE \
  --filter '{"Dimensions":{"Key":"SERVICE","Values":["Amazon Relational Database Service"]}}'

What FinOps reviews cover

• Instance class and storage performance against measured CPU, memory and I/O.
• Reserved capacity and savings plans against the post-optimization baseline.
• Replicas, snapshots and backups that outlived their purpose.
• Licence position for SQL Server, Oracle and Db2, including edition and core counts.
• Queries and indexes whose fix removes the need for the next scale-up.