explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KxGf

Settings
# exclusive inclusive rows x rows loops node
1. 412.504 84,540.890 ↓ 13.0 2,355 1

Result (cost=14,023.80..20,059.24 rows=181 width=346) (actual time=84,123.366..84,540.890 rows=2,355 loops=1)

2.          

CTE ledc2

3. 3.221 3.221 ↓ 1.1 15,284 1

Index Scan using idx_link_entity_delivery_country_4 on link_entity_delivery_country (cost=0.43..11,108.21 rows=14,472 width=18) (actual time=0.016..3.221 rows=15,284 loops=1)

  • Index Cond: (entity_type_id = 18)
4.          

Initplan (for Result)

5. 0.274 2.535 ↓ 1,177.5 2,355 1

Unique (cost=1,052.54..1,052.55 rows=2 width=4) (actual time=2.161..2.535 rows=2,355 loops=1)

6.          

CTE user_data_filter

7. 0.042 0.042 ↑ 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.017..0.042 rows=21 loops=1)

  • Index Cond: (user_id = 2,159,767)
  • Filter: (NOT deleted)
8. 0.662 2.261 ↓ 1,177.5 2,355 1

Sort (cost=932.66..932.67 rows=2 width=4) (actual time=2.161..2.261 rows=2,355 loops=1)

  • Sort Key: edl.entity_id
  • Sort Method: quicksort Memory: 207kB
9. 0.133 1.599 ↓ 1,177.5 2,355 1

Append (cost=0.43..932.65 rows=2 width=4) (actual time=0.043..1.599 rows=2,355 loops=1)

10. 0.173 1.463 ↓ 2,355.0 2,355 1

Nested Loop (cost=0.43..291.60 rows=1 width=4) (actual time=0.042..1.463 rows=2,355 loops=1)

11. 0.051 0.051 ↑ 5.1 21 1

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

  • Filter: self_access
12. 1.239 1.239 ↓ 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.003..0.059 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
13. 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))
14. 0.001 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)

15. 0.002 0.002 ↓ 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.002 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 21
16. 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))
17. 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)
18. 4.029 84,123.428 ↓ 13.0 2,355 1

Sort (cost=1,863.04..1,863.50 rows=181 width=258) (actual time=84,122.851..84,123.428 rows=2,355 loops=1)

  • Sort Key: action.status_id
  • Sort Method: quicksort Memory: 1,260kB
19. 4,033.346 84,119.399 ↓ 13.0 2,355 1

Group (cost=1,851.73..1,856.26 rows=181 width=258) (actual time=73,672.109..84,119.399 rows=2,355 loops=1)

  • Group Key: action.id, r.id, c.id, gbc.id, gb.id, et.id, lcat.name
20. 41,844.416 80,086.053 ↓ 190,379.6 34,458,705 1

Sort (cost=1,851.73..1,852.18 rows=181 width=341) (actual time=73,672.100..80,086.053 rows=34,458,705 loops=1)

  • Sort Key: action.id, r.id, c.id, gbc.id, gb.id, et.id, lcat.name
  • Sort Method: external merge Disk: 13,037,896kB
21. 4,601.849 38,241.637 ↓ 190,379.6 34,458,705 1

Merge Left Join (cost=1,292.10..1,844.94 rows=181 width=341) (actual time=13.257..38,241.637 rows=34,458,705 loops=1)

  • Merge Cond: (action.id = ledc2.entity_id)
22. 11,768.100 33,629.442 ↓ 328,178.1 34,458,705 1

Nested Loop Left Join (cost=2.58..480.98 rows=105 width=341) (actual time=2.874..33,629.442 rows=34,458,705 loops=1)

23. 11,977.861 21,861.342 ↓ 328,178.1 34,458,705 1

Nested Loop Left Join (cost=2.30..448.51 rows=105 width=318) (actual time=2.865..21,861.342 rows=34,458,705 loops=1)

24. 4,321.447 9,883.481 ↓ 328,178.1 34,458,705 1

Nested Loop Left Join (cost=2.01..185.48 rows=105 width=288) (actual time=2.859..9,883.481 rows=34,458,705 loops=1)

25. 95.059 348.659 ↓ 20,853.5 41,707 1

Nested Loop Left Join (cost=1.57..83.30 rows=2 width=288) (actual time=2.844..348.659 rows=41,707 loops=1)

  • Join Filter: (action.type_id = lcat.type_id)
  • Rows Removed by Join Filter: 1,542,942
26. 24.208 170.186 ↓ 20,853.5 41,707 1

Nested Loop Left Join (cost=1.57..51.18 rows=2 width=278) (actual time=2.802..170.186 rows=41,707 loops=1)

  • Join Filter: (res.id = rcl.responsibility_id)
  • Rows Removed by Join Filter: 83,384
27. 31.700 145.978 ↓ 20,853.5 41,707 1

Nested Loop Left Join (cost=1.57..46.87 rows=2 width=282) (actual time=2.786..145.978 rows=41,707 loops=1)

  • Join Filter: (res.id = action.responsibility_id)
  • Rows Removed by Join Filter: 125,056
28. 10.664 114.278 ↓ 20,853.5 41,707 1

Nested Loop Left Join (cost=1.44..44.39 rows=2 width=282) (actual time=2.768..114.278 rows=41,707 loops=1)

29. 5.410 65.934 ↓ 1,177.5 2,355 1

Nested Loop Left Join (cost=1.01..39.07 rows=2 width=282) (actual time=2.753..65.934 rows=2,355 loops=1)

30. 5.508 48.749 ↓ 1,177.5 2,355 1

Nested Loop Left Join (cost=0.72..34.05 rows=2 width=252) (actual time=2.740..48.749 rows=2,355 loops=1)

31. 7.751 43.241 ↓ 1,177.5 2,355 1

Nested Loop Left Join (cost=0.43..29.04 rows=2 width=228) (actual time=2.738..43.241 rows=2,355 loops=1)

32. 28.425 28.425 ↓ 1,177.5 2,355 1

Index Scan using action_item_mgmt_id_idx on action_item_mgmt action (cost=0.29..24.30 rows=2 width=226) (actual time=2.730..28.425 rows=2,355 loops=1)

  • Index Cond: (id = ANY ($21))
  • Filter: ((NOT deleted) AND (client_id = 1,024))
33. 7.065 7.065 ↑ 1.0 1 2,355

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

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

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

  • Index Cond: (action.contract_id = id)
35. 11.775 11.775 ↑ 1.0 1 2,355

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

  • Index Cond: (action.relation_id = id)
36. 37.680 37.680 ↓ 18.0 18 2,355

Index Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.43..2.65 rows=1 width=8) (actual time=0.010..0.016 rows=18 loops=2,355)

  • Index Cond: ((action.id = entity_id) AND (entity_type_id = 18))
37. 0.000 0.000 ↑ 1.0 3 41,707

Materialize (cost=0.13..2.39 rows=3 width=4) (actual time=0.000..0.000 rows=3 loops=41,707)

38. 0.012 0.012 ↑ 1.0 3 1

Index Only Scan using pk_responsibility on responsibility res (cost=0.13..2.38 rows=3 width=4) (actual time=0.011..0.012 rows=3 loops=1)

  • Heap Fetches: 0
39. 0.000 0.000 ↑ 1.0 2 41,707

Materialize (cost=0.00..4.26 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=41,707)

40. 0.014 0.014 ↑ 1.0 2 1

Seq Scan on responsibility_client_link rcl (cost=0.00..4.25 rows=2 width=4) (actual time=0.012..0.014 rows=2 loops=1)

  • Filter: (client_id = 1,024)
  • Rows Removed by Filter: 18
41. 83.363 83.414 ↑ 1.0 37 41,707

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

42. 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.031..0.051 rows=37 loops=1)

  • Filter: (client_id = 1,024)
  • Rows Removed by Filter: 516
43. 5,213.375 5,213.375 ↓ 15.9 826 41,707

Index Scan using idx_link_entity_service_6 on link_entity_service les (cost=0.44..50.57 rows=52 width=8) (actual time=0.008..0.125 rows=826 loops=41,707)

  • Index Cond: ((action.id = entity_id) AND (entity_type_id = 18))
44. 0.000 0.000 ↓ 0.0 0 34,458,705

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=34,458,705)

  • Index Cond: (action.governance_body_child_id = id)
45. 0.000 0.000 ↓ 0.0 0 34,458,705

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=34,458,705)

  • Index Cond: (gbc.governance_body_id = id)
46. 3.934 10.346 ↓ 1.1 15,284 1

Sort (cost=1,289.53..1,325.71 rows=14,472 width=8) (actual time=9.606..10.346 rows=15,284 loops=1)

  • Sort Key: ledc2.entity_id
  • Sort Method: quicksort Memory: 1,101kB
47. 6.412 6.412 ↓ 1.1 15,284 1

CTE Scan on ledc2 (cost=0.00..289.44 rows=14,472 width=8) (actual time=0.017..6.412 rows=15,284 loops=1)

48.          

SubPlan (for Result)

49. 2.240 2.240 ↑ 1.0 1 2,240

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

  • Index Cond: (id = action.entity_id)
50. 0.052 0.052 ↑ 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.001 rows=1 loops=52)

  • Index Cond: (id = action.entity_id)
51. 0.056 0.056 ↑ 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.004..0.004 rows=1 loops=14)

  • Index Cond: (id = action.entity_id)
52. 0.009 0.009 ↑ 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.008..0.009 rows=1 loops=1)

  • Index Cond: (id = action.entity_id)
53. 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)
54. 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)
55. 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)
56. 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)
57. 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)
58. 0.031 0.031 ↑ 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.001 rows=1 loops=31)

  • Index Cond: (id = action.entity_id)
59. 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)
60. 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)
61. 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 : 2.975 ms
Execution time : 86,079.361 ms