Problem in query logic of some panels

[0xgouda]

This pattern of using a column and its lag to calculate the individual diffs in the specified time range is used in many panels in several dashboards:

SELECT
  $__timeGroup(time, $agg_interval),
  avg( ((seq_scan-seq_scan_lag)::numeric*3600) / extract(epoch from time - time_lag) ) as seq_scan,
  avg( ((idx_scan-idx_scan_lag)::numeric*3600) / extract(epoch from time - time_lag) ) as idx_scan
FROM (  
  SELECT
    (data->>'seq_scan')::int8 as seq_scan, lag((data->>'seq_scan')::int8) over w as seq_scan_lag,
    (data->>'idx_scan')::int8 as idx_scan, lag((data->>'idx_scan')::int8) over w as idx_scan_lag,
    time, lag(time) over w as time_lag  
  FROM
    table_stats
  WHERE
    $__timeFilter(time) AND dbname = '$dbname'
    AND tag_data @> jsonb_build_object('table_full_name', '$table_full_name')
  WINDOW w as (order by time)
) x
WHERE seq_scan >= seq_scan_lag and time > time_lag
GROUP BY 1
ORDER BY 1

The thing is, the lag() of the very first row is always null due to using $__timeFilter(time) in the inner query, so column - column_lag also evaluates to null, hence skipping that row's diff value, making the results less precise.

using $__timeFilter(time) in the inner query (current approach) the 22:55 - 23:00 chunk is empty because its row is being evaluated to null:

same panel, time interval, and everything but using $__timeFilter(time) in the outer query to avoid having the lag() of the first in-range row be null:

also for non-time-series panels this causes an imprecise drop/increase in the value.

[0xgouda]

To maintain performance for the inner query I guess it should be:

-- To be able to get the lag() of the first column included in the time range
-- as using time >= $__timeFrom() >= timestamptz will result in it being null
WITH last_out_of_range_row_time(time) AS (
    SELECT time 
    FROM table_stats
    WHERE dbname = '$dbname' AND time < $__timeFrom()::timestamptz
    ORDER BY time DESC
    LIMIT 1
)

SELECT
  $__timeGroup(time, $agg_interval),
  avg( ((seq_scan-seq_scan_lag)::numeric*3600) / extract(epoch from time - time_lag) ) as seq_scan,
  avg( ((idx_scan-idx_scan_lag)::numeric*3600) / extract(epoch from time - time_lag) ) as idx_scan
FROM (  
  SELECT
    (data->>'seq_scan')::int8 as seq_scan, lag((data->>'seq_scan')::int8) over w as seq_scan_lag,
    (data->>'idx_scan')::int8 as idx_scan, lag((data->>'idx_scan')::int8) over w as idx_scan_lag,
    time, lag(time) over w as time_lag  
  FROM
    table_stats
  WHERE 
    time <= $__timeTo()::timestamptz AND time >= (SELECT time FROM last_out_of_range_row_time)
    AND dbname = '$dbname' AND tag_data @> jsonb_build_object('table_full_name', '$table_full_name')
  WINDOW w as (order by time)
) x
WHERE seq_scan >= seq_scan_lag and time > time_lag
GROUP BY 1
ORDER BY 1

Here I wrote a CTE to get the first row just before the time interval boundaries and filter time by >= it not $__timeFilter(), so we have all the lags for our in-range rows

[0xgouda]

This is an example on the Table Scans panel in the Table Details dashboard, but this pattern is used in many other panels in several dashboards and needs to be fixed in all of them.

[0xgouda]

What do you think of this @pashagolub?