explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lKif

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 42,896.300 ↓ 3.4 17 1

GroupAggregate (cost=2,456,317.40..2,456,365.64 rows=5 width=3,091) (actual time=42,891.233..42,896.300 rows=17 loops=1)

  • Group Key: issue.id, r.id, c.id, lcmt.name, cy.short_name, v.status, (CASE WHEN (cl.name IS NOT NULL) THEN initcap(lower((cl.name)::text)) WHEN (slll.value IS NOT NULL) THEN initcap(lower((slll.value)::text)) WHEN (sl.name IS NOT NULL) THEN initcap(lower((sl.name)::text)) ELSE initcap(lower((et.description)::text)) END), tz.id, res.id, rcl.id, pt.id, gbc.id, gb.id, t.name, v.name, ets.description, et.id, (array_to_string(array_agg(DISTINCT rg1.name), ', '::text)), (array_to_string(array_agg(DISTINCT rg2.name), ','::text)), (array_to_string(array_agg(DISTINCT dc.name), ', '::text)), (array_to_string(array_agg(DISTINCT dc2.name), ', '::text)), (array_to_string(array_agg(DISTINCT ct.name), ', '::text)), (array_to_string(array_agg(DISTINCT cst.name), ', '::text)), (array_to_string(array_agg(DISTINCT dc_1.name), ', '::text)), (array_to_string(array_agg(DISTINCT hub.name), ', '::text)), (array_to_string(array_agg(DISTINCT mkt.name), ', '::text)), (array_to_string(array_agg(DISTINCT icp.name), ', '::text)), (array_to_string(array_agg(DISTINCT cc.name), ', '::text)), (array_to_string(array_agg(DISTINCT hub2.name), ', '::text)), (array_to_string(array_agg(DISTINCT mkt2.name), ', '::text)), (array_to_string(array_agg(DISTINCT icp2.name), ', '::text)), (array_to_string(array_agg(DISTINCT cc2.name), ', '::text))
2.          

Initplan (for GroupAggregate)

3. 0.986 196.627 ↓ 2.4 812 1

HashAggregate (cost=72,367.29..72,370.68 rows=339 width=4) (actual time=196.452..196.627 rows=812 loops=1)

  • Group Key: edl.entity_id
4.          

CTE user_data_filter

5. 31.080 31.080 ↑ 1.0 78,250 1

Seq Scan on user_data_access (cost=0.00..2,997.45 rows=79,305 width=23) (actual time=0.015..31.080 rows=78,250 loops=1)

  • Filter: ((NOT deleted) AND (user_id = 1,044))
  • Rows Removed by Filter: 58,837
6. 0.799 195.641 ↓ 9.0 3,063 1

Append (cost=16,821.96..69,369.00 rows=339 width=4) (actual time=136.447..195.641 rows=3,063 loops=1)

7. 11.075 157.904 ↓ 9.2 3,063 1

Merge Join (cost=16,821.96..17,269.05 rows=334 width=4) (actual time=136.446..157.904 rows=3,063 loops=1)

  • Merge Cond: ((edl.ancestor_id = da.entity_id) AND (edl.ancestor_type_id = da.entity_type_id))
8. 2.601 14.268 ↑ 3.6 5,380 1

Sort (cost=12,207.42..12,256.20 rows=19,515 width=12) (actual time=13.421..14.268 rows=5,380 loops=1)

  • Sort Key: edl.ancestor_id, edl.ancestor_type_id
  • Sort Method: quicksort Memory: 445kB
9. 11.667 11.667 ↑ 3.6 5,380 1

Index Scan using idx_entity_data_link_entity_type_id on entity_data_link edl (cost=0.43..10,816.75 rows=19,515 width=12) (actual time=0.096..11.667 rows=5,380 loops=1)

  • Index Cond: (entity_type_id = 17)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 31,931
10. 56.428 132.561 ↓ 1.3 52,732 1

Sort (cost=4,614.54..4,713.67 rows=39,652 width=8) (actual time=122.873..132.561 rows=52,732 loops=1)

  • Sort Key: da.entity_id, da.entity_type_id
  • Sort Method: external sort Disk: 1,688kB
11. 76.133 76.133 ↓ 2.0 78,250 1

CTE Scan on user_data_filter da (cost=0.00..1,586.10 rows=39,652 width=8) (actual time=0.018..76.133 rows=78,250 loops=1)

  • Filter: self_access
12. 0.002 36.938 ↓ 0.0 0 1

Merge Join (cost=51,753.05..52,094.86 rows=5 width=4) (actual time=36.938..36.938 rows=0 loops=1)

  • Merge Cond: ((edl_1.ancestor_id = da_1.entity_id) AND (edl_1.ancestor_type_id = da_1.entity_type_id))
  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
13. 1.494 29.441 ↑ 5,632.0 1 1

Sort (cost=47,138.51..47,152.59 rows=5,632 width=16) (actual time=29.440..29.441 rows=1 loops=1)

  • Sort Key: edl_1.ancestor_id, edl_1.ancestor_type_id
  • Sort Method: quicksort Memory: 389kB
14. 5.583 27.947 ↑ 1.3 4,198 1

Nested Loop (cost=0.86..46,787.65 rows=5,632 width=16) (actual time=0.119..27.947 rows=4,198 loops=1)

15. 11.604 11.604 ↑ 3.6 5,380 1

Index Scan using idx_entity_data_link_entity_type_id on entity_data_link edc (cost=0.43..10,816.75 rows=19,515 width=12) (actual time=0.097..11.604 rows=5,380 loops=1)

  • Index Cond: (entity_type_id = 17)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 31,931
16. 10.760 10.760 ↑ 1.0 1 5,380

Index Scan using idx_entity_data_link_entity_type_id_entity_id on entity_data_link edl_1 (cost=0.43..1.83 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=5,380)

  • Index Cond: ((entity_type_id = edc.ancestor_type_id) AND (entity_id = edc.ancestor_id))
  • Filter: (parent AND (NOT deleted))
  • Rows Removed by Filter: 2
17. 0.007 7.495 ↓ 0.0 0 1

Sort (cost=4,614.54..4,713.67 rows=39,652 width=40) (actual time=7.494..7.495 rows=0 loops=1)

  • Sort Key: da_1.entity_id, da_1.entity_type_id
  • Sort Method: quicksort Memory: 25kB
18. 7.488 7.488 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..1,586.10 rows=39,652 width=40) (actual time=7.488..7.488 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 78,250
19. 0.071 42,890.387 ↓ 3.4 17 1

Sort (cost=2,383,946.72..2,383,946.73 rows=5 width=3,534) (actual time=42,890.382..42,890.387 rows=17 loops=1)

  • Sort Key: issue.id, r.id, c.id, lcmt.name, cy.short_name, v.status, (CASE WHEN (cl.name IS NOT NULL) THEN initcap(lower((cl.name)::text)) WHEN (slll.value IS NOT NULL) THEN initcap(lower((slll.value)::text)) WHEN (sl.name IS NOT NULL) THEN initcap(lower((sl.name)::text)) ELSE initcap(lower((et.description)::text)) END), tz.id, res.id, rcl.id, pt.id, gbc.id, gb.id, t.name, v.name, ets.description, et.id, (array_to_string(array_agg(DISTINCT rg1.name), ', '::text)), (array_to_string(array_agg(DISTINCT rg2.name), ','::text)), (array_to_string(array_agg(DISTINCT dc.name), ', '::text)), (array_to_string(array_agg(DISTINCT dc2.name), ', '::text)), (array_to_string(array_agg(DISTINCT ct.name), ', '::text)), (array_to_string(array_agg(DISTINCT cst.name), ', '::text)), (array_to_string(array_agg(DISTINCT dc_1.name), ', '::text)), (array_to_string(array_agg(DISTINCT hub.name), ', '::text)), (array_to_string(array_agg(DISTINCT mkt.name), ', '::text)), (array_to_string(array_agg(DISTINCT icp.name), ', '::text)), (array_to_string(array_agg(DISTINCT cc.name), ', '::text)), (array_to_string(array_agg(DISTINCT hub2.name), ', '::text)), (array_to_string(array_agg(DISTINCT mkt2.name), ', '::text)), (array_to_string(array_agg(DISTINCT icp2.name), ', '::text)), (array_to_string(array_agg(DISTINCT cc2.name), ', '::text))
  • Sort Method: quicksort Memory: 48kB
20. 0.078 42,890.316 ↓ 3.4 17 1

Nested Loop Left Join (cost=2,359,729.38..2,383,946.66 rows=5 width=3,534) (actual time=42,552.213..42,890.316 rows=17 loops=1)

21. 0.083 42,890.102 ↓ 3.4 17 1

Merge Left Join (cost=2,359,728.95..2,383,911.98 rows=5 width=3,596) (actual time=42,552.181..42,890.102 rows=17 loops=1)

  • Merge Cond: (issue.id = leccc.entity_id)
22. 0.080 42,888.363 ↓ 3.4 17 1

Merge Left Join (cost=2,359,448.91..2,383,607.34 rows=5 width=3,564) (actual time=42,551.202..42,888.363 rows=17 loops=1)

  • Merge Cond: (issue.id = lecicp.entity_id)
23. 0.080 42,886.675 ↓ 3.4 17 1

Merge Left Join (cost=2,359,167.77..2,383,301.81 rows=5 width=3,532) (actual time=42,550.247..42,886.675 rows=17 loops=1)

  • Merge Cond: (issue.id = lecm.entity_id)
24. 0.089 42,884.954 ↓ 3.4 17 1

Merge Left Join (cost=2,358,885.69..2,382,995.34 rows=5 width=3,500) (actual time=42,549.257..42,884.954 rows=17 loops=1)

  • Merge Cond: (issue.id = lech.entity_id)
25. 0.080 42,883.222 ↓ 3.4 17 1

Merge Left Join (cost=2,358,604.51..2,382,689.77 rows=5 width=3,468) (actual time=42,548.290..42,883.222 rows=17 loops=1)

  • Merge Cond: (issue.id = lecc.entity_id)
26. 0.080 42,881.495 ↓ 3.4 17 1

Merge Left Join (cost=2,358,318.39..2,382,379.02 rows=5 width=3,436) (actual time=42,547.309..42,881.495 rows=17 loops=1)

  • Merge Cond: (issue.id = leicp.entity_id)
27. 0.080 42,879.814 ↓ 3.4 17 1

Merge Left Join (cost=2,358,032.18..2,382,068.38 rows=5 width=3,404) (actual time=42,546.388..42,879.814 rows=17 loops=1)

  • Merge Cond: (issue.id = lem.entity_id)
28. 0.082 42,878.071 ↓ 3.4 17 1

Merge Left Join (cost=2,357,732.25..2,381,744.13 rows=5 width=3,372) (actual time=42,545.380..42,878.071 rows=17 loops=1)

  • Merge Cond: (issue.id = leh.entity_id)
29. 0.028 42,876.339 ↓ 3.4 17 1

Nested Loop Left Join (cost=2,357,446.00..2,381,433.45 rows=5 width=3,340) (actual time=42,544.410..42,876.339 rows=17 loops=1)

  • Join Filter: (cl.client_id = issue.client_id)
30. 0.312 42,876.243 ↓ 3.4 17 1

Merge Left Join (cost=2,357,445.59..2,381,431.08 rows=5 width=3,319) (actual time=42,544.390..42,876.243 rows=17 loops=1)

  • Merge Cond: (issue.id = ledc.entity_id)
31. 0.574 42,851.649 ↓ 3.4 17 1

Merge Left Join (cost=2,353,866.89..2,377,457.35 rows=5 width=3,287) (actual time=42,527.685..42,851.649 rows=17 loops=1)

  • Merge Cond: (issue.id = les.entity_id)
32. 0.682 42,770.124 ↓ 8.5 17 1

Merge Left Join (cost=2,337,608.72..2,360,149.56 rows=2 width=3,255) (actual time=42,470.753..42,770.124 rows=17 loops=1)

  • Merge Cond: (issue.id = lef.entity_id)
33. 0.679 42,750.354 ↓ 8.5 17 1

Merge Left Join (cost=2,337,608.16..2,352,268.54 rows=2 width=3,223) (actual time=42,470.702..42,750.354 rows=17 loops=1)

  • Merge Cond: (issue.id = ller.entity_id)
34. 0.755 42,716.981 ↓ 8.5 17 1

Merge Left Join (cost=2,337,607.46..2,342,843.58 rows=2 width=3,159) (actual time=42,470.604..42,716.981 rows=17 loops=1)

  • Merge Cond: (issue.id = lec.entity_id)
35. 0.037 199.015 ↓ 17.0 17 1

Sort (cost=50.73..50.73 rows=1 width=3,095) (actual time=199.010..199.015 rows=17 loops=1)

  • Sort Key: issue.id
  • Sort Method: quicksort Memory: 33kB
36. 0.025 198.978 ↓ 17.0 17 1

Nested Loop Left Join (cost=23.29..50.72 rows=1 width=3,095) (actual time=198.051..198.978 rows=17 loops=1)

37. 0.031 198.902 ↓ 17.0 17 1

Nested Loop Left Join (cost=22.86..49.70 rows=1 width=3,060) (actual time=198.035..198.902 rows=17 loops=1)

38. 0.033 198.837 ↓ 17.0 17 1

Nested Loop Left Join (cost=22.57..48.19 rows=1 width=3,020) (actual time=198.018..198.837 rows=17 loops=1)

39. 0.018 198.770 ↓ 17.0 17 1

Nested Loop Left Join (cost=22.29..45.69 rows=1 width=2,991) (actual time=198.000..198.770 rows=17 loops=1)

40. 0.020 198.735 ↓ 17.0 17 1

Nested Loop Left Join (cost=22.01..45.32 rows=1 width=2,974) (actual time=197.992..198.735 rows=17 loops=1)

41. 0.048 198.698 ↓ 17.0 17 1

Nested Loop Left Join (cost=21.73..42.82 rows=1 width=2,951) (actual time=197.983..198.698 rows=17 loops=1)

  • Join Filter: ((lcmt.client_id = issue.client_id) AND (issue.type_id = lcmt.type_id))
  • Rows Removed by Join Filter: 136
42. 0.022 198.548 ↓ 17.0 17 1

Nested Loop Left Join (cost=21.73..40.87 rows=1 width=2,935) (actual time=197.972..198.548 rows=17 loops=1)

  • Join Filter: (pt.id = issue.priority)
  • Rows Removed by Join Filter: 16
43. 0.014 198.509 ↓ 17.0 17 1

Nested Loop Left Join (cost=21.73..39.76 rows=1 width=2,419) (actual time=197.965..198.509 rows=17 loops=1)

  • Join Filter: ((rcl.client_id = issue.client_id) AND (res.id = rcl.responsibility_id))
44. 0.032 198.478 ↓ 17.0 17 1

Nested Loop Left Join (cost=21.73..38.72 rows=1 width=1,899) (actual time=197.959..198.478 rows=17 loops=1)

  • Join Filter: (res.id = issue.responsibility_id)
  • Rows Removed by Join Filter: 51
45. 0.051 198.429 ↓ 17.0 17 1

Nested Loop Left Join (cost=21.73..37.65 rows=1 width=1,383) (actual time=197.950..198.429 rows=17 loops=1)

  • Join Filter: (tz.id = issue.time_zone_id)
  • Rows Removed by Join Filter: 212
46. 0.028 198.327 ↓ 17.0 17 1

Nested Loop Left Join (cost=21.73..35.73 rows=1 width=835) (actual time=197.931..198.327 rows=17 loops=1)

47. 0.022 198.282 ↓ 17.0 17 1

Nested Loop Left Join (cost=21.58..33.34 rows=1 width=835) (actual time=197.922..198.282 rows=17 loops=1)

48. 0.012 198.226 ↓ 17.0 17 1

Nested Loop Left Join (cost=21.30..30.84 rows=1 width=784) (actual time=197.912..198.226 rows=17 loops=1)

49. 0.070 198.163 ↓ 17.0 17 1

Nested Loop Left Join (cost=20.88..28.21 rows=1 width=759) (actual time=197.900..198.163 rows=17 loops=1)

  • Join Filter: (issue.tier_id = t.id)
  • Rows Removed by Join Filter: 312
50. 0.018 198.025 ↓ 17.0 17 1

Nested Loop Left Join (cost=20.88..26.47 rows=1 width=247) (actual time=197.884..198.025 rows=17 loops=1)

51. 0.030 197.973 ↓ 17.0 17 1

Nested Loop Left Join (cost=20.74..24.09 rows=1 width=221) (actual time=197.873..197.973 rows=17 loops=1)

52. 0.063 197.909 ↓ 17.0 17 1

Bitmap Heap Scan on issue_mgmt issue (cost=20.46..21.59 rows=1 width=203) (actual time=197.859..197.909 rows=17 loops=1)

  • Recheck Cond: ((status_id = ANY ('{1552,1,2,1556}'::integer[])) AND (id = ANY ($4)))
  • Filter: ((NOT deleted) AND (NOT public_view) AND (NOT hide_from_requester_view) AND (ageing > '10'::numeric) AND (client_id = 1,002))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=18
53. 0.004 197.846 ↓ 0.0 0 1

BitmapAnd (cost=20.46..20.46 rows=1 width=0) (actual time=197.846..197.846 rows=0 loops=1)

54. 0.020 0.020 ↑ 1.1 28 1

Bitmap Index Scan on idx_issue_mgmt_6 (cost=0.00..6.28 rows=30 width=0) (actual time=0.020..0.020 rows=28 loops=1)

  • Index Cond: (status_id = ANY ('{1552,1,2,1556}'::integer[]))
55. 197.822 197.822 ↓ 81.2 812 1

Bitmap Index Scan on pk_issue_mgmt (cost=0.00..13.92 rows=10 width=0) (actual time=197.822..197.822 rows=812 loops=1)

  • Index Cond: (id = ANY ($4))
56. 0.034 0.034 ↑ 1.0 1 17

Index Scan using pk_work_flow_status on work_flow_status ets (cost=0.28..2.50 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (id = issue.status_id)
57. 0.034 0.034 ↑ 1.0 1 17

Index Scan using pk_entity_type on entity_type et (cost=0.15..2.37 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (issue.entity_type_id = id)
58. 0.068 0.068 ↑ 1.7 19 17

Seq Scan on tier t (cost=0.00..1.33 rows=33 width=520) (actual time=0.001..0.004 rows=19 loops=17)

59. 0.051 0.051 ↑ 1.0 1 17

Index Scan using pk_contract on contract c (cost=0.41..2.63 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=17)

  • Index Cond: (issue.contract_id = id)
60. 0.034 0.034 ↑ 1.0 1 17

Index Scan using pk_relation on relation r (cost=0.28..2.50 rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (issue.relation_id = id)
61. 0.017 0.017 ↑ 1.0 1 17

Index Scan using pk_currency on currency cy (cost=0.14..2.36 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=17)

  • Index Cond: (issue.currency_id = id)
62. 0.051 0.051 ↑ 3.2 13 17

Seq Scan on time_zone tz (cost=0.00..1.41 rows=41 width=552) (actual time=0.001..0.003 rows=13 loops=17)

63. 0.017 0.017 ↑ 1.0 3 17

Seq Scan on responsibility res (cost=0.00..1.03 rows=3 width=520) (actual time=0.001..0.001 rows=3 loops=17)

64. 0.017 0.017 ↓ 0.0 0 17

Seq Scan on responsibility_client_link rcl (cost=0.00..1.02 rows=1 width=528) (actual time=0.001..0.001 rows=0 loops=17)

  • Filter: (client_id = 1,002)
  • Rows Removed by Filter: 2
65. 0.017 0.017 ↑ 2.5 2 17

Seq Scan on priority_table pt (cost=0.00..1.05 rows=5 width=520) (actual time=0.001..0.001 rows=2 loops=17)

66. 0.102 0.102 ↑ 1.0 9 17

Seq Scan on link_client_issue_type lcmt (cost=0.00..1.81 rows=9 width=24) (actual time=0.001..0.006 rows=9 loops=17)

  • Filter: (client_id = 1,002)
  • Rows Removed by Filter: 56
67. 0.017 0.017 ↓ 0.0 0 17

Index Scan using pk_governance_body_child on governance_body_child gbc (cost=0.29..2.50 rows=1 width=27) (actual time=0.001..0.001 rows=0 loops=17)

  • Index Cond: (issue.governance_body_child_id = id)
68. 0.017 0.017 ↓ 0.0 0 17

Index Scan using pk_governance_body on governance_body gb (cost=0.28..0.37 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=17)

  • Index Cond: (gbc.governance_body_id = id)
69. 0.034 0.034 ↑ 1.0 1 17

Index Scan using pk_vendor on vendor v (cost=0.28..2.50 rows=1 width=37) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (issue.vendor_id = id)
70. 0.034 0.034 ↑ 1.0 1 17

Index Scan using pk_system_label on system_label sl (cost=0.29..1.51 rows=1 width=44) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (id = et.description_label_id)
71. 0.051 0.051 ↑ 1.0 1 17

Index Scan using uc_system_label_language_link on system_label_language_link slll (cost=0.42..1.02 rows=1 width=39) (actual time=0.003..0.003 rows=1 loops=17)

  • Index Cond: ((system_label_id = sl.id) AND (system_language_id = 1))
72. 209.927 42,517.211 ↑ 7.1 3,561 1

GroupAggregate (cost=2,337,556.73..2,342,477.23 rows=25,247 width=68) (actual time=42,271.472..42,517.211 rows=3,561 loops=1)

  • Group Key: lec.entity_id
73. 228.730 42,307.284 ↑ 2.6 170,056 1

Sort (cost=2,337,556.73..2,338,660.62 rows=441,556 width=28) (actual time=42,271.343..42,307.284 rows=170,056 loops=1)

  • Sort Key: lec.entity_id
  • Sort Method: external merge Disk: 10,240kB
74. 21,523.667 42,078.554 ↓ 1.0 448,381 1

Nested Loop Left Join (cost=5,725.33..2,289,966.97 rows=441,556 width=28) (actual time=21.146..42,078.554 rows=448,381 loops=1)

  • Join Filter: ((NOT lec.global) AND (dc.id = lec.country_id))
  • Rows Removed by Join Filter: 150,207,635
75. 220.676 377.742 ↓ 1.0 448,381 1

Hash Left Join (cost=5,725.33..71,139.88 rows=441,556 width=25) (actual time=20.886..377.742 rows=448,381 loops=1)

  • Hash Cond: (lec.country_id = dc.id)
  • Join Filter: lec.global
  • Rows Removed by Join Filter: 349
76. 137.723 156.886 ↓ 1.0 448,381 1

Bitmap Heap Scan on link_entity_country lec (cost=5,713.79..69,955.24 rows=441,556 width=9) (actual time=20.696..156.886 rows=448,381 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=12,038
77. 19.163 19.163 ↓ 1.0 451,579 1

Bitmap Index Scan on idx_link_entity_country_5 (cost=0.00..5,603.40 rows=441,556 width=0) (actual time=19.163..19.163 rows=451,579 loops=1)

  • Index Cond: (entity_type_id = 17)
78. 0.086 0.180 ↑ 1.0 335 1

Hash (cost=7.35..7.35 rows=335 width=16) (actual time=0.180..0.180 rows=335 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
79. 0.094 0.094 ↑ 1.0 335 1

Seq Scan on delivery_country dc (cost=0.00..7.35 rows=335 width=16) (actual time=0.006..0.094 rows=335 loops=1)

80. 20,177.060 20,177.145 ↑ 1.0 335 448,381

Materialize (cost=0.00..9.03 rows=335 width=12) (actual time=0.000..0.045 rows=335 loops=448,381)

81. 0.085 0.085 ↑ 1.0 335 1

Seq Scan on delivery_country dc2 (cost=0.00..7.35 rows=335 width=12) (actual time=0.002..0.085 rows=335 loops=1)

82. 10.131 32.694 ↑ 3.8 3,561 1

GroupAggregate (cost=0.70..9,256.65 rows=13,462 width=68) (actual time=0.076..32.694 rows=3,561 loops=1)

  • Group Key: ller.entity_id
83. 3.911 22.563 ↑ 2.7 6,380 1

Nested Loop Left Join (cost=0.70..8,856.04 rows=17,516 width=1,036) (actual time=0.054..22.563 rows=6,380 loops=1)

  • Join Filter: (NOT ller.global)
  • Rows Removed by Join Filter: 6,263
84. 2.903 12.272 ↑ 2.7 6,380 1

Nested Loop Left Join (cost=0.56..5,919.13 rows=17,516 width=525) (actual time=0.046..12.272 rows=6,380 loops=1)

  • Join Filter: ller.global
  • Rows Removed by Join Filter: 117
85. 2.989 2.989 ↑ 2.7 6,380 1

Index Scan using idx_link_entity_region_2 on link_entity_region ller (cost=0.42..2,990.74 rows=17,516 width=9) (actual time=0.031..2.989 rows=6,380 loops=1)

  • Index Cond: (entity_type_id = 17)
86. 6.380 6.380 ↑ 1.0 1 6,380

Index Scan using pk_region on region rg1 (cost=0.14..0.16 rows=1 width=520) (actual time=0.001..0.001 rows=1 loops=6,380)

  • Index Cond: (ller.region_id = id)
87. 6.380 6.380 ↑ 1.0 1 6,380

Index Scan using pk_region on region rg2 (cost=0.14..0.16 rows=1 width=520) (actual time=0.001..0.001 rows=1 loops=6,380)

  • Index Cond: (ller.region_id = id)
88. 7.104 19.088 ↑ 3.6 3,563 1

GroupAggregate (cost=0.56..7,721.11 rows=12,791 width=36) (actual time=0.036..19.088 rows=3,563 loops=1)

  • Group Key: lef.entity_id
89. 2.361 11.984 ↑ 2.7 6,114 1

Nested Loop Left Join (cost=0.56..7,447.75 rows=16,298 width=520) (actual time=0.022..11.984 rows=6,114 loops=1)

90. 3.509 3.509 ↑ 2.7 6,114 1

Index Scan using idx_link_entity_function_2 on link_entity_function lef (cost=0.42..4,928.78 rows=16,298 width=8) (actual time=0.012..3.509 rows=6,114 loops=1)

  • Index Cond: (entity_type_id = 17)
91. 6.114 6.114 ↑ 1.0 1 6,114

Index Scan using pk_contract_type on contract_type ct (cost=0.14..0.16 rows=1 width=520) (actual time=0.001..0.001 rows=1 loops=6,114)

  • Index Cond: (lef.function_id = id)
92. 20.747 80.951 ↑ 6.9 3,563 1

GroupAggregate (cost=16,258.17..17,000.09 rows=24,612 width=36) (actual time=56.904..80.951 rows=3,563 loops=1)

  • Group Key: les.entity_id
93. 18.819 60.204 ↑ 2.2 22,412 1

Sort (cost=16,258.17..16,382.41 rows=49,699 width=22) (actual time=56.850..60.204 rows=22,412 loops=1)

  • Sort Key: les.entity_id
  • Sort Method: quicksort Memory: 4,612kB
94. 19.257 41.385 ↑ 1.0 48,690 1

Hash Left Join (cost=573.21..12,381.41 rows=49,699 width=22) (actual time=3.158..41.385 rows=48,690 loops=1)

  • Hash Cond: (les.service_id = cst.id)
95. 19.532 22.056 ↑ 1.0 48,690 1

Bitmap Heap Scan on link_entity_service les (cost=569.29..12,240.53 rows=49,699 width=8) (actual time=3.075..22.056 rows=48,690 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=4,557
96. 2.524 2.524 ↑ 1.0 48,901 1

Bitmap Index Scan on idx_link_entity_service_5 (cost=0.00..556.87 rows=49,699 width=0) (actual time=2.524..2.524 rows=48,901 loops=1)

  • Index Cond: (entity_type_id = 17)
97. 0.031 0.072 ↑ 1.0 85 1

Hash (cost=2.85..2.85 rows=85 width=22) (actual time=0.072..0.072 rows=85 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
98. 0.041 0.041 ↑ 1.0 85 1

Seq Scan on contract_sub_type cst (cost=0.00..2.85 rows=85 width=22) (actual time=0.008..0.041 rows=85 loops=1)

99. 6.655 24.282 ↑ 5.4 1,940 1

GroupAggregate (cost=3,578.70..3,843.20 rows=10,437 width=36) (actual time=16.691..24.282 rows=1,940 loops=1)

  • Group Key: ledc.entity_id
100. 5.516 17.627 ↑ 2.2 6,447 1

Sort (cost=3,578.70..3,614.68 rows=14,393 width=16) (actual time=16.654..17.627 rows=6,447 loops=1)

  • Sort Key: ledc.entity_id
  • Sort Method: quicksort Memory: 1,147kB
101. 5.767 12.111 ↓ 1.0 14,440 1

Hash Left Join (cost=11.96..2,584.64 rows=14,393 width=16) (actual time=0.217..12.111 rows=14,440 loops=1)

  • Hash Cond: (ledc.country_id = dc_1.id)
102. 6.165 6.165 ↓ 1.0 14,440 1

Index Scan using idx_link_entity_delivery_country_4 on link_entity_delivery_country ledc (cost=0.42..2,534.84 rows=14,393 width=8) (actual time=0.026..6.165 rows=14,440 loops=1)

  • Index Cond: (entity_type_id = 17)
103. 0.084 0.179 ↑ 1.0 335 1

Hash (cost=7.35..7.35 rows=335 width=16) (actual time=0.179..0.179 rows=335 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
104. 0.095 0.095 ↑ 1.0 335 1

Seq Scan on delivery_country dc_1 (cost=0.00..7.35 rows=335 width=16) (actual time=0.009..0.095 rows=335 loops=1)

105. 0.068 0.068 ↓ 0.0 0 17

Index Scan using client_label_client_id_label_id_language_id_key on client_label cl (cost=0.41..0.46 rows=1 width=33) (actual time=0.004..0.004 rows=0 loops=17)

  • Index Cond: ((client_id = 1,002) AND (label_id = slll.system_label_id) AND (language_id = 1))
106. 0.616 1.650 ↑ 1.7 400 1

GroupAggregate (cost=286.25..301.98 rows=694 width=36) (actual time=0.961..1.650 rows=400 loops=1)

  • Group Key: leh.entity_id
107. 0.293 1.034 ↑ 1.4 505 1

Sort (cost=286.25..288.02 rows=709 width=520) (actual time=0.951..1.034 rows=505 loops=1)

  • Sort Key: leh.entity_id
  • Sort Method: quicksort Memory: 59kB
108. 0.306 0.741 ↓ 1.0 732 1

Hash Left Join (cost=10.62..252.68 rows=709 width=520) (actual time=0.088..0.741 rows=732 loops=1)

  • Hash Cond: (leh.hub_id = hub.id)
109. 0.371 0.413 ↓ 1.0 732 1

Bitmap Heap Scan on link_entity_hub leh (cost=9.08..248.94 rows=709 width=8) (actual time=0.057..0.413 rows=732 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=109
110. 0.042 0.042 ↓ 1.0 732 1

Bitmap Index Scan on idx_link_entity_hub_4 (cost=0.00..8.90 rows=709 width=0) (actual time=0.042..0.042 rows=732 loops=1)

  • Index Cond: (entity_type_id = 17)
111. 0.008 0.022 ↑ 1.0 24 1

Hash (cost=1.24..1.24 rows=24 width=520) (actual time=0.021..0.022 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
112. 0.014 0.014 ↑ 1.0 24 1

Seq Scan on hub (cost=0.00..1.24 rows=24 width=520) (actual time=0.008..0.014 rows=24 loops=1)

113. 0.596 1.663 ↑ 1.7 400 1

GroupAggregate (cost=299.93..315.59 rows=691 width=36) (actual time=1.000..1.663 rows=400 loops=1)

  • Group Key: lem.entity_id
114. 0.293 1.067 ↑ 1.4 505 1

Sort (cost=299.93..301.70 rows=706 width=15) (actual time=0.991..1.067 rows=505 loops=1)

  • Sort Key: lem.entity_id
  • Sort Method: quicksort Memory: 59kB
115. 0.306 0.774 ↓ 1.0 732 1

Hash Left Join (cost=11.75..266.52 rows=706 width=15) (actual time=0.100..0.774 rows=732 loops=1)

  • Hash Cond: (lem.market_id = mkt.id)
116. 0.388 0.427 ↓ 1.0 732 1

Bitmap Heap Scan on link_entity_market lem (cost=9.06..261.88 rows=706 width=8) (actual time=0.051..0.427 rows=732 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=111
117. 0.039 0.039 ↓ 1.0 732 1

Bitmap Index Scan on idx_link_entity_market_4 (cost=0.00..8.88 rows=706 width=0) (actual time=0.039..0.039 rows=732 loops=1)

  • Index Cond: (entity_type_id = 17)
118. 0.020 0.041 ↑ 1.0 75 1

Hash (cost=1.75..1.75 rows=75 width=15) (actual time=0.041..0.041 rows=75 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
119. 0.021 0.021 ↑ 1.0 75 1

Seq Scan on market mkt (cost=0.00..1.75 rows=75 width=15) (actual time=0.004..0.021 rows=75 loops=1)

120. 0.621 1.601 ↑ 1.7 400 1

GroupAggregate (cost=286.22..301.94 rows=694 width=36) (actual time=0.912..1.601 rows=400 loops=1)

  • Group Key: leicp.entity_id
121. 0.266 0.980 ↑ 1.4 505 1

Sort (cost=286.22..287.99 rows=709 width=520) (actual time=0.903..0.980 rows=505 loops=1)

  • Sort Key: leicp.entity_id
  • Sort Method: quicksort Memory: 59kB
122. 0.294 0.714 ↓ 1.0 732 1

Hash Left Join (cost=10.53..252.65 rows=709 width=520) (actual time=0.071..0.714 rows=732 loops=1)

  • Hash Cond: (leicp.icp_id = icp.id)
123. 0.365 0.404 ↓ 1.0 732 1

Bitmap Heap Scan on link_entity_icp leicp (cost=9.08..248.94 rows=709 width=8) (actual time=0.050..0.404 rows=732 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=108
124. 0.039 0.039 ↓ 1.0 732 1

Bitmap Index Scan on idx_link_entity_icp_4 (cost=0.00..8.90 rows=709 width=0) (actual time=0.039..0.039 rows=732 loops=1)

  • Index Cond: (entity_type_id = 17)
125. 0.006 0.016 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=520) (actual time=0.016..0.016 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
126. 0.010 0.010 ↑ 1.0 20 1

Seq Scan on internal_contracting_party icp (cost=0.00..1.20 rows=20 width=520) (actual time=0.005..0.010 rows=20 loops=1)

127. 0.605 1.647 ↑ 1.8 400 1

GroupAggregate (cost=286.12..301.98 rows=700 width=36) (actual time=0.972..1.647 rows=400 loops=1)

  • Group Key: lecc.entity_id
128. 0.288 1.042 ↑ 1.4 505 1

Sort (cost=286.12..287.91 rows=715 width=520) (actual time=0.963..1.042 rows=505 loops=1)

  • Sort Key: lecc.entity_id
  • Sort Method: quicksort Memory: 59kB
129. 0.297 0.754 ↓ 1.0 732 1

Hash Left Join (cost=11.25..252.22 rows=715 width=520) (actual time=0.100..0.754 rows=732 loops=1)

  • Hash Cond: (lecc.company_code_id = cc.id)
130. 0.376 0.414 ↓ 1.0 732 1

Bitmap Heap Scan on link_entity_company_code lecc (cost=9.13..248.07 rows=715 width=8) (actual time=0.049..0.414 rows=732 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=109
131. 0.038 0.038 ↓ 1.0 732 1

Bitmap Index Scan on idx_link_entity_company_code_4 (cost=0.00..8.95 rows=715 width=0) (actual time=0.038..0.038 rows=732 loops=1)

  • Index Cond: (entity_type_id = 17)
132. 0.020 0.043 ↑ 1.0 50 1

Hash (cost=1.50..1.50 rows=50 width=520) (actual time=0.043..0.043 rows=50 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
133. 0.023 0.023 ↑ 1.0 50 1

Seq Scan on company_code cc (cost=0.00..1.50 rows=50 width=520) (actual time=0.006..0.023 rows=50 loops=1)

134. 0.617 1.643 ↑ 1.7 399 1

GroupAggregate (cost=281.17..296.88 rows=693 width=36) (actual time=0.958..1.643 rows=399 loops=1)

  • Group Key: lech.entity_id
135. 0.294 1.026 ↑ 1.4 504 1

Sort (cost=281.17..282.94 rows=708 width=520) (actual time=0.949..1.026 rows=504 loops=1)

  • Sort Key: lech.entity_id
  • Sort Method: quicksort Memory: 59kB
136. 0.298 0.732 ↓ 1.0 731 1

Hash Left Join (cost=10.61..247.66 rows=708 width=520) (actual time=0.074..0.732 rows=731 loops=1)

  • Hash Cond: (lech.hub_id = hub2.id)
137. 0.379 0.419 ↓ 1.0 731 1

Bitmap Heap Scan on link_entity_contracting_hub lech (cost=9.07..243.92 rows=708 width=8) (actual time=0.051..0.419 rows=731 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=113
138. 0.040 0.040 ↓ 1.0 731 1

Bitmap Index Scan on idx_link_entity_contracting_hub_4 (cost=0.00..8.90 rows=708 width=0) (actual time=0.040..0.040 rows=731 loops=1)

  • Index Cond: (entity_type_id = 17)
139. 0.007 0.015 ↑ 1.0 24 1

Hash (cost=1.24..1.24 rows=24 width=520) (actual time=0.015..0.015 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
140. 0.008 0.008 ↑ 1.0 24 1

Seq Scan on hub hub2 (cost=0.00..1.24 rows=24 width=520) (actual time=0.003..0.008 rows=24 loops=1)

141. 0.593 1.641 ↑ 1.7 399 1

GroupAggregate (cost=282.09..297.79 rows=693 width=36) (actual time=0.981..1.641 rows=399 loops=1)

  • Group Key: lecm.entity_id
142. 0.293 1.048 ↑ 1.4 504 1

Sort (cost=282.09..283.86 rows=708 width=15) (actual time=0.972..1.048 rows=504 loops=1)

  • Sort Key: lecm.entity_id
  • Sort Method: quicksort Memory: 59kB
143. 0.305 0.755 ↓ 1.0 731 1

Hash Left Join (cost=11.76..248.57 rows=708 width=15) (actual time=0.097..0.755 rows=731 loops=1)

  • Hash Cond: (lecm.market_id = mkt2.id)
144. 0.372 0.413 ↓ 1.0 731 1

Bitmap Heap Scan on link_entity_contracting_market lecm (cost=9.07..243.92 rows=708 width=8) (actual time=0.052..0.413 rows=731 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=112
145. 0.041 0.041 ↓ 1.0 731 1

Bitmap Index Scan on idx_link_entity_contracting_market_4 (cost=0.00..8.90 rows=708 width=0) (actual time=0.041..0.041 rows=731 loops=1)

  • Index Cond: (entity_type_id = 17)
146. 0.020 0.037 ↑ 1.0 75 1

Hash (cost=1.75..1.75 rows=75 width=15) (actual time=0.037..0.037 rows=75 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
147. 0.017 0.017 ↑ 1.0 75 1

Seq Scan on market mkt2 (cost=0.00..1.75 rows=75 width=15) (actual time=0.003..0.017 rows=75 loops=1)

148. 0.600 1.608 ↑ 1.7 399 1

GroupAggregate (cost=281.14..296.84 rows=693 width=36) (actual time=0.944..1.608 rows=399 loops=1)

  • Group Key: lecicp.entity_id
149. 0.284 1.008 ↑ 1.4 504 1

Sort (cost=281.14..282.91 rows=708 width=520) (actual time=0.935..1.008 rows=504 loops=1)

  • Sort Key: lecicp.entity_id
  • Sort Method: quicksort Memory: 59kB
150. 0.302 0.724 ↓ 1.0 731 1

Hash Left Join (cost=10.52..247.62 rows=708 width=520) (actual time=0.073..0.724 rows=731 loops=1)

  • Hash Cond: (lecicp.icp_id = icp2.id)
151. 0.368 0.407 ↓ 1.0 731 1

Bitmap Heap Scan on link_entity_contracting_icp lecicp (cost=9.07..243.92 rows=708 width=8) (actual time=0.050..0.407 rows=731 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=111
152. 0.039 0.039 ↓ 1.0 731 1

Bitmap Index Scan on idx_link_entity_contracting_icp_4 (cost=0.00..8.90 rows=708 width=0) (actual time=0.039..0.039 rows=731 loops=1)

  • Index Cond: (entity_type_id = 17)
153. 0.008 0.015 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=520) (actual time=0.015..0.015 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
154. 0.007 0.007 ↑ 1.0 20 1

Seq Scan on internal_contracting_party icp2 (cost=0.00..1.20 rows=20 width=520) (actual time=0.003..0.007 rows=20 loops=1)

155. 0.618 1.656 ↑ 1.8 399 1

GroupAggregate (cost=280.04..295.88 rows=699 width=36) (actual time=0.969..1.656 rows=399 loops=1)

  • Group Key: leccc.entity_id
156. 0.299 1.038 ↑ 1.4 504 1

Sort (cost=280.04..281.83 rows=714 width=520) (actual time=0.961..1.038 rows=504 loops=1)

  • Sort Key: leccc.entity_id
  • Sort Method: quicksort Memory: 59kB
157. 0.302 0.739 ↓ 1.0 731 1

Hash Left Join (cost=11.25..246.20 rows=714 width=520) (actual time=0.085..0.739 rows=731 loops=1)

  • Hash Cond: (leccc.company_code_id = cc2.id)
158. 0.370 0.411 ↓ 1.0 731 1

Bitmap Heap Scan on link_entity_contracting_company_code leccc (cost=9.12..242.05 rows=714 width=8) (actual time=0.052..0.411 rows=731 loops=1)

  • Recheck Cond: (entity_type_id = 17)
  • Heap Blocks: exact=107
159. 0.041 0.041 ↓ 1.0 731 1

Bitmap Index Scan on idx_link_entity_contracting_company_code_4 (cost=0.00..8.94 rows=714 width=0) (actual time=0.041..0.041 rows=731 loops=1)

  • Index Cond: (entity_type_id = 17)
160. 0.013 0.026 ↑ 1.0 50 1

Hash (cost=1.50..1.50 rows=50 width=520) (actual time=0.025..0.026 rows=50 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
161. 0.013 0.013 ↑ 1.0 50 1

Seq Scan on company_code cc2 (cost=0.00..1.50 rows=50 width=520) (actual time=0.003..0.013 rows=50 loops=1)

162. 0.136 0.136 ↓ 0.0 0 17

Index Scan using idx_mgmt_audit_log_6 on mgmt_audit_log (cost=0.42..6.91 rows=1 width=25) (actual time=0.008..0.008 rows=0 loops=17)

  • Index Cond: ((issue.id = entity_id) AND (entity_type_id = 17))
  • Filter: ((comment IS NOT NULL) AND (comment <> ''::text))
  • Rows Removed by Filter: 6
163.          

SubPlan (for GroupAggregate)

164. 0.017 0.170 ↓ 0.0 0 17

Limit (cost=6.92..6.92 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=17)

165. 0.034 0.153 ↓ 0.0 0 17

Sort (cost=6.92..6.92 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=17)

  • Sort Key: mal.user_date
  • Sort Method: quicksort Memory: 25kB
166. 0.119 0.119 ↓ 0.0 0 17

Index Scan using idx_mgmt_audit_log_6 on mgmt_audit_log mal (cost=0.42..6.91 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=17)

  • Index Cond: ((entity_id = issue.id) AND (entity_type_id = 17))
  • Filter: (status = 3)
  • Rows Removed by Filter: 6
Planning time : 16.062 ms
Execution time : 42,899.287 ms