Skip to content

Instantly share code, notes, and snippets.

@reasv

reasv/query_plan.json

Created July 17, 2024 09:55
Show Gist options
  • Save reasv/62df861d2bd96c74e69f1906e5e1f082 to your computer and use it in GitHub Desktop.
Save reasv/62df861d2bd96c74e69f1906e5e1f082 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
query_plan.json
[
{
"Plan": {
"Node Type": "Sort",
"Parallel Aware": false,
"Startup Cost": 67.07,
"Total Cost": 67.08,
"Plan Rows": 4,
"Plan Width": 495,
"Output": ["posts.post_id", "posts.board", "posts.no", "posts.resto", "posts.sticky", "posts.closed", "posts.now", "posts.\"time\"", "posts.name", "posts.trip", "posts.id", "posts.capcode", "posts.country", "posts.country_name", "posts.sub", "posts.com", "posts.tim", "posts.filename", "posts.ext", "posts.fsize", "posts.md5", "posts.w", "posts.h", "posts.tn_w", "posts.tn_h", "posts.filedeleted", "posts.spoiler", "posts.custom_spoiler", "posts.replies", "posts.images", "posts.bumplimit", "posts.imagelimit", "posts.tag", "posts.semantic_url", "posts.since4pass", "posts.unique_ips", "posts.m_img", "posts.archived", "posts.archived_on", "posts.last_modified", "posts.deleted_on", "posts.mitsuba_post_hidden", "posts.mitsuba_com_hidden", "(CASE WHEN (files.sha256 IS NOT NULL) THEN files.sha256 ELSE NULL::text END)", "(CASE WHEN (thumbnails.hidden IS NOT NULL) THEN thumbnails.hidden ELSE NULL::boolean END)", "(CASE WHEN (thumbnails.sha256 IS NOT NULL) THEN thumbnails.sha256 ELSE NULL::text END)", "(CASE WHEN ((blacklist_thumbnail.sha256 IS NOT NULL) OR (blacklist_file.sha256 IS NOT NULL)) THEN true ELSE false END)"],
"Sort Key": ["posts.no"],
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 39.50,
"Total Cost": 67.03,
"Plan Rows": 4,
"Plan Width": 495,
"Output": ["posts.post_id", "posts.board", "posts.no", "posts.resto", "posts.sticky", "posts.closed", "posts.now", "posts.\"time\"", "posts.name", "posts.trip", "posts.id", "posts.capcode", "posts.country", "posts.country_name", "posts.sub", "posts.com", "posts.tim", "posts.filename", "posts.ext", "posts.fsize", "posts.md5", "posts.w", "posts.h", "posts.tn_w", "posts.tn_h", "posts.filedeleted", "posts.spoiler", "posts.custom_spoiler", "posts.replies", "posts.images", "posts.bumplimit", "posts.imagelimit", "posts.tag", "posts.semantic_url", "posts.since4pass", "posts.unique_ips", "posts.m_img", "posts.archived", "posts.archived_on", "posts.last_modified", "posts.deleted_on", "posts.mitsuba_post_hidden", "posts.mitsuba_com_hidden", "CASE WHEN (files.sha256 IS NOT NULL) THEN files.sha256 ELSE NULL::text END", "CASE WHEN (thumbnails.hidden IS NOT NULL) THEN thumbnails.hidden ELSE NULL::boolean END", "CASE WHEN (thumbnails.sha256 IS NOT NULL) THEN thumbnails.sha256 ELSE NULL::text END", "CASE WHEN ((blacklist_thumbnail.sha256 IS NOT NULL) OR (blacklist_file.sha256 IS NOT NULL)) THEN true ELSE false END"],
"Inner Unique": true,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 39.37,
"Total Cost": 66.41,
"Plan Rows": 4,
"Plan Width": 566,
"Output": ["posts.post_id", "posts.board", "posts.no", "posts.resto", "posts.sticky", "posts.closed", "posts.now", "posts.\"time\"", "posts.name", "posts.trip", "posts.id", "posts.capcode", "posts.country", "posts.country_name", "posts.sub", "posts.com", "posts.tim", "posts.filename", "posts.ext", "posts.fsize", "posts.md5", "posts.w", "posts.h", "posts.tn_w", "posts.tn_h", "posts.filedeleted", "posts.spoiler", "posts.custom_spoiler", "posts.replies", "posts.images", "posts.bumplimit", "posts.imagelimit", "posts.tag", "posts.semantic_url", "posts.since4pass", "posts.unique_ips", "posts.m_img", "posts.archived", "posts.archived_on", "posts.last_modified", "posts.deleted_on", "posts.mitsuba_post_hidden", "posts.mitsuba_com_hidden", "files.sha256", "thumbnails.hidden", "thumbnails.sha256", "blacklist_file.sha256"],
"Inner Unique": true,
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 39.10,
"Total Cost": 39.22,
"Plan Rows": 4,
"Plan Width": 521,
"Output": ["posts.post_id", "posts.board", "posts.no", "posts.resto", "posts.sticky", "posts.closed", "posts.now", "posts.\"time\"", "posts.name", "posts.trip", "posts.id", "posts.capcode", "posts.country", "posts.country_name", "posts.sub", "posts.com", "posts.tim", "posts.filename", "posts.ext", "posts.fsize", "posts.md5", "posts.w", "posts.h", "posts.tn_w", "posts.tn_h", "posts.filedeleted", "posts.spoiler", "posts.custom_spoiler", "posts.replies", "posts.images", "posts.bumplimit", "posts.imagelimit", "posts.tag", "posts.semantic_url", "posts.since4pass", "posts.unique_ips", "posts.m_img", "posts.archived", "posts.archived_on", "posts.last_modified", "posts.deleted_on", "posts.mitsuba_post_hidden", "posts.mitsuba_com_hidden", "posts_files.thumbnail_id", "files.sha256", "blacklist_file.sha256"],
"Inner Unique": false,
"Merge Cond": "(files.file_id = posts_files.file_id)",
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 0.28,
"Total Cost": 103.69,
"Plan Rows": 1647,
"Plan Width": 92,
"Output": ["files.sha256", "files.file_id", "blacklist_file.sha256"],
"Inner Unique": true,
"Join Filter": "(files.sha256 = blacklist_file.sha256)",
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "files_pkey",
"Relation Name": "files",
"Schema": "public",
"Alias": "files",
"Startup Cost": 0.28,
"Total Cost": 77.98,
"Plan Rows": 1647,
"Plan Width": 60,
"Output": ["files.file_id", "files.sha256", "files.file_ext", "files.is_thumbnail", "files.hidden", "files.created_at"]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 1.00,
"Plan Rows": 1,
"Plan Width": 32,
"Output": ["blacklist_file.sha256"],
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "file_blacklist",
"Schema": "public",
"Alias": "blacklist_file",
"Startup Cost": 0.00,
"Total Cost": 1.00,
"Plan Rows": 1,
"Plan Width": 32,
"Output": ["blacklist_file.sha256"]
}
]
}
]
},
{
"Node Type": "Sort",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 38.82,
"Total Cost": 38.83,
"Plan Rows": 4,
"Plan Width": 445,
"Output": ["posts.post_id", "posts.board", "posts.no", "posts.resto", "posts.sticky", "posts.closed", "posts.now", "posts.\"time\"", "posts.name", "posts.trip", "posts.id", "posts.capcode", "posts.country", "posts.country_name", "posts.sub", "posts.com", "posts.tim", "posts.filename", "posts.ext", "posts.fsize", "posts.md5", "posts.w", "posts.h", "posts.tn_w", "posts.tn_h", "posts.filedeleted", "posts.spoiler", "posts.custom_spoiler", "posts.replies", "posts.images", "posts.bumplimit", "posts.imagelimit", "posts.tag", "posts.semantic_url", "posts.since4pass", "posts.unique_ips", "posts.m_img", "posts.archived", "posts.archived_on", "posts.last_modified", "posts.deleted_on", "posts.mitsuba_post_hidden", "posts.mitsuba_com_hidden", "posts_files.file_id", "posts_files.thumbnail_id"],
"Sort Key": ["posts_files.file_id"],
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 23.85,
"Total Cost": 38.78,
"Plan Rows": 4,
"Plan Width": 445,
"Output": ["posts.post_id", "posts.board", "posts.no", "posts.resto", "posts.sticky", "posts.closed", "posts.now", "posts.\"time\"", "posts.name", "posts.trip", "posts.id", "posts.capcode", "posts.country", "posts.country_name", "posts.sub", "posts.com", "posts.tim", "posts.filename", "posts.ext", "posts.fsize", "posts.md5", "posts.w", "posts.h", "posts.tn_w", "posts.tn_h", "posts.filedeleted", "posts.spoiler", "posts.custom_spoiler", "posts.replies", "posts.images", "posts.bumplimit", "posts.imagelimit", "posts.tag", "posts.semantic_url", "posts.since4pass", "posts.unique_ips", "posts.m_img", "posts.archived", "posts.archived_on", "posts.last_modified", "posts.deleted_on", "posts.mitsuba_post_hidden", "posts.mitsuba_com_hidden", "posts_files.file_id", "posts_files.thumbnail_id"],
"Inner Unique": true,
"Hash Cond": "(posts.post_id = posts_files.post_id)",
"Plans": [
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "posts",
"Schema": "public",
"Alias": "posts",
"Startup Cost": 8.60,
"Total Cost": 23.52,
"Plan Rows": 4,
"Plan Width": 429,
"Output": ["posts.post_id", "posts.board", "posts.no", "posts.resto", "posts.sticky", "posts.closed", "posts.now", "posts.\"time\"", "posts.name", "posts.trip", "posts.id", "posts.capcode", "posts.country", "posts.country_name", "posts.sub", "posts.com", "posts.tim", "posts.filename", "posts.ext", "posts.fsize", "posts.md5", "posts.w", "posts.h", "posts.tn_w", "posts.tn_h", "posts.filedeleted", "posts.spoiler", "posts.custom_spoiler", "posts.replies", "posts.images", "posts.bumplimit", "posts.imagelimit", "posts.tag", "posts.semantic_url", "posts.since4pass", "posts.unique_ips", "posts.m_img", "posts.archived", "posts.archived_on", "posts.last_modified", "posts.deleted_on", "posts.mitsuba_post_hidden", "posts.mitsuba_com_hidden"],
"Recheck Cond": "((((posts.board)::text = 'po'::text) AND (posts.no = 570368)) OR (posts.resto = 570368))",
"Filter": "((posts.board)::text = 'po'::text)",
"Plans": [
{
"Node Type": "BitmapOr",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 8.60,
"Total Cost": 8.60,
"Plan Rows": 4,
"Plan Width": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Index Name": "posts_board_no_key",
"Startup Cost": 0.00,
"Total Cost": 4.29,
"Plan Rows": 1,
"Plan Width": 0,
"Index Cond": "(((posts.board)::text = 'po'::text) AND (posts.no = 570368))"
},
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Index Name": "resto_index",
"Startup Cost": 0.00,
"Total Cost": 4.30,
"Plan Rows": 3,
"Plan Width": 0,
"Index Cond": "(posts.resto = 570368)"
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 15.15,
"Total Cost": 15.15,
"Plan Rows": 8,
"Plan Width": 24,
"Output": ["posts_files.post_id", "posts_files.file_id", "posts_files.thumbnail_id"],
"Plans": [
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "posts_files",
"Schema": "public",
"Alias": "posts_files",
"Startup Cost": 4.34,
"Total Cost": 15.15,
"Plan Rows": 8,
"Plan Width": 24,
"Output": ["posts_files.post_id", "posts_files.file_id", "posts_files.thumbnail_id"],
"Recheck Cond": "(posts_files.idx = 0)",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "idx_idx",
"Startup Cost": 0.00,
"Total Cost": 4.34,
"Plan Rows": 8,
"Plan Width": 0,
"Index Cond": "(posts_files.idx = 0)"
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "files_pkey",
"Relation Name": "files",
"Schema": "public",
"Alias": "thumbnails",
"Startup Cost": 0.28,
"Total Cost": 6.79,
"Plan Rows": 1,
"Plan Width": 61,
"Output": ["thumbnails.file_id", "thumbnails.sha256", "thumbnails.file_ext", "thumbnails.is_thumbnail", "thumbnails.hidden", "thumbnails.created_at"],
"Index Cond": "(thumbnails.file_id = posts_files.thumbnail_id)"
}
]
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "file_blacklist_pkey",
"Relation Name": "file_blacklist",
"Schema": "public",
"Alias": "blacklist_thumbnail",
"Startup Cost": 0.12,
"Total Cost": 0.15,
"Plan Rows": 1,
"Plan Width": 32,
"Output": ["blacklist_thumbnail.sha256"],
"Index Cond": "(blacklist_thumbnail.sha256 = thumbnails.sha256)"
}
]
}
]
}
}
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment