perf/migrations/cdb.sql

-- This is a useful file for playing around with SQL queries against a database
-- populated with Perf data. You can use this file by running:
--
--   cockroach sql --insecure --host=localhost < ./migrations/cdb.sql
--
-- Make sure to apply the migrations the first time:
--
--   cockroach sql --insecure --host=localhost < ./migrations/cockroachdb/0001_create_initial_tables.up.sql
--
-- You should be able to run this file against the same database more than once
-- w/o error.
UPSERT INTO TraceNames (trace_id, params)
VALUES
    (
        '\xfe385b159ff55dca481069805e5ff050',
        '{"arch": "x86",   "config":"8888"}'
    ),
    (
        '\x277262a9236d571883d47dab102070bc',
        '{"arch":"x86",   "config": "565"}'
    ),
    (
        '\x0f17700460ee99c6488c2f6130804de5',
        '{"arch":"arm",   "config":"8888"}'
    ),
    (
        '\x6a5622e86c6059d74373f6a79df96054',
        '{"arch":"arm",   "config":"565"}'
    ),
    (
        '\x0d1f35f01672b2105bbc3f19adfcef67',
        '{"arch":"riscv",   "config":"565"}'
    );

UPSERT INTO TraceValues2 (trace_id, commit_number, val, source_file_id)
VALUES
    ('\xfe385b159ff55dca481069805e5ff050', 1, 1.2, 1),
    ('\xfe385b159ff55dca481069805e5ff050', 2, 1.3, 2),
    ('\xfe385b159ff55dca481069805e5ff050', 3, 1.4, 3),
    (
        '\x0d1f35f01672b2105bbc3f19adfcef67',
        256,
        1.1,
        4
    ),
    ('\x277262a9236d571883d47dab102070bc', 1, 2.2, 1),
    ('\x277262a9236d571883d47dab102070bc', 2, 2.3, 2),
    ('\x277262a9236d571883d47dab102070bc', 3, 2.4, 3),
    (
        '\x0d1f35f01672b2105bbc3f19adfcef67',
        256,
        2.1,
        4
    );

-- All trace_ids that match a particular key=value.
SELECT
    encode(trace_id, 'hex'),
    params
FROM
    TraceNames
WHERE
    params ->> 'arch' IN ('x86');

-- Retrieve matching values.
SELECT
    encode(trace_id, 'hex'),
    commit_number,
    val
FROM
    TraceValues2
WHERE
    commit_number >= 0
    AND commit_number < 255
    AND trace_id IN (
        '\xfe385b159ff55dca481069805e5ff050',
        '\x277262a9236d571883d47dab102070bc'
    );

-- Compound queries.
SELECT
    params
FROM
    TraceNames
WHERE
    params ->> 'arch' IN ('x86', 'arm')
    AND params ->> 'config' IN ('8888');

-- ParamSet for two Tiles
SELECT
    DISTINCT TraceNames.params
FROM
    TraceNames
    INNER JOIN TraceValues2 ON TraceNames.trace_id = TraceValues2.trace_id
WHERE
    TraceValues2.commit_number >= 0
    AND TraceValues2.commit_number < 512;

-- Count the number traces that are in a single tile.
SELECT
    COUNT(DISTINCT trace_id)
FROM
    TraceValues2
WHERE
    commit_number > 0
    AND commit_number < 256;

-- Most recent commit.
SELECT
    commit_number
FROM
    TraceValues2
ORDER BY
    commit_number DESC
LIMIT
    1;

-- GetSource by trace_id.
SELECT
    SourceFiles.source_file
FROM
    TraceNames
    INNER JOIN TraceValues2 ON TraceValues2.trace_id = TraceNames.trace_id
    INNER JOIN SourceFiles ON SourceFiles.source_file_id = TraceValues2.source_file_id
WHERE
    TraceNames.trace_id = '\xfe385b159ff55dca481069805e5ff050'
    AND TraceValues2.commit_number = 3;

-- Count the number of matches to a query.
SELECT
    COUNT(*)
FROM
    TraceNames
    INNER JOIN TraceValues2 ON TraceNames.trace_id = TraceValues2.trace_id
WHERE
    TraceNames.params ->> 'arch' IN ('riscv')
    AND TraceValues2.commit_number >= 256
    AND TraceValues2.commit_number < 512;

-- Fully query traces from tile based on query plan w/o the sub-select.
SELECT
    TraceNames.params,
    TraceValues2.commit_number,
    TraceValues2.val
FROM
    TraceNames
    INNER JOIN TraceValues2 ON TraceValues2.trace_id = TraceNames.trace_id
WHERE
    TraceNames.params ->> 'arch' IN ('x86')
    AND TraceNames.params ->> 'config' IN ('565', '8888')
    AND TraceValues2.commit_number >= 0
    AND TraceValues2.commit_number < 255;