Skip to content

Instantly share code, notes, and snippets.

@ddohler

ddohler/analyze1.json

Created July 10, 2017 18:51
Show Gist options
  • Save ddohler/1431369c6388428a14edc73276d1ff25 to your computer and use it in GitHub Desktop.
Save ddohler/1431369c6388428a14edc73276d1ff25 to your computer and use it in GitHub Desktop.
Download ZIP
DRIVER Query Plans
Raw
analyze1.json
[
{
"Plan": {
"Node Type": "Hash Join",
"Join Type": "Inner",
"Startup Cost": 85.62,
"Total Cost": 485.81,
"Plan Rows": 35,
"Plan Width": 2676,
"Actual Startup Time": 3.867,
"Actual Total Time": 68.913,
"Actual Rows": 5769,
"Actual Loops": 1,
"Output": ["ashlar_record.uuid", "ashlar_record.created", "ashlar_record.modified", "ashlar_record.occurred_from", "ashlar_record.occurred_to", "ashlar_record.geom", "ashlar_record.data", "ashlar_record.schema_id", "ashlar_record.location_text", "ashlar_record.city", "ashlar_record.city_district", "ashlar_record.county", "ashlar_record.neighborhood", "ashlar_record.road", "ashlar_record.state", "ashlar_record.light", "ashlar_record.weather", "ashlar_record.archived", "ashlar_recordschema.uuid", "ashlar_recordschema.created", "ashlar_recordschema.modified", "ashlar_recordschema.version", "ashlar_recordschema.schema", "ashlar_recordschema.next_version_id", "ashlar_recordschema.record_type_id"],
"Hash Cond": "(ashlar_record.schema_id = ashlar_recordschema.uuid)",
"Shared Hit Blocks": 2206,
"Shared Read Blocks": 0,
"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 Heap Scan",
"Parent Relationship": "Outer",
"Relation Name": "ashlar_record",
"Schema": "public",
"Alias": "ashlar_record",
"Startup Cost": 80.89,
"Total Cost": 480.52,
"Plan Rows": 58,
"Plan Width": 2155,
"Actual Startup Time": 3.608,
"Actual Total Time": 35.477,
"Actual Rows": 5769,
"Actual Loops": 1,
"Output": ["ashlar_record.uuid", "ashlar_record.created", "ashlar_record.modified", "ashlar_record.occurred_from", "ashlar_record.occurred_to", "ashlar_record.geom", "ashlar_record.data", "ashlar_record.schema_id", "ashlar_record.location_text", "ashlar_record.city", "ashlar_record.city_district", "ashlar_record.county", "ashlar_record.neighborhood", "ashlar_record.road", "ashlar_record.state", "ashlar_record.light", "ashlar_record.weather", "ashlar_record.archived"],
"Recheck Cond": "((ashlar_record.data @> '{\"accidentDetails\": {\"Collision type\": \"Side swipe\"}}'::jsonb) OR (ashlar_record.data @> '{\"accidentDetails\": {\"Collision type\": [\"Side swipe\"]}}'::jsonb))",
"Rows Removed by Index Recheck": 0,
"Filter": "(NOT ashlar_record.archived)",
"Rows Removed by Filter": 0,
"Exact Heap Blocks": 2156,
"Lossy Heap Blocks": 0,
"Shared Hit Blocks": 2199,
"Shared Read Blocks": 0,
"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": "BitmapOr",
"Parent Relationship": "Outer",
"Startup Cost": 80.89,
"Total Cost": 80.89,
"Plan Rows": 115,
"Plan Width": 0,
"Actual Startup Time": 2.960,
"Actual Total Time": 2.960,
"Actual Rows": 0,
"Actual Loops": 1,
"Shared Hit Blocks": 43,
"Shared Read Blocks": 0,
"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",
"Index Name": "ashlar_record_data_gin",
"Startup Cost": 0.00,
"Total Cost": 40.43,
"Plan Rows": 58,
"Plan Width": 0,
"Actual Startup Time": 2.750,
"Actual Total Time": 2.750,
"Actual Rows": 5769,
"Actual Loops": 1,
"Index Cond": "(ashlar_record.data @> '{\"accidentDetails\": {\"Collision type\": \"Side swipe\"}}'::jsonb)",
"Shared Hit Blocks": 29,
"Shared Read Blocks": 0,
"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",
"Index Name": "ashlar_record_data_gin",
"Startup Cost": 0.00,
"Total Cost": 40.43,
"Plan Rows": 58,
"Plan Width": 0,
"Actual Startup Time": 0.196,
"Actual Total Time": 0.196,
"Actual Rows": 0,
"Actual Loops": 1,
"Index Cond": "(ashlar_record.data @> '{\"accidentDetails\": {\"Collision type\": [\"Side swipe\"]}}'::jsonb)",
"Shared Hit Blocks": 14,
"Shared Read Blocks": 0,
"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": "Hash",
"Parent Relationship": "Inner",
"Startup Cost": 4.45,
"Total Cost": 4.45,
"Plan Rows": 22,
"Plan Width": 521,
"Actual Startup Time": 0.220,
"Actual Total Time": 0.220,
"Actual Rows": 32,
"Actual Loops": 1,
"Output": ["ashlar_recordschema.uuid", "ashlar_recordschema.created", "ashlar_recordschema.modified", "ashlar_recordschema.version", "ashlar_recordschema.schema", "ashlar_recordschema.next_version_id", "ashlar_recordschema.record_type_id"],
"Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 4,
"Shared Hit Blocks": 4,
"Shared Read Blocks": 0,
"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": "Seq Scan",
"Parent Relationship": "Outer",
"Relation Name": "ashlar_recordschema",
"Schema": "public",
"Alias": "ashlar_recordschema",
"Startup Cost": 0.00,
"Total Cost": 4.45,
"Plan Rows": 22,
"Plan Width": 521,
"Actual Startup Time": 0.019,
"Actual Total Time": 0.123,
"Actual Rows": 32,
"Actual Loops": 1,
"Output": ["ashlar_recordschema.uuid", "ashlar_recordschema.created", "ashlar_recordschema.modified", "ashlar_recordschema.version", "ashlar_recordschema.schema", "ashlar_recordschema.next_version_id", "ashlar_recordschema.record_type_id"],
"Filter": "(ashlar_recordschema.record_type_id = '75903939-0ab0-4de8-9e21-dc92631975d5'::uuid)",
"Rows Removed by Filter": 14,
"Shared Hit Blocks": 4,
"Shared Read Blocks": 0,
"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
}
]
}
]
},
"Planning Time": 2.358,
"Triggers": [
],
"Execution Time": 83.898
}
]
Raw
analyze2.json
[
{
"Plan": {
"Node Type": "Nested Loop",
"Join Type": "Inner",
"Startup Cost": 120.94,
"Total Cost": 134.06,
"Plan Rows": 1,
"Plan Width": 2676,
"Actual Startup Time": 9.099,
"Actual Total Time": 301.005,
"Actual Rows": 1494,
"Actual Loops": 1,
"Output": ["ashlar_record.uuid", "ashlar_record.created", "ashlar_record.modified", "ashlar_record.occurred_from", "ashlar_record.occurred_to", "ashlar_record.geom", "ashlar_record.data", "ashlar_record.schema_id", "ashlar_record.location_text", "ashlar_record.city", "ashlar_record.city_district", "ashlar_record.county", "ashlar_record.neighborhood", "ashlar_record.road", "ashlar_record.state", "ashlar_record.light", "ashlar_record.weather", "ashlar_record.archived", "ashlar_recordschema.uuid", "ashlar_recordschema.created", "ashlar_recordschema.modified", "ashlar_recordschema.version", "ashlar_recordschema.schema", "ashlar_recordschema.next_version_id", "ashlar_recordschema.record_type_id"],
"Join Filter": "(ashlar_record.schema_id = ashlar_recordschema.uuid)",
"Rows Removed by Join Filter": 46314,
"Shared Hit Blocks": 7252,
"Shared Read Blocks": 0,
"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 Heap Scan",
"Parent Relationship": "Outer",
"Relation Name": "ashlar_record",
"Schema": "public",
"Alias": "ashlar_record",
"Startup Cost": 120.94,
"Total Cost": 129.34,
"Plan Rows": 1,
"Plan Width": 2155,
"Actual Startup Time": 9.063,
"Actual Total Time": 24.148,
"Actual Rows": 1494,
"Actual Loops": 1,
"Output": ["ashlar_record.uuid", "ashlar_record.created", "ashlar_record.modified", "ashlar_record.occurred_from", "ashlar_record.occurred_to", "ashlar_record.geom", "ashlar_record.data", "ashlar_record.schema_id", "ashlar_record.location_text", "ashlar_record.city", "ashlar_record.city_district", "ashlar_record.county", "ashlar_record.neighborhood", "ashlar_record.road", "ashlar_record.state", "ashlar_record.light", "ashlar_record.weather", "ashlar_record.archived"],
"Recheck Cond": "((ashlar_record.geom && '0103000020E610000001000000050000000000000020D15D40372560CF1F6C28400000000020D15D40E3B28F78DDA2304000000000A09B5E40E3B28F78DDA2304000000000A09B5E40372560CF1F6C28400000000020D15D40372560CF1F6C2840'::geometry) AND ((ashlar_record.data @> '{\"accidentDetails\": {\"Collision type\": \"Side swipe\"}}'::jsonb) OR (ashlar_record.data @> '{\"accidentDetails\": {\"Collision type\": [\"Side swipe\"]}}'::jsonb)))",
"Rows Removed by Index Recheck": 0,
"Filter": "((NOT ashlar_record.archived) AND _st_intersects(ashlar_record.geom, '0103000020E610000001000000050000000000000020D15D40372560CF1F6C28400000000020D15D40E3B28F78DDA2304000000000A09B5E40E3B28F78DDA2304000000000A09B5E40372560CF1F6C28400000000020D15D40372560CF1F6C2840'::geometry))",
"Rows Removed by Filter": 0,
"Exact Heap Blocks": 959,
"Lossy Heap Blocks": 0,
"Shared Hit Blocks": 1276,
"Shared Read Blocks": 0,
"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": "BitmapAnd",
"Parent Relationship": "Outer",
"Startup Cost": 120.94,
"Total Cost": 120.94,
"Plan Rows": 2,
"Plan Width": 0,
"Actual Startup Time": 8.810,
"Actual Total Time": 8.810,
"Actual Rows": 0,
"Actual Loops": 1,
"Shared Hit Blocks": 317,
"Shared Read Blocks": 0,
"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",
"Index Name": "ashlar_record_geom_id",
"Startup Cost": 0.00,
"Total Cost": 39.82,
"Plan Rows": 1006,
"Plan Width": 0,
"Actual Startup Time": 5.716,
"Actual Total Time": 5.716,
"Actual Rows": 36696,
"Actual Loops": 1,
"Index Cond": "(ashlar_record.geom && '0103000020E610000001000000050000000000000020D15D40372560CF1F6C28400000000020D15D40E3B28F78DDA2304000000000A09B5E40E3B28F78DDA2304000000000A09B5E40372560CF1F6C28400000000020D15D40372560CF1F6C2840'::geometry)",
"Shared Hit Blocks": 274,
"Shared Read Blocks": 0,
"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": "BitmapOr",
"Parent Relationship": "Member",
"Startup Cost": 80.86,
"Total Cost": 80.86,
"Plan Rows": 115,
"Plan Width": 0,
"Actual Startup Time": 2.664,
"Actual Total Time": 2.664,
"Actual Rows": 0,
"Actual Loops": 1,
"Shared Hit Blocks": 43,
"Shared Read Blocks": 0,
"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",
"Index Name": "ashlar_record_data_gin",
"Startup Cost": 0.00,
"Total Cost": 40.43,
"Plan Rows": 58,
"Plan Width": 0,
"Actual Startup Time": 2.541,
"Actual Total Time": 2.541,
"Actual Rows": 5769,
"Actual Loops": 1,
"Index Cond": "(ashlar_record.data @> '{\"accidentDetails\": {\"Collision type\": \"Side swipe\"}}'::jsonb)",
"Shared Hit Blocks": 29,
"Shared Read Blocks": 0,
"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",
"Index Name": "ashlar_record_data_gin",
"Startup Cost": 0.00,
"Total Cost": 40.43,
"Plan Rows": 58,
"Plan Width": 0,
"Actual Startup Time": 0.113,
"Actual Total Time": 0.113,
"Actual Rows": 0,
"Actual Loops": 1,
"Index Cond": "(ashlar_record.data @> '{\"accidentDetails\": {\"Collision type\": [\"Side swipe\"]}}'::jsonb)",
"Shared Hit Blocks": 14,
"Shared Read Blocks": 0,
"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": "Seq Scan",
"Parent Relationship": "Inner",
"Relation Name": "ashlar_recordschema",
"Schema": "public",
"Alias": "ashlar_recordschema",
"Startup Cost": 0.00,
"Total Cost": 4.45,
"Plan Rows": 22,
"Plan Width": 521,
"Actual Startup Time": 0.005,
"Actual Total Time": 0.093,
"Actual Rows": 32,
"Actual Loops": 1494,
"Output": ["ashlar_recordschema.uuid", "ashlar_recordschema.created", "ashlar_recordschema.modified", "ashlar_recordschema.version", "ashlar_recordschema.schema", "ashlar_recordschema.next_version_id", "ashlar_recordschema.record_type_id"],
"Filter": "(ashlar_recordschema.record_type_id = '75903939-0ab0-4de8-9e21-dc92631975d5'::uuid)",
"Rows Removed by Filter": 14,
"Shared Hit Blocks": 5976,
"Shared Read Blocks": 0,
"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
}
]
},
"Planning Time": 12.874,
"Triggers": [
],
"Execution Time": 304.943
}
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment