1. Home
  2. Knowledge Base
  3. Comparing query_log With Different Intervals

Comparing query_log With Different Intervals

The query filters the data from the ‘system.query_log’ table to include only entries of type 2 (QueryFinish) and 4 (ExceptionWhileProcessing) and groups the results by the ‘normalized_query_hash’. The results are then sorted by the difference between ‘SelectedRangesAfter’ and ‘SelectedRangesBefore’ in descending order and limited to the top 10 rows.

The provided output shows that all metrics have values of 0, which suggests that there may be no data matching the specified criteria in the database for the given time intervals and query types. Further investigation may be needed to determine the cause of these zero values.

    toStartOfInterval(event_time, toIntervalMinute(5)) = '2023-06-30 13:00:00' AS before,
    toStartOfInterval(event_time, toIntervalMinute(5)) = '2023-06-30 15:00:00' AS after
    anyIf(query, before) AS QueryBefore,
    anyIf(query, after) AS QueryAfter,
    countIf(before) AS CountBefore,
    sumIf(query_duration_ms, before) / 1000 AS QueriesDurationBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'RealTimeMicroseconds')], before) / 1000000 AS RealTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')], before) / 1000000 AS UserTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')], before) / 1000000 AS SystemTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'DiskReadElapsedMicroseconds')], before) / 1000000 AS DiskReadTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'DiskWriteElapsedMicroseconds')], before) / 1000000 AS DiskWriteTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'NetworkSendElapsedMicroseconds')], before) / 1000000 AS NetworkSendTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'NetworkReceiveElapsedMicroseconds')], before) / 1000000 AS NetworkReceiveTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'ZooKeeperWaitMicroseconds')], before) / 1000000 AS ZooKeeperWaitTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSIOWaitMicroseconds')], before) / 1000000 AS OSIOWaitTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSCPUWaitMicroseconds')], before) / 1000000 AS OSCPUWaitTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')], before) / 1000000 AS OSCPUVirtualTimeBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SelectedBytes')], before) AS SelectedBytesBefore,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SelectedRanges')], before) AS SelectedRangesBefore,
    sumIf(read_rows, before) AS ReadRowsBefore,
    formatReadableSize(sumIf(read_bytes, before) AS ReadBytesBefore),
    sumIf(written_rows, before) AS WrittenTowsBefore,
    formatReadableSize(sumIf(written_bytes, before)) AS WrittenBytesBefore,
    sumIf(result_rows, before) AS ResultRowsBefore,
    formatReadableSize(sumIf(result_bytes, before)) AS ResultBytesBefore,
    countIf(after) AS CountAfter,
    sumIf(query_duration_ms, after) / 1000 AS QueriesDurationAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'RealTimeMicroseconds')], after) / 1000000 AS RealTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')], after) / 1000000 AS UserTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')], after) / 1000000 AS SystemTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'DiskReadElapsedMicroseconds')], after) / 1000000 AS DiskReadTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'DiskWriteElapsedMicroseconds')], after) / 1000000 AS DiskWriteTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'NetworkSendElapsedMicroseconds')], after) / 1000000 AS NetworkSendTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'NetworkReceiveElapsedMicroseconds')], after) / 1000000 AS NetworkReceiveTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'ZooKeeperWaitMicroseconds')], after) / 1000000 AS ZooKeeperWaitTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSIOWaitMicroseconds')], after) / 1000000 AS OSIOWaitTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSCPUWaitMicroseconds')], after) / 1000000 AS OSCPUWaitTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'OSCPUVirtualTimeMicroseconds')], after) / 1000000 AS OSCPUVirtualTimeAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SelectedBytes')], after) AS SelectedBytesAfter,
    sumIf(ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SelectedRanges')], after) AS SelectedRangesAfter,
    sumIf(read_rows, after) AS ReadRowsAfter,
    formatReadableSize(sumIf(read_bytes, after) AS ReadBytesAfter),
    sumIf(written_rows, after) AS WrittenTowsAfter,
    formatReadableSize(sumIf(written_bytes, after)) AS WrittenBytesAfter,
    sumIf(result_rows, after) AS ResultRowsAfter,
    formatReadableSize(sumIf(result_bytes, after)) AS ResultBytesAfter
FROM system.query_log
WHERE (before OR after) AND (type IN (2, 4))
GROUP BY normalized_query_hash
ORDER BY SelectedRangesAfter - SelectedRangesBefore DESC
FORMAT Vertical


normalized_query_hash:                         0
CountBefore:                                   0
QueriesDurationBefore:                         0
RealTimeBefore:                                0
UserTimeBefore:                                0
SystemTimeBefore:                              0
DiskReadTimeBefore:                            0
DiskWriteTimeBefore:                           0
NetworkSendTimeBefore:                         0
NetworkReceiveTimeBefore:                      0
ZooKeeperWaitTimeBefore:                       0
OSIOWaitTimeBefore:                            0
OSCPUWaitTimeBefore:                           0
OSCPUVirtualTimeBefore:                        0
SelectedBytesBefore:                           0
SelectedRangesBefore:                          0
ReadRowsBefore:                                0
formatReadableSize(sumIf(read_bytes, before)): 0.00 B
WrittenTowsBefore:                             0
WrittenBytesBefore:                            0.00 B
ResultRowsBefore:                              0
ResultBytesBefore:                             0.00 B
CountAfter:                                    0
QueriesDurationAfter:                          0
RealTimeAfter:                                 0
UserTimeAfter:                                 0
SystemTimeAfter:                               0
DiskReadTimeAfter:                             0
DiskWriteTimeAfter:                            0
NetworkSendTimeAfter:                          0
NetworkReceiveTimeAfter:                       0
ZooKeeperWaitTimeAfter:                        0
OSIOWaitTimeAfter:                             0
OSCPUWaitTimeAfter:                            0
OSCPUVirtualTimeAfter:                         0
SelectedBytesAfter:                            0
SelectedRangesAfter:                           0
ReadRowsAfter:                                 0
formatReadableSize(sumIf(read_bytes, after)):  0.00 B
WrittenTowsAfter:                              0
WrittenBytesAfter:                             0.00 B
ResultRowsAfter:                               0
ResultBytesAfter:                              0.00 B
  • QueryBefore and QueryAfter: The actual SQL queries executed.
  • CountBefore and CountAfter: The count of queries in each interval.
  • QueriesDurationBefore and QueriesDurationAfter: The total duration of queries in seconds.
  • RealTimeBefore and RealTimeAfter: The real-time duration in seconds.
  • UserTimeBefore and UserTimeAfter: The user CPU time in seconds.
  • SystemTimeBefore and SystemTimeAfter: The system CPU time in seconds.
  • DiskReadTimeBefore and DiskReadTimeAfter: Disk read time in seconds.
  • DiskWriteTimeBefore and DiskWriteTimeAfter: Disk write time in seconds.
  • NetworkSendTimeBefore and NetworkSendTimeAfter: Network send time in seconds.
  • NetworkReceiveTimeBefore and NetworkReceiveTimeAfter: Network receive time in seconds.
  • ZooKeeperWaitTimeBefore and ZooKeeperWaitTimeAfter: ZooKeeper wait time in seconds.
  • OSIOWaitTimeBefore and OSIOWaitTimeAfter: OS I/O wait time in seconds.
  • OSCPUWaitTimeBefore and OSCPUWaitTimeAfter: OS CPU wait time in seconds.
  • OSCPUVirtualTimeBefore and OSCPUVirtualTimeAfter: OS CPU virtual time in seconds.
  • SelectedBytesBefore and SelectedBytesAfter: The total selected bytes.
  • SelectedRangesBefore and SelectedRangesAfter: The total selected ranges.
  • ReadRowsBefore and ReadRowsAfter: The total rows read.
  • ReadBytesBefore and ReadBytesAfter: The total bytes read in a human-readable format.
  • WrittenTowsBefore and WrittenTowsAfter: The total rows written.
  • WrittenBytesBefore and WrittenBytesAfter: The total bytes written in a human-readable format.
  • ResultRowsBefore and ResultRowsAfter: The total result rows.
  • ResultBytesBefore and ResultBytesAfter: The total result bytes in a human-readable format.
Was this article helpful?


In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at


Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.