Hologres:Get and analyze slow query logs

Version guide

This feature requires Hologres V0.10 or later. To check your instance version, go to the instance details page in the Hologres console. To upgrade an earlier instance, see Common upgrade preparation errors or contact Hologres support. For more information, see How do I get more online support?.

Limitations

• Slow query logs are retained for one month by default.

• A single query returns a maximum of 10,000 slow query log entries. Some fields have length limits — see the field descriptions in the hg_query_log table section.

• Slow query logs are part of the Hologres metadata warehouse. A failed slow query log search does not affect your business queries, and log availability is not covered by the Hologres Service-Level Agreement (SLA).

How it works

Hologres stores slow query logs in the `hologres.hg_query_log` system table. The table records completed SQL statements only — queries still in progress are not written to the table. This behavior is consistent across V2, V3, and later versions.

What gets logged:

• After you upgrade to V0.10: slow DML queries running longer than 100 ms, and all DDL operations.

• From V3.0.2: in addition to detailed records for queries over 100 ms, aggregated records are also written for DQL and DML queries that complete in under 100 ms.

How aggregation works (V3.0.2+):

For fast queries (under 100 ms), the system groups successful DQL and DML queries that share the same SQL fingerprint (digest). The aggregation key is: server_addr, usename, datname, warehouse_id, application_name, and digest. Each connection flushes one aggregated record per minute.

Prerequisites

To view slow query logs, you need one of the following permissions:

View logs for all databases in an instance:

• Superuser: Run the following command. Replace Alibaba Cloud account ID with the actual username. For RAM users, use p4_AccountID (account ID, not the RAM user name).

  ALTER USER "Alibaba Cloud account ID" SUPERUSER;

• pg_read_all_stats group (for non-superusers): Contact a superuser to add you to this group.

  -- Standard PostgreSQL authorization
  GRANT pg_read_all_stats TO "Alibaba Cloud account ID";
  
  -- Simple permission model (SPM)
  CALL spm_grant('pg_read_all_stats', 'Alibaba Cloud account ID');
  
  -- Schema-level permission model (SLPM)
  CALL slpm_grant('pg_read_all_stats', 'Alibaba Cloud account ID');

View logs for the current database only:

Enable SPM or SLPM and add the user to the db_admin role.

-- SPM
CALL spm_grant('<db_name>_admin', 'Alibaba Cloud account ID');

-- SLPM
CALL slpm_grant('<db_name>.admin', 'Alibaba Cloud account ID');

Regular users can only view their own queries in the current database, with no additional setup required.

View slow query logs

Hologres provides two ways to view slow query logs. Use HoloWeb for visual exploration — use SQL for custom filters, time ranges, and export.

SQL fingerprint

From V2.2, the digest column in hg_query_log stores an SQL fingerprint for each query. For SELECT, INSERT, DELETE, and UPDATE, Hologres calculates an MD5 hash as the fingerprint.

When to use `digest` vs `query_id`:

• Use digest to group and analyze queries of the same type — for example, to find which query patterns consume the most CPU on average.

• Use query_id to trace a specific query execution — for example, to retrieve the full details of a particular failed query.

How fingerprints are calculated:

• Fingerprints are collected for SELECT, INSERT, DELETE, and UPDATE only.

• Whitespace is ignored (spaces, line breaks, tabs).

• Constant values are ignored: SELECT * FROM t WHERE a > 1 and SELECT * FROM t WHERE a > 2 produce the same fingerprint.

• Array element counts are ignored: WHERE a IN (1, 2) and WHERE a IN (3, 4, 5) produce the same fingerprint.

• For INSERT with constant data, the fingerprint is not affected by the number of rows inserted.

• Letter case follows Hologres query rules.

• The fingerprint includes the database name and fully qualified schema, so SELECT * FROM t and SELECT * FROM public.t have the same fingerprint only when t is in the public schema and both queries reference the same table.

Diagnose queries

The following SQL examples cover the most common diagnostic scenarios. All queries target hologres.hg_query_log.

Count all queries in the log (default: last month):

SELECT count(*) FROM hologres.hg_query_log;

Example output — 44 slow queries in the last month:

 count
-------
    44
(1 row)

Count slow queries per user:

SELECT usename AS "User",
       count(1) AS "Query count"
FROM hologres.hg_query_log
GROUP BY usename
ORDER BY count(1) DESC;

Example output:

        User         | Query count
---------------------+-------------
 1111111111111111    |          27
 2222222222222222    |          11
 3333333333333333    |           4
 4444444444444444    |           2
(4 rows)

Look up a specific query by ID:

SELECT * FROM hologres.hg_query_log WHERE query_id = '13001450118416xxxx';

For a description of the returned fields, see The hg_query_log table.

Find resource-intensive queries in the last 10 minutes:

Adjust the interval to match your target time window.

SELECT status           AS "Status",
       duration         AS "Duration (ms)",
       query_start      AS "Start time",
       (read_bytes / 1048576)::text || ' MB'  AS "Data read",
       (memory_bytes / 1048576)::text || ' MB' AS "Memory",
       (shuffle_bytes / 1048576)::text || ' MB' AS "Shuffle",
       (cpu_time_ms / 1000)::text || ' s'    AS "CPU time",
       physical_reads   AS "Disk reads",
       query_id         AS "Query ID",
       query::char(30)
FROM hologres.hg_query_log
WHERE query_start >= now() - interval '10 min'
ORDER BY duration DESC,
         read_bytes DESC,
         shuffle_bytes DESC,
         memory_bytes DESC,
         cpu_time_ms DESC,
         physical_reads DESC
LIMIT 100;

Example output:

 Status  | Duration (ms) |       Start time       | Data read | Memory | Shuffle | CPU time | Disk reads |       Query ID     |         query
---------+---------------+------------------------+-----------+--------+---------+----------+------------+--------------------+--------------------------------
 SUCCESS |           149 | 2021-03-30 23:45:01+08 | 0 MB      | 25 MB  | 454 MB  | 321 s    |          0 | 13001450118416xxxx | explain analyze SELECT  * FROM
 SUCCESS |           137 | 2021-03-30 23:49:18+08 | 247 MB    | 21 MB  | 213 MB  | 803 s    |       7771 | 13001491818416xxxx | explain analyze SELECT  * FROM
 FAILED  |            53 | 2021-03-30 23:48:43+08 | 0 MB      | 0 MB   | 0 MB    | 0 s      |          0 | 13001484318416xxxx | SELECT ds::bigint / 0 FROM pub
(3 rows)

Break down duration by stage:

Use this to identify which stage (optimization_cost, start_query_cost, or get_next_cost) accounts for most of the delay. See the duration breakdown table in the hg_query_log table section for details on each stage.

SELECT status              AS "Status",
       duration            AS "Duration (ms)",
       optimization_cost   AS "Optimization cost (ms)",
       start_query_cost    AS "Startup cost (ms)",
       get_next_cost       AS "Execution cost (ms)",
       duration - optimization_cost - start_query_cost - get_next_cost AS "Other cost (ms)",
       query_id            AS "Query ID",
       query::char(30)
FROM hologres.hg_query_log
WHERE query_start >= now() - interval '10 min'
ORDER BY duration DESC,
         start_query_cost DESC,
         optimization_cost,
         get_next_cost DESC,
         duration - optimization_cost - start_query_cost - get_next_cost DESC
LIMIT 100;

Example output:

 Status  | Duration (ms) | Optimization cost (ms) | Startup cost (ms) | Execution cost (ms) | Other cost (ms) |       Query ID     |         query
---------+---------------+------------------------+-------------------+---------------------+-----------------+--------------------+--------------------------------
 SUCCESS |          4572 |                    521 |               320 |                3726 |               5 | 6000260625679xxxx  | -- /* user: wang ip: xxx.xx.x
 SUCCESS |          1490 |                    538 |                98 |                 846 |               8 | 12000250867886xxxx | -- /* user: lisa ip: xxx.xx.x
 SUCCESS |          1230 |                    502 |                95 |                 625 |               8 | 26000512070295xxxx | -- /* user: zhang ip: xxx.xx.
(3 rows)

View query volume and data read per hour (last 3 hours):

SELECT date_trunc('hour', query_start) AS query_start,
       count(1)           AS query_count,
       sum(read_bytes)    AS read_bytes,
       sum(cpu_time_ms)   AS cpu_time_ms
FROM hologres.hg_query_log
WHERE query_start >= now() - interval '3 h'
GROUP BY 1;

Compare traffic against the same window yesterday:

SELECT query_date,
       count(1)           AS query_count,
       sum(read_bytes)    AS read_bytes,
       sum(cpu_time_ms)   AS cpu_time_ms
FROM hologres.hg_query_log
WHERE query_start >= now() - interval '3 h'
GROUP BY query_date
UNION ALL
SELECT query_date,
       count(1)           AS query_count,
       sum(read_bytes)    AS read_bytes,
       sum(cpu_time_ms)   AS cpu_time_ms
FROM hologres.hg_query_log
WHERE query_start >= now() - interval '1d 3h'
  AND query_start <= now() - interval '1d'
GROUP BY query_date;

Find the first failed query in a time window:

SELECT status              AS "Status",
       regexp_replace(message, '\n', ' ')::char(150) AS "Error message",
       duration            AS "Duration (ms)",
       query_start         AS "Start time",
       query_id            AS "Query ID",
       query::char(100)    AS "Query"
FROM hologres.hg_query_log
WHERE query_start BETWEEN '2021-03-25 17:00:00'::timestamptz
                      AND '2021-03-25 17:42:00'::timestamptz + interval '2 min'
  AND status = 'FAILED'
ORDER BY query_start ASC
LIMIT 100;

Example output:

 Status |                        Error message                        | Duration (ms) |       Start time       |      Query ID      | Query
--------+--------------------------------------------------------------+---------------+------------------------+--------------------+-------
 FAILED | Query:[1070285448673xxxx] code: kActorInvokeError msg: "..." |          1460 | 2021-03-25 17:28:54+08 | 1070285448673xxxx  | S...
 FAILED | Query:[1016285560553xxxx] code: kActorInvokeError msg: "..." |           131 | 2021-03-25 17:28:55+08 | 1016285560553xxxx  | S...
(2 rows)

Find new query patterns from yesterday (total count):

Queries that appear for the first time compared to the day before yesterday, grouped by fingerprint.

SELECT COUNT(1)
FROM (
    SELECT DISTINCT t1.digest
    FROM hologres.hg_query_log t1
    WHERE t1.query_start >= CURRENT_DATE - INTERVAL '1 day'
      AND t1.query_start < CURRENT_DATE
      AND NOT EXISTS (
          SELECT 1
          FROM hologres.hg_query_log t2
          WHERE t2.digest = t1.digest
            AND t2.query_start < CURRENT_DATE - INTERVAL '1 day'
      )
      AND digest IS NOT NULL
) AS a;

Example output — 10 new query patterns yesterday:

 count
-------
    10
(1 row)

Find new query patterns from yesterday (by type):

SELECT a.command_tag,
       COUNT(1)
FROM (
    SELECT DISTINCT t1.digest, t1.command_tag
    FROM hologres.hg_query_log t1
    WHERE t1.query_start >= CURRENT_DATE - INTERVAL '1 day'
      AND t1.query_start < CURRENT_DATE
      AND NOT EXISTS (
          SELECT 1
          FROM hologres.hg_query_log t2
          WHERE t2.digest = t1.digest
            AND t2.query_start < CURRENT_DATE - INTERVAL '1 day'
      )
      AND t1.digest IS NOT NULL
) AS a
GROUP BY 1
ORDER BY 2 DESC;

Example output:

 command_tag | count
-------------+-------
 INSERT      |     8
 SELECT      |     2
(2 rows)

Find new query patterns from yesterday (with details):

SELECT a.usename, a.status, a.query_id, a.digest,
       a.datname, a.command_tag, a.query, a.cpu_time_ms, a.memory_bytes
FROM (
    SELECT DISTINCT
        t1.usename, t1.status, t1.query_id, t1.digest,
        t1.datname, t1.command_tag, t1.query, t1.cpu_time_ms, t1.memory_bytes
    FROM hologres.hg_query_log t1
    WHERE t1.query_start >= CURRENT_DATE - INTERVAL '1 day'
      AND t1.query_start < CURRENT_DATE
      AND NOT EXISTS (
          SELECT 1
          FROM hologres.hg_query_log t2
          WHERE t2.digest = t1.digest
            AND t2.query_start < CURRENT_DATE - INTERVAL '1 day'
      )
      AND t1.digest IS NOT NULL
) AS a;

Find new query patterns from yesterday (by hour):

SELECT to_char(a.query_start, 'HH24') AS query_start_hour,
       a.command_tag,
       COUNT(1)
FROM (
    SELECT DISTINCT t1.query_start, t1.digest, t1.command_tag
    FROM hologres.hg_query_log t1
    WHERE t1.query_start >= CURRENT_DATE - INTERVAL '1 day'
      AND t1.query_start < CURRENT_DATE
      AND NOT EXISTS (
          SELECT 1
          FROM hologres.hg_query_log t2
          WHERE t2.digest = t1.digest
            AND t2.query_start < CURRENT_DATE - INTERVAL '1 day'
      )
      AND t1.digest IS NOT NULL
) AS a
GROUP BY 1, 2
ORDER BY 3 DESC;

Example output — at 21:00 yesterday, 8 INSERT patterns; at 11:00 and 13:00, 1 SELECT pattern each:

 query_start_hour | command_tag | count
------------------+-------------+-------
               21 | INSERT      |     8
               11 | SELECT      |     1
               13 | SELECT      |     1
(3 rows)

Count slow queries by fingerprint (yesterday):

SELECT digest,
       command_tag,
       count(1)
FROM hologres.hg_query_log
WHERE query_start >= CURRENT_DATE - INTERVAL '1 day'
  AND query_start < CURRENT_DATE
GROUP BY 1, 2
ORDER BY 3 DESC;

Find the top 10 query patterns with the highest average CPU time (last day):

SELECT digest,
       avg(cpu_time_ms)
FROM hologres.hg_query_log
WHERE query_start >= CURRENT_DATE - INTERVAL '1 day'
  AND query_start < CURRENT_DATE
  AND digest IS NOT NULL
  AND usename != 'system'
  AND cpu_time_ms IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Find the top 10 query patterns with the highest average memory usage (last week):

SELECT digest,
       avg(memory_bytes)
FROM hologres.hg_query_log
WHERE query_start >= CURRENT_DATE - INTERVAL '7 day'
  AND query_start < CURRENT_DATE
  AND digest IS NOT NULL
  AND memory_bytes IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Configuration parameters

Use these GUC parameters to control what gets logged and how much detail is captured.

-- Database level (superuser only)
ALTER DATABASE dbname SET log_min_duration_statement = '250ms';

-- Session level
SET log_min_duration_statement = '250ms';

-- Database level (superuser only)
ALTER DATABASE dbname SET log_min_duration_query_stats = '20s';

-- Session level
SET log_min_duration_query_stats = '20s';

-- Database level (superuser only)
ALTER DATABASE dbname SET log_min_duration_query_plan = '10s';

-- Session level
SET log_min_duration_query_plan = '10s';

Modify log retention

From V3.0.27, you can change how long slow query logs are retained at the database level.

ALTER DATABASE <db_name> SET hg_query_log_retention_time_sec = 2592000;

Export slow query logs

Export data from hg_query_log to a Hologres internal table, MaxCompute foreign table, or OSS for long-term storage or analysis.

Before you export, note:

• The account running the INSERT INTO ... SELECT ... FROM hologres.hg_query_log command must have access to hg_query_log. For instance-wide exports, superuser or pg_read_all_stats permissions are required — otherwise the exported data will be incomplete.

• query_start is an indexed column. Always include it in your WHERE clause to improve performance and reduce resource usage.

• Do not apply functions to query_start in the WHERE clause — this prevents the index from being used.

  -- Correct: use range conditions on query_start directly
  WHERE query_start >= '2022-08-03' AND query_start < '2022-08-04'
  
  -- Incorrect: wrapping query_start in a function bypasses the index
  WHERE to_char(query_start, 'yyyymmdd') = '20220101'

-- Step 1: Create the target table
CREATE TABLE query_log_download (
    usename text,
    status text,
    query_id text,
    datname text,
    command_tag text,
    duration integer,
    message text,
    query_start timestamp with time zone,
    query_date text,
    query text,
    result_rows bigint,
    result_bytes bigint,
    read_rows bigint,
    read_bytes bigint,
    affected_rows bigint,
    affected_bytes bigint,
    memory_bytes bigint,
    shuffle_bytes bigint,
    cpu_time_ms bigint,
    physical_reads bigint,
    pid integer,
    application_name text,
    engine_type text[],
    client_addr text,
    table_write text,
    table_read text[],
    session_id text,
    session_start timestamp with time zone,
    trans_id text,
    command_id text,
    optimization_cost integer,
    start_query_cost integer,
    get_next_cost integer,
    extended_cost text,
    plan text,
    statistics text,
    visualization_info text,
    query_detail text,
    query_extinfo text[]
);

-- Step 2: Export logs for a specific date
INSERT INTO query_log_download
SELECT
    usename, status, query_id, datname, command_tag, duration, message,
    query_start, query_date, query, result_rows, result_bytes, read_rows,
    read_bytes, affected_rows, affected_bytes, memory_bytes, shuffle_bytes,
    cpu_time_ms, physical_reads, pid, application_name, engine_type,
    client_addr, table_write, table_read, session_id, session_start,
    trans_id, command_id, optimization_cost, start_query_cost, get_next_cost,
    extended_cost, plan, statistics, visualization_info, query_detail, query_extinfo
FROM hologres.hg_query_log
WHERE query_start >= '2022-08-03'
  AND query_start < '2022-08-04';

FAQ

Query rows returned and rows read are missing in Hologres V1.1.

This happens because slow query log collection is incomplete in affected V1.1 versions. In V1.1.36 to V1.1.49, enable the following GUC parameter to collect complete statistics:

-- Database level (recommended — set once per database)
ALTER DATABASE <db_name> SET hg_experimental_force_sync_collect_execution_statistics = ON;

-- Session level
SET hg_experimental_force_sync_collect_execution_statistics = ON;

Replace <db_name> with your database name.

If your instance is earlier than V1.1.36, see Common upgrade preparation errors or contact Hologres support. For more information, see How do I get more online support?.

This behavior is resolved by default in V1.1.49 and later.

What's next

To monitor and manage active queries in your instance, see Manage queries.