explain.depesz.com

PostgreSQL's explain analyze made readable

Result: klC7

Settings
# exclusive inclusive rows x rows loops node
1. 4.438 1,099.886 ↑ 1.0 1 1

Limit (cost=38,688.46..38,751.16 rows=1 width=4,115) (actual time=1,095.449..1,099.886 rows=1 loops=1)

2. 0.077 1,095.448 ↑ 1,603.0 1 1

Result (cost=38,688.46..139,198.72 rows=1,603 width=4,115) (actual time=1,095.448..1,095.448 rows=1 loops=1)

3. 15.072 1,078.515 ↑ 1,603.0 1 1

Sort (cost=38,688.46..38,692.46 rows=1,603 width=2,794) (actual time=1,078.515..1,078.515 rows=1 loops=1)

  • Sort Key: lit.name, li.client_entity_seq_id
  • Sort Method: top-N heapsort Memory: 27kB
4. 11.041 1,063.443 ↑ 1.6 1,024 1

GroupAggregate (cost=31,334.69..38,680.44 rows=1,603 width=2,794) (actual time=1,041.503..1,063.443 rows=1,024 loops=1)

  • Group Key: li.id, bi.id, bitz.time_zone, lit.id, tz.time_zone, cur.id, sct.id, csd.id, ssct.name, po.id, c.id, sdc.short_name, rel.id, drt.name, ilis.marked_duplicate, bulk_request_data.entity_id, bulk_request_data.task_id, ilicd.id
5. 89.125 1,052.402 ↓ 3.1 5,024 1

Sort (cost=31,334.69..31,338.70 rows=1,603 width=1,716) (actual time=1,041.364..1,052.402 rows=5,024 loops=1)

  • Sort Key: li.id, bi.id, bitz.time_zone, lit.id, tz.time_zone, cur.id, sct.id, csd.id, ssct.name, po.id, c.id, sdc.short_name, rel.id, drt.name, ilis.marked_duplicate, bulk_request_data.entity_id, bulk_request_data.task_id, ilicd.id
  • Sort Method: quicksort Memory: 2788kB
6. 8.458 963.277 ↓ 3.1 5,024 1

Nested Loop Left Join (cost=23,749.93..31,249.36 rows=1,603 width=1,716) (actual time=36.854..963.277 rows=5,024 loops=1)

7. 12.609 824.195 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,749.37..29,781.67 rows=527 width=1,664) (actual time=36.838..824.195 rows=5,024 loops=1)

  • Join Filter: (bulk_request_data.entity_id = li.id)
8. 6.523 811.586 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,654.94..29,679.31 rows=527 width=1,656) (actual time=36.819..811.586 rows=5,024 loops=1)

9. 8.304 789.991 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,654.80..29,590.56 rows=527 width=1,634) (actual time=36.815..789.991 rows=5,024 loops=1)

10. 19.053 711.351 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,654.52..29,366.39 rows=527 width=1,616) (actual time=36.804..711.351 rows=5,024 loops=1)

11. 26.834 637.034 ↓ 9.5 5,024 1

Hash Left Join (cost=23,654.38..29,280.69 rows=527 width=1,616) (actual time=36.796..637.034 rows=5,024 loops=1)

  • Hash Cond: (bi.contract_id = c.id)
12. 27.748 608.499 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,264.81..28,889.74 rows=527 width=1,596) (actual time=35.085..608.499 rows=5,024 loops=1)

13. 8.370 580.751 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,264.67..28,796.37 rows=527 width=1,584) (actual time=35.081..580.751 rows=5,024 loops=1)

14. 2.701 562.333 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,264.52..28,700.78 rows=527 width=1,564) (actual time=35.073..562.333 rows=5,024 loops=1)

15. 18.832 519.440 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,264.25..28,547.74 rows=527 width=1,541) (actual time=35.062..519.440 rows=5,024 loops=1)

16. 54.304 480.512 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,263.96..27,798.50 rows=527 width=1,484) (actual time=35.050..480.512 rows=5,024 loops=1)

17. 7.512 406.112 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,263.82..27,707.45 rows=527 width=1,480) (actual time=35.039..406.112 rows=5,024 loops=1)

18. 5.699 378.504 ↓ 9.5 5,024 1

Hash Left Join (cost=23,263.68..27,621.43 rows=527 width=1,468) (actual time=35.033..378.504 rows=5,024 loops=1)

  • Hash Cond: (bi.time_zone_id = bitz.id)
19. 7.384 372.782 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,261.75..27,617.98 rows=527 width=1,456) (actual time=35.001..372.782 rows=5,024 loops=1)

  • Join Filter: (bi.id = li.invoice_id)
20. 37.203 365.398 ↓ 9.5 5,024 1

Nested Loop Left Join (cost=23,261.33..27,607.44 rows=527 width=1,429) (actual time=34.985..365.398 rows=5,024 loops=1)

21. 164.108 318.147 ↓ 9.5 5,024 1

Hash Right Join (cost=23,261.19..27,517.48 rows=527 width=1,403) (actual time=34.975..318.147 rows=5,024 loops=1)

  • Hash Cond: (ilis.line_item_id = li.id)
22. 119.168 119.168 ↓ 1.0 193,673 1

Seq Scan on invoice_line_item_state ilis (cost=0.00..3,748.07 rows=193,607 width=5) (actual time=0.005..119.168 rows=193,673 loops=1)

23. 6.311 34.871 ↓ 1.9 1,024 1

Hash (cost=23,254.60..23,254.60 rows=527 width=1,402) (actual time=34.871..34.871 rows=1,024 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 221kB
24. 4.923 28.560 ↓ 1.9 1,024 1

Index Scan using idx_invoice_id on invoice_line_item li (cost=0.44..23,254.60 rows=527 width=1,402) (actual time=0.065..28.560 rows=1,024 loops=1)

  • Index Cond: (invoice_id = 5365)
  • Filter: ((NOT deleted) AND (NOT saved_as_draft) AND (client_id = 1005) AND (status_id = ANY ('{5,1,6,2,7255,7254,4322,4323,4321,4325,4324}'::integer[])) AND COALESCE(NULLIF((alternatives: SubPlan 15 or hashed SubPlan 16), false), NULLIF((alternatives: SubPlan 17 or hashed SubPlan 18), false), NULLIF((SubPlan 19), false), NULLIF((SubPlan 20), false), NULLIF((SubPlan 21), false)))
  • Rows Removed by Filter: 1
25.          

SubPlan (forIndex Scan)

26. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..3.84 rows=1 width=0) (never executed)

  • Join Filter: (sda.data_id = ed.id)
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on secondary_data_access sda (cost=0.00..1.19 rows=1 width=4) (never executed)

  • Filter: (user_id = 1232)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_entity_type_id_entity_id on entity_data ed (cost=0.42..2.64 rows=1 width=4) (never executed)

  • Index Cond: ((entity_type_id = 61) AND (li.contract_id = entity_id))
29. 0.001 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.42..3.83 rows=1 width=4) (actual time=0.008..0.009 rows=0 loops=1)

30. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on secondary_data_access sda_1 (cost=0.00..1.19 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: (user_id = 1232)
  • Rows Removed by Filter: 17
31. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_entity_data on entity_data ed_1 (cost=0.42..2.64 rows=1 width=8) (never executed)

  • Index Cond: (id = sda_1.data_id)
  • Filter: (entity_type_id = 61)
32. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..3.84 rows=1 width=0) (never executed)

  • Join Filter: (sda_2.data_id = ed_2.id)
33. 0.000 0.000 ↓ 0.0 0

Seq Scan on secondary_data_access sda_2 (cost=0.00..1.19 rows=1 width=4) (never executed)

  • Filter: (user_id = 1232)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_entity_type_id_entity_id on entity_data ed_2 (cost=0.42..2.64 rows=1 width=4) (never executed)

  • Index Cond: ((entity_type_id = 1) AND (li.relation_id = entity_id))
35. 0.000 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.42..3.83 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)

36. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on secondary_data_access sda_3 (cost=0.00..1.19 rows=1 width=4) (actual time=0.003..0.004 rows=0 loops=1)

  • Filter: (user_id = 1232)
  • Rows Removed by Filter: 17
37. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_entity_data on entity_data ed_3 (cost=0.42..2.64 rows=1 width=8) (never executed)

  • Index Cond: (id = sda_3.data_id)
  • Filter: (entity_type_id = 1)
38. 1.024 23.552 ↑ 1.0 1 1,024

Nested Loop (cost=0.86..4.20 rows=1 width=0) (actual time=0.023..0.023 rows=1 loops=1,024)

39. 7.168 7.168 ↑ 1.0 1 1,024

Index Scan using pk_invoice_line_item on invoice_line_item et (cost=0.44..2.66 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1,024)

  • Index Cond: (id = li.id)
40. 15.360 15.360 ↑ 1.0 1 1,024

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr (cost=0.42..1.55 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1,024)

  • Index Cond: ((user_id = 1232) AND (entity_type_id = 64) AND (entity_id = et.service_data_id))
  • Heap Fetches: 0
41. 0.009 0.072 ↑ 1.0 1 9

Nested Loop (cost=0.86..4.20 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=9)

42. 0.036 0.036 ↑ 1.0 1 9

Index Scan using pk_invoice_line_item on invoice_line_item et_1 (cost=0.44..2.66 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=9)

  • Index Cond: (id = li.id)
43. 0.027 0.027 ↑ 1.0 1 9

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_1 (cost=0.42..1.55 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=9)

  • Index Cond: ((user_id = 1232) AND (entity_type_id = 61) AND (entity_id = et_1.contract_id))
  • Heap Fetches: 0
44. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..4.20 rows=1 width=0) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_invoice_line_item on invoice_line_item et_2 (cost=0.44..2.66 rows=1 width=4) (never executed)

  • Index Cond: (id = li.id)
46. 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.42..1.55 rows=1 width=4) (never executed)

  • Index Cond: ((user_id = 1232) AND (entity_type_id = 1) AND (entity_id = et_2.relation_id))
  • Heap Fetches: 0
47. 10.048 10.048 ↑ 1.0 1 5,024

Index Scan using pk_line_item_type on line_item_type lit (cost=0.14..0.17 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=5,024)

  • Index Cond: (id = li.line_item_type)
48. 0.000 0.000 ↑ 1.0 1 5,024

Materialize (cost=0.42..2.64 rows=1 width=31) (actual time=0.000..0.000 rows=1 loops=5,024)

49. 0.012 0.012 ↑ 1.0 1 1

Index Scan using invoice_id_indx on base_invoice bi (cost=0.42..2.64 rows=1 width=31) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (id = 5365)
50. 0.010 0.023 ↑ 1.0 41 1

Hash (cost=1.41..1.41 rows=41 width=20) (actual time=0.023..0.023 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
51. 0.013 0.013 ↑ 1.0 41 1

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

52. 20.096 20.096 ↑ 1.0 1 5,024

Index Scan using pk_time_zone on time_zone tz (cost=0.14..0.16 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=5,024)

  • Index Cond: (id = li.time_zone_id)
53. 20.096 20.096 ↑ 1.0 1 5,024

Index Scan using pk_currency on currency cur (cost=0.14..0.17 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=5,024)

  • Index Cond: (id = li.currency_id)
54. 20.096 20.096 ↑ 1.0 1 5,024

Index Scan using pk_contract_service_data on contract_service_data csd (cost=0.29..1.42 rows=1 width=57) (actual time=0.004..0.004 rows=1 loops=5,024)

  • Index Cond: (id = li.service_data_id)
55. 40.192 40.192 ↑ 1.0 1 5,024

Index Scan using pk_service_sub_category on service_sub_category ssct (cost=0.27..0.29 rows=1 width=31) (actual time=0.008..0.008 rows=1 loops=5,024)

  • Index Cond: (id = csd.service_sub_category_id)
56. 10.048 10.048 ↑ 1.0 1 5,024

Index Scan using pk_service_category on service_category sct (cost=0.15..0.18 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=5,024)

  • Index Cond: (id = ssct.service_category_id)
57. 0.000 0.000 ↓ 0.0 0 5,024

Index Scan using pk_purchase_order on purchase_order po (cost=0.14..0.18 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=5,024)

  • Index Cond: (id = li.po_id)
58. 0.619 1.701 ↓ 1.0 2,515 1

Hash (cost=358.14..358.14 rows=2,514 width=24) (actual time=1.701..1.701 rows=2,515 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 173kB
59. 1.082 1.082 ↓ 1.0 2,515 1

Seq Scan on contract c (cost=0.00..358.14 rows=2,514 width=24) (actual time=0.004..1.082 rows=2,515 loops=1)

60. 55.264 55.264 ↑ 1.0 1 5,024

Index Scan using pk_currency on currency sdc (cost=0.14..0.16 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=5,024)

  • Index Cond: (id = csd.currency_id)
61. 70.336 70.336 ↑ 1.0 1 5,024

Index Scan using pk_relation on relation rel (cost=0.27..0.43 rows=1 width=22) (actual time=0.014..0.014 rows=1 loops=5,024)

  • Index Cond: (li.relation_id = id)
62. 15.072 15.072 ↓ 0.0 0 5,024

Index Scan using pk_discrepancy_reason_type on discrepancy_reason_type drt (cost=0.14..0.17 rows=1 width=30) (actual time=0.003..0.003 rows=0 loops=5,024)

  • Index Cond: (id = li.discrepancy_reason_id)
63. 0.000 0.000 ↓ 0.0 0 5,024

Materialize (cost=94.43..94.45 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=5,024)

64. 0.000 0.015 ↓ 0.0 0 1

Subquery Scan on bulk_request_data (cost=94.43..94.45 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=1)

65. 0.001 0.015 ↓ 0.0 0 1

Group (cost=94.43..94.44 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=1)

  • Group Key: ebep.entity_id, ut.id
66. 0.005 0.014 ↓ 0.0 0 1

Sort (cost=94.43..94.43 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1)

  • Sort Key: ebep.entity_id, ut.id
  • Sort Method: quicksort Memory: 25kB
67. 0.000 0.009 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.86..94.42 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1)

68. 0.001 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.57..3.31 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1)

69. 0.008 0.008 ↓ 0.0 0 1

Index Scan using idx_ebep_entity_type_id_entity_id on entity_bulk_edit_pending ebep (cost=0.29..1.90 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (entity_type_id = 165)
  • Filter: (NOT is_request_processed)
70. 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
71. 0.000 0.000 ↓ 0.0 0

Index Scan using job_id_request_id on user_tasks ut (cost=0.29..91.10 rows=1 width=8) (never executed)

  • Index Cond: (request_id = ber.id)
  • Filter: (user_id = 1232)
72. 130.624 130.624 ↑ 1.0 1 5,024

Index Scan using idx_line_item_id on invoice_line_item_amount_fields ilicd (cost=0.57..2.79 rows=1 width=56) (actual time=0.026..0.026 rows=1 loops=5,024)

  • Index Cond: (line_item_id = li.id)
73.          

SubPlan (forResult)

74. 0.000 0.000 ↓ 0.0 0

Seq Scan on blocked_entity (cost=0.00..17.36 rows=1 width=0) (never executed)

  • Filter: ((entity_id = li.id) AND (entity_type_id = 165))
75. 0.000 0.000 ↓ 0.0 0

Seq Scan on blocked_entity blocked_entity_1 (cost=0.00..16.14 rows=1 width=4) (never executed)

  • Filter: (entity_type_id = 165)
76. 0.000 0.000 ↓ 0.0 0

Seq Scan on blocked_entity blocked_entity_2 (cost=0.00..17.36 rows=1 width=0) (never executed)

  • Filter: ((entity_id = li.id) AND (entity_type_id = 165))
77. 16.796 16.796 ↓ 0.0 0 1

Seq Scan on blocked_entity blocked_entity_3 (cost=0.00..16.14 rows=1 width=4) (actual time=16.796..16.796 rows=0 loops=1)

  • Filter: (entity_type_id = 165)
  • Rows Removed by Filter: 491
78. 0.010 0.010 ↑ 1.0 1 1

Index Scan using pk_work_flow_status on work_flow_status (cost=0.28..2.50 rows=1 width=18) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (id = bi.status_id)
79. 0.005 0.005 ↑ 1.0 1 1

Index Scan using pk_work_flow_status on work_flow_status work_flow_status_1 (cost=0.28..2.50 rows=1 width=18) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (id = li.status_id)
80. 0.000 0.000 ↓ 0.0 0

Seq Scan on region (cost=0.00..1.90 rows=1 width=8) (never executed)

  • Filter: (id = li.attribute_value)
81. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_delivery_country on delivery_country (cost=0.15..2.37 rows=1 width=12) (never executed)

  • Index Cond: (id = li.attribute_value)
82. 0.000 0.000 ↓ 0.0 0

Seq Scan on department (cost=0.00..1.16 rows=1 width=9) (never executed)

  • Filter: (id = li.attribute_value)
83. 0.005 0.005 ↑ 1.0 1 1

Index Scan using pk_currency on currency (cost=0.14..2.36 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (id = li.system_currency_id)
84. 0.009 0.022 ↑ 1.0 1 1

Aggregate (cost=4.12..4.13 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=1)

85. 0.006 0.013 ↓ 0.0 0 1

Hash Right Join (cost=2.52..4.11 rows=1 width=10) (actual time=0.013..0.013 rows=0 loops=1)

  • Hash Cond: (ct.id = lef.function_id)
86. 0.000 0.000 ↓ 0.0 0

Seq Scan on contract_type ct (cost=0.00..1.42 rows=42 width=14) (never executed)

87. 0.000 0.007 ↓ 0.0 0 1

Hash (cost=2.51..2.51 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
88. 0.007 0.007 ↓ 0.0 0 1

Index Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.29..2.51 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((li.service_data_id = entity_id) AND (entity_type_id = 64))
89. 0.003 0.011 ↑ 1.0 1 1

Aggregate (cost=5.04..5.05 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1)

90. 0.000 0.008 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.56..5.04 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1)

91. 0.008 0.008 ↓ 0.0 0 1

Index Scan using idx_link_entity_service_6 on link_entity_service les (cost=0.42..2.64 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((li.service_data_id = entity_id) AND (entity_type_id = 64))
92. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_contract_sub_type on contract_sub_type cst (cost=0.14..2.36 rows=1 width=20) (never executed)

  • Index Cond: (les.service_id = id)
93. 0.006 0.006 ↑ 1.0 1 1

Index Scan using pk_unit_type on unit_type (cost=0.14..2.36 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = li.unit_type_id)
94. 0.001 0.001 ↓ 0.0 0 1

Index Scan using pk_unit_type on unit_type unit_type_1 (cost=0.14..2.36 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (id = li.system_unit_id)