explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4PGe

Settings
# exclusive inclusive rows x rows loops node
1. 1.061 13,387.937 ↓ 1.2 2,354 1

Sort (cost=135,488.36..135,493.10 rows=1,897 width=44) (actual time=13,387.836..13,387.937 rows=2,354 loops=1)

  • Sort Key: action.status_id
  • Sort Method: quicksort Memory: 282kB
2.          

CTE user_data_filter

3. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.42..52.02 rows=86 width=23) (never executed)

  • Index Cond: (user_id = 2,159,767)
  • Filter: (NOT deleted)
4. 1,655.701 13,386.876 ↓ 1.2 2,354 1

Group (cost=1,707.42..135,333.05 rows=1,897 width=44) (actual time=10.464..13,386.876 rows=2,354 loops=1)

  • Group Key: action.id
5. 2,882.220 11,728.635 ↓ 325.5 34,458,607 1

Merge Left Join (cost=1,707.42..72,799.38 rows=105,857 width=20) (actual time=10.447..11,728.635 rows=34,458,607 loops=1)

  • Merge Cond: (action.id = ledc2.entity_id)
6. 4,094.839 8,781.316 ↓ 325.5 34,458,607 1

Merge Left Join (cost=1,130.21..63,961.41 rows=105,857 width=20) (actual time=4.813..8,781.316 rows=34,458,607 loops=1)

  • Merge Cond: (action.id = les.entity_id)
7. 113.117 210.976 ↓ 22.0 41,700 1

Nested Loop Left Join (cost=228.29..9,660.44 rows=1,897 width=20) (actual time=2.127..210.976 rows=41,700 loops=1)

  • Join Filter: (action.type_id = lcat.type_id)
  • Rows Removed by Join Filter: 1,542,683
8. 23.357 56.159 ↓ 22.0 41,700 1

Nested Loop Left Join (cost=228.29..8,576.60 rows=1,897 width=24) (actual time=2.095..56.159 rows=41,700 loops=1)

  • Join Filter: (res.id = action.responsibility_id)
  • Rows Removed by Join Filter: 125,068
9. 9.118 32.802 ↓ 22.0 41,700 1

Merge Left Join (cost=228.16..8,484.51 rows=1,897 width=28) (actual time=2.065..32.802 rows=41,700 loops=1)

  • Merge Cond: (action.id = lef.entity_id)
10. 8.236 8.236 ↓ 1.2 2,354 1

Index Scan using action_item_mgmt_id_idx on action_item_mgmt action (cost=0.29..4,609.91 rows=1,897 width=60) (actual time=0.898..8.236 rows=2,354 loops=1)

  • Filter: ((NOT deleted) AND (client_id = 1,024))
  • Rows Removed by Filter: 6,029
11. 15.448 15.448 ↑ 1.1 45,875 1

Index Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.43..62,230.41 rows=50,171 width=8) (actual time=0.034..15.448 rows=45,875 loops=1)

  • Index Cond: (entity_type_id = 18)
12. 0.000 0.000 ↑ 1.0 3 41,700

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

13. 0.003 0.026 ↑ 1.0 3 1

Nested Loop Left Join (cost=0.13..6.72 rows=3 width=4) (actual time=0.023..0.026 rows=3 loops=1)

  • Join Filter: (res.id = rcl.responsibility_id)
  • Rows Removed by Join Filter: 4
14. 0.011 0.011 ↑ 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.010..0.011 rows=3 loops=1)

  • Heap Fetches: 0
15. 0.004 0.012 ↑ 1.0 2 3

Materialize (cost=0.00..4.26 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=3)

16. 0.008 0.008 ↑ 1.0 2 1

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

  • Filter: (client_id = 1,024)
  • Rows Removed by Filter: 18
17. 41.655 41.700 ↑ 1.0 37 41,700

Materialize (cost=0.00..31.10 rows=37 width=4) (actual time=0.000..0.001 rows=37 loops=41,700)

18. 0.045 0.045 ↑ 1.0 37 1

Seq Scan on link_client_action_type lcat (cost=0.00..30.91 rows=37 width=4) (actual time=0.028..0.045 rows=37 loops=1)

  • Filter: (client_id = 1,024)
  • Rows Removed by Filter: 516
19. 4,475.501 4,475.501 ↓ 19.3 34,473,847 1

Index Scan using idx_link_entity_service_6 on link_entity_service les (cost=0.44..640,881.36 rows=1,788,973 width=8) (actual time=0.089..4,475.501 rows=34,473,847 loops=1)

  • Index Cond: (entity_type_id = 18)
20. 1.403 65.099 ↑ 1.1 15,284 1

Materialize (cost=0.43..56,635.27 rows=16,882 width=8) (actual time=0.480..65.099 rows=15,284 loops=1)

21. 63.696 63.696 ↑ 1.1 15,284 1

Index Scan using idx_link_entity_delivery_country_6 on link_entity_delivery_country ledc2 (cost=0.43..56,593.06 rows=16,882 width=8) (actual time=0.471..63.696 rows=15,284 loops=1)

  • Index Cond: (entity_type_id = 18)
22.          

SubPlan (for Group)

23. 2.239 2.239 ↑ 1.0 1 2,239

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

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

  • Index Cond: (id = action.entity_id)
25. 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)
26. 0.010 0.010 ↑ 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.010..0.010 rows=1 loops=1)

  • Index Cond: (id = action.entity_id)
27. 0.024 0.024 ↑ 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.007..0.008 rows=1 loops=3)

  • Index Cond: (id = action.entity_id)
28. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: (id = action.entity_id)
29. 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)
30. 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.002..0.002 rows=1 loops=13)

  • Index Cond: (id = action.entity_id)
31. 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)
32. 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)
33. 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)
34. 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)
35. 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.117 ms
Execution time : 13,388.361 ms