Line data    Source code

       1              : import { QueryScope } from './hyper_log_stats.js';
       2              : 
       3              : // The tables of the queries of a connection as SQL over the query ends of the extract of the Hyper log.
       4              : // The overview runs them with its client context, they are listed as queries of the app.
       5              : 
       6              : /// The rows that a table of queries lists
       7            1 : const MAX_ROWS = 100;
       8              : 
       9              : /// A number rounded to a number of decimals. A numeric without a scale has none in Hyper, it would round to integers.
      10            1 : const rounded = (value: string, decimals: number) => `round((${value})::numeric(38, 6), ${decimals})::double precision`;
      11              : 
      12              : /// Seconds of a column of the query ends as milliseconds, rounded to hundredths
      13            1 : const ms = (seconds: string) => rounded(`${seconds} * 1000`, 2);
      14              : 
      15              : /// The order of the query ends, newest first. The log times of Hyper in Wasm have millisecond resolution, the request
      16              : /// number orders the queries of a session that end within the same millisecond. It is text, padded to compare as number.
      17            1 : const NEWEST_FIRST = `epoch_ms DESC, lpad(req, 20, '0') DESC`;
      18              : 
      19              : /// The predicate of the query ends of a scope
      20            1 : function scopePredicate(scope: QueryScope): string {
      21            1 :     switch (scope) {
      22            1 :         case QueryScope.User: return 'NOT sent_by_app';
      23            1 :         case QueryScope.App: return 'sent_by_app';
      24            1 :         case QueryScope.All: return 'TRUE';
      25            1 :     }
      26            1 : }
      27              : 
      28              : /// The query ends of a scope that ended since a time, in milliseconds since the epoch.
      29              : /// The query ends are the rows of queryEndsQuery().
      30            1 : function selectQueryEnds(queryEnds: string, scope: QueryScope, since: number): string {
      31            1 :     return `SELECT * FROM (${queryEnds}) ends WHERE epoch_ms >= ${since} AND ${scopePredicate(scope)}`;
      32            1 : }
      33              : 
      34              : /// The finished queries, newest first.
      35            1 : export function recentQueriesSql(queryEnds: string, scope: QueryScope, since: number): string {
      36            1 :     return `
      37              :         SELECT
      38              :             local_ts::time AS time,
      39              :             CASE WHEN succeeded THEN 'ok' ELSE coalesce(error_code, 'failed') END AS status,
      40              :             coalesce(query, statement) AS query,
      41            1 :             ${ms('elapsed')} AS elapsed_ms,
      42            1 :             ${ms('cpu_time')} AS cpu_ms,
      43            1 :             ${ms('wait_time')} AS wait_ms,
      44              :             processed_rows,
      45              :             peak_memory_mb AS memory_mb,
      46              :             client_query_type AS source,
      47              :             error_message AS error
      48            1 :         FROM (${selectQueryEnds(queryEnds, scope, since)}) runs
      49            1 :         ORDER BY ${NEWEST_FIRST}
      50            1 :         LIMIT ${MAX_ROWS}`;
      51            1 : }
      52              : 
      53              : /// The shapes of queries with the text of their newest query, most elapsed time first.
      54              : /// Queries that only differ in their literals or comments share their shape, see hyperLogQueryEndsTable().
      55              : /// The shape column is the part of the query hash that they share, NULL for queries without a hash.
      56            1 : export function queryShapesSql(queryEnds: string, scope: QueryScope, since: number): string {
      57            1 :     return `
      58              :         WITH newest AS (
      59            1 :             SELECT *, row_number() OVER (PARTITION BY query_shape ORDER BY ${NEWEST_FIRST}) = 1 AS is_newest
      60            1 :             FROM (${selectQueryEnds(queryEnds, scope, since)}) ends
      61              :         )
      62              :         SELECT
      63              :             max(CASE WHEN is_newest THEN coalesce(query, statement) END) AS query,
      64              :             count(*)::integer AS queries,
      65              :             (count(*) FILTER (WHERE NOT succeeded))::integer AS failed,
      66            1 :             ${ms('sum(elapsed)')} AS total_ms,
      67            1 :             ${rounded('100 * sum(elapsed) / nullif(sum(sum(elapsed)) OVER (), 0)', 1)} AS share_pct,
      68            1 :             ${ms('avg(elapsed)')} AS mean_ms,
      69              :             sum(processed_rows)::bigint AS processed_rows,
      70              :             max(peak_memory_mb) AS memory_mb,
      71              :             CASE WHEN query_shape LIKE 'text:%' THEN NULL ELSE query_shape END AS shape
      72              :         FROM newest
      73              :         GROUP BY query_shape
      74              :         ORDER BY total_ms DESC
      75            1 :         LIMIT ${MAX_ROWS}`;
      76            1 : }