explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Joqu

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 13,878.982 ↓ 1,177.0 2,354 1

Sort (cost=14,810.05..14,810.06 rows=2 width=40) (actual time=13,878.872..13,878.982 rows=2,354 loops=1)

  • Sort Key: action.status_id
  • Sort Method: quicksort Memory: 278kB
  • Buffers: shared hit=974,094
2.          

CTE ledc2

3. 3.295 3.295 ↑ 1.1 15,284 1

Index Scan using idx_link_entity_delivery_country_4 on link_entity_delivery_country (cost=0.43..12,523.85 rows=16,882 width=18) (actual time=0.013..3.295 rows=15,284 loops=1)

  • Index Cond: (entity_type_id = 18)
  • Buffers: shared hit=915
4.          

Initplan (for Sort)

5. 0.297 2.625 ↓ 1,177.0 2,354 1

Unique (cost=430.35..430.36 rows=2 width=4) (actual time=2.220..2.625 rows=2,354 loops=1)

  • Buffers: shared hit=1,872
6.          

CTE user_data_filter

7. 0.038 0.038 ↑ 4.1 21 1

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.42..52.02 rows=86 width=23) (actual time=0.012..0.038 rows=21 loops=1)

  • Index Cond: (user_id = 2,159,767)
  • Filter: (NOT deleted)
  • Buffers: shared hit=22
8. 0.599 2.328 ↓ 1,177.0 2,354 1

Sort (cost=378.33..378.33 rows=2 width=4) (actual time=2.219..2.328 rows=2,354 loops=1)

  • Sort Key: edl.entity_id
  • Sort Method: quicksort Memory: 207kB
  • Buffers: shared hit=1,872
9. 0.204 1.729 ↓ 1,177.0 2,354 1

Append (cost=0.43..378.32 rows=2 width=4) (actual time=0.035..1.729 rows=2,354 loops=1)

  • Buffers: shared hit=1,872
10. 0.214 1.522 ↓ 2,354.0 2,354 1

Nested Loop (cost=0.43..116.10 rows=1 width=4) (actual time=0.034..1.522 rows=2,354 loops=1)

  • Buffers: shared hit=1,872
11. 0.048 0.048 ↑ 2.0 21 1

CTE Scan on user_data_filter da (cost=0.00..1.72 rows=43 width=8) (actual time=0.014..0.048 rows=21 loops=1)

  • Filter: self_access
  • Buffers: shared hit=22
12. 1.260 1.260 ↓ 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.060 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
  • Buffers: shared hit=1,850
13. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.85..262.19 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..255.97 rows=12 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..1.72 rows=43 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.90 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. 2,050.224 13,877.733 ↓ 1,177.0 2,354 1

Group (cost=1,524.31..1,855.84 rows=2 width=40) (actual time=13.344..13,877.733 rows=2,354 loops=1)

  • Group Key: action.id
  • Buffers: shared hit=974,094
19. 3,334.199 11,822.742 ↓ 153,149.4 34,458,607 1

Merge Left Join (cost=1,524.31..1,789.63 rows=225 width=16) (actual time=13.323..11,822.742 rows=34,458,607 loops=1)

  • Merge Cond: (action.id = ledc2.entity_id)
  • Buffers: shared hit=967,030
20. 3,253.359 8,478.203 ↓ 307,666.1 34,458,607 1

Nested Loop Left Join (cost=1.28..179.66 rows=112 width=16) (actual time=2.920..8,478.203 rows=34,458,607 loops=1)

  • Buffers: shared hit=966,115
21. 95.482 262.544 ↓ 20,850.0 41,700 1

Nested Loop Left Join (cost=0.85..68.54 rows=2 width=16) (actual time=2.909..262.544 rows=41,700 loops=1)

  • Join Filter: (action.type_id = lcat.type_id)
  • Rows Removed by Join Filter: 1,542,683
  • Buffers: shared hit=18,832
22. 21.499 83.662 ↓ 20,850.0 41,700 1

Nested Loop Left Join (cost=0.85..36.42 rows=2 width=20) (actual time=2.877..83.662 rows=41,700 loops=1)

  • Join Filter: (res.id = rcl.responsibility_id)
  • Rows Removed by Join Filter: 83,370
  • Buffers: shared hit=18,826
23. 28.371 62.163 ↓ 20,850.0 41,700 1

Nested Loop Left Join (cost=0.85..32.11 rows=2 width=24) (actual time=2.867..62.163 rows=41,700 loops=1)

  • Join Filter: (res.id = action.responsibility_id)
  • Rows Removed by Join Filter: 125,035
  • Buffers: shared hit=18,825
24. 7.800 33.792 ↓ 20,850.0 41,700 1

Nested Loop Left Join (cost=0.72..29.63 rows=2 width=24) (actual time=2.859..33.792 rows=41,700 loops=1)

  • Buffers: shared hit=18,823
25. 9.514 9.514 ↓ 1,177.0 2,354 1

Index Scan using action_item_mgmt_id_idx on action_item_mgmt action (cost=0.29..24.31 rows=2 width=56) (actual time=2.848..9.514 rows=2,354 loops=1)

  • Index Cond: (id = ANY ($21))
  • Filter: ((NOT deleted) AND (client_id = 1,024))
  • Buffers: shared hit=8,874
26. 16.478 16.478 ↓ 18.0 18 2,354

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.004..0.007 rows=18 loops=2,354)

  • Index Cond: ((action.id = entity_id) AND (entity_type_id = 18))
  • Buffers: shared hit=9,949
27. 0.000 0.000 ↑ 1.0 3 41,700

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

  • Buffers: shared hit=2
28. 0.005 0.005 ↑ 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.004..0.005 rows=3 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=2
29. 0.000 0.000 ↑ 1.0 2 41,700

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

  • Buffers: shared hit=1
30. 0.011 0.011 ↑ 1.0 2 1

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

  • Filter: (client_id = 1,024)
  • Rows Removed by Filter: 18
  • Buffers: shared hit=1
31. 83.349 83.400 ↑ 1.0 37 41,700

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

  • Buffers: shared hit=6
32. 0.051 0.051 ↑ 1.0 37 1

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

  • Filter: (client_id = 1,024)
  • Rows Removed by Filter: 516
  • Buffers: shared hit=6
33. 4,962.300 4,962.300 ↓ 14.8 826 41,700

Index Scan using idx_link_entity_service_6 on link_entity_service les (cost=0.44..55.00 rows=56 width=8) (actual time=0.007..0.119 rows=826 loops=41,700)

  • Index Cond: ((action.id = entity_id) AND (entity_type_id = 18))
  • Buffers: shared hit=947,283
34. 3.681 10.340 ↑ 1.1 15,284 1

Sort (cost=1,523.03..1,565.23 rows=16,882 width=8) (actual time=9.505..10.340 rows=15,284 loops=1)

  • Sort Key: ledc2.entity_id
  • Sort Method: quicksort Memory: 1,101kB
  • Buffers: shared hit=915
35. 6.659 6.659 ↑ 1.1 15,284 1

CTE Scan on ledc2 (cost=0.00..337.64 rows=16,882 width=8) (actual time=0.015..6.659 rows=15,284 loops=1)

  • Buffers: shared hit=915
36.          

SubPlan (for Group)

37. 4.478 4.478 ↑ 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.002..0.002 rows=1 loops=2,239)

  • Index Cond: (id = action.entity_id)
  • Buffers: shared hit=6,717
38. 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.003..0.003 rows=1 loops=52)

  • Index Cond: (id = action.entity_id)
  • Buffers: shared hit=156
39. 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)
  • Buffers: shared hit=42
40. 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.009..0.009 rows=1 loops=1)

  • Index Cond: (id = action.entity_id)
  • Buffers: shared hit=4
41. 0.015 0.015 ↑ 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.005..0.005 rows=1 loops=3)

  • Index Cond: (id = action.entity_id)
  • Buffers: shared hit=9
42. 0.010 0.010 ↑ 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.010..0.010 rows=1 loops=1)

  • Index Cond: (id = action.entity_id)
  • Buffers: shared hit=4
43. 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)
44. 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)
  • Buffers: shared hit=39
45. 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)
46. 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)
  • Buffers: shared hit=93
47. 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)
48. 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)
49. 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.400 ms
Execution time : 13,879.522 ms