explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nwIN

Settings
# exclusive inclusive rows x rows loops node
1. 651.112 2,932.083 ↓ 7.5 2,357 1

Merge Left Join (cost=272,897.39..671,279.63 rows=314 width=868) (actual time=811.518..2,932.083 rows=2,357 loops=1)

  • Merge Cond: (action.id = lef.entity_id)
2.          

Initplan (for Merge Left Join)

3. 0.271 2.462 ↓ 1,178.5 2,357 1

Unique (cost=1,052.54..1,052.55 rows=2 width=4) (actual time=2.082..2.462 rows=2,357 loops=1)

4.          

CTE user_data_filter

5. 0.037 0.037 ↑ 10.3 21 1

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.43..119.88 rows=216 width=23) (actual time=0.012..0.037 rows=21 loops=1)

  • Index Cond: (user_id = 2,159,767)
  • Filter: (NOT deleted)
6. 0.587 2.191 ↓ 1,178.5 2,357 1

Sort (cost=932.66..932.67 rows=2 width=4) (actual time=2.081..2.191 rows=2,357 loops=1)

  • Sort Key: edl.entity_id
  • Sort Method: quicksort Memory: 207kB
7. 0.137 1.604 ↓ 1,178.5 2,357 1

Append (cost=0.43..932.65 rows=2 width=4) (actual time=0.035..1.604 rows=2,357 loops=1)

8. 0.221 1.464 ↓ 2,357.0 2,357 1

Nested Loop (cost=0.43..291.60 rows=1 width=4) (actual time=0.035..1.464 rows=2,357 loops=1)

9. 0.046 0.046 ↑ 5.1 21 1

CTE Scan on user_data_filter da (cost=0.00..4.32 rows=108 width=8) (actual time=0.014..0.046 rows=21 loops=1)

  • Filter: self_access
10. 1.197 1.197 ↓ 112.0 112 21

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edl (cost=0.43..2.65 rows=1 width=12) (actual time=0.002..0.057 rows=112 loops=21)

  • Index Cond: ((entity_type_id = 18) AND (ancestor_type_id = da.entity_type_id) AND (ancestor_id = da.entity_id))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
11. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.85..641.02 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
12. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.43..634.27 rows=13 width=40) (actual time=0.003..0.003 rows=0 loops=1)

13. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..4.32 rows=108 width=40) (actual time=0.002..0.003 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 21
14. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_ancestor_type_id_ancestor_id on entity_data_link edl_1 (cost=0.43..5.82 rows=1 width=16) (never executed)

  • Index Cond: ((ancestor_type_id = da_1.entity_type_id) AND (ancestor_id = da_1.entity_id))
  • Filter: (parent AND (NOT deleted))
15. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edc (cost=0.43..0.50 rows=1 width=12) (never executed)

  • Index Cond: ((entity_type_id = 18) AND (ancestor_type_id = edl_1.entity_type_id) AND (ancestor_id = edl_1.entity_id))
  • Filter: (NOT deleted)
16. 2.807 2,236.791 ↓ 34.7 2,357 1

Merge Left Join (cost=271,844.41..628,169.25 rows=68 width=768) (actual time=807.776..2,236.791 rows=2,357 loops=1)

  • Merge Cond: (action.id = les.entity_id)
17. 3.074 1,516.924 ↓ 168.4 2,357 1

Merge Left Join (cost=271,843.97..279,810.68 rows=14 width=736) (actual time=803.581..1,516.924 rows=2,357 loops=1)

  • Merge Cond: (action.id = ledc_1.entity_id)
18. 3.205 173.158 ↓ 785.7 2,357 1

Merge Left Join (cost=25,059.73..26,120.58 rows=3 width=672) (actual time=47.790..173.158 rows=2,357 loops=1)

  • Merge Cond: (action.id = ller.entity_id)
19. 1.363 116.987 ↓ 1,178.5 2,357 1

Merge Left Join (cost=13,343.11..13,831.82 rows=2 width=608) (actual time=22.876..116.987 rows=2,357 loops=1)

  • Merge Cond: (action.id = ledc.entity_id)
20. 2.329 86.379 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=2.85..111.60 rows=2 width=576) (actual time=2.794..86.379 rows=2,357 loops=1)

21. 3.086 84.050 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=2.57..110.98 rows=2 width=553) (actual time=2.792..84.050 rows=2,357 loops=1)

22. 2.372 76.250 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=2.28..105.96 rows=2 width=527) (actual time=2.778..76.250 rows=2,357 loops=1)

23. 3.311 73.878 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=1.99..100.95 rows=2 width=501) (actual time=2.769..73.878 rows=2,357 loops=1)

24. 6.422 68.210 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=1.84..96.19 rows=2 width=499) (actual time=2.756..68.210 rows=2,357 loops=1)

  • Join Filter: (action.type_id = lcat.type_id)
  • Rows Removed by Join Filter: 87,024
25. 2.169 57.074 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=1.84..64.08 rows=2 width=489) (actual time=2.723..57.074 rows=2,357 loops=1)

  • Join Filter: (res.id = rcl.responsibility_id)
  • Rows Removed by Join Filter: 4,684
26. 2.762 54.905 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=1.84..59.76 rows=2 width=478) (actual time=2.714..54.905 rows=2,357 loops=1)

  • Join Filter: (res.id = action.responsibility_id)
  • Rows Removed by Join Filter: 7,006
27. 2.645 52.143 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=1.71..56.19 rows=2 width=465) (actual time=2.707..52.143 rows=2,357 loops=1)

  • Join Filter: (pt.id = action.priority)
  • Rows Removed by Join Filter: 233
28. 3.090 49.498 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=1.58..52.52 rows=2 width=464) (actual time=2.702..49.498 rows=2,357 loops=1)

29. 3.614 44.051 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=1.44..48.83 rows=2 width=423) (actual time=2.696..44.051 rows=2,357 loops=1)

30. 2.980 38.080 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=1.29..44.07 rows=2 width=423) (actual time=2.691..38.080 rows=2,357 loops=1)

31. 2.637 32.743 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=1.00..39.05 rows=2 width=393) (actual time=2.684..32.743 rows=2,357 loops=1)

32. 4.029 30.106 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=0.71..34.04 rows=2 width=369) (actual time=2.681..30.106 rows=2,357 loops=1)

33. 5.075 23.720 ↓ 1,178.5 2,357 1

Nested Loop Left Join (cost=0.57..29.30 rows=2 width=351) (actual time=2.675..23.720 rows=2,357 loops=1)

34. 13.931 13.931 ↓ 1,178.5 2,357 1

Index Scan using action_item_mgmt_id_idx on action_item_mgmt action (cost=0.29..24.30 rows=2 width=336) (actual time=2.654..13.931 rows=2,357 loops=1)

  • Index Cond: (id = ANY ($20))
  • Filter: ((NOT deleted) AND (client_id = 1,024))
35. 4.714 4.714 ↑ 1.0 1 2,357

Index Scan using pk_work_flow_status on work_flow_status ets (cost=0.28..2.50 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=2,357)

  • Index Cond: (id = action.status_id)
36. 2.357 2.357 ↑ 1.0 1 2,357

Index Scan using pk_entity_type on entity_type et (cost=0.15..2.37 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=2,357)

  • Index Cond: (action.entity_type_id = id)
37. 0.000 0.000 ↓ 0.0 0 2,357

Index Scan using pk_contract on contract c (cost=0.29..2.51 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=2,357)

  • Index Cond: (action.contract_id = id)
38. 2.357 2.357 ↑ 1.0 1 2,357

Index Scan using pk_relation on relation r (cost=0.29..2.51 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=2,357)

  • Index Cond: (action.relation_id = id)
39. 2.357 2.357 ↑ 1.0 1 2,357

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=2,357)

  • Index Cond: (action.currency_id = id)
40. 2.357 2.357 ↑ 1.0 1 2,357

Index Scan using pk_time_zone on time_zone tz (cost=0.14..1.81 rows=1 width=49) (actual time=0.001..0.001 rows=1 loops=2,357)

  • Index Cond: (id = action.time_zone_id)
41. 0.000 0.000 ↑ 5.0 1 2,357

Materialize (cost=0.13..3.53 rows=5 width=9) (actual time=0.000..0.000 rows=1 loops=2,357)

42. 0.006 0.006 ↑ 1.2 4 1

Index Scan using pk_priority_table on priority_table pt (cost=0.13..3.51 rows=5 width=9) (actual time=0.003..0.006 rows=4 loops=1)

43. 0.000 0.000 ↑ 1.0 3 2,357

Materialize (cost=0.13..3.49 rows=3 width=17) (actual time=0.000..0.000 rows=3 loops=2,357)

44. 0.004 0.004 ↑ 1.0 3 1

Index Scan using pk_responsibility on responsibility res (cost=0.13..3.48 rows=3 width=17) (actual time=0.003..0.004 rows=3 loops=1)

45. 0.000 0.000 ↑ 1.0 2 2,357

Materialize (cost=0.00..4.26 rows=2 width=19) (actual time=0.000..0.000 rows=2 loops=2,357)

46. 0.009 0.009 ↑ 1.0 2 1

Seq Scan on responsibility_client_link rcl (cost=0.00..4.25 rows=2 width=19) (actual time=0.006..0.009 rows=2 loops=1)

  • Filter: (client_id = 1,024)
  • Rows Removed by Filter: 18
47. 4.663 4.714 ↑ 1.0 37 2,357

Materialize (cost=0.00..31.10 rows=37 width=18) (actual time=0.000..0.002 rows=37 loops=2,357)

48. 0.051 0.051 ↑ 1.0 37 1

Seq Scan on link_client_action_type lcat (cost=0.00..30.91 rows=37 width=18) (actual time=0.030..0.051 rows=37 loops=1)

  • Filter: (client_id = 1,024)
  • Rows Removed by Filter: 516
49. 2.357 2.357 ↑ 1.0 1 2,357

Index Scan using pk_tier on tier (cost=0.14..2.36 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=2,357)

  • Index Cond: (action.tier_id = id)
50. 0.000 0.000 ↓ 0.0 0 2,357

Index Scan using pk_governance_body_child on governance_body_child gbc (cost=0.29..2.51 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=2,357)

  • Index Cond: (action.governance_body_child_id = id)
51. 4.714 4.714 ↑ 1.0 1 2,357

Index Scan using pk_vendor on vendor v (cost=0.29..2.51 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=2,357)

  • Index Cond: (action.vendor_id = id)
52. 0.000 0.000 ↓ 0.0 0 2,357

Index Scan using pk_governance_body on governance_body gb (cost=0.28..0.31 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=2,357)

  • Index Cond: (gbc.governance_body_id = id)
53. 15.167 29.245 ↑ 6.2 1,595 1

GroupAggregate (cost=13,340.26..13,596.84 rows=9,869 width=36) (actual time=12.646..29.245 rows=1,595 loops=1)

  • Group Key: ledc.entity_id
54. 4.373 14.078 ↓ 1.1 15,284 1

Sort (cost=13,340.26..13,376.44 rows=14,472 width=18) (actual time=12.616..14.078 rows=15,284 loops=1)

  • Sort Key: ledc.entity_id
  • Sort Method: quicksort Memory: 1,231kB
55. 2.147 9.705 ↓ 1.1 15,284 1

Merge Left Join (cost=12,108.57..12,340.18 rows=14,472 width=18) (actual time=6.335..9.705 rows=15,284 loops=1)

  • Merge Cond: (ledc.country_id = dc.id)
56. 3.652 7.409 ↓ 1.1 15,284 1

Sort (cost=12,108.29..12,144.47 rows=14,472 width=8) (actual time=6.324..7.409 rows=15,284 loops=1)

  • Sort Key: ledc.country_id
  • Sort Method: quicksort Memory: 1,101kB
57. 3.757 3.757 ↓ 1.1 15,284 1

Index Scan using idx_link_entity_delivery_country_4 on link_entity_delivery_country ledc (cost=0.43..11,108.21 rows=14,472 width=8) (actual time=0.013..3.757 rows=15,284 loops=1)

  • Index Cond: (entity_type_id = 18)
58. 0.149 0.149 ↑ 1.4 381 1

Index Scan using pk_delivery_country on delivery_country dc (cost=0.28..26.36 rows=534 width=18) (actual time=0.008..0.149 rows=381 loops=1)

59. 28.301 52.966 ↑ 2.4 5,662 1

GroupAggregate (cost=11,716.61..12,122.32 rows=13,312 width=68) (actual time=22.396..52.966 rows=5,662 loops=1)

  • Group Key: ller.entity_id
60. 5.490 24.665 ↑ 1.0 13,389 1

Sort (cost=11,716.61..11,751.48 rows=13,947 width=22) (actual time=22.383..24.665 rows=13,389 loops=1)

  • Sort Key: ller.entity_id
  • Sort Method: quicksort Memory: 1,048kB
61. 2.574 19.175 ↑ 1.0 13,389 1

Merge Left Join (cost=10,202.07..10,756.53 rows=13,947 width=22) (actual time=12.805..19.175 rows=13,389 loops=1)

  • Merge Cond: (ller.region_id = rg2.id)
  • Join Filter: (NOT ller.global)
  • Rows Removed by Join Filter: 13,206
62. 2.257 16.025 ↑ 1.0 13,389 1

Merge Left Join (cost=10,201.72..10,496.38 rows=13,947 width=18) (actual time=12.796..16.025 rows=13,389 loops=1)

  • Merge Cond: (ller.region_id = rg1.id)
  • Join Filter: ller.global
  • Rows Removed by Join Filter: 183
63. 3.095 13.673 ↑ 1.0 13,389 1

Sort (cost=10,201.37..10,236.24 rows=13,947 width=9) (actual time=12.778..13.673 rows=13,389 loops=1)

  • Sort Key: ller.region_id
  • Sort Method: quicksort Memory: 1,012kB
64. 10.578 10.578 ↑ 1.0 13,389 1

Index Scan using idx_link_entity_region_5 on link_entity_region ller (cost=0.43..9,241.29 rows=13,947 width=9) (actual time=0.039..10.578 rows=13,389 loops=1)

  • Index Cond: (entity_type_id = 18)
65. 0.033 0.095 ↓ 1.6 400 1

Materialize (cost=0.14..16.85 rows=250 width=13) (actual time=0.008..0.095 rows=400 loops=1)

66. 0.062 0.062 ↑ 1.0 247 1

Index Scan using pk_region on region rg1 (cost=0.14..16.22 rows=250 width=13) (actual time=0.006..0.062 rows=247 loops=1)

67. 0.526 0.576 ↓ 53.5 13,365 1

Materialize (cost=0.14..16.85 rows=250 width=13) (actual time=0.005..0.576 rows=13,365 loops=1)

68. 0.050 0.050 ↑ 1.0 248 1

Index Scan using pk_region on region rg2 (cost=0.14..16.22 rows=250 width=13) (actual time=0.004..0.050 rows=248 loops=1)

69. 546.905 1,340.692 ↑ 7.2 5,659 1

GroupAggregate (cost=246,784.24..253,182.92 rows=40,562 width=68) (actual time=741.868..1,340.692 rows=5,659 loops=1)

  • Group Key: ledc_1.entity_id
70. 280.129 793.787 ↑ 1.1 527,798 1

Sort (cost=246,784.24..248,181.10 rows=558,744 width=32) (actual time=741.849..793.787 rows=527,798 loops=1)

  • Sort Key: ledc_1.entity_id
  • Sort Method: external merge Disk: 12,992kB
71. 105.131 513.658 ↑ 1.1 527,798 1

Merge Left Join (cost=172,443.57..193,447.02 rows=558,744 width=32) (actual time=214.647..513.658 rows=527,798 loops=1)

  • Merge Cond: (ledc_1.country_id = dc2.id)
  • Join Filter: (NOT ledc_1.global)
  • Rows Removed by Join Filter: 527,080
72. 97.499 388.032 ↑ 1.1 527,798 1

Merge Left Join (cost=172,443.30..183,643.45 rows=558,744 width=23) (actual time=214.636..388.032 rows=527,798 loops=1)

  • Merge Cond: (ledc_1.country_id = dc1.id)
  • Join Filter: ledc_1.global
  • Rows Removed by Join Filter: 718
73. 176.231 290.211 ↑ 1.1 527,798 1

Sort (cost=172,443.02..173,839.88 rows=558,744 width=9) (actual time=214.616..290.211 rows=527,798 loops=1)

  • Sort Key: ledc_1.country_id
  • Sort Method: quicksort Memory: 37,848kB
74. 113.980 113.980 ↑ 1.1 527,798 1

Index Scan using idx_link_entity_country_5 on link_entity_country ledc_1 (cost=0.44..119,105.80 rows=558,744 width=9) (actual time=0.036..113.980 rows=527,798 loops=1)

  • Index Cond: (entity_type_id = 18)
75. 0.105 0.322 ↓ 2.3 1,207 1

Materialize (cost=0.28..27.69 rows=534 width=18) (actual time=0.015..0.322 rows=1,207 loops=1)

76. 0.217 0.217 ↑ 1.0 510 1

Index Scan using pk_delivery_country on delivery_country dc1 (cost=0.28..26.36 rows=534 width=18) (actual time=0.013..0.217 rows=510 loops=1)

77. 20.250 20.495 ↓ 987.4 527,263 1

Materialize (cost=0.28..27.69 rows=534 width=18) (actual time=0.006..20.495 rows=527,263 loops=1)

78. 0.245 0.245 ↑ 1.0 511 1

Index Scan using pk_delivery_country on delivery_country dc2 (cost=0.28..26.36 rows=534 width=18) (actual time=0.006..0.245 rows=511 loops=1)

79. 410.461 717.060 ↑ 5.7 5,684 1

GroupAggregate (cost=0.44..347,953.85 rows=32,332 width=36) (actual time=0.036..717.060 rows=5,684 loops=1)

  • Group Key: les.entity_id
80. 306.599 306.599 ↓ 1.0 1,741,469 1

Index Scan using idx_link_entity_service_2 on link_entity_service les (cost=0.44..339,064.84 rows=1,696,971 width=8) (actual time=0.031..306.599 rows=1,741,469 loops=1)

  • Index Cond: (entity_type_id = 18)
81. 16.291 36.984 ↑ 6.6 5,684 1

GroupAggregate (cost=0.43..30,647.48 rows=37,454 width=36) (actual time=0.030..36.984 rows=5,684 loops=1)

  • Group Key: lef.entity_id
82. 20.693 20.693 ↑ 1.0 45,903 1

Index Scan using idx_link_entity_function_2 on link_entity_function lef (cost=0.43..29,940.92 rows=47,678 width=8) (actual time=0.025..20.693 rows=45,903 loops=1)

  • Index Cond: (entity_type_id = 18)
83.          

SubPlan (for Merge Left Join)

84. 4.484 4.484 ↑ 1.0 1 2,242

Index Scan using pk_relation on relation src (cost=0.29..2.51 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=2,242)

  • Index Cond: (id = action.entity_id)
85. 0.104 0.104 ↑ 1.0 1 52

Index Scan using pk_contract on contract src_1 (cost=0.29..2.51 rows=1 width=24) (actual time=0.001..0.002 rows=1 loops=52)

  • Index Cond: (id = action.entity_id)
86. 0.042 0.042 ↑ 1.0 1 14

Index Scan using pk_dno on dno src_2 (cost=0.29..2.50 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=14)

  • Index Cond: (id = action.entity_id)
87. 0.007 0.007 ↑ 1.0 1 1

Index Scan using pk_child_dno on child_dno src_3 (cost=0.42..2.64 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (id = action.entity_id)
88. 0.006 0.006 ↑ 1.0 1 3

Index Scan using pk_sla on sla src_4 (cost=0.29..2.51 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=3)

  • Index Cond: (id = action.entity_id)
89. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pk_child_sla on child_sla src_5 (cost=0.42..2.64 rows=1 width=45) (actual time=0.002..0.003 rows=1 loops=1)

  • Index Cond: (id = action.entity_id)
90. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_contract_intpn on contract_intpn src_6 (cost=0.28..2.50 rows=1 width=20) (never executed)

  • Index Cond: (id = action.entity_id)
91. 0.026 0.026 ↑ 1.0 1 13

Index Scan using pk_issue_mgmt on issue_mgmt src_7 (cost=0.28..2.50 rows=1 width=18) (actual time=0.001..0.002 rows=1 loops=13)

  • Index Cond: (id = action.entity_id)
92. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dispute_mgmt on dispute_mgmt src_8 (cost=0.28..2.50 rows=1 width=17) (never executed)

  • Index Cond: (id = action.entity_id)
93. 0.062 0.062 ↑ 1.0 1 31

Index Scan using action_item_mgmt_id_idx on action_item_mgmt src_9 (cost=0.29..2.50 rows=1 width=20) (actual time=0.001..0.002 rows=1 loops=31)

  • Index Cond: (id = action.entity_id)
94. 0.000 0.000 ↓ 0.0 0

Index Scan using change_request_pkey on change_request src_10 (cost=0.28..2.50 rows=1 width=20) (never executed)

  • Index Cond: (id = action.entity_id)
95. 0.000 0.000 ↓ 0.0 0

Index Scan using base_invoice_pkey on base_invoice src_11 (cost=0.28..2.50 rows=1 width=20) (never executed)

  • Index Cond: (id = action.entity_id)
96. 0.000 0.000 ↓ 0.0 0

Index Scan using work_order_request_pkey on work_order_request src_12 (cost=0.28..2.50 rows=1 width=12) (never executed)

  • Index Cond: (id = action.entity_id)
Planning time : 7.477 ms
Execution time : 2,936.034 ms