explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8g1l

Settings
# exclusive inclusive rows x rows loops node
1. 87.537 10,489.245 ↓ 4,298.6 51,583 1

Unique (cost=14,021,874.71..14,021,875.31 rows=12 width=396) (actual time=9,955.000..10,489.245 rows=51,583 loops=1)

2.          

CTE milestone_ind

3. 59.073 582.511 ↑ 1.1 223,423 1

Hash Right Join (cost=13,853,006.01..13,854,592.38 rows=253,678 width=40) (actual time=148.714..582.511 rows=223,423 loops=1)

  • Hash Cond: (mi6.version_id = mi1.version_id)
4.          

CTE mi6

5. 152.905 353.713 ↓ 161.0 82,277 1

Seq Scan on milestone_indicators mi_ (cost=0.00..13,810,784.04 rows=511 width=24) (actual time=0.031..353.713 rows=82,277 loops=1)

  • Filter: ((milestone_id = 6) AND (execution_sequential = (SubPlan 1)))
  • Rows Removed by Filter: 826,789
6.          

SubPlan (for Seq Scan)

7. 0.000 200.808 ↑ 1.0 1 100,404

GroupAggregate (cost=0.42..15.15 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=100,404)

  • Group Key: mi_2.version_id
8. 200.808 200.808 ↑ 1.0 1 100,404

Index Scan using idx_milestone_indicators_lookup on milestone_indicators mi_2 (cost=0.42..15.14 rows=1 width=10) (actual time=0.001..0.002 rows=1 loops=100,404)

  • Index Cond: (version_id = mi_.version_id)
  • Filter: (milestone_id = 6)
  • Rows Removed by Filter: 2
9. 374.882 374.882 ↓ 161.0 82,277 1

CTE Scan on mi6 (cost=0.00..10.22 rows=511 width=24) (actual time=0.033..374.882 rows=82,277 loops=1)

10. 25.728 148.556 ↑ 1.1 223,423 1

Hash (cost=37,563.99..37,563.99 rows=253,678 width=24) (actual time=148.556..148.556 rows=223,423 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,576kB
11. 122.828 122.828 ↑ 1.1 223,423 1

Seq Scan on milestone_indicators mi1 (cost=0.00..37,563.99 rows=253,678 width=24) (actual time=0.014..122.828 rows=223,423 loops=1)

  • Filter: ((milestone_id = 1) AND (execution_sequential = 1))
  • Rows Removed by Filter: 685,643
12.          

CTE vcl_filtered

13. 233.313 285.342 ↓ 1.3 614,474 1

Bitmap Heap Scan on vcl (cost=16,966.37..118,984.92 rows=490,177 width=8) (actual time=57.880..285.342 rows=614,474 loops=1)

  • Recheck Cond: (profile_id = ANY ('{144,155,1492,1948,146,2012,149,147,153,150,1493,2286}'::bigint[]))
  • Filter: ((office_id IS NULL) OR (office_id = 119))
  • Rows Removed by Filter: 155,097
  • Heap Blocks: exact=49,567
14. 52.029 52.029 ↓ 1.0 769,571 1

Bitmap Index Scan on vcl_profile_id_version_id_idx (cost=0.00..16,843.83 rows=757,147 width=0) (actual time=52.029..52.029 rows=769,571 loops=1)

  • Index Cond: (profile_id = ANY ('{144,155,1492,1948,146,2012,149,147,153,150,1493,2286}'::bigint[]))
15. 3,064.525 10,401.708 ↓ 12,344.6 148,135 1

Sort (cost=48,297.41..48,297.44 rows=12 width=396) (actual time=9,954.998..10,401.708 rows=148,135 loops=1)

  • Sort Key: document.code, version.sequential, version.id, document.title, vi.office_name, vi.current_step_label, mi.edd1, mi.eat1, mi.edd6, mi.eat6, (CASE WHEN (vi.current_qualification_label IS NULL) THEN NULL::text WHEN (vi.current_qualification_comment_id IS NOT NULL) THEN (SubPlan 5) ELSE NULL::text END), vi.current_qualification_label, ((document.content)::text), ((metadata.content)::text), ((((COALESCE(version.version_principal, ''::character varying))::text || (COALESCE(version.version_secondary, ''::character varying))::text) || COALESCE(('-'::text || (version.version_integration)::text), ''::text))), (CASE WHEN (vi.current_qualification_label IS NULL) THEN NULL::text WHEN (co.comment IS NOT NULL) THEN co.comment ELSE (SubPlan 6) END), metadata.label, ((version.content)::text), office_user.name
  • Sort Method: external merge Disk: 549,584kB
16. 3,741.252 7,337.183 ↓ 12,344.6 148,135 1

Hash Join (cost=37,780.93..48,297.19 rows=12 width=396) (actual time=1,308.634..7,337.183 rows=148,135 loops=1)

  • Hash Cond: ((ah.project_id = mcig.config_item_group_id) AND ((document.content ->> 'typeName'::text) = (metadata.content ->> 'typeName'::text)))
17. 46.506 2,924.996 ↓ 449.6 158,274 1

Hash Join (cost=37,618.72..47,457.86 rows=352 width=1,139) (actual time=1,305.802..2,924.996 rows=158,274 loops=1)

  • Hash Cond: (vi.version_id = vcl_filtered.version_id)
18. 87.827 2,406.320 ↓ 224.8 158,274 1

Nested Loop Left Join (cost=26,585.24..36,421.73 rows=704 width=1,147) (actual time=833.625..2,406.320 rows=158,274 loops=1)

19. 17.950 2,160.219 ↓ 224.8 158,274 1

Nested Loop (cost=26,584.82..35,972.99 rows=704 width=1,053) (actual time=833.612..2,160.219 rows=158,274 loops=1)

20. 0.004 0.048 ↑ 1.0 1 1

Nested Loop (cost=4.58..19.40 rows=1 width=18) (actual time=0.045..0.048 rows=1 loops=1)

21. 0.013 0.033 ↑ 1.0 1 1

Bitmap Heap Scan on user_project (cost=4.30..11.09 rows=1 width=16) (actual time=0.032..0.033 rows=1 loops=1)

  • Recheck Cond: (user_id = 6,301)
  • Filter: (config_item_group_id = 104)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
22. 0.020 0.020 ↑ 1.0 2 1

Bitmap Index Scan on user_project_user_id_idx (cost=0.00..4.30 rows=2 width=0) (actual time=0.020..0.020 rows=2 loops=1)

  • Index Cond: (user_id = 6,301)
23. 0.011 0.011 ↑ 1.0 1 1

Index Scan using "officePK" on office office_user (cost=0.28..8.29 rows=1 width=18) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (id = user_project.office_id)
24. 0.000 2,142.221 ↓ 224.8 158,274 1

Nested Loop (cost=26,580.24..35,946.55 rows=704 width=1,051) (actual time=833.565..2,142.221 rows=158,274 loops=1)

25. 54.339 1,827.541 ↓ 224.2 158,274 1

Nested Loop (cost=26,579.82..35,519.58 rows=706 width=998) (actual time=833.550..1,827.541 rows=158,274 loops=1)

26. 142.682 1,456.654 ↓ 36.6 158,274 1

Hash Right Join (cost=26,579.40..32,641.10 rows=4,330 width=233) (actual time=833.536..1,456.654 rows=158,274 loops=1)

  • Hash Cond: (mi.version_id = version.id)
27. 632.195 632.195 ↑ 1.1 223,423 1

CTE Scan on milestone_ind mi (cost=0.00..5,073.56 rows=253,678 width=40) (actual time=148.715..632.195 rows=223,423 loops=1)

28. 68.154 681.777 ↓ 36.6 158,274 1

Hash (cost=26,525.28..26,525.28 rows=4,330 width=201) (actual time=681.777..681.777 rows=158,274 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 16 (originally 1) Memory Usage: 3,901kB
29. 0.000 613.623 ↓ 36.6 158,274 1

Nested Loop (cost=2.83..26,525.28 rows=4,330 width=201) (actual time=0.104..613.623 rows=158,274 loops=1)

30. 27.034 307.133 ↓ 36.2 158,274 1

Nested Loop (cost=2.41..6,755.54 rows=4,369 width=548) (actual time=0.089..307.133 rows=158,274 loops=1)

31.          

CTE action_ids

32. 0.004 0.004 ↑ 1.0 44 1

Values Scan on "*VALUES*" (cost=0.00..0.55 rows=44 width=4) (actual time=0.001..0.004 rows=44 loops=1)

33. 0.131 0.666 ↓ 1.1 19 1

Hash Join (cost=1.43..132.01 rows=17 width=12) (actual time=0.028..0.666 rows=19 loops=1)

  • Hash Cond: (a.id = ad.id)
34. 0.519 0.519 ↑ 1.0 1,829 1

Seq Scan on action a (cost=0.00..123.55 rows=1,829 width=8) (actual time=0.007..0.519 rows=1,829 loops=1)

  • Filter: (action_type = 1)
  • Rows Removed by Filter: 2,935
35. 0.003 0.016 ↑ 1.0 44 1

Hash (cost=0.88..0.88 rows=44 width=4) (actual time=0.016..0.016 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
36. 0.013 0.013 ↑ 1.0 44 1

CTE Scan on action_ids ad (cost=0.00..0.88 rows=44 width=4) (actual time=0.003..0.013 rows=44 loops=1)

37. 279.433 279.433 ↓ 6.7 8,330 19

Index Scan using action_history_action_idx on action_history ah (cost=0.43..377.21 rows=1,238 width=24) (actual time=0.012..14.707 rows=8,330 loops=19)

  • Index Cond: (action_id = a.id)
  • Filter: (project_id = 104)
  • Rows Removed by Filter: 478
38. 316.548 316.548 ↑ 1.0 1 158,274

Index Scan using version_workflow_idx on version (cost=0.42..4.51 rows=1 width=193) (actual time=0.002..0.002 rows=1 loops=158,274)

  • Index Cond: (workflow_id = ah.workflow_id)
39. 316.548 316.548 ↑ 1.0 1 158,274

Index Scan using "documentPK" on document (cost=0.42..0.65 rows=1 width=781) (actual time=0.002..0.002 rows=1 loops=158,274)

  • Index Cond: (id = version.document_id)
  • Filter: (project_id = 104)
40. 316.548 316.548 ↑ 1.0 1 158,274

Index Scan using idx_version_info_v on version_info vi (cost=0.42..0.59 rows=1 width=53) (actual time=0.002..0.002 rows=1 loops=158,274)

  • Index Cond: (version_id = version.id)
41. 158.274 158.274 ↑ 1.0 1 158,274

Index Scan using "commentaryPK" on commentary co (cost=0.42..0.63 rows=1 width=102) (actual time=0.001..0.001 rows=1 loops=158,274)

  • Index Cond: (id = vi.current_qualification_comment_id)
42. 4.532 472.170 ↓ 274.7 54,936 1

Hash (cost=11,030.98..11,030.98 rows=200 width=8) (actual time=472.170..472.170 rows=54,936 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,658kB
43. 89.116 467.638 ↓ 274.7 54,936 1

HashAggregate (cost=11,028.98..11,030.98 rows=200 width=8) (actual time=461.256..467.638 rows=54,936 loops=1)

  • Group Key: vcl_filtered.version_id
44. 378.522 378.522 ↓ 1.3 614,474 1

CTE Scan on vcl_filtered (cost=0.00..9,803.54 rows=490,177 width=8) (actual time=57.882..378.522 rows=614,474 loops=1)

45. 1.579 2.780 ↑ 1.0 680 1

Hash (cost=152.01..152.01 rows=680 width=1,094) (actual time=2.780..2.780 rows=680 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 784kB
46. 0.328 1.201 ↑ 1.0 680 1

Hash Join (cost=23.81..152.01 rows=680 width=1,094) (actual time=0.367..1.201 rows=680 loops=1)

  • Hash Cond: (metadata.id = mcig.metadata_id)
47. 0.516 0.516 ↑ 1.0 756 1

Seq Scan on metadata (cost=0.00..118.56 rows=756 width=1,094) (actual time=0.004..0.516 rows=756 loops=1)

48. 0.149 0.357 ↑ 1.0 680 1

Hash (cost=15.31..15.31 rows=680 width=16) (actual time=0.357..0.357 rows=680 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
49. 0.208 0.208 ↑ 1.0 680 1

Seq Scan on metadata_config_item_group mcig (cost=0.00..15.31 rows=680 width=16) (actual time=0.011..0.208 rows=680 loops=1)

  • Filter: (status = 1)
  • Rows Removed by Filter: 65
50.          

SubPlan (for Hash Join)

51. 87.082 87.082 ↑ 1.0 1 87,082

Index Scan using "commentaryPK" on commentary c (cost=0.42..8.44 rows=1 width=94) (actual time=0.001..0.001 rows=1 loops=87,082)

  • Index Cond: (id = vi.current_qualification_comment_id)
52. 0.000 581.073 ↑ 1.0 1 20,037

Limit (cost=45.90..45.90 rows=1 width=40) (actual time=0.029..0.029 rows=1 loops=20,037)

53. 40.074 581.073 ↑ 1.0 1 20,037

Sort (cost=45.90..45.90 rows=1 width=40) (actual time=0.029..0.029 rows=1 loops=20,037)

  • Sort Key: ah_1.creation DESC
  • Sort Method: quicksort Memory: 27kB
54. 21.636 540.999 ↓ 2.0 2 20,037

Nested Loop (cost=0.71..45.89 rows=1 width=40) (actual time=0.009..0.027 rows=2 loops=20,037)

55. 480.888 480.888 ↓ 2.0 2 20,037

Index Scan using action_history_worflow_idx on action_history ah_1 (cost=0.43..41.56 rows=1 width=243) (actual time=0.007..0.024 rows=2 loops=20,037)

  • Index Cond: (workflow_id = version.workflow_id)
  • Filter: ((((content -> 'properties'::text) -> 'kc:commentary'::text) IS NOT NULL) AND (((content -> 'properties'::text) ->> 'kc:undone'::text) IS NULL) AND ((((content -> 'properties'::text) -> 'kc:qualification'::text) ->> 'label'::text) IS NULL))
  • Rows Removed by Filter: 22
56. 38.475 38.475 ↑ 1.0 1 38,475

Index Only Scan using "actionPK" on action a_1 (cost=0.28..4.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=38,475)

  • Index Cond: (id = ah_1.action_id)
  • Heap Fetches: 0
Planning time : 4.166 ms
Execution time : 11,126.101 ms