explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d9t2

Settings
# exclusive inclusive rows x rows loops node
1. 86.388 14,513.937 ↓ 4,298.6 51,583 1

Unique (cost=265,096.00..265,096.60 rows=12 width=396) (actual time=13,986.689..14,513.937 rows=51,583 loops=1)

2.          

CTE milestone_ind

3. 51.735 341.376 ↑ 1.1 223,423 1

Hash Right Join (cost=79,785.95..97,813.67 rows=253,678 width=40) (actual time=155.710..341.376 rows=223,423 loops=1)

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

CTE mi6

5. 114.827 114.827 ↑ 1.0 82,277 1

Seq Scan on milestone_indicators (cost=0.00..37,563.99 rows=85,055 width=24) (actual time=0.009..114.827 rows=82,277 loops=1)

  • Filter: ((milestone_id = 6) AND (execution_sequential = 1))
  • Rows Removed by Filter: 826,789
6. 134.055 134.055 ↑ 1.0 82,277 1

CTE Scan on mi6 (cost=0.00..1,701.10 rows=85,055 width=24) (actual time=0.011..134.055 rows=82,277 loops=1)

7. 27.303 155.586 ↑ 1.1 223,423 1

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

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,576kB
8. 128.283 128.283 ↑ 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.016..128.283 rows=223,423 loops=1)

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

CTE vcl_filtered

10. 640.675 717.107 ↓ 1.3 614,474 1

Bitmap Heap Scan on vcl (cost=16,966.37..118,984.92 rows=490,177 width=8) (actual time=82.361..717.107 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
11. 76.432 76.432 ↓ 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=76.432..76.432 rows=769,571 loops=1)

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

Sort (cost=48,297.41..48,297.44 rows=12 width=396) (actual time=13,986.688..14,427.549 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 4) 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 5) END), metadata.label, ((version.content)::text), office_user.name
  • Sort Method: external merge Disk: 549,584kB
13. 3,726.109 11,047.698 ↓ 12,344.6 148,135 1

Hash Join (cost=37,780.93..48,297.19 rows=12 width=396) (actual time=5,323.226..11,047.698 rows=148,135 loops=1)

  • Hash Cond: ((ah.project_id = mcig.config_item_group_id) AND ((document.content ->> 'typeName'::text) = (metadata.content ->> 'typeName'::text)))
14. 43.399 6,670.552 ↓ 449.6 158,274 1

Hash Join (cost=37,618.72..47,457.86 rows=352 width=1,139) (actual time=5,320.253..6,670.552 rows=158,274 loops=1)

  • Hash Cond: (vi.version_id = vcl_filtered.version_id)
15. 78.264 5,724.502 ↓ 224.8 158,274 1

Nested Loop Left Join (cost=26,585.24..36,421.73 rows=704 width=1,147) (actual time=4,417.596..5,724.502 rows=158,274 loops=1)

16. 17.569 5,487.964 ↓ 224.8 158,274 1

Nested Loop (cost=26,584.82..35,972.99 rows=704 width=1,053) (actual time=4,417.581..5,487.964 rows=158,274 loops=1)

17. 0.004 0.303 ↑ 1.0 1 1

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

18. 0.012 0.030 ↑ 1.0 1 1

Bitmap Heap Scan on user_project (cost=4.30..11.09 rows=1 width=16) (actual time=0.029..0.030 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
19. 0.018 0.018 ↑ 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.018..0.018 rows=2 loops=1)

  • Index Cond: (user_id = 6,301)
20. 0.269 0.269 ↑ 1.0 1 1

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

  • Index Cond: (id = user_project.office_id)
21. 0.000 5,470.092 ↓ 224.8 158,274 1

Nested Loop (cost=26,580.24..35,946.55 rows=704 width=1,051) (actual time=4,417.278..5,470.092 rows=158,274 loops=1)

22. 63.091 5,165.441 ↓ 224.2 158,274 1

Nested Loop (cost=26,579.82..35,519.58 rows=706 width=998) (actual time=4,417.263..5,165.441 rows=158,274 loops=1)

23. 136.472 4,785.802 ↓ 36.6 158,274 1

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

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

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

25. 81.911 4,260.350 ↓ 36.6 158,274 1

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

  • Buckets: 32,768 (originally 8192) Batches: 16 (originally 1) Memory Usage: 3,901kB
26. 118.583 4,178.439 ↓ 36.6 158,274 1

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

27. 31.037 3,426.760 ↓ 36.2 158,274 1

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

28.          

CTE action_ids

29. 0.005 0.005 ↑ 1.0 44 1

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

30. 0.154 0.765 ↓ 1.1 19 1

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

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

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

  • Filter: (action_type = 1)
  • Rows Removed by Filter: 2,935
32. 0.005 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
33. 0.011 0.011 ↑ 1.0 44 1

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

34. 3,394.958 3,394.958 ↓ 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.248..178.682 rows=8,330 loops=19)

  • Index Cond: (action_id = a.id)
  • Filter: (project_id = 104)
  • Rows Removed by Filter: 478
35. 633.096 633.096 ↑ 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.004..0.004 rows=1 loops=158,274)

  • Index Cond: (workflow_id = ah.workflow_id)
36. 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)
37. 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.001..0.002 rows=1 loops=158,274)

  • Index Cond: (version_id = version.id)
38. 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)
39. 4.563 902.651 ↓ 274.7 54,936 1

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

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,658kB
40. 88.367 898.088 ↓ 274.7 54,936 1

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

  • Group Key: vcl_filtered.version_id
41. 809.721 809.721 ↓ 1.3 614,474 1

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

42. 1.771 2.919 ↑ 1.0 680 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 784kB
43. 0.314 1.148 ↑ 1.0 680 1

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

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

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

45. 0.107 0.284 ↑ 1.0 680 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
46. 0.177 0.177 ↑ 1.0 680 1

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

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

SubPlan (for Hash Join)

48. 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)
49. 0.000 561.036 ↑ 1.0 1 20,037

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

50. 20.037 561.036 ↑ 1.0 1 20,037

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

  • Sort Key: ah_1.creation DESC
  • Sort Method: quicksort Memory: 27kB
51. 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)

52. 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
53. 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 : 12.936 ms
Execution time : 15,566.607 ms