explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wm7i

Settings
# exclusive inclusive rows x rows loops node
1. 84.371 16,601.393 ↓ 4,298.6 51,583 1

Unique (cost=161,228.35..161,228.83 rows=12 width=460) (actual time=15,837.010..16,601.393 rows=51,583 loops=1)

2.          

CTE vcl_filtered

3. 272.037 348.911 ↓ 1.3 614,474 1

Bitmap Heap Scan on vcl (cost=16,966.37..118,984.92 rows=490,177 width=8) (actual time=83.144..348.911 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. 76.874 76.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=76.874..76.874 rows=769,571 loops=1)

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

Sort (cost=42,243.44..42,243.47 rows=12 width=460) (actual time=15,837.008..16,517.022 rows=148,135 loops=1)

  • Sort Key: document.code, version.sequential, version.id, document.title, vi.office_name, vi.current_step_label, (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: 546,456kB
6. 3,967.136 8,611.766 ↓ 12,344.6 148,135 1

Hash Join (cost=11,195.87..42,243.22 rows=12 width=460) (actual time=563.642..8,611.766 rows=148,135 loops=1)

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

Hash Join (cost=11,065.97..41,438.33 rows=2,251 width=1,107) (actual time=554.908..3,406.788 rows=1,107,918 loops=1)

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

Hash Join (cost=32.49..30,387.93 rows=4,502 width=1,115) (actual time=0.497..2,618.439 rows=1,107,918 loops=1)

  • Hash Cond: (ah.project_id = mcig.config_item_group_id)
9. 37.940 2,456.588 ↓ 224.8 158,274 1

Nested Loop Left Join (cost=8.67..30,305.90 rows=704 width=1,115) (actual time=0.191..2,456.588 rows=158,274 loops=1)

10. 19.297 2,102.100 ↓ 224.8 158,274 1

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

11. 0.003 0.036 ↑ 1.0 1 1

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

12. 0.011 0.028 ↑ 1.0 1 1

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

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

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

  • Index Cond: (id = user_project.office_id)
15. 137.811 2,082.767 ↓ 224.8 158,274 1

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

16. 117.732 1,628.408 ↓ 224.2 158,274 1

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

17. 7.809 1,035.854 ↓ 36.6 158,274 1

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

18. 34.334 394.949 ↓ 36.2 158,274 1

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

19.          

CTE action_ids

20. 0.008 0.008 ↑ 1.0 44 1

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

21. 0.131 0.698 ↓ 1.1 19 1

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

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

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

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

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

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

25. 359.917 359.917 ↓ 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.017..18.943 rows=8,330 loops=19)

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

Index Scan using "documentPK" on document (cost=0.42..0.65 rows=1 width=781) (actual time=0.003..0.003 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. 316.548 316.548 ↑ 1.0 1 158,274

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

  • Index Cond: (id = vi.current_qualification_comment_id)
30. 0.108 0.301 ↑ 1.0 680 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
31. 0.193 0.193 ↑ 1.0 680 1

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

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

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

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

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

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

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

35. 1.640 8.651 ↑ 1.0 756 1

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

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

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

37.          

SubPlan (for Hash Join)

38. 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)
39. 0.000 1,142.109 ↑ 1.0 1 20,037

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

40. 40.074 1,142.109 ↑ 1.0 1 20,037

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

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

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

42. 1,021.887 1,021.887 ↓ 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.013..0.051 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
43. 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 : 3.158 ms
Execution time : 16,667.478 ms