explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O5EM

Settings
# exclusive inclusive rows x rows loops node
1. 87.181 11,798.275 ↓ 4,299.0 51,588 1

Unique (cost=161,949.95..161,950.55 rows=12 width=396) (actual time=11,221.555..11,798.275 rows=51,588 loops=1)

2.          

CTE vcl_filtered

3. 248.679 302.470 ↓ 1.3 614,474 1

Bitmap Heap Scan on vcl (cost=16,966.37..118,984.92 rows=490,177 width=8) (actual time=60.178..302.470 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. 53.791 53.791 ↓ 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=53.791..53.791 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,589.441 11,711.094 ↓ 12,345.8 148,150 1

Sort (cost=42,965.04..42,965.07 rows=12 width=396) (actual time=11,221.553..11,711.094 rows=148,150 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, mi2.execution_due_date, mi2.executed_at, (CASE WHEN (vi.current_qualification_label IS NULL) THEN NULL::text WHEN (vi.current_qualification_comment_id IS NOT NULL) THEN (SubPlan 2) 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 3) END), metadata.label, ((version.content)::text), office_user.name
  • Sort Method: external merge Disk: 549,616kB
6. 3,523.819 9,121.653 ↓ 12,345.8 148,150 1

Nested Loop Left Join (cost=11,205.21..42,964.82 rows=12 width=396) (actual time=503.468..9,121.653 rows=148,150 loops=1)

7. 373.347 3,295.148 ↓ 12,345.8 148,150 1

Hash Join (cost=11,204.79..42,304.80 rows=12 width=2,123) (actual time=503.407..3,295.148 rows=148,150 loops=1)

  • Hash Cond: ((ah.project_id = mcig.config_item_group_id) AND ((document.content ->> 'typeName'::text) = (metadata.content ->> 'typeName'::text)))
8. 71.153 2,916.532 ↓ 449.7 158,289 1

Hash Join (cost=11,042.58..42,117.84 rows=352 width=1,045) (actual time=498.121..2,916.532 rows=158,289 loops=1)

  • Hash Cond: (vi.version_id = vcl_filtered.version_id)
9. 0.000 2,347.476 ↓ 224.8 158,289 1

Nested Loop Left Join (cost=9.10..31,081.71 rows=704 width=1,053) (actual time=0.202..2,347.476 rows=158,289 loops=1)

  • Join Filter: (mi1.execution_sequential = 1)
10. 0.000 2,042.449 ↓ 224.8 158,274 1

Nested Loop Left Join (cost=8.68..30,471.19 rows=704 width=1,039) (actual time=0.197..2,042.449 rows=158,274 loops=1)

11. 19.105 1,589.691 ↓ 224.8 158,274 1

Nested Loop (cost=8.25..29,857.16 rows=704 width=1,021) (actual time=0.187..1,589.691 rows=158,274 loops=1)

12. 0.002 0.024 ↑ 1.0 1 1

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

13. 0.007 0.019 ↑ 1.0 1 1

Bitmap Heap Scan on user_project (cost=4.30..11.09 rows=1 width=16) (actual time=0.018..0.019 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
14. 0.012 0.012 ↑ 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.012..0.012 rows=2 loops=1)

  • Index Cond: (user_id = 6,301)
15. 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)
16. 42.197 1,570.562 ↓ 224.8 158,274 1

Nested Loop (cost=3.67..29,830.72 rows=704 width=1,019) (actual time=0.164..1,570.562 rows=158,274 loops=1)

17. 121.072 1,211.817 ↓ 224.2 158,274 1

Nested Loop (cost=3.25..29,403.75 rows=706 width=966) (actual time=0.156..1,211.817 rows=158,274 loops=1)

18. 0.000 774.197 ↓ 36.6 158,274 1

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

19. 32.090 315.859 ↓ 36.2 158,274 1

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

20.          

CTE action_ids

21. 0.009 0.009 ↑ 1.0 44 1

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

22. 0.131 0.650 ↓ 1.1 19 1

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

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

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

  • Filter: (action_type = 1)
  • Rows Removed by Filter: 2,935
24. 0.006 0.037 ↑ 1.0 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
25. 0.031 0.031 ↑ 1.0 44 1

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

26. 283.119 283.119 ↓ 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.013..14.901 rows=8,330 loops=19)

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

  • Index Cond: (workflow_id = ah.workflow_id)
28. 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)
29. 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)
30. 474.822 474.822 ↑ 1.0 1 158,274

Index Scan using idx_milestone_indicators_lookup on milestone_indicators mi1 (cost=0.42..0.86 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=158,274)

  • Index Cond: (version_id = version.id)
  • Filter: ((milestone_id = 1) AND (execution_sequential = 1))
  • Rows Removed by Filter: 3
31. 316.548 316.548 ↓ 0.0 0 158,274

Index Scan using idx_milestone_indicators_lookup on milestone_indicators mi2 (cost=0.42..0.85 rows=1 width=24) (actual time=0.001..0.002 rows=0 loops=158,274)

  • Index Cond: (version_id = version.id)
  • Filter: (milestone_id = 6)
  • Rows Removed by Filter: 3
32. 5.085 497.903 ↓ 274.7 54,936 1

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

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

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

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

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

35. 1.435 5.269 ↑ 1.0 680 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 784kB
36. 0.259 3.834 ↑ 1.0 680 1

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

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

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

38. 0.077 0.208 ↑ 1.0 680 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
39. 0.131 0.131 ↑ 1.0 680 1

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

  • Filter: (status = 1)
  • Rows Removed by Filter: 65
40. 592.600 592.600 ↑ 1.0 1 148,150

Index Scan using "commentaryPK" on commentary co (cost=0.42..0.63 rows=1 width=102) (actual time=0.004..0.004 rows=1 loops=148,150)

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

SubPlan (for Nested Loop Left Join)

42. 87.089 87.089 ↑ 1.0 1 87,089

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,089)

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

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

44. 40.074 1,622.997 ↑ 1.0 1 20,037

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

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

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

46. 1,502.775 1,502.775 ↓ 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.030..0.075 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
47. 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.458 ms
Execution time : 11,856.016 ms