explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y40

Settings
# exclusive inclusive rows x rows loops node
1. 43,959.932 3,785,174.204 ↓ 24.6 2,082,064 1

Sort (cost=19,857,109.06..19,857,320.50 rows=84,576 width=4,717) (actual time=3,776,240.143..3,785,174.204 rows=2,082,064 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: external merge Disk: 1760952kB
2. 1,800.487 3,741,214.272 ↓ 24.6 2,082,064 1

Hash Left Join (cost=19,478,420.26..19,507,334.38 rows=84,576 width=4,717) (actual time=3,732,467.160..3,741,214.272 rows=2,082,064 loops=1)

  • Hash Cond: (customers.company_id = companies_1.company_id)
3. 1,675.329 3,739,413.772 ↓ 24.6 2,082,064 1

Hash Left Join (cost=19,478,419.12..19,502,364.40 rows=84,576 width=4,108) (actual time=3,732,467.139..3,739,413.772 rows=2,082,064 loops=1)

  • Hash Cond: (services_2.system_id = systems.system_id)
4. 5,229.522 3,737,738.409 ↓ 24.6 2,082,064 1

Hash Right Join (cost=19,478,417.16..19,501,199.52 rows=84,576 width=2,007) (actual time=3,732,467.095..3,737,738.409 rows=2,082,064 loops=1)

  • Hash Cond: (s_1.service_id = services_2.service_id)
5. 20.224 1,472.189 ↑ 3.6 80,401 1

Unique (cost=19,214,454.55..19,218,110.55 rows=292,480 width=151) (actual time=1,429.843..1,472.189 rows=80,401 loops=1)

6.          

CTE iptv_endpoint

7. 7.313 87.696 ↓ 1.6 33,377 1

Nested Loop (cost=1,323.51..9,020.95 rows=20,937 width=26) (actual time=17.271..87.696 rows=33,377 loops=1)

8. 2.500 63.787 ↑ 1.0 4,149 1

Hash Join (cost=1,323.09..6,437.81 rows=4,149 width=26) (actual time=17.248..63.787 rows=4,149 loops=1)

  • Hash Cond: (iptv_subscr.ep_service_id = endp.service_id)
9. 29.879 46.082 ↑ 1.0 4,571 1

Hash Join (cost=134.85..5,190.93 rows=4,571 width=17) (actual time=1.812..46.082 rows=4,571 loops=1)

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

Seq Scan on services service_w_endpoint (cost=0.00..4,459.91 rows=146,791 width=17) (actual time=0.003..14.613 rows=146,791 loops=1)

11. 0.798 1.590 ↑ 1.0 4,571 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 241kB
12. 0.792 0.792 ↑ 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.009..0.792 rows=4,571 loops=1)

13. 8.426 15.205 ↑ 1.0 38,322 1

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

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

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

15. 16.596 16.596 ↓ 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.004 rows=8 loops=4,149)

  • Index Cond: (package_id = service_w_endpoint.package_id)
16. 103.587 1,451.965 ↑ 3.6 80,411 1

Sort (cost=19,205,433.60..19,206,164.80 rows=292,480 width=151) (actual time=1,429.841..1,451.965 rows=80,411 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 (...)
  • Sort Method: external merge Disk: 2256kB
17. 131.023 1,348.378 ↑ 3.6 80,411 1

Hash Left Join (cost=4,823.10..19,156,885.37 rows=292,480 width=151) (actual time=159.734..1,348.378 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
18. 33.596 1,088.536 ↑ 2.0 146,801 1

Hash Left Join (cost=2,792.55..19,145,710.84 rows=292,480 width=111) (actual time=28.982..1,088.536 rows=146,801 loops=1)

  • Hash Cond: (s_1.service_id = sta.service_id)
19. 34.499 1,054.922 ↑ 2.0 146,801 1

Hash Left Join (cost=2,791.01..19,144,612.02 rows=292,480 width=43) (actual time=28.948..1,054.922 rows=146,801 loops=1)

  • Hash Cond: (s_1.service_id = cs.service_id)
20. 34.992 1,020.284 ↑ 2.0 146,791 1

Hash Left Join (cost=2,779.29..19,140,940.86 rows=292,480 width=39) (actual time=28.804..1,020.284 rows=146,791 loops=1)

  • Hash Cond: (p_2.package_id = ga.package_id)
21. 167.369 984.631 ↑ 2.0 146,791 1

Nested Loop Left Join (cost=2,738.50..19,139,803.21 rows=292,480 width=34) (actual time=28.136..984.631 rows=146,791 loops=1)

22. 136.510 523.680 ↑ 2.0 146,791 1

Nested Loop Left Join (cost=2,718.74..12,175,318.75 rows=292,480 width=25) (actual time=28.123..523.680 rows=146,791 loops=1)

23. 41.642 240.379 ↑ 1.0 146,791 1

Hash Left Join (cost=2,643.77..10,919.36 rows=146,791 width=29) (actual time=28.105..240.379 rows=146,791 loops=1)

  • Hash Cond: (s_1.service_id = wa.service_id)
24. 49.230 196.986 ↑ 1.0 146,791 1

Hash Left Join (cost=2,385.77..10,082.89 rows=146,791 width=25) (actual time=26.331..196.986 rows=146,791 loops=1)

  • Hash Cond: (s_1.service_id = cr.service_id)
25. 31.520 143.916 ↑ 1.0 146,791 1

Hash Left Join (cost=2,039.07..9,072.53 rows=146,791 width=21) (actual time=22.425..143.916 rows=146,791 loops=1)

  • Hash Cond: (s_1.service_id = oc.service_id)
26. 70.504 112.118 ↑ 1.0 146,791 1

Hash Join (cost=2,022.47..8,500.75 rows=146,791 width=8) (actual time=22.135..112.118 rows=146,791 loops=1)

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

Seq Scan on services s_1 (cost=0.00..4,459.91 rows=146,791 width=8) (actual time=0.015..19.754 rows=146,791 loops=1)

28. 9.583 21.860 ↑ 1.0 49,043 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2237kB
29. 12.277 12.277 ↑ 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.006..12.277 rows=49,043 loops=1)

30. 0.120 0.278 ↑ 1.0 471 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
31. 0.158 0.158 ↑ 1.0 471 1

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

32. 1.839 3.840 ↑ 1.0 11,320 1

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

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

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

34. 0.464 1.751 ↑ 1.0 2,800 1

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

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

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

36. 0.000 146.791 ↓ 0.0 0 146,791

Bitmap Heap Scan on services s2 (cost=74.97..82.85 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
37. 146.791 146.791 ↓ 0.0 0 146,791

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

38. 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)
39. 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)
40. 0.000 293.582 ↓ 0.0 0 146,791

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

  • Recheck Cond: ((service_id = s_1.service_id) OR (service_id = s2.service_id))
  • Heap Blocks: exact=52420
41. 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)

42. 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)
43. 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)
44. 0.006 0.661 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.002 0.655 ↑ 1.0 1 1

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

46. 0.000 0.653 ↑ 1.0 1 1

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

47. 0.367 0.653 ↑ 1,651.0 1 1

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

  • Sort Key: ga_1.group_id
  • Sort Method: top-N heapsort Memory: 25kB
48. 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.008..0.286 rows=1,652 loops=1)

49. 0.035 0.139 ↑ 1.4 173 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
50. 0.062 0.104 ↑ 1.0 238 1

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

  • Hash Cond: (cs.facility_id = f.facility_id)
51. 0.032 0.032 ↑ 1.0 238 1

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

52. 0.005 0.010 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
53. 0.005 0.005 ↑ 1.0 4 1

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

54. 0.009 0.018 ↑ 1.0 24 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
55. 0.009 0.009 ↑ 1.0 24 1

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

56. 5.645 128.819 ↓ 153.9 30,771 1

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

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1774kB
57. 3.818 123.174 ↓ 153.9 30,771 1

Subquery Scan on newest_iptv_endpoint (cost=1,921.36..2,028.05 rows=200 width=44) (actual time=111.611..123.174 rows=30,771 loops=1)

58. 4.292 119.356 ↓ 153.9 30,771 1

Unique (cost=1,921.36..2,026.05 rows=200 width=44) (actual time=111.609..119.356 rows=30,771 loops=1)

59. 15.028 115.064 ↓ 1.6 33,377 1

Sort (cost=1,921.36..1,973.71 rows=20,937 width=44) (actual time=111.608..115.064 rows=33,377 loops=1)

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

CTE Scan on iptv_endpoint (cost=0.00..418.74 rows=20,937 width=44) (actual time=17.277..100.036 rows=33,377 loops=1)

61. 6,650.163 3,731,036.698 ↓ 67.3 2,082,064 1

Hash (cost=256,172.85..256,172.85 rows=30,940 width=1,935) (actual time=3,731,036.698..3,731,036.698 rows=2,082,064 loops=1)

  • Buckets: 4096 (originally 4096) Batches: 32 (originally 16) Memory Usage: 51716kB
62. 1,817.320 3,724,386.535 ↓ 67.3 2,082,064 1

Hash Left Join (cost=195,343.72..256,172.85 rows=30,940 width=1,935) (actual time=25,655.811..3,724,386.535 rows=2,082,064 loops=1)

  • Hash Cond: (packages_1.package_id = p_1.package_id)
63. 12,840.885 3,722,487.748 ↓ 67.3 2,082,064 1

Hash Left Join (cost=192,197.11..252,655.45 rows=30,940 width=1,899) (actual time=25,574.292..3,722,487.748 rows=2,082,064 loops=1)

  • Hash Cond: (services_2.service_id = s.service_id)
64. 2,601.581 3,709,260.191 ↓ 67.3 2,082,064 1

Hash Left Join (cost=174,683.33..219,307.24 rows=30,940 width=1,863) (actual time=25,187.577..3,709,260.191 rows=2,082,064 loops=1)

  • Hash Cond: ((services_2.unit_type_id)::text = (unit_types.unit_type_id)::text)
65. 2,885.584 3,706,658.593 ↓ 67.3 2,082,064 1

Hash Left Join (cost=174,682.08..219,120.24 rows=30,940 width=1,731) (actual time=25,187.544..3,706,658.593 rows=2,082,064 loops=1)

  • Hash Cond: (packages_1.package_id = line_items.package_id)
66. 1,816,709.224 3,702,425.969 ↓ 15.1 466,935 1

Nested Loop Left Join (cost=112,876.83..156,984.02 rows=30,940 width=1,632) (actual time=23,840.486..3,702,425.969 rows=466,935 loops=1)

  • Join Filter: (sub_line_items_1.service_id = services_2.service_id)
  • Rows Removed by Join Filter: 14444364047
67. 2,270.812 24,776.443 ↓ 15.1 466,518 1

Hash Left Join (cost=105,864.61..108,922.05 rows=30,940 width=1,511) (actual time=22,469.578..24,776.443 rows=466,518 loops=1)

  • Hash Cond: (books.book_id = packages_1.book_id)
68. 6.760 85.714 ↓ 1.8 13,777 1

Hash Left Join (cost=2,674.56..5,597.09 rows=7,676 width=805) (actual time=48.231..85.714 rows=13,777 loops=1)

  • Hash Cond: (books.book_id = one_time_charges.book_id)
69. 24.889 78.943 ↓ 1.8 13,777 1

Hash Join (cost=2,670.03..5,563.76 rows=7,676 width=681) (actual time=48.210..78.943 rows=13,777 loops=1)

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

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

71. 14.087 48.120 ↑ 1.0 13,702 1

Hash (cost=1,790.83..1,790.83 rows=14,336 width=371) (actual time=48.120..48.120 rows=13,702 loops=1)

  • Buckets: 16384 Batches: 2 Memory Usage: 1752kB
72. 34.033 34.033 ↑ 1.0 13,702 1

Seq Scan on customers (cost=0.00..1,790.83 rows=14,336 width=371) (actual time=0.046..34.033 rows=13,702 loops=1)

  • Filter: (((customer_type)::text = 'Customer'::text) AND ((billing_cycle_id)::text = 'Billing Cycle 1'::text) AND (customer_id <> ALL ('{1000000,1002558,1008178}'::integer (...)
  • Rows Removed by Filter: 23782
73. 0.001 0.011 ↓ 0.0 0 1

Hash (cost=4.52..4.52 rows=1 width=124) (actual time=0.011..0.011 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
74. 0.010 0.010 ↓ 0.0 0 1

Index Scan using one_time_charges_pkey on one_time_charges (cost=0.28..4.52 rows=1 width=124) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (charge_id IS NULL)
75. 1,049.290 22,419.917 ↓ 3,357.5 621,130 1

Hash (cost=103,187.74..103,187.74 rows=185 width=706) (actual time=22,419.917..22,419.917 rows=621,130 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 64 (originally 1) Memory Usage: 3969kB
76. 803.908 21,370.627 ↓ 3,357.5 621,130 1

Hash Right Join (cost=16,864.76..103,187.74 rows=185 width=706) (actual time=592.790..21,370.627 rows=621,130 loops=1)

  • Hash Cond: (services_2.package_id = packages_1.package_id)
77. 19,782.522 20,287.109 ↓ 5.8 619,414 1

Hash Join (cost=12,126.97..96,976.91 rows=106,996 width=624) (actual time=313.157..20,287.109 rows=619,414 loops=1)

  • Hash Cond: (sub_line_items_2.service_id = services_2.service_id)
78. 191.782 191.782 ↑ 1.0 774,980 1

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

79. 78.454 312.805 ↓ 2.9 58,902 1

Hash (cost=10,247.63..10,247.63 rows=20,507 width=624) (actual time=312.805..312.805 rows=58,902 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 8 (originally 4) Memory Usage: 4033kB
80. 31.403 234.351 ↓ 2.9 58,902 1

Hash Join (cost=1.09..10,247.63 rows=20,507 width=624) (actual time=0.066..234.351 rows=58,902 loops=1)

  • Hash Cond: ((services_2.status)::text = (service_statuses.status)::text)
81. 202.937 202.937 ↓ 2.9 58,902 1

Seq Scan on services services_2 (cost=0.00..9,964.57 rows=20,507 width=624) (actual time=0.042..202.937 rows=58,902 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
83. 0.007 0.007 ↑ 1.0 4 1

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

84. 14.036 279.610 ↓ 393.7 18,109 1

Hash (cost=4,737.21..4,737.21 rows=46 width=342) (actual time=279.610..279.610 rows=18,109 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3068kB
85. 9.944 265.574 ↓ 393.7 18,109 1

Nested Loop Left Join (cost=0.29..4,737.21 rows=46 width=342) (actual time=0.099..265.574 rows=18,109 loops=1)

  • Join Filter: (packages_1.package_id = package_charges.package_id)
86. 255.630 255.630 ↓ 393.7 18,109 1

Seq Scan on packages packages_1 (cost=0.00..4,731.33 rows=46 width=518) (actual time=0.090..255.630 rows=18,109 loops=1)

  • Filter: ((activation_date IS NOT NULL) AND (activation_date < ('2019-03-01'::date + ((((frequency)::character varying)::text || 'month'::text))::interval)) AND ((de (...)
  • Rows Removed by Filter: 30934
87. 0.000 0.000 ↓ 0.0 0 18,109

Materialize (cost=0.29..4.73 rows=1 width=63) (actual time=0.000..0.000 rows=0 loops=18,109)

88. 0.004 0.004 ↓ 0.0 0 1

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

  • Index Cond: (charge_id IS NULL)
89. 1,859,588.969 1,860,940.302 ↓ 30,963.0 30,963 466,518

Materialize (cost=7,012.22..47,597.87 rows=1 width=121) (actual time=0.004..3.989 rows=30,963 loops=466,518)

90. 12.332 1,351.333 ↓ 30,963.0 30,963 1

Nested Loop (cost=7,012.22..47,597.86 rows=1 width=121) (actual time=175.284..1,351.333 rows=30,963 loops=1)

  • Join Filter: (services_1.package_id = line_items_1.package_id)
  • Rows Removed by Join Filter: 5
91. 964.489 1,277.065 ↓ 24.3 30,968 1

Hash Join (cost=7,011.80..46,940.41 rows=1,276 width=125) (actual time=175.264..1,277.065 rows=30,968 loops=1)

  • Hash Cond: (sub_line_items_1.service_id = services_1.service_id)
  • Join Filter: (sub_line_items_1.billing_period_start_date = ('2019-03-01'::date - ((((services_1.frequency)::character varying)::text || 'month'::text))::interval))
  • Rows Removed by Join Filter: 686504
92. 229.369 229.369 ↓ 2.8 717,472 1

Seq Scan on sub_line_items sub_line_items_1 (cost=0.00..21,634.25 rows=258,327 width=121) (actual time=0.007..229.369 rows=717,472 loops=1)

  • Filter: (period_start_date >= billing_period_start_date)
  • Rows Removed by Filter: 57508
93. 32.612 83.207 ↑ 1.0 146,791 1

Hash (cost=4,459.91..4,459.91 rows=146,791 width=12) (actual time=83.207..83.207 rows=146,791 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2610kB
94. 50.595 50.595 ↑ 1.0 146,791 1

Seq Scan on services services_1 (cost=0.00..4,459.91 rows=146,791 width=12) (actual time=0.006..50.595 rows=146,791 loops=1)

95. 61.936 61.936 ↑ 1.0 1 30,968

Index Scan using line_items_pkey on line_items line_items_1 (cost=0.42..0.50 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=30,968)

  • Index Cond: (line_item_id = sub_line_items_1.line_item_id)
96. 12.442 1,347.040 ↓ 8.7 32,933 1

Hash (cost=61,757.76..61,757.76 rows=3,800 width=99) (actual time=1,347.040..1,347.040 rows=32,933 loops=1)

  • Buckets: 65536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3798kB
97. 13.330 1,334.598 ↓ 8.7 32,933 1

Nested Loop (cost=6,869.51..61,757.76 rows=3,800 width=99) (actual time=243.445..1,334.598 rows=32,933 loops=1)

98. 16.435 1,255.402 ↓ 8.6 32,933 1

Nested Loop (cost=6,869.22..60,480.80 rows=3,829 width=99) (actual time=243.432..1,255.402 rows=32,933 loops=1)

99. 838.529 1,173.101 ↓ 8.6 32,933 1

Hash Join (cost=6,868.80..58,677.67 rows=3,829 width=4) (actual time=243.410..1,173.101 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
100. 228.531 228.531 ↑ 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.012..228.531 rows=774,980 loops=1)

101. 42.184 106.041 ↑ 1.0 146,791 1

Hash (cost=4,459.91..4,459.91 rows=146,791 width=8) (actual time=106.041..106.041 rows=146,791 loops=1)

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

Seq Scan on services (cost=0.00..4,459.91 rows=146,791 width=8) (actual time=0.004..63.857 rows=146,791 loops=1)

103. 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)
104. 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
105. 0.007 0.017 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=132) (actual time=0.017..0.017 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
106. 0.010 0.010 ↑ 1.0 11 1

Seq Scan on unit_types (cost=0.00..1.11 rows=11 width=132) (actual time=0.008..0.010 rows=11 loops=1)

107. 32.004 386.672 ↑ 1.0 146,791 1

Hash (cost=14,531.90..14,531.90 rows=146,791 width=36) (actual time=386.672..386.672 rows=146,791 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2448kB
108. 209.312 354.668 ↑ 1.0 146,791 1

Hash Join (cost=8,053.61..14,531.90 rows=146,791 width=36) (actual time=130.172..354.668 rows=146,791 loops=1)

  • Hash Cond: (s.package_id = p.package_id)
109. 15.244 15.244 ↑ 1.0 146,791 1

Seq Scan on services s (cost=0.00..4,459.91 rows=146,791 width=21) (actual time=0.003..15.244 rows=146,791 loops=1)

110. 11.120 130.112 ↑ 1.0 49,043 1

Hash (cost=7,440.57..7,440.57 rows=49,043 width=12) (actual time=130.112..130.112 rows=49,043 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2556kB
111. 19.147 118.992 ↑ 1.0 49,043 1

Hash Left Join (cost=4,172.17..7,440.57 rows=49,043 width=12) (actual time=51.253..118.992 rows=49,043 loops=1)

  • Hash Cond: (oi.opportunity_id = o.opportunity_id)
112. 20.025 75.495 ↑ 1.0 49,043 1

Hash Left Join (cost=2,328.55..4,922.61 rows=49,043 width=12) (actual time=26.848..75.495 rows=49,043 loops=1)

  • Hash Cond: (p.opportunity_item_id = oi.opportunity_item_id)
113. 23.014 35.008 ↑ 1.0 49,043 1

Hash Left Join (cost=644.60..2,641.79 rows=49,043 width=12) (actual time=6.300..35.008 rows=49,043 loops=1)

  • Hash Cond: (p.book_id = b.book_id)
114. 5.724 5.724 ↑ 1.0 49,043 1

Seq Scan on packages p (cost=0.00..1,409.43 rows=49,043 width=12) (actual time=0.006..5.724 rows=49,043 loops=1)

115. 2.724 6.270 ↑ 1.0 20,071 1

Hash (cost=393.71..393.71 rows=20,071 width=8) (actual time=6.270..6.270 rows=20,071 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1041kB
116. 3.546 3.546 ↑ 1.0 20,071 1

Seq Scan on books b (cost=0.00..393.71 rows=20,071 width=8) (actual time=0.004..3.546 rows=20,071 loops=1)

117. 8.631 20.462 ↑ 1.0 48,531 1

Hash (cost=1,077.31..1,077.31 rows=48,531 width=8) (actual time=20.462..20.462 rows=48,531 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2408kB
118. 11.831 11.831 ↑ 1.0 48,531 1

Seq Scan on opportunity_items oi (cost=0.00..1,077.31 rows=48,531 width=8) (actual time=0.915..11.831 rows=48,531 loops=1)

119. 9.360 24.350 ↑ 1.0 48,961 1

Hash (cost=1,231.61..1,231.61 rows=48,961 width=8) (actual time=24.350..24.350 rows=48,961 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2425kB
120. 14.990 14.990 ↑ 1.0 48,961 1

Seq Scan on opportunities o (cost=0.00..1,231.61 rows=48,961 width=8) (actual time=0.165..14.990 rows=48,961 loops=1)

121. 9.710 81.467 ↓ 1.0 40,420 1

Hash (cost=2,641.79..2,641.79 rows=40,385 width=36) (actual time=81.467..81.467 rows=40,420 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2628kB
122. 59.642 71.757 ↓ 1.0 40,420 1

Hash Join (cost=644.60..2,641.79 rows=40,385 width=36) (actual time=6.932..71.757 rows=40,420 loops=1)

  • Hash Cond: (p_1.book_id = b_1.book_id)
123. 5.232 5.232 ↑ 1.0 49,043 1

Seq Scan on packages p_1 (cost=0.00..1,409.43 rows=49,043 width=21) (actual time=0.002..5.232 rows=49,043 loops=1)

124. 3.225 6.883 ↑ 1.0 20,071 1

Hash (cost=393.71..393.71 rows=20,071 width=8) (actual time=6.883..6.883 rows=20,071 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1041kB
125. 3.658 3.658 ↑ 1.0 20,071 1

Seq Scan on books b_1 (cost=0.00..393.71 rows=20,071 width=8) (actual time=0.004..3.658 rows=20,071 loops=1)

126. 0.014 0.034 ↑ 1.0 43 1

Hash (cost=1.43..1.43 rows=43 width=2,101) (actual time=0.034..0.034 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
127. 0.020 0.020 ↑ 1.0 43 1

Seq Scan on systems (cost=0.00..1.43 rows=43 width=2,101) (actual time=0.012..0.020 rows=43 loops=1)

128. 0.002 0.013 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
129. 0.011 0.011 ↑ 1.0 6 1

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

Planning time : 11.213 ms
Execution time : 3,785,547.446 ms