explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ySN0

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 4,797.171 ↑ 1.0 20 1

Subquery Scan on data (cost=305,780.72..305,801.17 rows=20 width=4) (actual time=4,796.642..4,797.171 rows=20 loops=1)

2. 0.024 4,797.167 ↑ 1.0 20 1

Limit (cost=305,780.72..305,800.97 rows=20 width=1,952) (actual time=4,796.641..4,797.167 rows=20 loops=1)

3. 0.585 4,797.143 ↑ 75.1 20 1

Result (cost=305,780.72..307,301.49 rows=1,502 width=1,952) (actual time=4,796.640..4,797.143 rows=20 loops=1)

4. 0.686 4,796.558 ↑ 75.1 20 1

Sort (cost=305,780.72..305,784.47 rows=1,502 width=1,968) (actual time=4,796.556..4,796.558 rows=20 loops=1)

  • Sort Key: csd.client_entity_seq_id DESC
  • Sort Method: top-N heapsort Memory: 32kB
5. 277.294 4,795.872 ↑ 1.1 1,356 1

GroupAggregate (cost=305,658.14..305,740.75 rows=1,502 width=1,968) (actual time=4,343.877..4,795.872 rows=1,356 loops=1)

  • Group Key: csd.id, ct.id, tz.id, ssc.id, cs.id, ut.id, currency.id, sdfc.id, csdp.id, scat.id, rn.id, wfs.id, ebep.entity_id, ut_1.id, au_crby.id, au_lmby.id, cur.id
6. 1,225.664 4,518.578 ↓ 748.4 1,124,163 1

Sort (cost=305,658.14..305,661.89 rows=1,502 width=120) (actual time=4,343.862..4,518.578 rows=1,124,163 loops=1)

  • Sort Key: csd.id, ct.id, tz.id, ssc.id, cs.id, ut.id, currency.id, sdfc.id, csdp.id, scat.id, rn.id, wfs.id, ebep.entity_id, ut_1.id, au_crby.id, au_lmby.id, cur.id
  • Sort Method: external merge Disk: 119536kB
7. 833.954 3,292.914 ↓ 748.4 1,124,163 1

Nested Loop Left Join (cost=8,914.58..305,578.89 rows=1,502 width=120) (actual time=27.317..3,292.914 rows=1,124,163 loops=1)

8. 283.802 2,458.960 ↓ 748.4 1,124,163 1

Nested Loop Left Join (cost=8,914.30..304,770.33 rows=1,502 width=120) (actual time=27.312..2,458.960 rows=1,124,163 loops=1)

9. 198.715 1,050.995 ↓ 748.4 1,124,163 1

Merge Left Join (cost=8,914.01..303,961.78 rows=1,502 width=120) (actual time=27.300..1,050.995 rows=1,124,163 loops=1)

  • Merge Cond: (csd.id = ebep.entity_id)
10. 131.726 841.785 ↓ 748.4 1,124,163 1

Nested Loop Left Join (cost=303.10..295,347.09 rows=1,502 width=112) (actual time=16.801..841.785 rows=1,124,163 loops=1)

11. 10.152 283.519 ↓ 94.7 142,180 1

Nested Loop Left Join (cost=302.67..291,722.93 rows=1,502 width=112) (actual time=16.792..283.519 rows=142,180 loops=1)

12. 20.616 129.287 ↓ 48.0 72,040 1

Nested Loop Left Join (cost=302.25..288,515.47 rows=1,502 width=112) (actual time=16.784..129.287 rows=72,040 loops=1)

13. 3.723 69.773 ↓ 12.9 19,449 1

Merge Left Join (cost=301.82..285,137.06 rows=1,502 width=112) (actual time=16.773..69.773 rows=19,449 loops=1)

  • Merge Cond: (csd.id = les.entity_id)
14. 3.435 64.883 ↓ 12.9 19,449 1

Nested Loop Left Join (cost=3.26..284,832.77 rows=1,502 width=112) (actual time=15.574..64.883 rows=19,449 loops=1)

15. 0.653 51.956 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=2.83..281,002.45 rows=1,502 width=112) (actual time=15.564..51.956 rows=1,356 loops=1)

16. 0.027 51.303 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=2.69..280,756.17 rows=1,502 width=112) (actual time=15.561..51.303 rows=1,356 loops=1)

17. 0.255 49.920 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=2.54..280,510.05 rows=1,502 width=112) (actual time=15.556..49.920 rows=1,356 loops=1)

18. 0.385 48.309 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=2.27..280,059.28 rows=1,502 width=112) (actual time=15.548..48.309 rows=1,356 loops=1)

19. 0.478 46.568 ↑ 1.1 1,356 1

Merge Left Join (cost=1.99..279,609.61 rows=1,502 width=112) (actual time=15.541..46.568 rows=1,356 loops=1)

  • Merge Cond: (csd.id = sdfc.service_id)
20. 0.546 45.993 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=1.84..279,592.02 rows=1,502 width=108) (actual time=15.518..45.993 rows=1,356 loops=1)

21. 2.767 44.091 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=1.57..279,148.33 rows=1,502 width=108) (actual time=15.509..44.091 rows=1,356 loops=1)

  • Join Filter: (tz.id = ct.time_zone_id)
  • Rows Removed by Join Filter: 30753
22. 0.546 39.968 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=1.57..278,234.80 rows=1,502 width=92) (actual time=15.497..39.968 rows=1,356 loops=1)

23. 1.143 38.066 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=1.29..277,781.83 rows=1,502 width=92) (actual time=15.489..38.066 rows=1,356 loops=1)

24. 0.676 35.567 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=1.00..276,301.18 rows=1,502 width=88) (actual time=15.480..35.567 rows=1,356 loops=1)

25. 0.528 34.891 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=0.86..276,054.90 rows=1,502 width=88) (actual time=15.474..34.891 rows=1,356 loops=1)

26. 0.676 33.007 ↑ 1.1 1,356 1

Nested Loop Left Join (cost=0.57..275,583.40 rows=1,502 width=88) (actual time=15.464..33.007 rows=1,356 loops=1)

27. 21.115 32.331 ↑ 1.1 1,356 1

Index Scan using pk_contract_service_data on contract_service_data csd (cost=0.29..274,967.99 rows=1,502 width=88) (actual time=15.458..32.331 rows=1,356 loops=1)

  • Filter: ((NOT deleted) AND (status_id = ANY ('{6742,8099}'::integer[])) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4) OR (alternatives: SubPlan 5 or hashed SubPlan 6)))
  • Rows Removed by Filter: 22534
28.          

SubPlan (for Index Scan)

29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr (cost=0.43..2.65 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 9298) AND (entity_type_id = 64) AND (entity_id = csd.id))
  • Heap Fetches: 0
30. 0.018 0.018 ↓ 3.8 42 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_1 (cost=0.43..2.85 rows=11 width=4) (actual time=0.013..0.018 rows=42 loops=1)

  • Index Cond: ((user_id = 9298) AND (entity_type_id = 64))
  • Heap Fetches: 0
31. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.72..5.17 rows=1 width=0) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_contract_service_data on contract_service_data et (cost=0.29..2.51 rows=1 width=8) (never executed)

  • Index Cond: (id = csd.id)
33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_2 (cost=0.43..2.65 rows=1 width=4) (never executed)

  • Index Cond: ((user_id = 9298) AND (entity_type_id = 61) AND (entity_id = et.contract_id))
  • Heap Fetches: 0
34. 2.033 11.198 ↓ 3.2 2,930 1

Hash Join (cost=19.65..1,863.29 rows=913 width=4) (actual time=0.337..11.198 rows=2,930 loops=1)

  • Hash Cond: (et_1.contract_id = udr_3.entity_id)
35. 8.841 8.841 ↑ 1.0 23,890 1

Seq Scan on contract_service_data et_1 (cost=0.00..1,780.90 rows=23,890 width=12) (actual time=0.005..8.841 rows=23,890 loops=1)

36. 0.145 0.324 ↓ 2.8 1,296 1

Hash (cost=13.95..13.95 rows=456 width=4) (actual time=0.324..0.324 rows=1,296 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 62kB
37. 0.179 0.179 ↓ 2.8 1,296 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_3 (cost=0.43..13.95 rows=456 width=4) (actual time=0.008..0.179 rows=1,296 loops=1)

  • Index Cond: ((user_id = 9298) AND (entity_type_id = 61))
  • Heap Fetches: 4
38. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=2.95..3.60 rows=1 width=0) (never executed)

  • Merge Cond: (et_2.relation_id = udr_4.entity_id)
39. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.52..2.52 rows=1 width=4) (never executed)

  • Sort Key: et_2.relation_id
40. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_contract_service_data on contract_service_data et_2 (cost=0.29..2.51 rows=1 width=4) (never executed)

  • Index Cond: (id = csd.id)
41. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_4 (cost=0.43..3.55 rows=46 width=4) (never executed)

  • Index Cond: ((user_id = 9298) AND (entity_type_id = 1))
  • Heap Fetches: 0
42. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=4.12..1,847.77 rows=155 width=4) (never executed)

  • Hash Cond: (et_3.relation_id = udr_5.entity_id)
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on contract_service_data et_3 (cost=0.00..1,780.90 rows=23,890 width=8) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.55..3.55 rows=46 width=4) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_5 (cost=0.43..3.55 rows=46 width=4) (never executed)

  • Index Cond: ((user_id = 9298) AND (entity_type_id = 1))
  • Heap Fetches: 0
46. 0.000 0.000 ↓ 0.0 0 1,356

Index Only Scan using pk_contract_service_data on contract_service_data csdp (cost=0.29..0.41 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1,356)

  • Index Cond: (id = csd.parent_id)
  • Heap Fetches: 0
47. 1.356 1.356 ↑ 1.0 1 1,356

Index Only Scan using pk_work_flow_status on work_flow_status wfs (cost=0.28..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,356)

  • Index Cond: (id = csd.status_id)
  • Heap Fetches: 0
48. 0.000 0.000 ↓ 0.0 0 1,356

Index Only Scan using pk_contract_service on contract_service cs (cost=0.14..0.16 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1,356)

  • Index Cond: (id = csd.service_id)
  • Heap Fetches: 0
49. 1.356 1.356 ↑ 1.0 1 1,356

Index Scan using pk_contract on contract ct (cost=0.29..0.99 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,356)

  • Index Cond: (id = csd.contract_id)
50. 1.356 1.356 ↑ 1.0 1 1,356

Index Only Scan using pk_relation on relation rn (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,356)

  • Index Cond: (id = csd.relation_id)
  • Heap Fetches: 68
51. 1.343 1.356 ↑ 1.7 24 1,356

Materialize (cost=0.00..1.62 rows=41 width=20) (actual time=0.000..0.001 rows=24 loops=1,356)

52. 0.013 0.013 ↑ 1.0 41 1

Seq Scan on time_zone tz (cost=0.00..1.41 rows=41 width=20) (actual time=0.005..0.013 rows=41 loops=1)

53. 1.356 1.356 ↑ 1.0 1 1,356

Index Only Scan using pk_unit_type on unit_type ut (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,356)

  • Index Cond: (id = csd.unit_type_id)
  • Heap Fetches: 0
54. 0.097 0.097 ↑ 1.0 351 1

Index Scan using service_data_forecast_details_service_id_key on service_data_forecast_details sdfc (cost=0.15..12.74 rows=352 width=8) (actual time=0.007..0.097 rows=351 loops=1)

55. 1.356 1.356 ↑ 1.0 1 1,356

Index Only Scan using pk_service_category on service_category scat (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,356)

  • Index Cond: (id = csd.service_category_id)
  • Heap Fetches: 0
56. 1.356 1.356 ↑ 1.0 1 1,356

Index Only Scan using pk_service_sub_category on service_sub_category ssc (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,356)

  • Index Cond: (id = csd.service_sub_category_id)
  • Heap Fetches: 0
57. 1.356 1.356 ↑ 1.0 1 1,356

Index Only Scan using pk_currency on currency (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,356)

  • Index Cond: (id = csd.currency_id)
  • Heap Fetches: 0
58. 0.000 0.000 ↓ 0.0 0 1,356

Index Only Scan using pk_currency on currency cur (cost=0.14..0.16 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1,356)

  • Index Cond: (id = csd.invoicing_currency)
  • Heap Fetches: 0
59. 9.492 9.492 ↓ 14.0 14 1,356

Index Scan using idx_link_entity_country_3 on link_entity_country lec (cost=0.43..2.54 rows=1 width=8) (actual time=0.003..0.007 rows=14 loops=1,356)

  • Index Cond: ((entity_id = csd.id) AND (entity_type_id = 64))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 10
60. 0.235 1.167 ↑ 1.1 1,156 1

Sort (cost=293.37..296.56 rows=1,273 width=8) (actual time=1.110..1.167 rows=1,156 loops=1)

  • Sort Key: les.entity_id
  • Sort Method: quicksort Memory: 103kB
61. 0.932 0.932 ↑ 1.1 1,156 1

Seq Scan on link_entity_state les (cost=0.00..227.72 rows=1,273 width=8) (actual time=0.005..0.932 rows=1,156 loops=1)

  • Filter: ((NOT deleted) AND (entity_type_id = 64))
  • Rows Removed by Filter: 10902
62. 38.898 38.898 ↓ 4.0 4 19,449

Index Scan using idx_link_entity_region_2 on link_entity_region ler (cost=0.42..2.24 rows=1 width=8) (actual time=0.002..0.002 rows=4 loops=19,449)

  • Index Cond: ((entity_type_id = 64) AND (entity_id = csd.id))
  • Filter: (NOT deleted)
63. 144.080 144.080 ↓ 2.0 2 72,040

Index Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.42..2.13 rows=1 width=8) (actual time=0.001..0.002 rows=2 loops=72,040)

  • Index Cond: ((entity_id = csd.id) AND (entity_type_id = 64))
64. 426.540 426.540 ↓ 8.0 8 142,180

Index Scan using idx_link_entity_service_6 on link_entity_service lesr (cost=0.43..2.40 rows=1 width=8) (actual time=0.002..0.003 rows=8 loops=142,180)

  • Index Cond: ((entity_id = csd.id) AND (entity_type_id = 64))
65. 0.003 10.495 ↓ 0.0 0 1

Sort (cost=8,610.91..8,610.92 rows=1 width=8) (actual time=10.495..10.495 rows=0 loops=1)

  • Sort Key: ebep.entity_id
  • Sort Method: quicksort Memory: 25kB
66. 0.001 10.492 ↓ 0.0 0 1

Limit (cost=8,610.89..8,610.89 rows=1 width=8) (actual time=10.492..10.492 rows=0 loops=1)

67. 0.000 10.491 ↓ 0.0 0 1

Group (cost=8,610.89..8,610.90 rows=2 width=8) (actual time=10.491..10.491 rows=0 loops=1)

  • Group Key: ut_1.id, ebep.entity_id
68. 0.003 10.491 ↓ 0.0 0 1

Sort (cost=8,610.89..8,610.89 rows=2 width=8) (actual time=10.491..10.491 rows=0 loops=1)

  • Sort Key: ut_1.id DESC, ebep.entity_id
  • Sort Method: quicksort Memory: 25kB
69. 0.000 10.488 ↓ 0.0 0 1

Nested Loop Left Join (cost=284.03..8,610.88 rows=2 width=8) (actual time=10.488..10.488 rows=0 loops=1)

  • Join Filter: (ut_1.request_id = ber.id)
70. 0.001 10.488 ↓ 0.0 0 1

Nested Loop (cost=283.74..8,571.99 rows=2 width=8) (actual time=10.488..10.488 rows=0 loops=1)

71. 8.977 10.487 ↓ 0.0 0 1

Bitmap Heap Scan on entity_bulk_edit_pending ebep (cost=283.46..8,569.18 rows=2 width=8) (actual time=10.487..10.487 rows=0 loops=1)

  • Recheck Cond: (entity_type_id = 64)
  • Filter: (NOT is_request_processed)
  • Rows Removed by Filter: 23313
  • Heap Blocks: exact=4602
72. 1.510 1.510 ↑ 1.0 23,313 1

Bitmap Index Scan on idx_ebep_entity_type_id_entity_id (cost=0.00..283.46 rows=23,658 width=0) (actual time=1.510..1.510 rows=23,313 loops=1)

  • Index Cond: (entity_type_id = 64)
73. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_bulk_edit_request on bulk_edit_request ber (cost=0.29..1.41 rows=1 width=4) (never executed)

  • Index Cond: (id = ebep.bulk_edit_request_id)
  • Heap Fetches: 0
74. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.29..37.62 rows=46 width=8) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_user_tasks_user_id_client_id on user_tasks ut_1 (cost=0.29..37.39 rows=46 width=8) (never executed)

  • Index Cond: (user_id = 9298)
76. 1,124.163 1,124.163 ↑ 1.0 1 1,124,163

Index Only Scan using pk_app_user on app_user au_crby (cost=0.29..0.54 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,124,163)

  • Index Cond: (id = csd.created_by_user_id)
  • Heap Fetches: 1124095
77. 0.000 0.000 ↓ 0.0 0 1,124,163

Index Only Scan using pk_app_user on app_user au_lmby (cost=0.29..0.54 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1,124,163)

  • Index Cond: (id = csd.last_modified_by_user_id)
  • Heap Fetches: 518769
Planning time : 13.112 ms
Execution time : 4,813.635 ms