explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 52Qd

Settings
# exclusive inclusive rows x rows loops node
1. 2,551.114 389,324.088 ↑ 3.5 176,144 1

Sort (cost=23,315,441.49..23,316,993.70 rows=620,883 width=4,717) (actual time=388,405.910..389,324.088 rows=176,144 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_1.service (...)
  • Sort Method: external merge Disk: 145456kB
2. 155.386 386,772.974 ↑ 3.5 176,144 1

Hash Left Join (cost=19,413,088.84..19,480,372.74 rows=620,883 width=4,717) (actual time=386,027.835..386,772.974 rows=176,144 loops=1)

  • Hash Cond: (services_1.system_id = systems.system_id)
3. 551.719 386,617.554 ↑ 3.5 176,144 1

Hash Right Join (cost=19,413,086.87..19,443,893.90 rows=620,883 width=2,616) (actual time=386,027.789..386,617.554 rows=176,144 loops=1)

  • Hash Cond: (s_1.service_id = services_1.service_id)
4. 19.204 1,505.131 ↑ 3.6 80,401 1

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

5.          

CTE iptv_endpoint

6. 5.263 77.828 ↓ 1.6 33,377 1

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

7. 2.253 55.969 ↑ 1.0 4,149 1

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

  • Hash Cond: (iptv_subscr.ep_service_id = endp.service_id)
8. 26.631 41.416 ↑ 1.0 4,571 1

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

  • Hash Cond: (service_w_endpoint.service_id = iptv_subscr.service_id)
9. 13.566 13.566 ↑ 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..13.566 rows=146,791 loops=1)

10. 0.608 1.219 ↑ 1.0 4,571 1

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

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

12. 6.092 12.300 ↑ 1.0 38,322 1

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

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

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

14. 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.002..0.004 rows=8 loops=4,149)

  • Index Cond: (package_id = service_w_endpoint.package_id)
15. 102.678 1,485.927 ↑ 3.6 80,411 1

Sort (cost=19,205,433.60..19,206,164.80 rows=292,480 width=151) (actual time=1,466.293..1,485.927 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.addre (...)
  • Sort Method: external merge Disk: 2256kB
16. 140.292 1,383.249 ↑ 3.6 80,411 1

Hash Left Join (cost=4,823.10..19,156,885.37 rows=292,480 width=151) (actual time=140.464..1,383.249 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
17. 33.496 1,127.485 ↑ 2.0 146,801 1

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

  • Hash Cond: (s_1.service_id = sta.service_id)
18. 35.408 1,093.978 ↑ 2.0 146,801 1

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

  • Hash Cond: (s_1.service_id = cs.service_id)
19. 36.888 1,058.456 ↑ 2.0 146,791 1

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

  • Hash Cond: (p_2.package_id = ga.package_id)
20. 183.691 1,020.965 ↑ 2.0 146,791 1

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

21. 149.982 543.692 ↑ 2.0 146,791 1

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

22. 43.480 246.919 ↑ 1.0 146,791 1

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

  • Hash Cond: (s_1.service_id = wa.service_id)
23. 50.815 202.393 ↑ 1.0 146,791 1

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

  • Hash Cond: (s_1.service_id = cr.service_id)
24. 33.213 148.447 ↑ 1.0 146,791 1

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

  • Hash Cond: (s_1.service_id = oc.service_id)
25. 76.112 115.066 ↑ 1.0 146,791 1

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

  • Hash Cond: (s_1.package_id = p_2.package_id)
26. 20.461 20.461 ↑ 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.009..20.461 rows=146,791 loops=1)

27. 7.516 18.493 ↑ 1.0 49,043 1

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

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

29. 0.076 0.168 ↑ 1.0 471 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
30. 0.092 0.092 ↑ 1.0 471 1

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

31. 1.483 3.131 ↑ 1.0 11,320 1

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

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

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

33. 0.367 1.046 ↑ 1.0 2,800 1

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

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

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

35. 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
36. 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)

37. 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)
38. 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)
39. 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
40. 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)

41. 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)
42. 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)
43. 0.001 0.603 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.000 0.602 ↑ 1.0 1 1

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

45. 0.001 0.602 ↑ 1.0 1 1

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

46. 0.363 0.601 ↑ 1,651.0 1 1

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

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

48. 0.025 0.114 ↑ 1.4 173 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
49. 0.065 0.089 ↑ 1.0 238 1

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

  • Hash Cond: (cs.facility_id = f.facility_id)
50. 0.019 0.019 ↑ 1.0 238 1

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

51. 0.002 0.005 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.003 0.003 ↑ 1.0 4 1

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

53. 0.006 0.011 ↑ 1.0 24 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
54. 0.005 0.005 ↑ 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.005 rows=24 loops=1)

55. 5.091 115.472 ↓ 153.9 30,771 1

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

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1774kB
56. 3.789 110.381 ↓ 153.9 30,771 1

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

57. 4.184 106.592 ↓ 153.9 30,771 1

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

58. 12.537 102.408 ↓ 1.6 33,377 1

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

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

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

60. 628.585 384,560.704 ↓ 5.7 176,144 1

Hash (cost=188,546.59..188,546.59 rows=30,938 width=2,544) (actual time=384,560.704..384,560.704 rows=176,144 loops=1)

  • Buckets: 2048 (originally 2048) Batches: 64 (originally 32) Memory Usage: 4081kB
61. 189.853 383,932.119 ↓ 5.7 176,144 1

Hash Left Join (cost=126,734.74..188,546.59 rows=30,938 width=2,544) (actual time=5,308.686..383,932.119 rows=176,144 loops=1)

  • Hash Cond: (customers.company_id = companies_1.company_id)
62. 165.863 383,742.259 ↓ 5.7 176,144 1

Hash Left Join (cost=126,733.60..188,120.06 rows=30,938 width=1,935) (actual time=5,308.655..383,742.259 rows=176,144 loops=1)

  • Hash Cond: (packages_1.package_id = p_1.package_id)
63. 1,137.106 383,501.877 ↓ 5.7 176,144 1

Hash Left Join (cost=123,587.00..184,602.68 rows=30,938 width=1,899) (actual time=5,234.088..383,501.877 rows=176,144 loops=1)

  • Hash Cond: (services_1.service_id = s.service_id)
64. 231.283 382,002.219 ↓ 5.7 176,144 1

Hash Left Join (cost=106,073.22..151,254.50 rows=30,938 width=1,863) (actual time=4,871.496..382,002.219 rows=176,144 loops=1)

  • Hash Cond: ((services_1.unit_type_id)::text = (unit_types.unit_type_id)::text)
65. 251.018 381,770.921 ↓ 5.7 176,144 1

Hash Left Join (cost=106,071.97..151,067.52 rows=30,938 width=1,731) (actual time=4,871.467..381,770.921 rows=176,144 loops=1)

  • Hash Cond: (packages_1.package_id = line_items.package_id)
66. 183,046.549 380,277.988 ↓ 1.5 46,273 1

Nested Loop Left Join (cost=33,168.72..77,832.61 rows=30,938 width=1,632) (actual time=3,629.533..380,277.988 rows=46,273 loops=1)

  • Join Filter: (sub_line_items_1.service_id = services_1.service_id)
  • Rows Removed by Join Filter: 1432255497
67. 238.845 2,716.549 ↓ 1.5 46,258 1

Hash Left Join (cost=26,156.50..29,770.67 rows=30,938 width=1,511) (actual time=2,426.131..2,716.549 rows=46,258 loops=1)

  • Hash Cond: (books.book_id = packages_1.book_id)
68. 9.078 86.868 ↓ 1.8 13,778 1

Hash Left Join (cost=2,820.20..6,299.46 rows=7,676 width=805) (actual time=35.058..86.868 rows=13,778 loops=1)

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

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

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

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

71. 11.337 34.175 ↑ 1.0 13,702 1

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

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

Seq Scan on customers (cost=0.00..1,790.83 rows=14,336 width=371) (actual time=0.017..22.838 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.018 0.794 ↑ 1.0 58 1

Hash (cost=149.44..149.44 rows=59 width=124) (actual time=0.794..0.794 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
74. 0.776 0.776 ↑ 1.0 58 1

Seq Scan on one_time_charges (cost=0.00..149.44 rows=59 width=124) (actual time=0.707..0.776 rows=58 loops=1)

  • Filter: ((charge_date > '2019-02-01'::date) AND (charge_date <= '2019-03-01'::date))
  • Rows Removed by Filter: 4838
75. 84.557 2,390.836 ↓ 319.1 59,033 1

Hash (cost=23,333.99..23,333.99 rows=185 width=706) (actual time=2,390.836..2,390.836 rows=59,033 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3969kB
76. 69.331 2,306.279 ↓ 319.1 59,033 1

Hash Right Join (cost=5,481.54..23,333.99 rows=185 width=706) (actual time=259.094..2,306.279 rows=59,033 loops=1)

  • Hash Cond: (services_1.package_id = packages_1.package_id)
77. 1,747.790 1,978.110 ↓ 4.0 58,872 1

Hash Join (cost=2.34..17,652.54 rows=14,574 width=624) (actual time=0.248..1,978.110 rows=58,872 loops=1)

  • Hash Cond: ((services_1.status)::text = (service_statuses.status)::text)
78. 29.573 230.305 ↓ 4.0 58,872 1

Hash Left Join (cost=1.25..10,164.06 rows=14,574 width=624) (actual time=0.057..230.305 rows=58,872 loops=1)

  • Hash Cond: ((services_1.unit_type_id)::text = (unit_types_1.unit_type_id)::text)
  • Filter: ((services_1.deactivation_date IS NULL) OR ((services_1.deactivation_date > ('2019-03-01'::date - ((((services_1.frequency)::character varying)::text || ' month': (...)
  • Rows Removed by Filter: 30
79. 200.722 200.722 ↓ 3.0 58,902 1

Seq Scan on services services_1 (cost=0.00..9,964.57 rows=19,929 width=624) (actual time=0.037..200.722 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 + ((((frequency (...)
  • Rows Removed by Filter: 87889
80. 0.004 0.010 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=33) (actual time=0.010..0.010 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
81. 0.006 0.006 ↑ 1.0 11 1

Seq Scan on unit_types unit_types_1 (cost=0.00..1.11 rows=11 width=33) (actual time=0.003..0.006 rows=11 loops=1)

82. 0.004 0.015 ↑ 1.0 4 1

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

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

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

84. 7.377 258.838 ↓ 395.3 18,183 1

Hash (cost=5,478.63..5,478.63 rows=46 width=342) (actual time=258.838..258.838 rows=18,183 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3090kB
85. 8.960 251.461 ↓ 395.3 18,183 1

Hash Right Join (cost=4,732.37..5,478.63 rows=46 width=342) (actual time=238.122..251.461 rows=18,183 loops=1)

  • Hash Cond: (package_charges.package_id = packages_1.package_id)
86. 4.600 4.600 ↑ 1.0 5,920 1

Seq Scan on package_charges (cost=0.00..723.78 rows=5,959 width=63) (actual time=0.005..4.600 rows=5,920 loops=1)

  • Filter: ((NOT billed) OR (charge_date = '2019-03-01'::date))
  • Rows Removed by Filter: 23422
87. 13.711 237.901 ↓ 393.7 18,109 1

Hash (cost=4,731.79..4,731.79 rows=46 width=279) (actual time=237.901..237.901 rows=18,109 loops=1)

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

Seq Scan on packages packages_1 (cost=0.00..4,731.33 rows=46 width=518) (actual time=0.077..224.190 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)) A (...)
  • Rows Removed by Filter: 30934
89. 193,329.591 194,514.890 ↓ 30,963.0 30,963 46,258

Materialize (cost=7,012.22..47,597.87 rows=1 width=121) (actual time=0.009..4.205 rows=30,963 loops=46,258)

90. 13.066 1,185.299 ↓ 30,963.0 30,963 1

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

  • Join Filter: (services.package_id = line_items_1.package_id)
  • Rows Removed by Join Filter: 5
91. 813.743 1,110.297 ↓ 24.3 30,968 1

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

  • Hash Cond: (sub_line_items_1.service_id = services.service_id)
  • Join Filter: (sub_line_items_1.billing_period_start_date = ('2019-03-01'::date - ((((services.frequency)::character varying)::text || ' month'::text))::interval))
  • Rows Removed by Join Filter: 686504
92. 218.298 218.298 ↓ 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..218.298 rows=717,472 loops=1)

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

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

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

Seq Scan on services (cost=0.00..4,459.91 rows=146,791 width=12) (actual time=0.003..48.546 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. 10.574 1,241.915 ↓ 8.6 33,207 1

Hash (cost=72,855.19..72,855.19 rows=3,845 width=99) (actual time=1,241.915..1,241.915 rows=33,207 loops=1)

  • Buckets: 65536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3825kB
97. 774.562 1,231.341 ↓ 8.6 33,207 1

Hash Join (cost=18,956.61..72,855.19 rows=3,845 width=99) (actual time=524.802..1,231.341 rows=33,207 loops=1)

  • Hash Cond: (sub_line_items.line_item_id = line_items.line_item_id)
  • Join Filter: (('2019-03-01'::date - ((((packages.frequency)::character varying)::text || ' month'::text))::interval) = sub_line_items.billing_period_start_date)
  • Rows Removed by Join Filter: 741773
98. 187.896 187.896 ↑ 1.0 774,980 1

Seq Scan on sub_line_items (cost=0.00..19,696.80 rows=774,980 width=8) (actual time=0.011..187.896 rows=774,980 loops=1)

99. 92.437 268.883 ↑ 1.0 261,734 1

Hash (cost=11,593.43..11,593.43 rows=261,775 width=103) (actual time=268.883..268.883 rows=261,734 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 2039kB
100. 134.314 176.446 ↑ 1.0 261,734 1

Hash Join (cost=2,022.47..11,593.43 rows=261,775 width=103) (actual time=18.283..176.446 rows=261,734 loops=1)

  • Hash Cond: (line_items.package_id = packages.package_id)
101. 23.896 23.896 ↑ 1.0 263,797 1

Seq Scan on line_items (cost=0.00..5,963.97 rows=263,797 width=99) (actual time=0.003..23.896 rows=263,797 loops=1)

102. 7.014 18.236 ↑ 1.0 49,043 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2428kB
103. 11.222 11.222 ↑ 1.0 49,043 1

Seq Scan on packages (cost=0.00..1,409.43 rows=49,043 width=8) (actual time=0.003..11.222 rows=49,043 loops=1)

104. 0.006 0.015 ↑ 1.0 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
105. 0.009 0.009 ↑ 1.0 11 1

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

106. 32.504 362.552 ↑ 1.0 146,791 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2448kB
107. 202.993 330.048 ↑ 1.0 146,791 1

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

  • Hash Cond: (s.package_id = p.package_id)
108. 14.842 14.842 ↑ 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..14.842 rows=146,791 loops=1)

109. 10.646 112.213 ↑ 1.0 49,043 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2556kB
110. 18.266 101.567 ↑ 1.0 49,043 1

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

  • Hash Cond: (oi.opportunity_id = o.opportunity_id)
111. 19.246 66.431 ↑ 1.0 49,043 1

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

  • Hash Cond: (p.opportunity_item_id = oi.opportunity_item_id)
112. 22.335 33.969 ↑ 1.0 49,043 1

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

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

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

114. 2.616 6.021 ↑ 1.0 20,071 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1041kB
115. 3.405 3.405 ↑ 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.405 rows=20,071 loops=1)

116. 6.697 13.216 ↑ 1.0 48,531 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2408kB
117. 6.519 6.519 ↑ 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.005..6.519 rows=48,531 loops=1)

118. 7.917 16.870 ↑ 1.0 48,961 1

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

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

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

120. 7.839 74.519 ↓ 1.0 40,420 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2628kB
121. 55.826 66.680 ↓ 1.0 40,420 1

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

  • Hash Cond: (p_1.book_id = b_1.book_id)
122. 4.700 4.700 ↑ 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.006..4.700 rows=49,043 loops=1)

123. 2.655 6.154 ↑ 1.0 20,071 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1041kB
124. 3.499 3.499 ↑ 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.499 rows=20,071 loops=1)

125. 0.004 0.007 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
126. 0.003 0.003 ↑ 1.0 6 1

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

127. 0.017 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
128. 0.017 0.017 ↑ 1.0 43 1

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

Planning time : 12.365 ms
Execution time : 389,362.449 ms