explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xY3C

Settings
# exclusive inclusive rows x rows loops node
1. 0.557 45,434.786 ↓ 1.2 2,354 1

Sort (cost=151,390.44..151,395.19 rows=1,897 width=44) (actual time=45,434.690..45,434.786 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. 4,192.320 45,434.229 ↓ 1.2 2,354 1

HashAggregate (cost=88,947.14..151,235.13 rows=1,897 width=44) (actual time=45,430.560..45,434.229 rows=2,354 loops=1)

  • Group Key: action.id
5. 3,710.857 41,239.408 ↓ 325.5 34,458,607 1

Merge Right Join (cost=80,566.95..88,682.50 rows=105,857 width=20) (actual time=34,781.812..41,239.408 rows=34,458,607 loops=1)

  • Merge Cond: (ledc2.entity_id = action.id)
6. 66.061 66.061 ↑ 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.476..66.061 rows=15,284 loops=1)

  • Index Cond: (entity_type_id = 18)
7. 13,384.320 37,462.490 ↓ 325.5 34,458,607 1

Sort (cost=80,006.27..80,270.92 rows=105,857 width=20) (actual time=34,776.934..37,462.490 rows=34,458,607 loops=1)

  • Sort Key: action.id
  • Sort Method: external sort Disk: 1,146,696kB
8. 4,492.182 24,078.170 ↓ 325.5 34,458,607 1

Merge Right Join (cost=69,605.54..71,171.58 rows=105,857 width=20) (actual time=16,870.791..24,078.170 rows=34,458,607 loops=1)

  • Merge Cond: (lcat.type_id = action.type_id)
9. 0.014 0.061 ↑ 1.2 32 1

Sort (cost=31.88..31.97 rows=37 width=4) (actual time=0.056..0.061 rows=32 loops=1)

  • Sort Key: lcat.type_id
  • Sort Method: quicksort Memory: 26kB
10. 0.047 0.047 ↑ 1.0 37 1

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

  • Filter: (client_id = 1,024)
  • Rows Removed by Filter: 516
11. 12,807.948 19,585.927 ↓ 325.5 34,458,607 1

Sort (cost=69,573.66..69,838.30 rows=105,857 width=24) (actual time=16,870.726..19,585.927 rows=34,458,607 loops=1)

  • Sort Key: action.type_id
  • Sort Method: external sort Disk: 1,146,696kB
12. 5,066.567 6,777.979 ↓ 325.5 34,458,607 1

Merge Right Join (cost=6,815.37..60,738.96 rows=105,857 width=24) (actual time=38.078..6,777.979 rows=34,458,607 loops=1)

  • Merge Cond: (les.entity_id = action.id)
13. 321.308 321.308 ↑ 1.0 1,741,406 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.093..321.308 rows=1,741,406 loops=1)

  • Index Cond: (entity_type_id = 18)
14. 1,364.083 1,390.104 ↓ 18,164.8 34,458,580 1

Sort (cost=5,913.45..5,918.19 rows=1,897 width=24) (actual time=35.358..1,390.104 rows=34,458,580 loops=1)

  • Sort Key: action.id
  • Sort Method: quicksort Memory: 4,794kB
15. 7.137 26.021 ↓ 22.0 41,700 1

Merge Right Join (cost=2,158.70..5,810.16 rows=1,897 width=24) (actual time=5.132..26.021 rows=41,700 loops=1)

  • Merge Cond: (lef.entity_id = action.id)
16. 14.734 14.734 ↑ 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.026..14.734 rows=45,875 loops=1)

  • Index Cond: (entity_type_id = 18)
17. 1.117 4.150 ↓ 1.2 2,354 1

Sort (cost=1,930.82..1,935.56 rows=1,897 width=56) (actual time=3.939..4.150 rows=2,354 loops=1)

  • Sort Key: action.id
  • Sort Method: quicksort Memory: 427kB
18. 3.033 3.033 ↓ 1.2 2,354 1

Index Scan using action_item_mgmt_client_id_idx on action_item_mgmt action (cost=0.29..1,827.54 rows=1,897 width=56) (actual time=0.035..3.033 rows=2,354 loops=1)

  • Index Cond: (client_id = 1,024)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 18
19.          

SubPlan (for HashAggregate)

20. 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)
21. 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.002..0.002 rows=1 loops=52)

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

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

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

  • Index Cond: (id = action.entity_id)
26. 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)
27. 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)
28. 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)
29. 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)
30. 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)
31. 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)
32. 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 : 1.537 ms
Execution time : 45,734.901 ms