explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v44B

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 5,992.812 ↑ 1.0 1 1

Unique (cost=496,826.40..496,826.42 rows=1 width=241) (actual time=5,992.812..5,992.812 rows=1 loops=1)

2. 0.017 5,992.798 ↑ 1.0 1 1

Sort (cost=496,826.40..496,826.41 rows=1 width=241) (actual time=5,992.797..5,992.798 rows=1 loops=1)

  • Sort Key: (change_tz(gbc.occurrence_date, 'UTC'::character varying, tz.time_zone)), gbc.id, gbc.client_entity_seq_id, gbc.title, (array_to_string(array_agg(DISTINCT ct.name), ', '::text)), gbc.status_id, (array_to_string(array_agg(DISTINCT rel.name), ', '::text)), tz.id, (array_to_string(array_agg(DISTINCT ct_1.name), ', '::text)), (array_to_string(array_agg(DISTINCT cst.name), ', '::text))
  • Sort Method: quicksort Memory: 48kB
3. 0.069 5,992.781 ↑ 1.0 1 1

Group (cost=496,826.27..496,826.40 rows=1 width=241) (actual time=5,992.775..5,992.781 rows=1 loops=1)

  • Group Key: gbc.id, tz.id, (array_to_string(array_agg(DISTINCT ct_1.name), ', '::text)), (array_to_string(array_agg(DISTINCT cst.name), ', '::text)), (array_to_string(array_agg(DISTINCT concat(rel.name, ':;', rel.id)), '::'::text)), (array_to_string(array_agg(DISTINCT rel.name), ', '::text)), (array_to_string(array_agg(DISTINCT ct.name), ', '::text))
4. 0.062 5,992.712 ↓ 3.0 3 1

Sort (cost=496,826.27..496,826.27 rows=1 width=221) (actual time=5,992.712..5,992.712 rows=3 loops=1)

  • Sort Key: gbc.id, tz.id, (array_to_string(array_agg(DISTINCT ct_1.name), ', '::text)), (array_to_string(array_agg(DISTINCT cst.name), ', '::text)), (array_to_string(array_agg(DISTINCT concat(rel.name, ':;', rel.id)), '::'::text)), (array_to_string(array_agg(DISTINCT rel.name), ', '::text)), (array_to_string(array_agg(DISTINCT ct.name), ', '::text))
  • Sort Method: quicksort Memory: 95kB
5. 0.020 5,992.650 ↓ 3.0 3 1

Nested Loop Left Join (cost=488,995.25..496,826.26 rows=1 width=221) (actual time=5,990.385..5,992.650 rows=3 loops=1)

  • Filter: (((du.user_id = 114,707) OR ('{114707}'::integer[] <@ ug.user_ids)) AND ((du.user_id = 114,707) OR ('{114707}'::integer[] <@ ug.user_ids)))
  • Rows Removed by Filter: 36
6. 0.033 5,992.630 ↓ 39.0 39 1

Nested Loop (cost=488,995.18..496,826.19 rows=1 width=229) (actual time=5,988.664..5,992.630 rows=39 loops=1)

  • Join Filter: (du.role_group_id = ANY (wftc.task_owners))
  • Rows Removed by Join Filter: 12
7. 2.238 5,992.495 ↓ 17.0 17 1

Hash Join (cost=488,994.98..496,804.05 rows=1 width=263) (actual time=5,988.646..5,992.495 rows=17 loops=1)

  • Hash Cond: ((du.entity_id = gbc.id) AND (du.role_group_id = rg.id))
  • Join Filter: (((du.role_group_id = 3,051) AND (gbc.status_id = ANY ('{4,3851,3849}'::integer[])) AND (gb.status_id = ANY ('{3846}'::integer[]))) OR ((du.role_group_id = 3,144) AND (gbc.status_id = ANY ('{4,3851,3849}'::integer[])) AND (gb.status_id = ANY ('{3846}'::integer[]))) OR ((du.role_group_id = 3,147) AND (gbc.status_id = ANY ('{4,3851,3849}'::integer[])) AND (gb.status_id = ANY ('{3846}'::integer[]))) OR ((du.entity_id = gbc.governance_body_id) AND (du.role_group_id = 3,046) AND (gbc.status_id = ANY ('{}'::integer[]))))
8. 5.451 6.191 ↓ 1.7 12,079 1

Bitmap Heap Scan on domain_user du (cost=46.59..7,691.01 rows=7,221 width=16) (actual time=1.027..6.191 rows=12,079 loops=1)

  • Recheck Cond: ((role_group_id = 3,051) OR (role_group_id = 3,144) OR (role_group_id = 3,147) OR (role_group_id = 3,046))
  • Heap Blocks: exact=2,505
9. 0.001 0.740 ↓ 0.0 0 1

BitmapOr (cost=46.59..46.59 rows=7,222 width=0) (actual time=0.740..0.740 rows=0 loops=1)

10. 0.230 0.230 ↓ 1.7 3,008 1

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..10.78 rows=1,806 width=0) (actual time=0.230..0.230 rows=3,008 loops=1)

  • Index Cond: (role_group_id = 3,051)
11. 0.322 0.322 ↓ 2.6 4,703 1

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..10.78 rows=1,806 width=0) (actual time=0.322..0.322 rows=4,703 loops=1)

  • Index Cond: (role_group_id = 3,144)
12. 0.158 0.158 ↓ 2.2 3,921 1

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..10.78 rows=1,806 width=0) (actual time=0.158..0.158 rows=3,921 loops=1)

  • Index Cond: (role_group_id = 3,147)
13. 0.029 0.029 ↑ 4.0 447 1

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..10.78 rows=1,806 width=0) (actual time=0.029..0.029 rows=447 loops=1)

  • Index Cond: (role_group_id = 3,046)
14. 3.768 5,984.066 ↑ 71.8 462 1

Hash (cost=488,835.55..488,835.55 rows=33,187 width=263) (actual time=5,984.066..5,984.066 rows=462 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 11,323kB
15. 0.123 5,980.298 ↑ 71.8 462 1

Nested Loop (cost=485,088.01..488,835.55 rows=33,187 width=263) (actual time=3,545.512..5,980.298 rows=462 loops=1)

16. 0.390 5,980.049 ↑ 71.8 6 1

Merge Left Join (cost=485,087.88..488,690.47 rows=431 width=259) (actual time=3,545.492..5,980.049 rows=6 loops=1)

  • Merge Cond: (gbc.id = les.entity_id)
17. 0.208 518.682 ↑ 35.3 6 1

Merge Left Join (cost=63,527.66..64,150.28 rows=212 width=227) (actual time=462.947..518.682 rows=6 loops=1)

  • Merge Cond: (gbc.id = lef.entity_id)
18. 0.757 260.206 ↑ 35.3 6 1

Merge Left Join (cost=26,676.62..27,228.77 rows=212 width=195) (actual time=236.986..260.206 rows=6 loops=1)

  • Merge Cond: (gbc.id = gbrl.entity_id)
19. 0.028 190.740 ↑ 35.3 6 1

Nested Loop Left Join (cost=17,238.39..17,583.54 rows=212 width=131) (actual time=173.484..190.740 rows=6 loops=1)

20. 0.870 190.652 ↑ 35.3 6 1

Merge Left Join (cost=17,238.25..17,509.25 rows=212 width=127) (actual time=173.468..190.652 rows=6 loops=1)

  • Merge Cond: (gbc.id = gbccl.governance_body_child_id)
21. 0.010 28.783 ↑ 35.3 6 1

Sort (cost=1,833.38..1,833.63 rows=212 width=95) (actual time=28.780..28.783 rows=6 loops=1)

  • Sort Key: gbc.id
  • Sort Method: quicksort Memory: 25kB
22. 25.187 28.773 ↑ 35.3 6 1

Hash Left Join (cost=138.76..1,829.44 rows=212 width=95) (actual time=11.674..28.773 rows=6 loops=1)

  • Hash Cond: (gbc.time_zone_id = tz.id)
  • Filter: (((change_tz(gbc.occurrence_date, 'UTC'::character varying, tz.time_zone))::date > (change_tz(now(), 'UTC'::character varying, tz.time_zone))::date) AND ((change_tz(gbc.occurrence_date, 'UTC'::character varying, tz.time_zone))::date <= ((change_tz(now(), 'UTC'::character varying, tz.time_zone))::date + 7)))
  • Rows Removed by Filter: 1,107
23. 2.541 3.569 ↑ 1.7 1,113 1

Bitmap Heap Scan on governance_body_child gbc (cost=137.63..1,802.76 rows=1,912 width=79) (actual time=1.165..3.569 rows=1,113 loops=1)

  • Recheck Cond: ((client_id = 1,014) AND ((status_id = ANY ('{4,3851,3849}'::integer[])) OR (status_id = ANY ('{4,3851,3849}'::integer[])) OR (status_id = ANY ('{4,3851,3849}'::integer[])) OR (status_id = ANY ('{}'::integer[]))))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1,498
  • Heap Blocks: exact=1,017
24. 0.060 1.028 ↓ 0.0 0 1

BitmapAnd (cost=137.63..137.63 rows=3,007 width=0) (actual time=1.028..1.028 rows=0 loops=1)

25. 0.301 0.301 ↓ 1.0 4,157 1

Bitmap Index Scan on idx_gbc_client_id (cost=0.00..33.12 rows=3,993 width=0) (actual time=0.301..0.301 rows=4,157 loops=1)

  • Index Cond: (client_id = 1,014)
26. 0.001 0.667 ↓ 0.0 0 1

BitmapOr (cost=104.16..104.16 rows=12,801 width=0) (actual time=0.667..0.667 rows=0 loops=1)

27. 0.301 0.301 ↓ 1.0 4,338 1

Bitmap Index Scan on idx_gbc_status_id (cost=0.00..34.00 rows=4,267 width=0) (actual time=0.301..0.301 rows=4,338 loops=1)

  • Index Cond: (status_id = ANY ('{4,3851,3849}'::integer[]))
28. 0.181 0.181 ↓ 1.0 4,338 1

Bitmap Index Scan on idx_gbc_status_id (cost=0.00..34.00 rows=4,267 width=0) (actual time=0.181..0.181 rows=4,338 loops=1)

  • Index Cond: (status_id = ANY ('{4,3851,3849}'::integer[]))
29. 0.184 0.184 ↓ 1.0 4,338 1

Bitmap Index Scan on idx_gbc_status_id (cost=0.00..34.00 rows=4,267 width=0) (actual time=0.184..0.184 rows=4,338 loops=1)

  • Index Cond: (status_id = ANY ('{4,3851,3849}'::integer[]))
30. 0.000 0.000 ↓ 0.0 0 1

Bitmap Index Scan on idx_gbc_status_id (cost=0.00..1.24 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (status_id = ANY ('{}'::integer[]))
31. 0.006 0.017 ↑ 1.0 41 1

Hash (cost=1.04..1.04 rows=41 width=20) (actual time=0.017..0.017 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
32. 0.011 0.011 ↑ 1.0 41 1

Seq Scan on time_zone tz (cost=0.00..1.04 rows=41 width=20) (actual time=0.004..0.011 rows=41 loops=1)

33. 89.121 160.999 ↓ 1.4 14,650 1

GroupAggregate (cost=15,404.88..15,652.98 rows=10,120 width=68) (actual time=68.802..160.999 rows=14,650 loops=1)

  • Group Key: gbccl.governance_body_child_id
34. 12.450 71.878 ↑ 1.0 56,961 1

Sort (cost=15,404.88..15,476.11 rows=59,359 width=39) (actual time=68.785..71.878 rows=56,961 loops=1)

  • Sort Key: gbccl.governance_body_child_id
  • Sort Method: quicksort Memory: 7,482kB
35. 12.947 59.428 ↓ 1.0 59,361 1

Hash Left Join (cost=12,574.68..13,145.84 rows=59,359 width=39) (actual time=42.388..59.428 rows=59,361 loops=1)

  • Hash Cond: (gbccl.contract_id = ct.id)
36. 4.179 4.179 ↓ 1.0 59,361 1

Seq Scan on governance_body_child_contract_link gbccl (cost=0.00..496.36 rows=59,359 width=8) (actual time=0.005..4.179 rows=59,361 loops=1)

37. 8.695 42.302 ↓ 1.0 52,310 1

Hash (cost=12,459.79..12,459.79 rows=52,223 width=39) (actual time=42.302..42.302 rows=52,310 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,255kB
38. 33.607 33.607 ↓ 1.0 52,310 1

Seq Scan on contract ct (cost=0.00..12,459.79 rows=52,223 width=39) (actual time=0.007..33.607 rows=52,310 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 3,564
39. 0.060 0.060 ↑ 1.0 1 6

Index Scan using pk_governance_body on governance_body gb (cost=0.13..0.35 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=6)

  • Index Cond: (gbc.governance_body_id = id)
40. 25.243 68.709 ↑ 1.1 12,912 1

GroupAggregate (cost=9,438.23..9,612.40 rows=14,727 width=68) (actual time=42.725..68.709 rows=12,912 loops=1)

  • Group Key: gbrl.entity_id
41. 3.634 43.466 ↑ 1.1 12,923 1

Sort (cost=9,438.23..9,455.98 rows=14,791 width=43) (actual time=42.703..43.466 rows=12,923 loops=1)

  • Sort Key: gbrl.entity_id
  • Sort Method: quicksort Memory: 1,482kB
42. 2.608 39.832 ↓ 1.0 14,793 1

Hash Left Join (cost=8,801.13..8,946.49 rows=14,791 width=43) (actual time=35.859..39.832 rows=14,793 loops=1)

  • Hash Cond: (gbrl.relation_id = rel.id)
43. 1.531 1.531 ↓ 1.0 14,793 1

Index Only Scan using idx_governance_body_relation_link_1 on governance_body_relation_link gbrl (cost=0.14..126.86 rows=14,791 width=8) (actual time=0.011..1.531 rows=14,793 loops=1)

  • Index Cond: (entity_type_id = 87)
  • Heap Fetches: 59
44. 12.459 35.693 ↓ 1.0 76,257 1

Hash (cost=8,633.25..8,633.25 rows=76,248 width=39) (actual time=35.693..35.693 rows=76,257 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,421kB
45. 23.234 23.234 ↓ 1.0 76,257 1

Seq Scan on relation rel (cost=0.00..8,633.25 rows=76,248 width=39) (actual time=0.006..23.234 rows=76,257 loops=1)

46. 41.913 258.268 ↑ 2.4 3,122 1

GroupAggregate (cost=36,851.04..36,904.39 rows=7,622 width=36) (actual time=210.333..258.268 rows=3,122 loops=1)

  • Group Key: lef.entity_id
47. 22.759 216.355 ↓ 7.8 59,208 1

Sort (cost=36,851.04..36,860.19 rows=7,622 width=20) (actual time=210.316..216.355 rows=59,208 loops=1)

  • Sort Key: lef.entity_id
  • Sort Method: quicksort Memory: 8,377kB
48. 11.216 193.596 ↓ 10.1 76,627 1

Nested Loop (cost=831.75..36,615.14 rows=7,622 width=20) (actual time=5.774..193.596 rows=76,627 loops=1)

49. 0.058 0.058 ↑ 1.0 21 1

Seq Scan on contract_type ct_1 (cost=0.00..3.53 rows=21 width=20) (actual time=0.013..0.058 rows=21 loops=1)

  • Filter: (client_id = 1,014)
  • Rows Removed by Filter: 222
50. 72.240 182.322 ↓ 3.5 3,649 21

Bitmap Heap Scan on link_entity_function lef (cost=831.75..1,742.37 rows=1,038 width=8) (actual time=5.603..8.682 rows=3,649 loops=21)

  • Recheck Cond: ((function_id = ct_1.id) AND (entity_type_id = 87))
  • Heap Blocks: exact=60,296
51. 5.040 110.082 ↓ 0.0 0 21

BitmapAnd (cost=831.75..831.75 rows=1,038 width=0) (actual time=5.242..5.242 rows=0 loops=21)

52. 18.228 18.228 ↑ 4.5 10,437 21

Bitmap Index Scan on idx_link_entity_function_3 (cost=0.00..285.69 rows=46,528 width=0) (actual time=0.868..0.868 rows=10,437 loops=21)

  • Index Cond: (function_id = ct_1.id)
53. 86.814 86.814 ↓ 1.0 90,969 21

Bitmap Index Scan on idx_link_entity_function_4 (cost=0.00..535.23 rows=88,194 width=0) (actual time=4.134..4.134 rows=90,969 loops=21)

  • Index Cond: (entity_type_id = 87)
54. 3,308.949 5,460.977 ↑ 11.1 3,122 1

GroupAggregate (cost=421,560.22..424,463.53 rows=34,531 width=36) (actual time=1,906.588..5,460.977 rows=3,122 loops=1)

  • Group Key: les.entity_id
55. 1,011.501 2,152.028 ↓ 3.0 2,353,552 1

Sort (cost=421,560.22..422,488.86 rows=773,863 width=28) (actual time=1,906.566..2,152.028 rows=2,353,552 loops=1)

  • Sort Key: les.entity_id
  • Sort Method: external merge Disk: 121,320kB
56. 599.009 1,140.527 ↓ 3.9 3,048,557 1

Hash Join (cost=18,477.62..385,228.81 rows=773,863 width=28) (actual time=139.919..1,140.527 rows=3,048,557 loops=1)

  • Hash Cond: (les.service_id = cst.id)
57. 413.463 541.191 ↓ 1.0 3,092,503 1

Bitmap Heap Scan on link_entity_service les (cost=18,429.29..381,364.18 rows=3,027,680 width=8) (actual time=138.758..541.191 rows=3,092,503 loops=1)

  • Recheck Cond: (entity_type_id = 87)
  • Heap Blocks: exact=62,446
58. 127.728 127.728 ↓ 1.0 3,129,676 1

Bitmap Index Scan on idx_link_entity_service_5 (cost=0.00..18,065.97 rows=3,027,680 width=0) (actual time=127.728..127.728 rows=3,129,676 loops=1)

  • Index Cond: (entity_type_id = 87)
59. 0.117 0.327 ↑ 1.0 845 1

Hash (cost=46.48..46.48 rows=845 width=28) (actual time=0.327..0.327 rows=845 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 61kB
60. 0.210 0.210 ↑ 1.0 845 1

Index Scan using idx_contract_sub_type_client_id on contract_sub_type cst (cost=0.13..46.48 rows=845 width=28) (actual time=0.015..0.210 rows=845 loops=1)

  • Index Cond: (client_id = 1,014)
61. 0.049 0.126 ↑ 1.0 77 6

Materialize (cost=0.13..72.16 rows=77 width=4) (actual time=0.003..0.021 rows=77 loops=6)

62. 0.077 0.077 ↑ 1.0 77 1

Index Scan using idx_role_group_entity_type_id on role_group rg (cost=0.13..71.97 rows=77 width=4) (actual time=0.016..0.077 rows=77 loops=1)

  • Index Cond: (entity_type_id = 87)
63. 0.102 0.102 ↓ 3.0 3 17

Index Scan using idx_work_flow_task_configuration_2 on work_flow_task_configuration wftc (cost=0.20..22.13 rows=1 width=20) (actual time=0.002..0.006 rows=3 loops=17)

  • Index Cond: ((task_id = ANY (gbc.next_manual_task_ids)) AND (task_id = ANY (gbc.next_manual_task_ids)))
64. 0.000 0.000 ↓ 0.0 0 39

Index Scan using pk_user_group on user_group ug (cost=0.07..0.07 rows=1 width=45) (actual time=0.000..0.000 rows=0 loops=39)

  • Index Cond: (id = du.user_group_id)
Planning time : 3.037 ms
Execution time : 6,012.231 ms