explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cmy

Settings
# exclusive inclusive rows x rows loops node
1. 85.988 9,652.131 ↓ 4,298.6 51,583 1

Unique (cost=229,189.62..229,190.22 rows=12 width=396) (actual time=9,138.318..9,652.131 rows=51,583 loops=1)

2.          

CTE milestone_ind_1

3. 77.668 88.559 ↑ 1.1 223,423 1

Bitmap Heap Scan on milestone_indicators mi (cost=5,484.62..33,217.79 rows=253,678 width=194) (actual time=13.259..88.559 rows=223,423 loops=1)

  • Recheck Cond: ((milestone_id = 1) AND (execution_sequential = 1))
  • Heap Blocks: exact=21,586
4. 10.891 10.891 ↑ 1.1 223,423 1

Bitmap Index Scan on milestone_indicators_1 (cost=0.00..5,421.21 rows=253,678 width=0) (actual time=10.891..10.891 rows=223,423 loops=1)

  • Index Cond: ((milestone_id = 1) AND (execution_sequential = 1))
5.          

CTE milestone_ind_6

6. 47.769 52.096 ↑ 1.0 82,277 1

Bitmap Heap Scan on milestone_indicators mi_1 (cost=1,840.24..27,044.06 rows=85,055 width=194) (actual time=5.852..52.096 rows=82,277 loops=1)

  • Recheck Cond: ((milestone_id = 6) AND (execution_sequential = 1))
  • Heap Blocks: exact=14,475
7. 4.327 4.327 ↑ 1.0 82,277 1

Bitmap Index Scan on milestone_indicators_1 (cost=0.00..1,818.98 rows=85,055 width=0) (actual time=4.327..4.327 rows=82,277 loops=1)

  • Index Cond: ((milestone_id = 6) AND (execution_sequential = 1))
8.          

CTE vcl_filtered

9. 234.462 277.336 ↓ 1.3 614,474 1

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

  • Index Cond: (profile_id = ANY ('{144,155,1492,1948,146,2012,149,147,153,150,1493,2286}'::bigint[]))
11. 2,544.461 9,566.143 ↓ 12,344.6 148,135 1

Sort (cost=49,942.84..49,942.87 rows=12 width=396) (actual time=9,138.317..9,566.143 rows=148,135 loops=1)

  • Sort Key: document.code, version.sequential, version.id, document.title, vi.office_name, vi.current_step_label, mi1.execution_due_date, mi1.executed_at, mi6.execution_due_date, mi6.executed_at, (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,560kB
12. 3,424.503 7,021.682 ↓ 12,344.6 148,135 1

Nested Loop Left Join (cost=39,867.46..49,942.63 rows=12 width=396) (actual time=1,385.263..7,021.682 rows=148,135 loops=1)

13. 375.381 2,780.889 ↓ 12,344.6 148,135 1

Hash Join (cost=39,867.04..49,282.61 rows=12 width=2,123) (actual time=1,385.209..2,780.889 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. 45.061 2,404.171 ↓ 449.6 158,274 1

Hash Join (cost=39,704.83..49,095.65 rows=352 width=1,045) (actual time=1,383.858..2,404.171 rows=158,274 loops=1)

  • Hash Cond: (vi.version_id = vcl_filtered.version_id)
15. 18.417 1,894.497 ↓ 224.8 158,274 1

Nested Loop (cost=28,671.35..38,059.52 rows=704 width=1,053) (actual time=919.240..1,894.497 rows=158,274 loops=1)

16. 0.004 0.050 ↑ 1.0 1 1

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

17. 0.014 0.034 ↑ 1.0 1 1

Bitmap Heap Scan on user_project (cost=4.30..11.09 rows=1 width=16) (actual time=0.033..0.034 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
18. 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)
19. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: (id = user_project.office_id)
20. 134.143 1,876.030 ↓ 224.8 158,274 1

Nested Loop (cost=28,666.77..38,033.08 rows=704 width=1,051) (actual time=919.191..1,876.030 rows=158,274 loops=1)

21. 35.295 1,583.613 ↓ 224.2 158,274 1

Nested Loop (cost=28,666.35..37,606.11 rows=706 width=998) (actual time=919.175..1,583.613 rows=158,274 loops=1)

22. 176.703 1,231.770 ↓ 36.6 158,274 1

Hash Right Join (cost=28,665.93..34,727.63 rows=4,330 width=233) (actual time=919.154..1,231.770 rows=158,274 loops=1)

  • Hash Cond: (mi1.version_id = version.id)
23. 152.973 152.973 ↑ 1.1 223,423 1

CTE Scan on milestone_ind_1 mi1 (cost=0.00..5,073.56 rows=253,678 width=24) (actual time=13.261..152.973 rows=223,423 loops=1)

24. 49.012 902.094 ↓ 36.6 158,274 1

Hash (cost=28,611.81..28,611.81 rows=4,330 width=217) (actual time=902.094..902.094 rows=158,274 loops=1)

  • Buckets: 16,384 (originally 8192) Batches: 16 (originally 1) Memory Usage: 3,969kB
25. 93.211 853.082 ↓ 36.6 158,274 1

Hash Right Join (cost=26,579.40..28,611.81 rows=4,330 width=217) (actual time=690.843..853.082 rows=158,274 loops=1)

  • Hash Cond: (mi6.version_id = version.id)
26. 77.318 77.318 ↑ 1.0 82,277 1

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

27. 69.002 682.553 ↓ 36.6 158,274 1

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

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

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

29. 28.702 308.586 ↓ 36.2 158,274 1

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

30.          

CTE action_ids

31. 0.006 0.006 ↑ 1.0 44 1

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

32. 0.118 0.641 ↓ 1.1 19 1

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

  • Hash Cond: (a.id = ad.id)
33. 0.506 0.506 ↑ 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.506 rows=1,829 loops=1)

  • Filter: (action_type = 1)
  • Rows Removed by Filter: 2,935
34. 0.005 0.017 ↑ 1.0 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
35. 0.012 0.012 ↑ 1.0 44 1

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

36. 279.243 279.243 ↓ 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.011..14.697 rows=8,330 loops=19)

  • Index Cond: (action_id = a.id)
  • Filter: (project_id = 104)
  • Rows Removed by Filter: 478
37. 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)
38. 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)
39. 158.274 158.274 ↑ 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.001 rows=1 loops=158,274)

  • Index Cond: (version_id = version.id)
40. 4.570 464.613 ↓ 274.7 54,936 1

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

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,658kB
41. 89.430 460.043 ↓ 274.7 54,936 1

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

  • Group Key: vcl_filtered.version_id
42. 370.613 370.613 ↓ 1.3 614,474 1

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

43. 0.745 1.337 ↑ 1.0 680 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 784kB
44. 0.139 0.592 ↑ 1.0 680 1

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

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

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

46. 0.059 0.150 ↑ 1.0 680 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
47. 0.091 0.091 ↑ 1.0 680 1

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

  • Filter: (status = 1)
  • Rows Removed by Filter: 65
48. 148.135 148.135 ↑ 1.0 1 148,135

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=148,135)

  • Index Cond: (id = vi.current_qualification_comment_id)
49.          

SubPlan (for Nested Loop Left Join)

50. 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)
51. 20.037 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)

52. 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: 25kB
53. 21.636 540.999 ↓ 2.0 2 20,037

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

54. 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
55. 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 : 2.565 ms
Execution time : 9,718.649 ms