explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OmWZ

Settings
# exclusive inclusive rows x rows loops node
1. 47,580.673 47,580.673 ↑ 1.0 12 1

CTE Scan on tmp (cost=10,803,436.74..10,803,437.01 rows=12 width=148) (actual time=47,580.667..47,580.673 rows=12 loops=1)

2.          

CTE tmp

3. 0.050 47,580.657 ↑ 1.0 12 1

Sort (cost=10,803,436.71..10,803,436.74 rows=12 width=77) (actual time=47,580.656..47,580.657 rows=12 loops=1)

  • Sort Key: priorities.priority, priorities.status, priorities.created_at DESC
  • Sort Method: quicksort Memory: 26kB
4. 0.132 47,580.607 ↑ 1.0 12 1

Nested Loop (cost=0.28..10,803,436.49 rows=12 width=77) (actual time=3,498.253..47,580.607 rows=12 loops=1)

5. 0.079 0.079 ↑ 1.0 12 1

Seq Scan on priorities (cost=0.00..3.76 rows=12 width=40) (actual time=0.016..0.079 rows=12 loops=1)

  • Filter: (workflow_type = 'MSL'::text)
  • Rows Removed by Filter: 136
6. 0.216 0.216 ↑ 1.0 1 12

Index Scan using tags_pkey on tags (cost=0.28..7.97 rows=1 width=29) (actual time=0.015..0.018 rows=1 loops=12)

  • Index Cond: (id = priorities.tag_id)
7.          

SubPlan (for Nested Loop)

8. 88.632 8,233.644 ↑ 1.0 1 12

Aggregate (cost=644,066.58..644,066.59 rows=1 width=8) (actual time=686.137..686.137 rows=1 loops=12)

9. 247.338 8,145.012 ↓ 56.0 100,065 12

Nested Loop (cost=1.13..644,062.11 rows=1,788 width=0) (actual time=0.066..678.751 rows=100,065 loops=12)

10. 117.720 117.720 ↓ 42.9 30,873 12

Index Scan using idx_task_tags_tag_id_type on task_tags (cost=0.56..2,716.96 rows=720 width=4) (actual time=0.020..9.810 rows=30,873 loops=12)

  • Index Cond: ((tag_id = tags.id) AND (type = 'PRIORITY'::text))
11. 7,779.954 7,779.954 ↑ 2.0 3 370,474

Index Scan using idx_tasks_root_id on tasks (cost=0.57..890.70 rows=6 width=4) (actual time=0.010..0.021 rows=3 loops=370,474)

  • Index Cond: (root_id = task_tags.task_id)
  • Filter: ((status = ANY ('{created,started,processed}'::text[])) AND (type = ANY ('{SellGround,SellVegetation,SellVegetationQA,SellClumping,SellClumpingQA,SellIvandar2D,SellIvandar3D,SellIvandarQA,SellMSL,SellMSLQA,SellMSLImageSegmentation,SellMSLImageSegmentationQA,SellAudit}'::text[])))
  • Rows Removed by Filter: 9
12. 36.144 39,346.536 ↑ 1.0 1 12

Aggregate (cost=256,211.49..256,211.50 rows=1 width=8) (actual time=3,278.878..3,278.878 rows=1 loops=12)

13. 226.680 39,310.392 ↓ 453.2 26,284 12

Merge Join (cost=255,432.68..256,211.34 rows=58 width=0) (actual time=3,260.564..3,275.866 rows=26,284 loops=12)

  • Merge Cond: (tasks_1.root_id = task_tags_1.task_id)
14. 2,447.712 38,790.024 ↓ 1.1 194,555 12

Sort (cost=252,593.03..253,030.46 rows=174,972 width=4) (actual time=3,206.385..3,232.502 rows=194,555 loops=12)

  • Sort Key: tasks_1.root_id
  • Sort Method: external merge Disk: 5,400kB
15. 25,682.064 36,342.312 ↓ 2.3 395,696 12

Bitmap Heap Scan on tasks tasks_1 (cost=213,769.06..234,961.80 rows=174,972 width=4) (actual time=899.837..3,028.526 rows=395,696 loops=12)

  • Recheck Cond: ((type = ANY ('{SellGround,SellVegetation,SellVegetationQA,SellClumping,SellClumpingQA,SellIvandar2D,SellIvandar3D,SellIvandarQA,SellMSL,SellMSLQA,SellMSLImageSegmentation,SellMSLImageSegmentationQA,SellAudit}'::text[])) AND (status = ANY ('{created,available,started}'::text[])) AND (status = ANY ('{cr
  • Rows Removed by Index Recheck: 4,629,874
  • Heap Blocks: exact=478,405 lossy=2,341,488
16. 167.892 10,660.248 ↓ 0.0 0 12

BitmapAnd (cost=213,769.06..213,769.06 rows=5,383 width=0) (actual time=888.354..888.354 rows=0 loops=12)

17. 1,160.232 1,160.232 ↓ 2.1 396,326 12

Bitmap Index Scan on idx_task_type_oldest_available (cost=0.00..16,589.07 rows=186,381 width=0) (actual time=96.686..96.686 rows=396,326 loops=12)

  • Index Cond: (type = ANY ('{SellGround,SellVegetation,SellVegetationQA,SellClumping,SellClumpingQA,SellIvandar2D,SellIvandar3D,SellIvandarQA,SellMSL,SellMSLQA,SellMSLImageSegmentation,SellMSLImageSegmentationQA,SellAudit}'::text[]))
18. 9,332.124 9,332.124 ↑ 1.5 3,923,554 12

Bitmap Index Scan on idx_tasks_state (cost=0.00..197,092.26 rows=5,705,483 width=0) (actual time=777.677..777.677 rows=3,923,554 loops=12)

  • Index Cond: (status = ANY ('{created,started}'::text[]))
19. 203.292 293.688 ↓ 42.9 30,922 12

Sort (cost=2,751.13..2,752.93 rows=720 width=4) (actual time=21.003..24.474 rows=30,922 loops=12)

  • Sort Key: task_tags_1.task_id
  • Sort Method: quicksort Memory: 60kB
20. 90.396 90.396 ↓ 42.9 30,873 12

Index Scan using idx_task_tags_tag_id_type on task_tags task_tags_1 (cost=0.56..2,716.96 rows=720 width=4) (actual time=0.029..7.533 rows=30,873 loops=12)

  • Index Cond: ((tag_id = tags.id) AND (type = 'PRIORITY'::text))