explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2EQS

Settings
# exclusive inclusive rows x rows loops node
1. 16.890 90,734.217 ↓ 211.4 2,325 1

Sort (cost=19,398,247.09..19,398,247.12 rows=11 width=4,717) (actual time=90,733.675..90,734.217 rows=2,325 loops=1)

  • Sort Key: books.customer_id, books.book_id, one_time_charges.tax_jurisdiction, one_time_charges.tax_class, one_time_charges.charge_id, packages_1.tax_jurisdiction, packages_1.tax_class, packages_1.purchase_order, packages_1.package_id, services_2.service (...)
  • Sort Method: quicksort Memory: 2454kB
2. 8.776 90,717.327 ↓ 211.4 2,325 1

Merge Right Join (cost=19,390,931.84..19,398,246.90 rows=11 width=4,717) (actual time=90,688.253..90,717.327 rows=2,325 loops=1)

  • Merge Cond: (s_1.service_id = services_2.service_id)
3. 16.073 1,612.553 ↑ 4.1 71,002 1

Unique (cost=19,224,265.42..19,227,922.62 rows=292,576 width=151) (actual time=1,579.518..1,612.553 rows=71,002 loops=1)

4.          

CTE iptv_endpoint

5. 7.145 100.121 ↓ 1.6 33,377 1

Nested Loop (cost=1,323.51..9,021.49 rows=20,938 width=26) (actual time=17.191..100.121 rows=33,377 loops=1)

6. 2.609 72.231 ↑ 1.0 4,149 1

Hash Join (cost=1,323.09..6,438.47 rows=4,149 width=26) (actual time=17.177..72.231 rows=4,149 loops=1)

  • Hash Cond: (iptv_subscr.ep_service_id = endp.service_id)
7. 34.953 54.352 ↑ 1.0 4,571 1

Hash Join (cost=134.85..5,191.59 rows=4,571 width=17) (actual time=1.849..54.352 rows=4,571 loops=1)

  • Hash Cond: (service_w_endpoint.service_id = iptv_subscr.service_id)
8. 17.765 17.765 ↑ 1.0 146,791 1

Seq Scan on services service_w_endpoint (cost=0.00..4,460.39 rows=146,839 width=17) (actual time=0.007..17.765 rows=146,791 loops=1)

9. 0.847 1.634 ↑ 1.0 4,571 1

Hash (cost=77.71..77.71 rows=4,571 width=8) (actual time=1.634..1.634 rows=4,571 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 241kB
10. 0.787 0.787 ↑ 1.0 4,571 1

Seq Scan on iptv_subscribers iptv_subscr (cost=0.00..77.71 rows=4,571 width=8) (actual time=0.007..0.787 rows=4,571 loops=1)

11. 8.057 15.270 ↑ 1.0 38,322 1

Hash (cost=709.22..709.22 rows=38,322 width=17) (actual time=15.270..15.270 rows=38,322 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2352kB
12. 7.213 7.213 ↑ 1.0 38,322 1

Seq Scan on endpoints endp (cost=0.00..709.22 rows=38,322 width=17) (actual time=0.004..7.213 rows=38,322 loops=1)

13. 20.745 20.745 ↓ 2.0 8 4,149

Index Scan using services_package_id_idx on services service_endp_lookup (cost=0.42..0.58 rows=4 width=8) (actual time=0.003..0.005 rows=8 loops=4,149)

  • Index Cond: (package_id = service_w_endpoint.package_id)
14. 102.916 1,596.480 ↑ 4.1 71,011 1

Sort (cost=19,215,243.93..19,215,975.37 rows=292,576 width=151) (actual time=1,579.517..1,596.480 rows=71,011 loops=1)

  • Sort Key: s_1.service_id, (CASE WHEN (oc.address_id IS NOT NULL) THEN oc.address_id WHEN (e.address_id IS NOT NULL) THEN e.address_id WHEN (ga.address_id IS NOT NULL) THEN ga.address_id WHEN (f.address_id IS NOT NULL) THEN f.address_id (...)
  • Sort Method: external merge Disk: 2256kB
15. 142.398 1,493.564 ↑ 3.6 80,411 1

Hash Left Join (cost=4,823.23..19,166,679.29 rows=292,576 width=151) (actual time=181.118..1,493.564 rows=80,411 loops=1)

  • Hash Cond: (s_1.service_id = newest_iptv_endpoint.service_id)
  • Filter: ((oc.address_id IS NOT NULL) OR (e.address_id IS NOT NULL) OR (ga.address_id IS NOT NULL) OR (f.address_id IS NOT NULL) OR (newest_iptv_endpoint.address_id IS NOT NULL) OR (sta.address_id IS NOT NULL))
  • Rows Removed by Filter: 66390
16. 37.525 1,201.919 ↑ 2.0 146,801 1

Hash Left Join (cost=2,792.57..19,155,501.66 rows=292,576 width=111) (actual time=30.256..1,201.919 rows=146,801 loops=1)

  • Hash Cond: (s_1.service_id = sta.service_id)
17. 36.884 1,164.384 ↑ 2.0 146,801 1

Hash Left Join (cost=2,791.03..19,154,402.48 rows=292,576 width=43) (actual time=30.243..1,164.384 rows=146,801 loops=1)

  • Hash Cond: (s_1.service_id = cs.service_id)
18. 39.351 1,127.360 ↑ 2.0 146,791 1

Hash Left Join (cost=2,779.31..19,150,730.11 rows=292,576 width=39) (actual time=30.097..1,127.360 rows=146,791 loops=1)

  • Hash Cond: (p_2.package_id = ga.package_id)
19. 74.327 1,087.303 ↑ 2.0 146,791 1

Nested Loop Left Join (cost=2,738.52..19,149,592.10 rows=292,576 width=34) (actual time=29.379..1,087.303 rows=146,791 loops=1)

20. 159.189 572.603 ↑ 2.0 146,791 1

Nested Loop Left Join (cost=2,718.76..12,182,822.27 rows=292,576 width=25) (actual time=29.366..572.603 rows=146,791 loops=1)

21. 47.743 266.623 ↑ 1.0 146,791 1

Hash Left Join (cost=2,643.77..10,921.04 rows=146,839 width=29) (actual time=29.350..266.623 rows=146,791 loops=1)

  • Hash Cond: (s_1.service_id = wa.service_id)
22. 53.552 217.297 ↑ 1.0 146,791 1

Hash Left Join (cost=2,385.77..10,084.39 rows=146,839 width=25) (actual time=27.751..217.297 rows=146,791 loops=1)

  • Hash Cond: (s_1.service_id = cr.service_id)
23. 34.727 160.142 ↑ 1.0 146,791 1

Hash Left Join (cost=2,039.07..9,073.85 rows=146,839 width=21) (actual time=24.133..160.142 rows=146,791 loops=1)

  • Hash Cond: (s_1.service_id = oc.service_id)
24. 78.303 125.244 ↑ 1.0 146,791 1

Hash Join (cost=2,022.47..8,501.89 rows=146,839 width=8) (actual time=23.953..125.244 rows=146,791 loops=1)

  • Hash Cond: (s_1.package_id = p_2.package_id)
25. 23.063 23.063 ↑ 1.0 146,791 1

Seq Scan on services s_1 (cost=0.00..4,460.39 rows=146,839 width=8) (actual time=0.008..23.063 rows=146,791 loops=1)

26. 9.658 23.878 ↑ 1.0 49,043 1

Hash (cost=1,409.43..1,409.43 rows=49,043 width=4) (actual time=23.878..23.878 rows=49,043 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2237kB
27. 14.220 14.220 ↑ 1.0 49,043 1

Seq Scan on packages p_2 (cost=0.00..1,409.43 rows=49,043 width=4) (actual time=0.010..14.220 rows=49,043 loops=1)

28. 0.080 0.171 ↑ 1.0 471 1

Hash (cost=10.71..10.71 rows=471 width=17) (actual time=0.171..0.171 rows=471 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
29. 0.091 0.091 ↑ 1.0 471 1

Seq Scan on offnet_circuits oc (cost=0.00..10.71 rows=471 width=17) (actual time=0.004..0.091 rows=471 loops=1)

30. 1.770 3.603 ↑ 1.0 11,320 1

Hash (cost=205.20..205.20 rows=11,320 width=8) (actual time=3.603..3.603 rows=11,320 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 571kB
31. 1.833 1.833 ↑ 1.0 11,320 1

Seq Scan on cpe_routers cr (cost=0.00..205.20 rows=11,320 width=8) (actual time=0.003..1.833 rows=11,320 loops=1)

32. 0.581 1.583 ↑ 1.0 2,800 1

Hash (cost=223.00..223.00 rows=2,800 width=8) (actual time=1.583..1.583 rows=2,800 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 142kB
33. 1.002 1.002 ↑ 1.0 2,800 1

Seq Scan on wifi_aps wa (cost=0.00..223.00 rows=2,800 width=8) (actual time=0.003..1.002 rows=2,800 loops=1)

34. 0.000 146.791 ↓ 0.0 0 146,791

Bitmap Heap Scan on services s2 (cost=75.00..82.87 rows=2 width=4) (actual time=0.001..0.001 rows=0 loops=146,791)

  • Recheck Cond: ((service_id = cr.endpoint_service_id) OR (service_id = wa.endpoint_service_id))
  • Heap Blocks: exact=14098
35. 146.791 146.791 ↓ 0.0 0 146,791

BitmapOr (cost=75.00..75.00 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=146,791)

36. 0.000 0.000 ↓ 0.0 0 146,791

Bitmap Index Scan on services_pkey (cost=0.00..0.57 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=146,791)

  • Index Cond: (service_id = cr.endpoint_service_id)
37. 0.000 0.000 ↓ 0.0 0 146,791

Bitmap Index Scan on services_pkey (cost=0.00..1.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=146,791)

  • Index Cond: (service_id = wa.endpoint_service_id)
38. 146.791 440.373 ↓ 0.0 0 146,791

Bitmap Heap Scan on endpoints e (cost=19.76..23.79 rows=2 width=17) (actual time=0.003..0.003 rows=0 loops=146,791)

  • Recheck Cond: ((service_id = s_1.service_id) OR (service_id = s2.service_id))
  • Heap Blocks: exact=52420
39. 146.791 293.582 ↓ 0.0 0 146,791

BitmapOr (cost=19.76..19.76 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=146,791)

40. 146.791 146.791 ↓ 0.0 0 146,791

Bitmap Index Scan on endpoints_pkey (cost=0.00..0.30 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=146,791)

  • Index Cond: (service_id = s_1.service_id)
41. 0.000 0.000 ↓ 0.0 0 146,791

Bitmap Index Scan on endpoints_pkey (cost=0.00..0.30 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=146,791)

  • Index Cond: (service_id = s2.service_id)
42. 0.001 0.706 ↑ 1.0 1 1

Hash (cost=40.78..40.78 rows=1 width=13) (actual time=0.706..0.706 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.002 0.705 ↑ 1.0 1 1

Subquery Scan on ga (cost=40.77..40.78 rows=1 width=13) (actual time=0.704..0.705 rows=1 loops=1)

44. 0.001 0.703 ↑ 1.0 1 1

Limit (cost=40.77..40.77 rows=1 width=30) (actual time=0.702..0.703 rows=1 loops=1)

45. 0.416 0.702 ↑ 1,651.0 1 1

Sort (cost=40.77..44.89 rows=1,651 width=30) (actual time=0.702..0.702 rows=1 loops=1)

  • Sort Key: ga_1.group_id
  • Sort Method: top-N heapsort Memory: 25kB
46. 0.286 0.286 ↓ 1.0 1,652 1

Seq Scan on group_addresses ga_1 (cost=0.00..32.51 rows=1,651 width=30) (actual time=0.004..0.286 rows=1,652 loops=1)

47. 0.032 0.140 ↑ 1.4 173 1

Hash (cost=8.74..8.74 rows=238 width=8) (actual time=0.140..0.140 rows=173 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
48. 0.081 0.108 ↑ 1.0 238 1

Hash Left Join (cost=1.09..8.74 rows=238 width=8) (actual time=0.014..0.108 rows=238 loops=1)

  • Hash Cond: (cs.facility_id = f.facility_id)
49. 0.024 0.024 ↑ 1.0 238 1

Seq Scan on cabinet_spaces cs (cost=0.00..4.38 rows=238 width=8) (actual time=0.004..0.024 rows=238 loops=1)

50. 0.001 0.003 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=8) (actual time=0.003..0.003 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.002 0.002 ↑ 1.0 4 1

Seq Scan on facilities f (cost=0.00..1.04 rows=4 width=8) (actual time=0.002..0.002 rows=4 loops=1)

52. 0.004 0.010 ↑ 1.0 24 1

Hash (cost=1.24..1.24 rows=24 width=72) (actual time=0.010..0.010 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
53. 0.006 0.006 ↑ 1.0 24 1

Seq Scan on sip_trunking_addresses sta (cost=0.00..1.24 rows=24 width=72) (actual time=0.002..0.006 rows=24 loops=1)

54. 7.236 149.247 ↓ 153.9 30,771 1

Hash (cost=2,028.15..2,028.15 rows=200 width=44) (actual time=149.247..149.247 rows=30,771 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1774kB
55. 5.431 142.011 ↓ 153.9 30,771 1

Subquery Scan on newest_iptv_endpoint (cost=1,921.46..2,028.15 rows=200 width=44) (actual time=125.829..142.011 rows=30,771 loops=1)

56. 5.997 136.580 ↓ 153.9 30,771 1

Unique (cost=1,921.46..2,026.15 rows=200 width=44) (actual time=125.828..136.580 rows=30,771 loops=1)

57. 15.573 130.583 ↓ 1.6 33,377 1

Sort (cost=1,921.46..1,973.81 rows=20,938 width=44) (actual time=125.826..130.583 rows=33,377 loops=1)

  • Sort Key: iptv_endpoint.service_id DESC
  • Sort Method: quicksort Memory: 3216kB
58. 115.010 115.010 ↓ 1.6 33,377 1

CTE Scan on iptv_endpoint (cost=0.00..418.76 rows=20,938 width=44) (actual time=17.195..115.010 rows=33,377 loops=1)

59. 16.470 89,095.998 ↓ 581.2 2,325 1

Sort (cost=166,666.43..166,666.44 rows=4 width=4,645) (actual time=89,095.405..89,095.998 rows=2,325 loops=1)

  • Sort Key: services_2.service_id
  • Sort Method: quicksort Memory: 2454kB
60. 5.135 89,079.528 ↓ 581.2 2,325 1

Hash Left Join (cost=19,021.57..166,666.39 rows=4 width=4,645) (actual time=9,094.990..89,079.528 rows=2,325 loops=1)

  • Hash Cond: (customers.company_id = companies_1.company_id)
61. 4.279 89,074.384 ↓ 581.2 2,325 1

Nested Loop Left Join (cost=19,020.43..166,665.20 rows=4 width=4,036) (actual time=9,094.963..89,074.384 rows=2,325 loops=1)

62. 3.509 89,063.130 ↓ 581.2 2,325 1

Nested Loop Left Join (cost=19,020.29..166,664.52 rows=4 width=1,935) (actual time=9,094.957..89,063.130 rows=2,325 loops=1)

63. 5.021 88,994.521 ↓ 581.2 2,325 1

Nested Loop Left Join (cost=19,018.72..166,656.43 rows=4 width=1,899) (actual time=9,094.921..88,994.521 rows=2,325 loops=1)

64. 5.297 88,952.300 ↓ 581.2 2,325 1

Nested Loop Left Join (cost=19,018.14..166,624.26 rows=4 width=1,863) (actual time=9,094.894..88,952.300 rows=2,325 loops=1)

65. 1,381.973 88,942.353 ↓ 581.2 2,325 1

Nested Loop Left Join (cost=19,018.00..166,623.61 rows=4 width=1,731) (actual time=9,094.891..88,942.353 rows=2,325 loops=1)

  • Join Filter: (line_items.package_id = packages_1.package_id)
  • Rows Removed by Join Filter: 10997297
66. 1.418 84,887.378 ↓ 83.5 334 1

Nested Loop Left Join (cost=12,147.41..104,627.27 rows=4 width=1,632) (actual time=8,818.264..84,887.378 rows=334 loops=1)

67. 679.054 84,841.872 ↓ 83.5 334 1

Nested Loop Left Join (cost=12,146.15..104,609.89 rows=4 width=1,511) (actual time=8,817.970..84,841.872 rows=334 loops=1)

  • Join Filter: (packages_1.package_id = services_2.package_id)
  • Rows Removed by Join Filter: 2477322
68. 0.007 452.282 ↓ 4.0 4 1

Nested Loop Left Join (cost=16.71..5,222.58 rows=1 width=1,147) (actual time=73.441..452.282 rows=4 loops=1)

  • Join Filter: (packages_1.package_id = package_charges.package_id)
69. 0.021 452.263 ↓ 4.0 4 1

Nested Loop Left Join (cost=16.42..5,217.84 rows=1 width=1,084) (actual time=73.435..452.263 rows=4 loops=1)

70. 7.276 452.170 ↓ 4.0 4 1

Nested Loop Left Join (cost=16.14..5,217.50 rows=1 width=960) (actual time=73.383..452.170 rows=4 loops=1)

  • Join Filter: (books.book_id = packages_1.book_id)
  • Rows Removed by Join Filter: 36214
71. 2.459 4.140 ↓ 2.0 2 1

Hash Join (cost=16.14..485.13 rows=1 width=681) (actual time=1.694..4.140 rows=2 loops=1)

  • Hash Cond: (books.customer_id = customers.customer_id)
72. 1.655 1.655 ↑ 1.0 20,071 1

Seq Scan on books (cost=0.00..393.71 rows=20,071 width=310) (actual time=0.007..1.655 rows=20,071 loops=1)

73. 0.006 0.026 ↓ 2.0 2 1

Hash (cost=16.13..16.13 rows=1 width=371) (actual time=0.026..0.026 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
74. 0.020 0.020 ↓ 2.0 2 1

Index Scan using customers_pkey on customers (cost=0.29..16.13 rows=1 width=371) (actual time=0.016..0.020 rows=2 loops=1)

  • Index Cond: (customer_id = ANY ('{1023505,1023811}'::integer[]))
  • Filter: (((customer_type)::text = 'Customer'::text) AND ((billing_cycle_id)::text = 'Billing Cycle 1'::text))
75. 440.754 440.754 ↓ 393.7 18,109 2

Seq Scan on packages packages_1 (cost=0.00..4,731.33 rows=46 width=518) (actual time=0.046..220.377 rows=18,109 loops=2)

  • Filter: ((activation_date IS NOT NULL) AND (activation_date < ('2019-03-01'::date + ((((frequency)::character varying)::text || 'month'::text))::interval)) AND ((deactiva (...)
  • Rows Removed by Filter: 30934
76. 0.072 0.072 ↓ 0.0 0 4

Index Scan using one_time_charges_book_id_idx on one_time_charges (cost=0.28..0.34 rows=1 width=124) (actual time=0.018..0.018 rows=0 loops=4)

  • Index Cond: (books.book_id = book_id)
  • Filter: (charge_id IS NULL)
  • Rows Removed by Filter: 0
77. 0.012 0.012 ↓ 0.0 0 4

Index Scan using package_charges_pkey on package_charges (cost=0.29..4.73 rows=1 width=63) (actual time=0.003..0.003 rows=0 loops=4)

  • Index Cond: (charge_id IS NULL)
78. 82,087.784 83,710.536 ↓ 5.8 619,414 4

Hash Join (cost=12,129.43..96,979.88 rows=106,997 width=624) (actual time=224.397..20,927.634 rows=619,414 loops=4)

  • Hash Cond: (sub_line_items_2.service_id = services_2.service_id)
79. 725.852 725.852 ↑ 1.0 774,980 4

Seq Scan on sub_line_items sub_line_items_2 (cost=0.00..19,696.80 rows=774,980 width=4) (actual time=0.004..181.463 rows=774,980 loops=4)

80. 223.496 896.900 ↓ 2.9 58,902 4

Hash (cost=10,250.01..10,250.01 rows=20,514 width=624) (actual time=224.225..224.225 rows=58,902 loops=4)

  • Buckets: 8192 (originally 8192) Batches: 8 (originally 4) Memory Usage: 4033kB
81. 92.752 673.404 ↓ 2.9 58,902 4

Hash Join (cost=1.09..10,250.01 rows=20,514 width=624) (actual time=0.020..168.351 rows=58,902 loops=4)

  • Hash Cond: ((services_2.status)::text = (service_statuses.status)::text)
82. 580.648 580.648 ↓ 2.9 58,902 4

Seq Scan on services services_2 (cost=0.00..9,966.85 rows=20,514 width=624) (actual time=0.015..145.162 rows=58,902 loops=4)

  • Filter: ((activation_date IS NOT NULL) AND ((status)::text = ANY ('{"In Service",De-activated}'::text[])) AND (activation_date < ('2019-03-01'::date + ((((frequency)::cha (...)
  • Rows Removed by Filter: 87889
83. 0.002 0.004 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=68) (actual time=0.004..0.004 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
84. 0.002 0.002 ↑ 1.0 4 1

Seq Scan on service_statuses (cost=0.00..1.04 rows=4 width=68) (actual time=0.001..0.002 rows=4 loops=1)

85. 1.336 44.088 ↑ 1.0 1 334

Nested Loop (cost=1.27..4.33 rows=1 width=121) (actual time=0.127..0.132 rows=1 loops=334)

  • Join Filter: (services_1.package_id = line_items_1.package_id)
86. 5.702 39.746 ↑ 1.0 1 334

Nested Loop (cost=0.84..3.82 rows=1 width=125) (actual time=0.117..0.119 rows=1 loops=334)

87. 13.360 13.360 ↓ 3.0 15 334

Index Scan using subline_items_service_id_idx on sub_line_items sub_line_items_1 (cost=0.42..1.23 rows=5 width=121) (actual time=0.016..0.040 rows=15 loops=334)

  • Index Cond: (service_id = services_2.service_id)
  • Filter: (period_start_date >= billing_period_start_date)
  • Rows Removed by Filter: 0
88. 20.684 20.684 ↓ 0.0 0 5,171

Index Scan using services_pkey on services services_1 (cost=0.42..0.51 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=5,171)

  • Index Cond: (service_id = sub_line_items_1.service_id)
  • Filter: (sub_line_items_1.billing_period_start_date = ('2019-03-01'::date - ((((frequency)::character varying)::text || 'month'::text))::interval))
  • Rows Removed by Filter: 1
89. 3.006 3.006 ↑ 1.0 1 334

Index Scan using line_items_pkey on line_items line_items_1 (cost=0.42..0.50 rows=1 width=8) (actual time=0.007..0.009 rows=1 loops=334)

  • Index Cond: (line_item_id = sub_line_items_1.line_item_id)
90. 1,437.297 2,673.002 ↓ 8.7 32,933 334

Materialize (cost=6,870.59..61,777.84 rows=3,800 width=99) (actual time=0.587..8.003 rows=32,933 loops=334)

91. 11.797 1,235.705 ↓ 8.7 32,933 1

Nested Loop (cost=6,870.59..61,758.84 rows=3,800 width=99) (actual time=193.450..1,235.705 rows=32,933 loops=1)

92. 17.921 1,158.042 ↓ 8.6 32,933 1

Nested Loop (cost=6,870.30..60,481.88 rows=3,829 width=99) (actual time=193.437..1,158.042 rows=32,933 loops=1)

93. 790.205 1,074.255 ↓ 8.6 32,933 1

Hash Join (cost=6,869.88..58,678.75 rows=3,829 width=4) (actual time=193.417..1,074.255 rows=32,933 loops=1)

  • Hash Cond: (sub_line_items.service_id = services.service_id)
  • Join Filter: (sub_line_items.billing_period_start_date = ('2019-03-01'::date - ((((services.frequency)::character varying)::text || 'month'::text))::interval))
  • Rows Removed by Join Filter: 732242
94. 206.230 206.230 ↑ 1.0 774,980 1

Seq Scan on sub_line_items (cost=0.00..19,696.80 rows=774,980 width=12) (actual time=0.003..206.230 rows=774,980 loops=1)

95. 30.570 77.820 ↑ 1.0 146,791 1

Hash (cost=4,460.39..4,460.39 rows=146,839 width=8) (actual time=77.820..77.820 rows=146,791 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3900kB
96. 47.250 47.250 ↑ 1.0 146,791 1

Seq Scan on services (cost=0.00..4,460.39 rows=146,839 width=8) (actual time=0.003..47.250 rows=146,791 loops=1)

97. 65.866 65.866 ↑ 1.0 1 32,933

Index Scan using line_items_pkey on line_items (cost=0.42..0.46 rows=1 width=99) (actual time=0.002..0.002 rows=1 loops=32,933)

  • Index Cond: (line_item_id = sub_line_items.line_item_id)
98. 65.866 65.866 ↑ 1.0 1 32,933

Index Only Scan using packages_pkey on packages (cost=0.29..0.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=32,933)

  • Index Cond: (package_id = line_items.package_id)
  • Heap Fetches: 32933
99. 4.650 4.650 ↓ 0.0 0 2,325

Index Scan using unit_types_pkey on unit_types (cost=0.14..0.15 rows=1 width=132) (actual time=0.002..0.002 rows=0 loops=2,325)

  • Index Cond: ((unit_type_id)::text = (services_2.unit_type_id)::text)
100. 16.275 37.200 ↑ 1.0 1 2,325

Nested Loop (cost=0.58..8.03 rows=1 width=36) (actual time=0.015..0.016 rows=1 loops=2,325)

101. 11.625 11.625 ↑ 1.0 1 2,325

Index Scan using packages_pkey on packages p_1 (cost=0.29..7.70 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=2,325)

  • Index Cond: (packages_1.package_id = package_id)
102. 9.300 9.300 ↑ 1.0 1 2,325

Index Scan using books_pkey on books b_1 (cost=0.29..0.33 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=2,325)

  • Index Cond: (book_id = p_1.book_id)
103. 11.625 65.100 ↑ 1.0 1 2,325

Nested Loop Left Join (cost=1.58..2.01 rows=1 width=36) (actual time=0.025..0.028 rows=1 loops=2,325)

104. 4.650 44.175 ↑ 1.0 1 2,325

Nested Loop Left Join (cost=1.29..1.62 rows=1 width=25) (actual time=0.017..0.019 rows=1 loops=2,325)

105. 2.325 30.225 ↑ 1.0 1 2,325

Nested Loop Left Join (cost=1.00..1.25 rows=1 width=25) (actual time=0.012..0.013 rows=1 loops=2,325)

106. 2.325 20.925 ↑ 1.0 1 2,325

Nested Loop (cost=0.71..0.91 rows=1 width=25) (actual time=0.009..0.009 rows=1 loops=2,325)

107. 11.625 11.625 ↑ 1.0 1 2,325

Index Scan using services_pkey on services s (cost=0.42..0.56 rows=1 width=21) (actual time=0.004..0.005 rows=1 loops=2,325)

  • Index Cond: (services_2.service_id = service_id)
108. 6.975 6.975 ↑ 1.0 1 2,325

Index Scan using packages_pkey on packages p (cost=0.29..0.34 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=2,325)

  • Index Cond: (package_id = s.package_id)
109. 6.975 6.975 ↑ 1.0 1 2,325

Index Scan using books_pkey on books b (cost=0.29..0.33 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=2,325)

  • Index Cond: (p.book_id = book_id)
110. 9.300 9.300 ↑ 1.0 1 2,325

Index Scan using opportunity_items_pkey on opportunity_items oi (cost=0.29..0.37 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=2,325)

  • Index Cond: (opportunity_item_id = p.opportunity_item_id)
111. 9.300 9.300 ↑ 1.0 1 2,325

Index Scan using opportunities_pkey on opportunities o (cost=0.29..0.38 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=2,325)

  • Index Cond: (opportunity_id = oi.opportunity_id)
112. 6.975 6.975 ↑ 1.0 1 2,325

Index Scan using systems_pkey on systems (cost=0.14..0.16 rows=1 width=2,101) (actual time=0.002..0.003 rows=1 loops=2,325)

  • Index Cond: (system_id = services_2.system_id)
113. 0.007 0.009 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=609) (actual time=0.009..0.009 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
114. 0.002 0.002 ↑ 1.0 6 1

Seq Scan on companies companies_1 (cost=0.00..1.06 rows=6 width=609) (actual time=0.002..0.002 rows=6 loops=1)

Planning time : 15.658 ms
Execution time : 90,737.504 ms