explain.depesz.com

PostgreSQL's explain analyze made readable

Result: crZn : Optimization for: plan #FfXc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.023 17,584.512 ↑ 1.0 12 1

Sort (cost=704,401,311.34..704,401,311.37 rows=12 width=84) (actual time=17,584.512..17,584.512 rows=12 loops=1)

  • Sort Key: priorities.priority, priorities.status, priorities.created_at DESC
  • Sort Method: quicksort Memory: 26kB
2.          

CTE relevant_tasks

3. 9,759.095 10,533.988 ↑ 1.7 3,192,394 1

Bitmap Heap Scan on tasks (cost=326,593.55..6,535,577.82 rows=5,421,108 width=218) (actual time=785.283..10,533.988 rows=3,192,394 loops=1)

  • Recheck Cond: ((type = ANY ('{SellGround,SellVegetation,SellVegetationQA,SellClumping,SellClumpingQA,SellIvandar2D,SellIvandar3D,SellIvandarQA,SellMSL,SellMSLQA,SellMSLImageSegmentation,SellMSLImageSegmentationQA,SellAudit}'::text[])) AND (status = ANY ('{created,started,processed}'::text[])))
  • Rows Removed by Index Recheck: 20,644,725
  • Heap Blocks: exact=31,692 lossy=910,059
4. 774.893 774.893 ↑ 1.7 3,203,617 1

Bitmap Index Scan on ix_tasks_type_status_updated (cost=0.00..325,238.27 rows=5,421,108 width=0) (actual time=774.893..774.893 rows=3,203,617 loops=1)

  • Index Cond: ((type = ANY ('{SellGround,SellVegetation,SellVegetationQA,SellClumping,SellClumpingQA,SellIvandar2D,SellIvandar3D,SellIvandarQA,SellMSL,SellMSLQA,SellMSLImageSegmentation,SellMSLImageSegmentationQA,SellAudit}'::text[])) AND (status = ANY ('{created,started,processed}'::text[])))
5.          

CTE priority_task_tags

6. 146.692 208.285 ↓ 1.0 653,310 1

Bitmap Heap Scan on task_tags (cost=14,502.33..148,685.09 rows=643,341 width=22) (actual time=65.051..208.285 rows=653,310 loops=1)

  • Recheck Cond: (type = 'PRIORITY'::text)
  • Heap Blocks: exact=14,028
7. 61.593 61.593 ↓ 1.0 670,236 1

Bitmap Index Scan on idx_task_tags_type (cost=0.00..14,341.49 rows=643,341 width=0) (actual time=61.593..61.593 rows=670,236 loops=1)

  • Index Cond: (type = 'PRIORITY'::text)
8.          

CTE tasks_in_queue

9. 182.510 4,191.253 ↑ 25.0 8 1

HashAggregate (cost=349,725,622.85..349,725,624.85 rows=200 width=12) (actual time=4,191.252..4,191.253 rows=8 loops=1)

  • Group Key: priority_task_tags.tag_id
10. 436.581 4,008.743 ↑ 14,522.3 1,200,782 1

Merge Join (cost=946,749.19..262,535,096.80 rows=17,438,105,209 width=4) (actual time=2,568.772..4,008.743 rows=1,200,782 loops=1)

  • Merge Cond: (priority_task_tags.task_id = relevant_tasks.root_id)
11. 309.695 399.872 ↓ 1.0 652,864 1

Sort (cost=83,732.86..85,341.22 rows=643,341 width=8) (actual time=284.322..399.872 rows=652,864 loops=1)

  • Sort Key: priority_task_tags.task_id
  • Sort Method: external merge Disk: 11,472kB
12. 90.177 90.177 ↓ 1.0 653,310 1

CTE Scan on priority_task_tags (cost=0.00..12,866.82 rows=643,341 width=8) (actual time=0.035..90.177 rows=653,310 loops=1)

13. 328.060 3,172.290 ↑ 1.7 3,192,643 1

Materialize (cost=863,016.33..890,121.87 rows=5,421,108 width=4) (actual time=2,284.393..3,172.290 rows=3,192,643 loops=1)

14. 2,047.198 2,844.230 ↑ 1.7 3,192,391 1

Sort (cost=863,016.33..876,569.10 rows=5,421,108 width=4) (actual time=2,284.387..2,844.230 rows=3,192,391 loops=1)

  • Sort Key: relevant_tasks.root_id
  • Sort Method: external merge Disk: 43,728kB
15. 797.032 797.032 ↑ 1.7 3,192,394 1

CTE Scan on relevant_tasks (cost=0.00..108,422.16 rows=5,421,108 width=4) (actual time=0.024..797.032 rows=3,192,394 loops=1)

16.          

CTE tasks_outstanding

17. 49.467 13,392.976 ↑ 50.0 4 1

HashAggregate (cost=347,991,298.86..347,991,300.86 rows=200 width=12) (actual time=13,392.973..13,392.976 rows=4 loops=1)

  • Group Key: priority_task_tags_1.tag_id
18. 128.116 13,343.509 ↑ 55,011.9 315,403 1

Merge Join (cost=956,333.09..261,236,732.85 rows=17,350,913,203 width=4) (actual time=13,007.660..13,343.509 rows=315,403 loops=1)

  • Merge Cond: (priority_task_tags_1.task_id = relevant_tasks_1.root_id)
19. 335.024 759.792 ↓ 1.0 652,864 1

Sort (cost=83,732.86..85,341.22 rows=643,341 width=8) (actual time=652.512..759.792 rows=652,864 loops=1)

  • Sort Key: priority_task_tags_1.task_id
  • Sort Method: external merge Disk: 11,472kB
20. 424.768 424.768 ↓ 1.0 653,310 1

CTE Scan on priority_task_tags priority_task_tags_1 (cost=0.00..12,866.82 rows=643,341 width=8) (actual time=65.057..424.768 rows=653,310 loops=1)

21. 40.346 12,455.601 ↑ 13.6 395,299 1

Materialize (cost=872,600.23..899,570.24 rows=5,394,002 width=4) (actual time=12,355.092..12,455.601 rows=395,299 loops=1)

22. 270.895 12,415.255 ↑ 13.6 395,269 1

Sort (cost=872,600.23..886,085.24 rows=5,394,002 width=4) (actual time=12,355.087..12,415.255 rows=395,269 loops=1)

  • Sort Key: relevant_tasks_1.root_id
  • Sort Method: external merge Disk: 5,392kB
23. 12,144.360 12,144.360 ↑ 13.6 395,272 1

CTE Scan on relevant_tasks relevant_tasks_1 (cost=0.00..121,974.93 rows=5,394,002 width=4) (actual time=785.292..12,144.360 rows=395,272 loops=1)

  • Filter: (status <> 'processed'::text)
  • Rows Removed by Filter: 2,797,122
24.          

CTE tmp

25. 0.026 17,584.275 ↑ 25.0 8 1

Hash Full Join (cost=6.50..18.00 rows=200 width=20) (actual time=17,584.259..17,584.275 rows=8 loops=1)

  • Hash Cond: (tasks_in_queue.tag_id = tasks_outstanding.tag_id)
26. 4,191.262 4,191.262 ↑ 25.0 8 1

CTE Scan on tasks_in_queue (cost=0.00..4.00 rows=200 width=12) (actual time=4,191.255..4,191.262 rows=8 loops=1)

27. 0.007 13,392.987 ↑ 50.0 4 1

Hash (cost=4.00..4.00 rows=200 width=12) (actual time=13,392.986..13,392.987 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 13,392.980 13,392.980 ↑ 50.0 4 1

CTE Scan on tasks_outstanding (cost=0.00..4.00 rows=200 width=12) (actual time=13,392.976..13,392.980 rows=4 loops=1)

29. 0.015 17,584.489 ↑ 1.0 12 1

Nested Loop Left Join (cost=4.20..104.50 rows=12 width=84) (actual time=17,584.353..17,584.489 rows=12 loops=1)

30. 0.018 17,584.342 ↑ 1.0 12 1

Hash Right Join (cost=3.91..8.78 rows=12 width=56) (actual time=17,584.312..17,584.342 rows=12 loops=1)

  • Hash Cond: (tmp.tag_id = priorities.tag_id)
31. 17,584.282 17,584.282 ↑ 25.0 8 1

CTE Scan on tmp (cost=0.00..4.00 rows=200 width=20) (actual time=17,584.262..17,584.282 rows=8 loops=1)

32. 0.002 0.042 ↑ 1.0 12 1

Hash (cost=3.76..3.76 rows=12 width=40) (actual time=0.042..0.042 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.040 0.040 ↑ 1.0 12 1

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

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

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

  • Index Cond: (id = priorities.tag_id)