explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NMrG

Settings
# exclusive inclusive rows x rows loops node
1. 81.364 11,515.063 ↓ 4,298.6 51,583 1

Unique (cost=161,634.19..161,634.79 rows=12 width=396) (actual time=10,966.160..11,515.063 rows=51,583 loops=1)

2.          

CTE vcl_filtered

3. 244.200 287.487 ↓ 1.3 614,474 1

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

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

Sort (cost=42,649.28..42,649.31 rows=12 width=396) (actual time=10,966.159..11,433.699 rows=148,135 loops=1)

  • Sort Key: document.code, version.sequential, version.id, document.title, vi.office_name, vi.current_step_label, ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), (CASE WHEN (vi.current_qualification_label IS NULL) THEN NULL::text WHEN (vi.current_qualification_comment_id IS NOT NULL) THEN (SubPlan 6) 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 7) END), metadata.label, ((version.content)::text), office_user.name
  • Sort Method: external merge Disk: 546,544kB
6. 3,889.831 8,811.322 ↓ 12,344.6 148,135 1

Hash Join (cost=11,195.87..42,649.06 rows=12 width=396) (actual time=483.866..8,811.322 rows=148,135 loops=1)

  • Hash Cond: ((mcig.metadata_id = metadata.id) AND ((document.content ->> 'typeName'::text) = (metadata.content ->> 'typeName'::text)))
7. 223.150 2,726.522 ↓ 492.2 1,107,918 1

Hash Join (cost=11,065.97..41,438.33 rows=2,251 width=1,115) (actual time=482.646..2,726.522 rows=1,107,918 loops=1)

  • Hash Cond: (vi.version_id = vcl_filtered.version_id)
8. 160.485 2,020.999 ↓ 246.1 1,107,918 1

Hash Join (cost=32.49..30,387.93 rows=4,502 width=1,123) (actual time=0.262..2,020.999 rows=1,107,918 loops=1)

  • Hash Cond: (ah.project_id = mcig.config_item_group_id)
9. 123.958 1,860.367 ↓ 224.8 158,274 1

Nested Loop Left Join (cost=8.67..30,305.90 rows=704 width=1,123) (actual time=0.112..1,860.367 rows=158,274 loops=1)

10. 19.178 1,578.135 ↓ 224.8 158,274 1

Nested Loop (cost=8.25..29,857.16 rows=704 width=1,029) (actual time=0.105..1,578.135 rows=158,274 loops=1)

11. 0.002 0.023 ↑ 1.0 1 1

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

12. 0.009 0.018 ↑ 1.0 1 1

Bitmap Heap Scan on user_project (cost=4.30..11.09 rows=1 width=16) (actual time=0.017..0.018 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
13. 0.009 0.009 ↑ 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.009..0.009 rows=2 loops=1)

  • Index Cond: (user_id = 6,301)
14. 0.003 0.003 ↑ 1.0 1 1

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

  • Index Cond: (id = user_project.office_id)
15. 32.126 1,558.934 ↓ 224.8 158,274 1

Nested Loop (cost=3.67..29,830.72 rows=704 width=1,027) (actual time=0.085..1,558.934 rows=158,274 loops=1)

16. 117.382 1,210.260 ↓ 224.2 158,274 1

Nested Loop (cost=3.25..29,403.75 rows=706 width=974) (actual time=0.078..1,210.260 rows=158,274 loops=1)

17. 133.674 776.330 ↓ 36.6 158,274 1

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

18. 33.784 326.108 ↓ 36.2 158,274 1

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

19.          

CTE action_ids

20. 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)

21. 0.131 0.617 ↓ 1.1 19 1

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
24. 0.009 0.009 ↑ 1.0 44 1

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

25. 291.707 291.707 ↓ 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..15.353 rows=8,330 loops=19)

  • Index Cond: (action_id = a.id)
  • Filter: (project_id = 104)
  • Rows Removed by Filter: 478
26. 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)
27. 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)
28. 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)
29. 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)
30. 0.064 0.147 ↑ 1.0 680 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
31. 0.083 0.083 ↑ 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.083 rows=680 loops=1)

  • Filter: (status = 1)
  • Rows Removed by Filter: 65
32. 5.000 482.373 ↓ 274.7 54,936 1

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

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,658kB
33. 95.619 477.373 ↓ 274.7 54,936 1

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

  • Group Key: vcl_filtered.version_id
34. 381.754 381.754 ↓ 1.3 614,474 1

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

35. 0.745 1.105 ↑ 1.0 756 1

Hash (cost=118.56..118.56 rows=756 width=1,094) (actual time=1.105..1.105 rows=756 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 843kB
36. 0.360 0.360 ↑ 1.0 756 1

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

37.          

SubPlan (for Hash Join)

38. 0.000 296.270 ↓ 0.0 0 148,135

Group (cost=8.45..8.46 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=148,135)

  • Group Key: mpd.first_due_date
39. 148.135 296.270 ↓ 0.0 0 148,135

Sort (cost=8.45..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=148,135)

  • Sort Key: mpd.first_due_date
  • Sort Method: quicksort Memory: 25kB
40. 148.135 148.135 ↓ 0.0 0 148,135

Index Scan using idx_miles_id_per_doc on milestone_per_document mpd (cost=0.42..8.44 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=148,135)

  • Index Cond: (document_id = document.id)
  • Filter: (milestone_type = 1)
  • Rows Removed by Filter: 0
41. 0.000 296.270 ↓ 0.0 0 148,135

Group (cost=8.45..8.46 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=148,135)

  • Group Key: mpd_1.first_executed_at
42. 148.135 296.270 ↓ 0.0 0 148,135

Sort (cost=8.45..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=148,135)

  • Sort Key: mpd_1.first_executed_at
  • Sort Method: quicksort Memory: 25kB
43. 148.135 148.135 ↓ 0.0 0 148,135

Index Scan using idx_miles_id_per_doc on milestone_per_document mpd_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=148,135)

  • Index Cond: (document_id = document.id)
  • Filter: (milestone_type = 1)
  • Rows Removed by Filter: 0
44. 0.000 296.270 ↓ 0.0 0 148,135

Group (cost=8.45..8.46 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=148,135)

  • Group Key: mpd_2.last_due_date
45. 148.135 296.270 ↓ 0.0 0 148,135

Sort (cost=8.45..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=148,135)

  • Sort Key: mpd_2.last_due_date
  • Sort Method: quicksort Memory: 25kB
46. 148.135 148.135 ↓ 0.0 0 148,135

Index Scan using idx_miles_id_per_doc on milestone_per_document mpd_2 (cost=0.42..8.44 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=148,135)

  • Index Cond: (document_id = document.id)
  • Filter: (milestone_type = 2)
  • Rows Removed by Filter: 0
47. 148.135 296.270 ↓ 0.0 0 148,135

Group (cost=8.45..8.46 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=148,135)

  • Group Key: mpd_3.last_executed_at
48. 0.000 148.135 ↓ 0.0 0 148,135

Sort (cost=8.45..8.45 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=148,135)

  • Sort Key: mpd_3.last_executed_at
  • Sort Method: quicksort Memory: 25kB
49. 148.135 148.135 ↓ 0.0 0 148,135

Index Scan using idx_miles_id_per_doc on milestone_per_document mpd_3 (cost=0.42..8.44 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=148,135)

  • Index Cond: (document_id = document.id)
  • Filter: (milestone_type = 2)
  • Rows Removed by Filter: 0
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. 0.000 921.702 ↑ 1.0 1 20,037

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

52. 20.037 921.702 ↑ 1.0 1 20,037

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

  • Sort Key: ah_1.creation DESC
  • Sort Method: quicksort Memory: 25kB
53. 41.673 901.665 ↓ 2.0 2 20,037

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

54. 821.517 821.517 ↓ 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.011..0.041 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.507 ms
Execution time : 11,580.354 ms