Skip to content

Instantly share code, notes, and snippets.

@felixarntz
Created October 26, 2023 23:36
Show Gist options
  • Save felixarntz/783c3a967afc61c04d3579a58d3ecbac to your computer and use it in GitHub Desktop.
Save felixarntz/783c3a967afc61c04d3579a58d3ecbac to your computer and use it in GitHub Desktop.
HTTP Archive query comparing Server-Timing `wp-total` (from Performance Lab plugin) with Time to First Byte
CREATE TEMPORARY FUNCTION GET_SERVER_TIMING_HEADER(headers ARRAY<STRUCT<name STRING, value STRING>>) RETURNS STRING AS (
IFNULL((
SELECT
value
FROM
UNNEST(headers) AS header
WHERE
LOWER(header.name) = 'server-timing'
LIMIT 1
), '')
);
WITH serverTimings AS (
SELECT
date,
IF(client = "mobile", "phone", "desktop") AS device,
page AS url,
httparchive.fn.PARSE_SERVER_TIMING_HEADER(GET_SERVER_TIMING_HEADER(response_headers)) AS serverTiming,
JSON_EXTRACT(lighthouse, "$.audits.server-response-time.numericValue") AS lighthouseTtfb
FROM
`httparchive.all.pages`,
UNNEST(technologies) AS technology
INNER JOIN
`httparchive.all.requests`
USING
(date, client, page, is_root_page)
WHERE
date = CAST("2023-10-01" AS DATE)
AND technology.technology = "Performance Lab"
AND is_main_document = TRUE
)
SELECT
date,
device,
url,
CAST(serverTimingRecord.dur AS FLOAT64) AS wpTotal,
CAST(lighthouseTtfb AS FLOAT64) AS ttfb,
CAST(serverTimingRecord.dur AS FLOAT64) / CAST(lighthouseTtfb AS FLOAT64) AS pctWpTotalOfTtfb
FROM
serverTimings,
UNNEST(serverTiming) AS serverTimingRecord
WHERE
serverTimingRecord.name = 'wp-total'
AND lighthouseTtfb IS NOT NULL
ORDER BY
device,
url
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment