:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
import { Page } from '@playwright/test'
import { SqlInsight } from '../page-models/insights/sqlInsight'
import { expect, PlaywrightWorkspaceSetupResult, test } from '../utils/workspace-test-base'
// Manual performance benchmarks for the SQL editor on a long query: typing latency, and
// scroll latency. Not part of the CI suite (perf numbers are machine-dependent and would be
// flaky as an assertion) — gated behind RUN_PERF_BENCH so they are skipped unless run
// explicitly:
//
// RUN_PERF_BENCH=1 BASE_URL='http://localhost:8010' \
// pnpm --filter=@posthog/playwright exec playwright test sql-editor-typing-perf --workers 1
//
// Each test drives a fixed burst of input at a large query and reports main-thread long-task
// time via PerformanceObserver. To compare before/after a change, run it, `git stash` the
// change, run again, then `git stash pop` — the printed numbers are the result, not pass/fail.
// A recognizable, autocomplete-safe run of characters (no spaces/dots) typed into a comment.
const TYPE_MARKER = 'perfbenchmark'
const TYPE_CHARS = TYPE_MARKER.repeat(6) // 78 keystrokes
const TYPE_DELAY_MS = 40 // faster than the 150ms decoration debounce, so coalescing is visible
// Monaco emits a scroll event per wheel tick. Ticks land faster than a 60fps frame so that
// per-event work has to compete with painting, which is what makes coalescing measurable.
const SCROLL_TICKS = 60
const SCROLL_DELTA_PX = 120
const SCROLL_TICK_DELAY_MS = 8
interface TypingStats {
// Primary: main-thread jank. A requestAnimationFrame loop runs during the burst; whenever a
// frame is delayed past 16.7ms the main thread was too busy to paint. frameBlockedMs sums
// that overflow across the burst — it captures the many small per-keystroke parses the
// unfixed code runs, which the coarse longtask (>50ms) API misses entirely.
frameBlockedMs: number
maxFrameGapMs: number
frames: number
// Secondary: worst single interaction latency + long-task count.
eventMaxMs: number
longTasks: number
// Raw frame timestamps and labelled marks, so a single tick can be scored on its own. The
// whole-burst aggregate above averages a one-off stall over 60 ticks and hides it — a 180ms
// first tick reads as 3ms/tick, indistinguishable from noise.
frameTimes: number[]
marks: { label: string; t: number }[]
}
const FRAME_BUDGET_MS = 1000 / 60
/**
* Blocked time and worst stall within a slice of the measured window. `frames` is reported so a
* window that landed in the wrong place is obvious — a near-empty slice scores a flattering 0.
*/
function statsBetween(
stats: TypingStats,
from: number,
to: number
): { blockedMs: number; maxGapMs: number; frames: number } {
let blocked = 0
let maxGap = 0
let frames = 0
for (let i = 1; i < stats.frameTimes.length; i++) {
const t = stats.frameTimes[i]
if (t < from || t > to) {
continue
}
const gap = stats.frameTimes[i] - stats.frameTimes[i - 1]
blocked += Math.max(0, gap - FRAME_BUDGET_MS)
maxGap = Math.max(maxGap, gap)
frames++
}
return { blockedMs: Math.round(blocked), maxGapMs: Math.round(maxGap), frames }
}
async function mark(page: Page, label: string): Promise<void> {
await page.evaluate((l) => (window as any).__mark(l), label)
}
/**
* Resolve once the main thread has been idle for `frames` consecutive frames, returning how long
* that took. A fixed timeout either measures work that has not started yet or wastes time waiting;
* the returned duration is itself a datum, being how long the editor stayed busy.
*/
async function waitForQuiescence(page: Page, frames = 30, maxGapMs = 20, timeoutMs = 30000): Promise<number> {
return await page.evaluate(
([frameTarget, maxGap, timeout]) =>
new Promise<number>((resolve) => {
const start = performance.now()
let quiet = 0
let last = performance.now()
const step = (t: number): void => {
const gap = t - last
last = t
quiet = gap <= maxGap ? quiet + 1 : 0
if (quiet >= frameTarget || performance.now() - start > timeout) {
resolve(Math.round(performance.now() - start))
return
}
requestAnimationFrame(step)
}
requestAnimationFrame(step)
}),
[frames, maxGapMs, timeoutMs]
)
}
// Build a deterministic, large, deeply-nested query: 12 chained CTEs plus a 20-level nested
// subquery tail. This is what makes the per-keystroke HogQL parse expensive.
function buildLargeQuery(): string {
const ctes: string[] = []
for (let i = 0; i < 12; i++) {
const cols = Array.from({ length: 8 }, (_, j) => `col_${i}_${j} AS c${j}`).join(',\n ')
const aggs = Array.from({ length: 5 }, (_, j) => `sum(metric_${i}_${j}) AS a${j}`).join(',\n ')
const groupBy = Array.from({ length: 8 }, (_, j) => `c${j}`).join(', ')
ctes.push(
`cte_${i} AS (\n SELECT\n ${cols},\n ${aggs}\n FROM events\n` +
` WHERE col_${i}_0 > ${i * 100}\n AND timestamp > now() - INTERVAL ${i + 1} DAY\n` +
` GROUP BY ${groupBy}\n)`
)
}
let nested = 'SELECT base AS v, sum(x0) AS m FROM events GROUP BY v'
for (let d = 1; d <= 20; d++) {
nested = `SELECT v, m, max(y${d}) AS m${d} FROM (\n${nested}\n) sub_${d} WHERE m > ${d} GROUP BY v, m`
}
const joins = Array.from(
{ length: 11 },
(_, i) => ` LEFT JOIN cte_${i + 1} ON cte_${i + 1}.c0 = cte_0.c${(i + 1) % 8}`
).join('\n')
const finalCols = Array.from({ length: 12 }, (_, i) => ` cte_${i}.a${i % 5} AS metric_${i}`).join(',\n')
return [
'-- perf benchmark query',
'WITH\n' + ctes.join(',\n'),
`SELECT\n${finalCols}\nFROM cte_0\n${joins}\nLIMIT 100;`,
'-- nested tail',
nested + ';',
].join('\n')
}
// The scroll benchmark needs a different shape of query than the typing one. The active-query
// outline overlay is repositioned on every scroll event and has to bound its whole range, so the
// cost scales with how many lines that range covers. The range is the *innermost SELECT
// containing the cursor* — and `findInnermostSelectAtOffset` returns null unless at least two
// SELECTs enclose the cursor, so a flat top-level query draws no outline at all and would measure
// nothing. Hence: a trivial outer SELECT wrapping a very long inner one, with the cursor parked
// inside the inner one. Must be syntactically valid HogQL or the outline never resolves; unknown
// column names are fine, since this only needs to parse, not resolve.
const SCROLL_QUERY_LINES = 900
function buildNestedLongQuery(): string {
const columns = Array.from({ length: SCROLL_QUERY_LINES }, (_, i) => ` sum(metric_${i}) AS agg_${i}`).join(
',\n'
)
return `SELECT sub.agg_0\nFROM (\n SELECT\n${columns}\n FROM events\n) AS sub`
}
async function startMeasuring(page: Page): Promise<void> {
await page.evaluate(() => {
const w = window as any
w.__frames = []
w.__events = []
w.__longTasks = []
w.__marks = []
w.__mark = (label: string): void => w.__marks.push({ label, t: performance.now() })
const loop = (t: number): void => {
w.__frames.push(t)
w.__raf = requestAnimationFrame(loop)
}
w.__raf = requestAnimationFrame(loop)
w.__evObs = new PerformanceObserver((list) => {
for (const entry of list.getEntries()) {
w.__events.push(entry.duration)
}
})
w.__evObs.observe({ type: 'event', durationThreshold: 16 })
w.__ltObs = new PerformanceObserver((list) => {
for (const entry of list.getEntries()) {
w.__longTasks.push(entry.duration)
}
})
w.__ltObs.observe({ type: 'longtask' })
})
}
async function stopMeasuring(page: Page): Promise<TypingStats> {
return await page.evaluate(() => {
const w = window as any
cancelAnimationFrame(w.__raf)
w.__evObs?.disconnect()
w.__ltObs?.disconnect()
const frames: number[] = w.__frames || []
const FRAME_BUDGET = 1000 / 60 // 16.7ms
let blocked = 0
let maxGap = 0
for (let i = 1; i < frames.length; i++) {
const gap = frames[i] - frames[i - 1]
blocked += Math.max(0, gap - FRAME_BUDGET)
maxGap = Math.max(maxGap, gap)
}
const events: number[] = w.__events || []
return {
frameBlockedMs: Math.round(blocked),
maxFrameGapMs: Math.round(maxGap),
frames: frames.length,
eventMaxMs: Math.round(events.length ? Math.max(...events) : 0),
longTasks: (w.__longTasks || []).length,
frameTimes: frames,
marks: w.__marks || [],
}
})
}
async function goToSqlEditor(page: Page): Promise<void> {
await page.goto('/sql')
await expect(page).toHaveURL(/\/sql(?:[?#].*)?$/)
await expect(page.getByTestId('editor-scene')).toBeVisible({ timeout: 60000 })
await expect(page.getByTestId('hogql-query-editor')).toBeVisible()
await expect(page.getByText('Loading...', { exact: true })).toHaveCount(0, { timeout: 60000 })
await page
.getByRole('button', { name: 'Minimize' })
.click({ timeout: 1000 })
.catch(() => {})
}
/**
* Put the cursor immediately after the first occurrence of `needle`, via Monaco's own API.
*
* Keyboard navigation is not reliable for this: ControlOrMeta+End is Cmd+End on macOS, which Monaco
* does not bind to end-of-document, so a "go to the end and walk up" sequence silently lands
* somewhere near the top and the benchmark then edits whatever line it happened to find.
*/
async function placeCursorAfterText(page: Page, needle: string): Promise<number> {
return await page.evaluate((text) => {
const editor = (window as any).__monacoEditors?.at(-1)
if (!editor) {
throw new Error('Monaco handle missing — is __PERF_MONACO_HOOK__ set before load?')
}
const model = editor.getModel()
for (let line = 1; line <= model.getLineCount(); line++) {
const index = model.getLineContent(line).indexOf(text)
if (index !== -1) {
editor.focus()
editor.setPosition({ lineNumber: line, column: index + text.length + 1 })
editor.revealLineInCenter(line)
return line
}
}
throw new Error(`no line containing ${JSON.stringify(text)}`)
}, needle)
}
test.describe('SQL editor performance', () => {
test.describe.configure({ mode: 'serial' })
test.setTimeout(180000)
test.skip(!process.env.RUN_PERF_BENCH, 'Perf benchmark — run manually with RUN_PERF_BENCH=1')
let workspace: PlaywrightWorkspaceSetupResult | null = null
test.beforeAll(async ({ playwrightSetup }) => {
workspace = await playwrightSetup.createWorkspace({
skip_onboarding: true,
use_current_time: true,
})
})
test.beforeEach(async ({ page, playwrightSetup }) => {
// Exposes the Monaco instance so the benchmarks can place the cursor exactly and time
// editor methods. Must run before any navigation, since the app reads it as it mounts.
await page.addInitScript(() => {
;(window as any).__PERF_MONACO_HOOK__ = true
})
await playwrightSetup.loginAndNavigateToTeam(page, workspace!)
await goToSqlEditor(page)
})
test('measure main-thread blocking while typing in a long query', async ({ page }, testInfo) => {
const sqlInsight = new SqlInsight(page)
const editorArea = page.getByTestId('hogql-query-editor')
await test.step('load the long query into the editor', async () => {
await sqlInsight.writeQuery(buildLargeQuery())
// Let the initial parse + metadata request settle so warm-up cost is not measured.
await waitForQuiescence(page)
})
await test.step('type a burst inside the leading comment and measure long tasks', async () => {
await editorArea.click()
await page.keyboard.press('ControlOrMeta+Home')
await page.keyboard.press('End') // end of the first comment line — no autocomplete here
await startMeasuring(page)
await page.keyboard.type(TYPE_CHARS, { delay: TYPE_DELAY_MS })
await page.waitForTimeout(600) // let any trailing debounced work run
const stats = await stopMeasuring(page)
// Prove the keystrokes actually landed in the editor.
await expect(editorArea).toContainText(TYPE_MARKER)
const summary =
`keystrokes=${TYPE_CHARS.length} ` +
`frameBlockedMs=${stats.frameBlockedMs} maxFrameGapMs=${stats.maxFrameGapMs} ` +
`frames=${stats.frames} eventMaxMs=${stats.eventMaxMs} longTasks=${stats.longTasks}`
testInfo.annotations.push({ type: 'typing-perf', description: summary })
// eslint-disable-next-line no-console
console.log(`\n[SQL editor typing perf] ${summary}\n`)
})
})
test('measure main-thread blocking while scrolling a long query', async ({ page }, testInfo) => {
const editorArea = page.getByTestId('hogql-query-editor')
await test.step('load a long nested query into the editor', async () => {
// Load via the editor's own `q` hash param instead of typing the query in. The editor
// restores whichever query was last open, so writing into it is not reproducible — a
// stale (and much larger) document from an earlier run gets measured instead.
await page.goto('/sql#q=' + encodeURIComponent(buildNestedLongQuery()))
await expect(page.getByTestId('editor-scene')).toBeVisible({ timeout: 60000 })
await expect(editorArea).toBeVisible()
// Let the initial parse + metadata request settle so warm-up cost is not measured.
await waitForQuiescence(page)
})
await test.step('put the cursor inside the long inner SELECT', async () => {
// Anywhere inside the inner SELECT will do — the outline then spans its ~900 lines.
await placeCursorAfterText(page, 'AS agg_0')
})
await test.step('confirm the active-query outline is showing', async () => {
// Without an active outline the scroll handler early-returns and this benchmark
// measures nothing at all, so fail loudly rather than print a misleading 0.
const outline = editorArea.locator('.active-query-outline')
await expect(outline).toBeVisible({ timeout: 15000 })
})
await test.step('scroll a fixed burst and measure main-thread blocking', async () => {
const box = await editorArea.boundingBox()
expect(box).not.toBeNull()
await page.mouse.move(box!.x + box!.width / 2, box!.y + box!.height / 2)
await startMeasuring(page)
for (let i = 0; i < SCROLL_TICKS; i++) {
await mark(page, `tick-${i}`)
await page.mouse.wheel(0, SCROLL_DELTA_PX)
await page.waitForTimeout(SCROLL_TICK_DELAY_MS)
}
await mark(page, 'tick-end')
await page.waitForTimeout(600) // let any trailing work run
const stats = await stopMeasuring(page)
// Score each tick on its own. The first tick after the editor has gone idle pays costs
// the rest do not — a forced view render, a freshly invalidated layout — and the burst
// total divides that by 60 until it looks like nothing.
const markAt = (label: string): number => stats.marks.find((m) => m.label === label)?.t ?? 0
const perTick = Array.from({ length: SCROLL_TICKS }, (_, i) =>
statsBetween(stats, markAt(`tick-${i}`), markAt(i === SCROLL_TICKS - 1 ? 'tick-end' : `tick-${i + 1}`))
)
const firstTickBlockedMs = perTick[0].blockedMs
const steadyBlockedMs = perTick.slice(1).reduce((sum, t) => sum + t.blockedMs, 0)
const summary =
`wheelTicks=${SCROLL_TICKS} ` +
`frameBlockedMs=${stats.frameBlockedMs} maxFrameGapMs=${stats.maxFrameGapMs} ` +
`frames=${stats.frames} eventMaxMs=${stats.eventMaxMs} longTasks=${stats.longTasks}`
const perTickSummary =
`firstTickBlockedMs=${firstTickBlockedMs} steadyBlockedMs=${steadyBlockedMs} ` +
`steadyPerTickMs=${(steadyBlockedMs / (SCROLL_TICKS - 1)).toFixed(1)} ` +
`worstTickMs=${Math.max(...perTick.map((t) => t.blockedMs))}`
testInfo.annotations.push({ type: 'scroll-perf', description: `${summary} ${perTickSummary}` })
// eslint-disable-next-line no-console
console.log(`\n[SQL editor scroll perf] ${summary}\n[SQL editor scroll perf] ${perTickSummary}\n`)
})
})
test('measure main-thread blocking on a cursor move and a single edit', async ({ page }, testInfo) => {
// Both paths reach the HogQL parser, which runs over the whole query. A cursor move
// changes no text at all, so any blocking there is pure waste; an edit has to reparse
// once. Reported separately because they have different fixes and different budgets.
const editorArea = page.getByTestId('hogql-query-editor')
// Park just after the first column's alias, so the cursor-move phase's single ArrowDown
// lands in the same column of the next line — the two lines are the same length — which is
// just after `agg_1`. Typing there extends the alias to `agg_1x` and leaves the query valid.
// That matters: an invalid query makes the parser fail fast, which reports as a perfect
// score. (Appending at end-of-line instead would glue the character onto the *next*
// column, which is exactly how this benchmark first measured a misleading zero.)
// Note the outline only resolves with the cursor near the top of the range; the same
// placement near the bottom leaves it hidden, which is worth a look on its own.
await test.step('load a long nested query with the cursor inside the inner SELECT', async () => {
await page.goto('/sql#q=' + encodeURIComponent(buildNestedLongQuery()))
await expect(page.getByTestId('editor-scene')).toBeVisible({ timeout: 60000 })
await expect(editorArea).toBeVisible()
await waitForQuiescence(page)
await placeCursorAfterText(page, 'AS agg_0')
await expect(editorArea.locator('.active-query-outline')).toBeVisible({ timeout: 30000 })
await waitForQuiescence(page)
})
await startMeasuring(page)
// One action per phase, and the edit happens wherever the cursor move left us, so neither
// phase has to reposition — repositioning is itself a cursor move, and its parse would land
// in whichever window it happened to fall in.
await test.step('move the cursor without changing any text', async () => {
await mark(page, 'cursor-move')
await page.keyboard.press('ArrowDown')
await waitForQuiescence(page)
})
await test.step('type one character', async () => {
await mark(page, 'edit')
await page.keyboard.type('x')
await waitForQuiescence(page)
await mark(page, 'end')
})
const stats = await stopMeasuring(page)
// A missing mark would silently produce a nonsense window (and a flattering 0), so refuse
// to report rather than guess.
const markAt = (label: string): number => {
const found = stats.marks.find((m) => m.label === label)
if (!found) {
throw new Error(`benchmark mark '${label}' was never recorded`)
}
return found.t
}
const cursor = statsBetween(stats, markAt('cursor-move'), markAt('edit'))
const edit = statsBetween(stats, markAt('edit'), markAt('end'))
// Prove the keystroke landed where it was aimed. Without this an edit that missed the
// editor — or hit the wrong line — reports as a perfect score. Checked against the model
// rather than the DOM, since Monaco only renders the lines currently on screen.
const edited = await page.evaluate(
(needle) => (window as any).__monacoEditors.at(-1).getModel().getValue().includes(needle),
'AS agg_1x,'
)
expect(edited, 'expected the typed character to extend the alias on the agg_1 line').toBe(true)
const summary =
`queryLines=${SCROLL_QUERY_LINES} ` +
`cursorMoveBlockedMs=${cursor.blockedMs} cursorMoveMaxGapMs=${cursor.maxGapMs} ` +
`cursorMoveFrames=${cursor.frames} ` +
`editBlockedMs=${edit.blockedMs} editMaxGapMs=${edit.maxGapMs} editFrames=${edit.frames}`
testInfo.annotations.push({ type: 'edit-perf', description: summary })
// eslint-disable-next-line no-console
console.log(`\n[SQL editor edit perf] ${summary}\n`)
})
})