Skip to content

Instantly share code, notes, and snippets.

@hexylena

hexylena/data.json

Last active March 5, 2020 14:22
Show Gist options
  • Save hexylena/467c7726b5ab9e18c47080893dbc072e to your computer and use it in GitHub Desktop.
Save hexylena/467c7726b5ab9e18c47080893dbc072e to your computer and use it in GitHub Desktop.
Download ZIP
PEV demo data from `query queue-overview`
Raw
data.json
[
{
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parallel Aware": false,
"Startup Cost": 5.43,
"Total Cost": 5.44,
"Plan Rows": 1,
"Plan Width": 256,
"Actual Startup Time": 11598.058,
"Actual Total Time": 11598.213,
"Actual Rows": 227,
"Actual Loops": 1,
"Output": ["queue.tool_id", "queue.tool_version", "queue.destination_id", "queue.handler", "queue.state", "queue.job_runner_name", "sum(queue.count)", "queue.user_id"],
"Group Key": ["queue.tool_id", "queue.tool_version", "queue.destination_id", "queue.handler", "queue.state", "queue.job_runner_name", "queue.user_id"],
"Shared Hit Blocks": 297774,
"Shared Read Blocks": 996680,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 26,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE queue",
"Parallel Aware": false,
"Startup Cost": 5.35,
"Total Cost": 5.39,
"Plan Rows": 1,
"Plan Width": 289,
"Actual Startup Time": 11595.718,
"Actual Total Time": 11597.132,
"Actual Rows": 227,
"Actual Loops": 1,
"Output": ["regexp_replace((job.tool_id)::text, '/[0-9.a-z+-]+$'::text, ''::text)", "job.tool_version", "(COALESCE(job.destination_id, 'unknown'::character varying))::text", "(COALESCE(job.handler, 'unknown'::character varying))::text", "(job.state)::text", "(COALESCE(job.job_runner_name, 'unknown'::character varying))::text", "count(*)", "(job.user_id)::text", "job.tool_id", "job.destination_id", "job.handler", "job.state", "job.job_runner_name", "job.user_id"],
"Group Key": ["job.tool_id", "job.tool_version", "job.destination_id", "job.handler", "job.state", "job.job_runner_name", "job.user_id"],
"Shared Hit Blocks": 297774,
"Shared Read Blocks": 996680,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 26,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 5.35,
"Total Cost": 5.35,
"Plan Rows": 1,
"Plan Width": 89,
"Actual Startup Time": 11595.499,
"Actual Total Time": 11595.625,
"Actual Rows": 436,
"Actual Loops": 1,
"Output": ["job.tool_version", "job.tool_id", "job.destination_id", "job.handler", "job.state", "job.job_runner_name", "job.user_id"],
"Sort Key": ["job.tool_id", "job.tool_version", "job.destination_id", "job.handler", "job.state", "job.job_runner_name", "job.user_id"],
"Sort Method": "quicksort",
"Sort Space Used": 109,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 297774,
"Shared Read Blocks": 996680,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 26,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "job",
"Schema": "galaxy",
"Alias": "job",
"Startup Cost": 4.32,
"Total Cost": 5.34,
"Plan Rows": 1,
"Plan Width": 89,
"Actual Startup Time": 5600.821,
"Actual Total Time": 11590.241,
"Actual Rows": 436,
"Actual Loops": 1,
"Output": ["job.tool_version", "job.tool_id", "job.destination_id", "job.handler", "job.state", "job.job_runner_name", "job.user_id"],
"Recheck Cond": "(((job.state)::text = 'running'::text) OR ((job.state)::text = 'queued'::text) OR ((job.state)::text = 'new'::text))",
"Rows Removed by Index Recheck": 4268864,
"Exact Heap Blocks": 114130,
"Lossy Heap Blocks": 1161816,
"Shared Hit Blocks": 297768,
"Shared Read Blocks": 996680,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 26,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "BitmapOr",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 4.32,
"Total Cost": 4.32,
"Plan Rows": 1,
"Plan Width": 0,
"Actual Startup Time": 1502.776,
"Actual Total Time": 1502.776,
"Actual Rows": 0,
"Actual Loops": 1,
"Shared Hit Blocks": 2376,
"Shared Read Blocks": 16126,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Index Name": "ix_job_state",
"Startup Cost": 0.00,
"Total Cost": 1.44,
"Plan Rows": 1,
"Plan Width": 0,
"Actual Startup Time": 702.979,
"Actual Total Time": 702.979,
"Actual Rows": 1556348,
"Actual Loops": 1,
"Index Cond": "((job.state)::text = 'running'::text)",
"Shared Hit Blocks": 1408,
"Shared Read Blocks": 4056,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Index Name": "ix_job_state",
"Startup Cost": 0.00,
"Total Cost": 1.44,
"Plan Rows": 1,
"Plan Width": 0,
"Actual Startup Time": 422.703,
"Actual Total Time": 422.703,
"Actual Rows": 2285567,
"Actual Loops": 1,
"Index Cond": "((job.state)::text = 'queued'::text)",
"Shared Hit Blocks": 968,
"Shared Read Blocks": 6964,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Index Name": "ix_partial_job_state_new",
"Startup Cost": 0.00,
"Total Cost": 1.44,
"Plan Rows": 1,
"Plan Width": 0,
"Actual Startup Time": 377.082,
"Actual Total Time": 377.082,
"Actual Rows": 1862539,
"Actual Loops": 1,
"Index Cond": "((job.state)::text = 'new'::text)",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 5106,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "queue",
"Alias": "queue",
"Startup Cost": 0.00,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 232,
"Actual Startup Time": 11595.726,
"Actual Total Time": 11597.636,
"Actual Rows": 227,
"Actual Loops": 1,
"Output": ["queue.tool_id", "queue.tool_version", "queue.destination_id", "queue.handler", "queue.state", "queue.job_runner_name", "queue.count", "queue.user_id"],
"Shared Hit Blocks": 297774,
"Shared Read Blocks": 996680,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 26,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
},
"Planning Time": 2.915,
"Triggers": [
],
"Execution Time": 11601.076
}
]
Raw
query
WITH
queue
AS (
SELECT
regexp_replace(tool_id, '/[0-9.a-z+-]+$', '')::STRING AS tool_id,
tool_version::STRING,
COALESCE(destination_id, 'unknown')::STRING AS destination_id,
COALESCE(handler, 'unknown')::STRING AS handler,
state::STRING,
COALESCE(job_runner_name, 'unknown')::STRING AS job_runner_name,
count(*) AS count,
user_id::STRING AS user_id
FROM
job
WHERE
state = 'running' OR state = 'queued' OR state = 'new'
GROUP BY
tool_id, tool_version, destination_id, handler, state, job_runner_name, user_id
)
SELECT
tool_id, tool_version, destination_id, handler, state, job_runner_name, sum(count), user_id
FROM
queue
GROUP BY
tool_id, tool_version, destination_id, handler, state, job_runner_name, user_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment