# Query log - Docs

The `query_log` table in PostHog's [data warehouse](/docs/data-warehouse/sources/posthog.md) provides access to query execution metadata and performance metrics. This table is built on top of the ClickHouse `system.query_log` table and offers a simplified, team-filtered interface for analyzing query performance. It filters for completed HogQL queries for your team.

It provides data that enables you to:

-   Monitor query performance and resource usage
-   Debug slow or failing queries
-   Analyze query patterns and usage metrics
-   Track resource consumption (CPU, memory, I/O)

**Querying the query log efficiently**

Because the query log can be so large, it's important to query it efficiently.

-   Use date/time filters to limit query scope for better performance
-   Consider using `event_date` for efficient partitioning
-   Large time ranges may require pagination for complex queries

## Table schema

### Core fields

| Field | Type | Description |
| --- | --- | --- |
| event_date | Date | Date when the query was executed |
| event_time | DateTime | Exact timestamp when query started |
| query_id | String | Unique identifier for the query |
| endpoint | String | API endpoint that triggered the query |
| query | String | The actual HogQL/SQL query that was executed |
| query_start_time | DateTime | When query execution began |
| query_duration_ms | Integer | Query execution time in milliseconds |
| name | String | Name assigned to the QueryRequest |
| created_by | Integer | User ID who initiated the query |
| status | String | Query status (QueryFinish, ExceptionWhileProcessing, etc.) |

### Performance metrics

| Field | Type | Description |
| --- | --- | --- |
| read_rows | Integer | Number of rows processed |
| read_bytes | Integer | Bytes read during execution |
| result_rows | Integer | Number of rows returned |
| result_bytes | Integer | Size of result set in bytes |
| memory_usage | Integer | Peak memory usage in bytes |
| cpu_microseconds | Integer | CPU time consumed (for computation, excluding waiting) |
| RealTimeMicroseconds | Integer | Wall clock time in microseconds |

### S3 storage metrics

| Field | Type | Description |
| --- | --- | --- |
| S3ListObjects | Integer | Number of S3 list operations |
| S3HeadObject | Integer | Number of S3 head operations |
| S3GetObjectAttributes | Integer | Number of S3 get attributes calls |
| S3GetObject | Integer | Number of S3 get operations |
| ReadBufferFromS3Bytes | Integer | Bytes read from S3 |

### Access control

| Field | Type | Description |
| --- | --- | --- |
| is_personal_api_key_request | Boolean | Whether request used personal API key |
| api_key_label | String | Label of the API key used |
| api_key_mask | String | Masked API key used |

## Query log use cases and examples

### Performance optimization

You can use the query log to identify slow queries. For example, to find slowest queries in the last 24 hours:

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++query_duration_ms%2C%0A++++name%2C%0A++++query_start_time%2C%0A++++read_rows%2C%0A++++memory_usage%2C%0A++++cpu_microseconds+%2F+1e6+as+cpu_sec%0AFROM+query_log+%0AWHERE+event_time+%3E%3D+now%28%29+-+INTERVAL+1+DAY%0AORDER+BY+query_duration_ms+DESC%0ALIMIT+10)

PostHog AI

```sql
SELECT
    query_duration_ms,
    name,
    query_start_time,
    read_rows,
    memory_usage,
    cpu_microseconds / 1e6 as cpu_sec
FROM query_log
WHERE event_time >= now() - INTERVAL 1 DAY
ORDER BY query_duration_ms DESC
LIMIT 10
```

You can also use the query log for resource analysis. For example, to analyze resource consumption by API endpoint:

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++endpoint%2C%0A++++count%28%29+as+query_count%2C%0A++++avg%28query_duration_ms%29+as+avg_duration%2C%0A++++avg%28memory_usage%29+as+avg_memory%2C%0A++++sum%28read_bytes%29+as+total_bytes_read%2C%0A++++sum%28cpu_microseconds+%2F+1e6%29+as+total_cpu_sec%0AFROM+query_log+%0AWHERE+event_date+%3D+today%28%29%0A++++AND+is_personal_api_key_request%0AGROUP+BY+endpoint%0AORDER+BY+avg_duration+DESC)

PostHog AI

```sql
SELECT
    endpoint,
    count() as query_count,
    avg(query_duration_ms) as avg_duration,
    avg(memory_usage) as avg_memory,
    sum(read_bytes) as total_bytes_read,
    sum(cpu_microseconds / 1e6) as total_cpu_sec
FROM query_log
WHERE event_date = today()
    AND is_personal_api_key_request
GROUP BY endpoint
ORDER BY avg_duration DESC
```

You can do something similar to track S3 operations and data transfer (applies to data warehouse queries, imported data, materialized views):

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++event_date%2C%0A++sum%28S3GetObject%29+as+get_operations%2C%0A++sum%28S3ListObjects%29+as+list_operations%2C%0A++sum%28ReadBufferFromS3Bytes%29+%2F+1024+%2F+1024+as+mb_read_from_s3%0AFROM+query_log+%0AWHERE+event_time+%3E%3D+now%28%29+-+INTERVAL+30+DAY%0A++++AND+is_personal_api_key_request%0AGROUP+BY+event_date%0AORDER+BY+event_date+DESC)

PostHog AI

```sql
SELECT
  event_date,
  sum(S3GetObject) as get_operations,
  sum(S3ListObjects) as list_operations,
  sum(ReadBufferFromS3Bytes) / 1024 / 1024 as mb_read_from_s3
FROM query_log
WHERE event_time >= now() - INTERVAL 30 DAY
    AND is_personal_api_key_request
GROUP BY event_date
ORDER BY event_date DESC
```

### Debugging and troubleshooting

The query log can help you find failing queries and understand failure patterns.

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++query_start_time%2C%0A++name%2C%0A++endpoint%2C%0A++query_duration_ms%2C%0A++substring%28query%2C+1%2C+100%29+as+query_preview%0AFROM+query_log+%0AWHERE+status+IN+%28'ExceptionWhileProcessing'%2C+'ExceptionBeforeStart'%29%0A++++AND+event_time+%3E%3D+now%28%29+-+INTERVAL+1+DAY%0A++++AND+is_personal_api_key_request%0AORDER+BY+query_start_time+DESC)

PostHog AI

```sql
SELECT
  query_start_time,
  name,
  endpoint,
  query_duration_ms,
  substring(query, 1, 100) as query_preview
FROM query_log
WHERE status IN ('ExceptionWhileProcessing', 'ExceptionBeforeStart')
    AND event_time >= now() - INTERVAL 1 DAY
    AND is_personal_api_key_request
ORDER BY query_start_time DESC
```

The benefit of this is that you can see the actual query that was executed and the error that was returned.

### Query analysis by request name and date

The `name` field enables you to analyze query patterns by their assigned names, such as daily query volume by request name:

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++event_date%2C%0A++++name%2C%0A++++count%28%29+as+daily_queries%2C%0A++++avg%28query_duration_ms%29+as+avg_duration_ms%2C%0A++++sum%28read_rows%29+as+total_rows_processed%2C%0A++++sum%28memory_usage%29+%2F+1024+%2F+1024+as+total_mb_memory%0AFROM+query_log+%0AWHERE+event_date+%3E%3D+today%28%29+-+INTERVAL+7+DAY%0A++++AND+is_personal_api_key_request%0A++++AND+endpoint+LIKE+'%25%2Fquery%2F'%0AGROUP+BY+event_date%2C+name%0AORDER+BY+event_date+DESC%2C+daily_queries+DESC)

PostHog AI

```sql
SELECT
    event_date,
    name,
    count() as daily_queries,
    avg(query_duration_ms) as avg_duration_ms,
    sum(read_rows) as total_rows_processed,
    sum(memory_usage) / 1024 / 1024 as total_mb_memory
FROM query_log
WHERE event_date >= today() - INTERVAL 7 DAY
    AND is_personal_api_key_request
    AND endpoint LIKE '%/query/'
GROUP BY event_date, name
ORDER BY event_date DESC, daily_queries DESC
```

Or the most common request names by time period:

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++name%2C%0A++++count%28%29+as+total_queries%2C%0A++++count%28DISTINCT+event_date%29+as+days_active%2C%0A++++avg%28query_duration_ms%29+as+avg_duration_ms%2C%0A++++percentile%28query_duration_ms%2C+0.95%29+as+p95_duration_ms%2C%0A++++sum%28cpu_microseconds%29+%2F+1e6+as+total_cpu_sec%0AFROM+query_log+%0AWHERE+event_time+%3E%3D+now%28%29+-+INTERVAL+30+DAY%0A++++AND+is_personal_api_key_request%0A++++AND+endpoint+LIKE+'%25%2Fquery%2F'%0AGROUP+BY+name%0AHAVING+total_queries+%3E%3D+10%0AORDER+BY+total_queries+DESC%0ALIMIT+20)

PostHog AI

```sql
SELECT
    name,
    count() as total_queries,
    count(DISTINCT event_date) as days_active,
    avg(query_duration_ms) as avg_duration_ms,
    percentile(query_duration_ms, 0.95) as p95_duration_ms,
    sum(cpu_microseconds) / 1e6 as total_cpu_sec
FROM query_log
WHERE event_time >= now() - INTERVAL 30 DAY
    AND is_personal_api_key_request
    AND endpoint LIKE '%/query/'
GROUP BY name
HAVING total_queries >= 10
ORDER BY total_queries DESC
LIMIT 20
```

You can also use the `name` property to see request performance trends over time:

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++event_date%2C%0A++++name%2C%0A++++count%28%29+as+query_count%2C%0A++++avg%28query_duration_ms%29+as+avg_duration%2C%0A++++avg%28memory_usage%29+%2F+1024+%2F+1024+as+avg_mb_memory%2C%0A++++avg%28read_rows%29+as+avg_rows_read%0AFROM+query_log+%0AWHERE+event_date+%3E%3D+today%28%29+-+INTERVAL+14+DAY%0A++++AND+is_personal_api_key_request%0A++++AND+endpoint+LIKE+'%25%2Fquery%2F'%0A++++AND+name+!%3D+''%0AGROUP+BY+event_date%2C+name%0AORDER+BY+event_date+DESC%2C+query_count+DESC)

PostHog AI

```sql
SELECT
    event_date,
    name,
    count() as query_count,
    avg(query_duration_ms) as avg_duration,
    avg(memory_usage) / 1024 / 1024 as avg_mb_memory,
    avg(read_rows) as avg_rows_read
FROM query_log
WHERE event_date >= today() - INTERVAL 14 DAY
    AND is_personal_api_key_request
    AND endpoint LIKE '%/query/'
    AND name != ''
GROUP BY event_date, name
ORDER BY event_date DESC, query_count DESC
```

You can use it to identify problematic request names too:

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++name%2C%0A++++count%28%29+as+total_queries%2C%0A++++countIf%28status+!%3D+'QueryFinish'%29+as+failed_queries%2C%0A++++failed_queries+%2F+count%28%29+as+failure_rate%2C%0A++++avg%28query_duration_ms%29+as+avg_duration_ms%2C%0A++++max%28query_duration_ms%29+as+max_duration_ms%2C%0A++++avg%28memory_usage%29+%2F+1024+%2F+1024+as+avg_mb_memory%0AFROM+query_log+%0AWHERE+event_time+%3E%3D+now%28%29+-+INTERVAL+7+DAY%0A++++AND+is_personal_api_key_request%0A++++AND+endpoint+LIKE+'%25%2Fquery%2F'%0A++++AND+name+!%3D+''%0AGROUP+BY+name%0AHAVING+total_queries+%3E%3D+5%0AORDER+BY+failure_rate+DESC%2C+avg_duration_ms+DESC)

PostHog AI

```sql
SELECT
    name,
    count() as total_queries,
    countIf(status != 'QueryFinish') as failed_queries,
    failed_queries / count() as failure_rate,
    avg(query_duration_ms) as avg_duration_ms,
    max(query_duration_ms) as max_duration_ms,
    avg(memory_usage) / 1024 / 1024 as avg_mb_memory
FROM query_log
WHERE event_time >= now() - INTERVAL 7 DAY
    AND is_personal_api_key_request
    AND endpoint LIKE '%/query/'
    AND name != ''
GROUP BY name
HAVING total_queries >= 5
ORDER BY failure_rate DESC, avg_duration_ms DESC
```

### Usage analytics

The query log lets you see what types of queries and usage patterns are most popular in your project. For example, to track query activity by user:

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT+%0A++++created_by%2C%0A++++count%28%29+as+queries_run%2C%0A++++avg%28query_duration_ms%29+as+avg_duration%2C%0A++++max%28query_duration_ms%29+as+max_duration%0AFROM+query_log+%0AWHERE+event_time+%3E%3D+now%28%29+-+INTERVAL+7+DAY%0A++++AND+is_personal_api_key_request%0A++++AND+endpoint+LIKE+'%25%2Fquery%2F'%0AGROUP+BY+created_by%0AORDER+BY+queries_run+DESC)

PostHog AI

```sql
SELECT
    created_by,
    count() as queries_run,
    avg(query_duration_ms) as avg_duration,
    max(query_duration_ms) as max_duration
FROM query_log
WHERE event_time >= now() - INTERVAL 7 DAY
    AND is_personal_api_key_request
    AND endpoint LIKE '%/query/'
GROUP BY created_by
ORDER BY queries_run DESC
```

### API key access tracking

This can also be useful to track API access patterns and permissions for security or compliance purposes.

For example, you can compare access patterns between personal API keys vs other methods.

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT%0A++++is_personal_api_key_request%2C%0A++++count%28%29+as+query_count%2C%0A++++count%28DISTINCT+created_by%29+as+unique_users%2C%0A++++avg%28query_duration_ms%29+as+avg_duration%2C%0A++++sum%28read_bytes%29+%2F+1024+%2F+1024+as+total_mb_read%2C%0A++++sum%28cpu_microseconds+%2F+1e6%29+as+total_cpu_sec%0AFROM+query_log%0AWHERE+event_time+%3E%3D+now%28%29+-+INTERVAL+7+DAY%0AGROUP+BY+is_personal_api_key_request%0AORDER+BY+query_count+DESC)

PostHog AI

```sql
SELECT
    is_personal_api_key_request,
    count() as query_count,
    count(DISTINCT created_by) as unique_users,
    avg(query_duration_ms) as avg_duration,
    sum(read_bytes) / 1024 / 1024 as total_mb_read,
    sum(cpu_microseconds / 1e6) as total_cpu_sec
FROM query_log
WHERE event_time >= now() - INTERVAL 7 DAY
GROUP BY is_personal_api_key_request
ORDER BY query_count DESC
```

For more specific tracking, you can monitor personal API key usage by specific keys (`api_key_mask` and `api_key_label` are only available when `is_personal_api_key_request` is `true`).

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT%0A++++api_key_label%2C%0A++++api_key_mask%2C%0A++++count%28%29+as+queries_run%2C%0A++++avg%28query_duration_ms%29+as+avg_duration_ms%2C%0A++++sum%28read_rows%29+as+total_rows_processed%2C%0A++++sum%28cpu_microseconds+%2F+1e6%29+as+total_cpu_sec%2C%0A++++min%28query_start_time%29+as+first_access%2C%0A++++max%28query_start_time%29+as+last_access%0AFROM+query_log%0AWHERE+is_personal_api_key_request+%3D+true%0A++AND+event_time+%3E%3D+now%28%29+-+INTERVAL+24+HOUR%0AGROUP+BY+api_key_label%2C+api_key_mask%0AORDER+BY+queries_run+DESC)

PostHog AI

```sql
SELECT
    api_key_label,
    api_key_mask,
    count() as queries_run,
    avg(query_duration_ms) as avg_duration_ms,
    sum(read_rows) as total_rows_processed,
    sum(cpu_microseconds / 1e6) as total_cpu_sec,
    min(query_start_time) as first_access,
    max(query_start_time) as last_access
FROM query_log
WHERE is_personal_api_key_request = true
  AND event_time >= now() - INTERVAL 24 HOUR
GROUP BY api_key_label, api_key_mask
ORDER BY queries_run DESC
```

To understand which keys are most active, you can see daily personal API key usage stats for the last 30 days.

SQL

[Run in PostHog](https://us.posthog.com/sql?open_query=SELECT%0A++++event_date%2C%0A++++count%28%29+as+daily_queries%2C%0A++++count%28DISTINCT+api_key_mask%29+as+unique_api_keys%2C%0A++++avg%28query_duration_ms%29+as+avg_duration_ms%2C%0A++++avg%28memory_usage%29+%2F+1024+%2F+1024+as+avg_mb_memory%2C%0A++++sum%28read_bytes%29+%2F+1024+%2F+1024+as+total_mb_read%2C%0A++++sum%28cpu_microseconds+%2F+1e6%29+as+total_cpu_sec%0AFROM+query_log%0AWHERE+is_personal_api_key_request+%3D+true%0A++AND+event_time+%3E%3D+now%28%29+-+INTERVAL+30+DAY%0AGROUP+BY+event_date%0AORDER+BY+event_date+DESC)

PostHog AI

```sql
SELECT
    event_date,
    count() as daily_queries,
    count(DISTINCT api_key_mask) as unique_api_keys,
    avg(query_duration_ms) as avg_duration_ms,
    avg(memory_usage) / 1024 / 1024 as avg_mb_memory,
    sum(read_bytes) / 1024 / 1024 as total_mb_read,
    sum(cpu_microseconds / 1e6) as total_cpu_sec
FROM query_log
WHERE is_personal_api_key_request = true
  AND event_time >= now() - INTERVAL 30 DAY
GROUP BY event_date
ORDER BY event_date DESC
```

## Query log and its relationship to internal tables

> **Note:** This section is mostly useful for the internal PostHog team.

The `query_log` table is a virtual table that:

-   Maps to the underlying `query_log_archive` ClickHouse table, which contains all system queries, not filtered by team
-   Automatically filters queries by `team_id` for data isolation
-   Only shows HogQL queries (`lc_query__kind = 'HogQLQuery'`)
-   Provides simplified field names and data types

This is similar to:

-   **System tables** which access ClickHouse system information for broader analysis
-   **Events tables** which correlate query performance with user activity patterns

### Technical architecture of the query log

PostHog AI

```
system.query_log (ClickHouse system table)
    ↓ (materialized view)
query_log_archive (persistent storage)
    ↓ (HogQL mapping/filtering)
query_log (HogQL table - what users access)
```

### Community questions

Ask a question

### Was this page useful?

HelpfulCould be better