explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xa8y

Settings
# exclusive inclusive rows x rows loops node
1. 73.600 23,715.102 ↓ 1.5 86,587 1

Append (cost=262,973.69..266,482.25 rows=58,476 width=5,805) (actual time=15,160.921..23,715.102 rows=86,587 loops=1)

2.          

CTE tp_25

3. 41.022 15,228.854 ↓ 1.2 40,643 1

Unique (cost=141,020.34..141,187.42 rows=33,415 width=822) (actual time=15,160.917..15,228.854 rows=40,643 loops=1)

4.          

Initplan (forUnique)

5. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on appointment_statuses (cost=0.00..1.23 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: ((name)::text = 'Delete'::text)
  • Rows Removed by Filter: 6
6. 0.014 0.014 ↑ 1.0 1 1

Result (cost=0.00..0.04 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

7. 80.544 15,187.809 ↓ 1.3 42,692 1

Sort (cost=141,019.08..141,102.61 rows=33,415 width=822) (actual time=15,160.916..15,187.809 rows=42,692 loops=1)

  • Sort Key: li.id
  • Sort Method: quicksort Memory: 16710kB
8. 111.634 15,107.265 ↓ 1.3 42,692 1

Hash Join (cost=46,602.56..138,508.24 rows=33,415 width=822) (actual time=1,141.495..15,107.265 rows=42,692 loops=1)

  • Hash Cond: (v.treatment_plan_id = tp.id)
9. 47.447 1,280.957 ↓ 1.3 42,692 1

Hash Left Join (cost=45,909.44..50,977.88 rows=33,415 width=792) (actual time=1,130.828..1,280.957 rows=42,692 loops=1)

  • Hash Cond: (cli.claim_id = c.id)
10. 67.801 1,199.513 ↓ 1.3 42,692 1

Hash Right Join (cost=43,219.25..47,159.94 rows=33,415 width=784) (actual time=1,096.686..1,199.513 rows=42,692 loops=1)

  • Hash Cond: (ce.visit_id = v.id)
11. 35.321 35.321 ↑ 1.1 46,237 1

Seq Scan on calendar_events ce (cost=0.00..2,942.59 rows=51,855 width=12) (actual time=0.033..35.321 rows=46,237 loops=1)

  • Filter: ((appointment_status_id IS NULL) OR (appointment_status_id <> $1))
  • Rows Removed by Filter: 13412
12. 41.054 1,096.391 ↓ 1.3 42,692 1

Hash (cost=42,133.26..42,133.26 rows=33,415 width=776) (actual time=1,096.391..1,096.391 rows=42,692 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 9340kB
13. 56.774 1,055.337 ↓ 1.3 42,692 1

Hash Join (cost=21,824.45..42,133.26 rows=33,415 width=776) (actual time=661.673..1,055.337 rows=42,692 loops=1)

  • Hash Cond: (v.visit_status_id = vs.id)
14. 77.844 998.542 ↓ 1.5 63,867 1

Hash Join (cost=21,822.93..40,968.18 rows=42,962 width=771) (actual time=661.640..998.542 rows=63,867 loops=1)

  • Hash Cond: (li.visit_id = v.id)
15. 70.180 851.817 ↓ 1.5 63,867 1

Hash Join (cost=17,195.24..34,890.59 rows=42,962 width=757) (actual time=592.503..851.817 rows=63,867 loops=1)

  • Hash Cond: (li.chargeable_item_id = ci.id)
16. 93.888 777.469 ↓ 1.5 63,867 1

Hash Right Join (cost=16,964.26..33,209.64 rows=42,962 width=599) (actual time=588.321..777.469 rows=63,867 loops=1)

  • Hash Cond: (te.line_item_id = li.id)
17. 174.684 427.458 ↓ 1.0 81,523 1

Hash Join (cost=4,183.17..19,628.76 rows=80,477 width=12) (actual time=48.252..427.458 rows=81,523 loops=1)

  • Hash Cond: (te.transaction_id = t.id)
18. 204.807 204.807 ↓ 1.0 226,526 1

Index Scan using idx_transaction_entries_line_item_id on transaction_entries te (cost=0.42..12,182.63 rows=226,420 width=8) (actual time=0.019..204.807 rows=226,526 loops=1)

  • Index Cond: (line_item_id IS NOT NULL)
19. 21.175 47.967 ↑ 1.0 37,750 1

Hash (cost=2,955.87..2,955.87 rows=37,750 width=12) (actual time=47.967..47.967 rows=37,750 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2135kB
20. 23.821 26.792 ↑ 1.0 37,750 1

Bitmap Heap Scan on transactions t (cost=507.81..2,955.87 rows=37,750 width=12) (actual time=3.126..26.792 rows=37,750 loops=1)

  • Recheck Cond: ((type)::text = ANY ('{"Add row correction",Invoice,"Add row adjustment"}'::text[]))
  • Heap Blocks: exact=1170
21. 2.971 2.971 ↑ 1.0 37,750 1

Bitmap Index Scan on idx_transactions_type (cost=0.00..498.37 rows=37,750 width=0) (actual time=2.971..2.971 rows=37,750 loops=1)

  • Index Cond: ((type)::text = ANY ('{"Add row correction",Invoice,"Add row adjustment"}'::text[]))
22. 44.294 256.123 ↓ 1.5 63,867 1

Hash (cost=11,384.83..11,384.83 rows=42,962 width=591) (actual time=256.123..256.123 rows=63,867 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 6901kB
23. 77.414 211.829 ↓ 1.5 63,867 1

Hash Right Join (cost=7,932.90..11,384.83 rows=42,962 width=591) (actual time=140.746..211.829 rows=63,867 loops=1)

  • Hash Cond: (cli.line_item_id = li.id)
24. 29.961 29.961 ↑ 1.0 62,726 1

Seq Scan on claims_line_items cli (cost=0.00..2,636.78 rows=62,726 width=8) (actual time=0.008..29.961 rows=62,726 loops=1)

25. 42.381 104.454 ↓ 1.4 61,818 1

Hash (cost=6,536.63..6,536.63 rows=42,962 width=587) (actual time=104.454..104.454 rows=61,818 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 6620kB
26. 62.073 62.073 ↓ 1.4 61,818 1

Seq Scan on line_items li (cost=0.00..6,536.63 rows=42,962 width=587) (actual time=0.048..62.073 rows=61,818 loops=1)

  • Filter: (is_active AND (created_at >= $2))
  • Rows Removed by Filter: 77586
27. 0.553 4.168 ↑ 1.0 765 1

Hash (cost=206.12..206.12 rows=765 width=166) (actual time=4.168..4.168 rows=765 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 158kB
28. 0.855 3.615 ↑ 1.0 765 1

Hash Left Join (cost=128.16..206.12 rows=765 width=166) (actual time=2.360..3.615 rows=765 loops=1)

  • Hash Cond: (ci.id = ciic.chargeable_item_id)
29. 0.418 0.418 ↑ 1.0 765 1

Seq Scan on chargeable_items ci (cost=0.00..52.95 rows=765 width=160) (actual time=0.007..0.418 rows=765 loops=1)

30. 0.391 2.342 ↑ 1.0 738 1

Hash (cost=104.17..104.17 rows=738 width=10) (actual time=2.342..2.342 rows=738 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
31. 0.836 1.951 ↑ 1.0 738 1

Hash Join (cost=51.12..104.17 rows=738 width=10) (actual time=0.787..1.951 rows=738 loops=1)

  • Hash Cond: (ic.id = ciic.insurance_code_id)
32. 0.346 0.346 ↑ 1.0 738 1

Seq Scan on insurance_codes ic (cost=0.00..28.14 rows=738 width=10) (actual time=0.006..0.346 rows=738 loops=1)

33. 0.397 0.769 ↑ 1.0 738 1

Hash (cost=27.14..27.14 rows=738 width=8) (actual time=0.769..0.769 rows=738 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
34. 0.372 0.372 ↑ 1.0 738 1

Seq Scan on chargeable_items_insurance_codes ciic (cost=0.00..27.14 rows=738 width=8) (actual time=0.007..0.372 rows=738 loops=1)

35. 35.417 68.881 ↑ 1.0 61,371 1

Hash (cost=2,633.13..2,633.13 rows=61,371 width=18) (actual time=68.881..68.881 rows=61,371 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3625kB
36. 33.464 33.464 ↑ 1.0 61,371 1

Seq Scan on visits v (cost=0.00..2,633.13 rows=61,371 width=18) (actual time=0.006..33.464 rows=61,371 loops=1)

37. 0.007 0.021 ↑ 1.0 7 1

Hash (cost=1.29..1.29 rows=7 width=13) (actual time=0.021..0.021 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.014 0.014 ↑ 1.0 7 1

Seq Scan on visit_statuses vs (cost=0.00..1.29 rows=7 width=13) (actual time=0.009..0.014 rows=7 loops=1)

  • Filter: ((name)::text <> ALL ('{PreExisting,Historical}'::text[]))
  • Rows Removed by Filter: 2
39. 16.577 33.997 ↑ 1.0 29,827 1

Hash (cost=1,720.81..1,720.81 rows=29,827 width=12) (actual time=33.997..33.997 rows=29,827 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1655kB
40. 17.420 17.420 ↑ 1.0 29,827 1

Seq Scan on claims c (cost=0.00..1,720.81 rows=29,827 width=12) (actual time=0.008..17.420 rows=29,827 loops=1)

41. 5.506 10.542 ↑ 1.0 9,618 1

Hash (cost=380.54..380.54 rows=9,618 width=17) (actual time=10.542..10.542 rows=9,618 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 612kB
42. 5.036 5.036 ↑ 1.0 9,618 1

Seq Scan on treatment_plans tp (cost=0.00..380.54 rows=9,618 width=17) (actual time=0.009..5.036 rows=9,618 loops=1)

43.          

SubPlan (forHash Join)

44. 6,659.952 13,704.132 ↑ 1.0 1 42,692

Aggregate (cost=2.53..2.56 rows=1 width=8) (actual time=0.320..0.321 rows=1 loops=42,692)

45. 7,044.180 7,044.180 ↓ 55.3 332 42,692

Index Only Scan using idx_visits_treatment_plan_id on visits v2 (cost=0.29..2.52 rows=6 width=4) (actual time=0.003..0.165 rows=332 loops=42,692)

  • Index Cond: (treatment_plan_id = v.treatment_plan_id)
  • Heap Fetches: 0
46.          

CTE linked_line

47. 47.140 8,319.384 ↓ 1.8 45,944 1

Unique (cost=121,660.96..121,786.27 rows=25,061 width=822) (actual time=8,243.848..8,319.384 rows=45,944 loops=1)

48.          

Initplan (forUnique)

49. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on appointment_statuses appointment_statuses_1 (cost=0.00..1.23 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: ((name)::text = 'Delete'::text)
  • Rows Removed by Filter: 6
50. 93.422 8,272.235 ↓ 2.0 50,332 1

Sort (cost=121,659.73..121,722.39 rows=25,061 width=822) (actual time=8,243.845..8,272.235 rows=50,332 loops=1)

  • Sort Key: li_1.id
  • Sort Method: quicksort Memory: 20079kB
51. 127.642 8,178.813 ↓ 2.0 50,332 1

Hash Join (cost=34,192.81..119,828.63 rows=25,061 width=822) (actual time=877.701..8,178.813 rows=50,332 loops=1)

  • Hash Cond: (v_1.treatment_plan_id = tp_1.id)
52. 58.303 1,648.307 ↓ 2.0 50,332 1

Hash Left Join (cost=33,499.69..54,008.23 rows=25,061 width=796) (actual time=866.910..1,648.307 rows=50,332 loops=1)

  • Hash Cond: (cli_1.claim_id = c_1.id)
53. 90.879 1,556.030 ↓ 2.0 50,332 1

Hash Join (cost=30,809.50..50,472.24 rows=25,061 width=788) (actual time=832.803..1,556.030 rows=50,332 loops=1)

  • Hash Cond: (li_1.visit_id = v_1.id)
54. 109.092 1,091.695 ↓ 1.5 95,768 1

Hash Join (cost=20,969.92..39,639.23 rows=64,443 width=757) (actual time=459.210..1,091.695 rows=95,768 loops=1)

  • Hash Cond: (li_1.chargeable_item_id = ci_1.id)
55. 124.790 978.485 ↓ 1.5 95,768 1

Hash Right Join (cost=20,738.94..37,233.30 rows=64,443 width=599) (actual time=455.073..978.485 rows=95,768 loops=1)

  • Hash Cond: (te_1.line_item_id = li_1.id)
56. 184.712 447.130 ↓ 1.0 81,523 1

Hash Join (cost=4,183.17..19,628.76 rows=80,477 width=12) (actual time=48.250..447.130 rows=81,523 loops=1)

  • Hash Cond: (te_1.transaction_id = t_1.id)
57. 214.241 214.241 ↓ 1.0 226,526 1

Index Scan using idx_transaction_entries_line_item_id on transaction_entries te_1 (cost=0.42..12,182.63 rows=226,420 width=8) (actual time=0.024..214.241 rows=226,526 loops=1)

  • Index Cond: (line_item_id IS NOT NULL)
58. 21.491 48.177 ↑ 1.0 37,750 1

Hash (cost=2,955.87..2,955.87 rows=37,750 width=12) (actual time=48.177..48.177 rows=37,750 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2135kB
59. 23.707 26.686 ↑ 1.0 37,750 1

Bitmap Heap Scan on transactions t_1 (cost=507.81..2,955.87 rows=37,750 width=12) (actual time=3.133..26.686 rows=37,750 loops=1)

  • Recheck Cond: ((type)::text = ANY ('{"Add row correction",Invoice,"Add row adjustment"}'::text[]))
  • Heap Blocks: exact=1170
60. 2.979 2.979 ↑ 1.0 37,750 1

Bitmap Index Scan on idx_transactions_type (cost=0.00..498.37 rows=37,750 width=0) (actual time=2.979..2.979 rows=37,750 loops=1)

  • Index Cond: ((type)::text = ANY ('{"Add row correction",Invoice,"Add row adjustment"}'::text[]))
61. 68.509 406.565 ↓ 1.5 95,768 1

Hash (cost=14,461.38..14,461.38 rows=64,443 width=591) (actual time=406.565..406.565 rows=95,768 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 10856kB
62. 102.301 338.056 ↓ 1.5 95,768 1

Hash Right Join (cost=10,719.47..14,461.38 rows=64,443 width=591) (actual time=205.914..338.056 rows=95,768 loops=1)

  • Hash Cond: (cli_1.line_item_id = li_1.id)
63. 30.118 30.118 ↑ 1.0 62,726 1

Seq Scan on claims_line_items cli_1 (cost=0.00..2,636.78 rows=62,726 width=8) (actual time=0.013..30.118 rows=62,726 loops=1)

64. 64.126 205.637 ↓ 1.4 88,244 1

Hash (cost=8,625.07..8,625.07 rows=64,443 width=587) (actual time=205.637..205.637 rows=88,244 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 9907kB
65. 117.000 141.511 ↓ 1.4 88,244 1

Seq Scan on line_items li_1 (cost=2,088.44..8,625.07 rows=64,443 width=587) (actual time=50.549..141.511 rows=88,244 loops=1)

  • Filter: (is_active AND (NOT (hashed SubPlan 7)))
  • Rows Removed by Filter: 51160
66.          

SubPlan (forSeq Scan)

67. 24.511 24.511 ↓ 1.2 40,643 1

CTE Scan on tp_25 tp_25_1 (cost=0.00..2,004.90 rows=33,415 width=4) (actual time=0.002..24.511 rows=40,643 loops=1)

68. 0.476 4.118 ↑ 1.0 765 1

Hash (cost=206.12..206.12 rows=765 width=166) (actual time=4.118..4.118 rows=765 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 158kB
69. 0.880 3.642 ↑ 1.0 765 1

Hash Left Join (cost=128.16..206.12 rows=765 width=166) (actual time=2.362..3.642 rows=765 loops=1)

  • Hash Cond: (ci_1.id = ciic_1.chargeable_item_id)
70. 0.425 0.425 ↑ 1.0 765 1

Seq Scan on chargeable_items ci_1 (cost=0.00..52.95 rows=765 width=160) (actual time=0.013..0.425 rows=765 loops=1)

71. 0.413 2.337 ↑ 1.0 738 1

Hash (cost=104.17..104.17 rows=738 width=10) (actual time=2.337..2.337 rows=738 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
72. 0.778 1.924 ↑ 1.0 738 1

Hash Join (cost=51.12..104.17 rows=738 width=10) (actual time=0.790..1.924 rows=738 loops=1)

  • Hash Cond: (ic_1.id = ciic_1.insurance_code_id)
73. 0.370 0.370 ↑ 1.0 738 1

Seq Scan on insurance_codes ic_1 (cost=0.00..28.14 rows=738 width=10) (actual time=0.008..0.370 rows=738 loops=1)

74. 0.386 0.776 ↑ 1.0 738 1

Hash (cost=27.14..27.14 rows=738 width=8) (actual time=0.776..0.776 rows=738 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
75. 0.390 0.390 ↑ 1.0 738 1

Seq Scan on chargeable_items_insurance_codes ciic_1 (cost=0.00..27.14 rows=738 width=8) (actual time=0.007..0.390 rows=738 loops=1)

76. 30.221 373.456 ↓ 2.0 46,754 1

Hash (cost=9,063.93..9,063.93 rows=23,866 width=35) (actual time=373.456..373.456 rows=46,754 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3622kB
77. 58.827 343.235 ↓ 2.0 46,754 1

Hash Right Join (cost=5,352.90..9,063.93 rows=23,866 width=35) (actual time=249.506..343.235 rows=46,754 loops=1)

  • Hash Cond: (ce_1.visit_id = v_1.id)
78. 35.063 35.063 ↑ 1.1 46,237 1

Seq Scan on calendar_events ce_1 (cost=0.00..2,942.59 rows=51,855 width=12) (actual time=0.031..35.063 rows=46,237 loops=1)

  • Filter: ((appointment_status_id IS NULL) OR (appointment_status_id <> $5))
  • Rows Removed by Filter: 13412
79. 33.176 249.345 ↓ 2.0 46,754 1

Hash (cost=4,577.25..4,577.25 rows=23,866 width=27) (actual time=249.345..249.345 rows=46,754 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3322kB
80. 48.899 216.169 ↓ 2.0 46,754 1

Hash Join (cost=2,090.25..4,577.25 rows=23,866 width=27) (actual time=55.729..216.169 rows=46,754 loops=1)

  • Hash Cond: (v_1.visit_status_id = vs_1.id)
81. 49.572 167.246 ↓ 1.5 46,759 1

Nested Loop (cost=2,088.73..3,744.65 rows=30,686 width=22) (actual time=55.695..167.246 rows=46,759 loops=1)

82. 36.446 58.225 ↓ 17.5 3,497 1

HashAggregate (cost=2,088.44..2,094.44 rows=200 width=4) (actual time=55.672..58.225 rows=3,497 loops=1)

  • Group Key: tp_25_2.treatment_plan_id
83. 21.779 21.779 ↓ 1.2 40,643 1

CTE Scan on tp_25 tp_25_2 (cost=0.00..2,004.90 rows=33,415 width=4) (actual time=0.002..21.779 rows=40,643 loops=1)

84. 59.449 59.449 ↓ 2.2 13 3,497

Index Scan using idx_visits_treatment_plan_id on visits v_1 (cost=0.29..8.07 rows=6 width=18) (actual time=0.003..0.017 rows=13 loops=3,497)

  • Index Cond: (treatment_plan_id = tp_25_2.treatment_plan_id)
85. 0.006 0.024 ↑ 1.0 7 1

Hash (cost=1.29..1.29 rows=7 width=13) (actual time=0.024..0.024 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
86. 0.018 0.018 ↑ 1.0 7 1

Seq Scan on visit_statuses vs_1 (cost=0.00..1.29 rows=7 width=13) (actual time=0.012..0.018 rows=7 loops=1)

  • Filter: ((name)::text <> ALL ('{PreExisting,Historical}'::text[]))
  • Rows Removed by Filter: 2
87. 16.411 33.974 ↑ 1.0 29,827 1

Hash (cost=1,720.81..1,720.81 rows=29,827 width=12) (actual time=33.974..33.974 rows=29,827 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1655kB
88. 17.563 17.563 ↑ 1.0 29,827 1

Seq Scan on claims c_1 (cost=0.00..1,720.81 rows=29,827 width=12) (actual time=0.016..17.563 rows=29,827 loops=1)

89. 5.400 10.700 ↑ 1.0 9,618 1

Hash (cost=380.54..380.54 rows=9,618 width=17) (actual time=10.700..10.700 rows=9,618 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 612kB
90. 5.300 5.300 ↑ 1.0 9,618 1

Seq Scan on treatment_plans tp_1 (cost=0.00..380.54 rows=9,618 width=17) (actual time=0.017..5.300 rows=9,618 loops=1)

91.          

SubPlan (forHash Join)

92. 3,070.252 6,392.164 ↑ 1.0 1 50,332

Aggregate (cost=2.53..2.56 rows=1 width=8) (actual time=0.126..0.127 rows=1 loops=50,332)

93. 3,321.912 3,321.912 ↓ 21.5 129 50,332

Index Only Scan using idx_visits_treatment_plan_id on visits v2_1 (cost=0.29..2.52 rows=6 width=4) (actual time=0.003..0.066 rows=129 loops=50,332)

  • Index Cond: (treatment_plan_id = v_1.treatment_plan_id)
  • Heap Fetches: 0
94. 15,272.668 15,272.668 ↓ 1.2 40,643 1

CTE Scan on tp_25 (cost=0.00..2,004.90 rows=33,415 width=5,805) (actual time=15,160.921..15,272.668 rows=40,643 loops=1)

95. 8,368.834 8,368.834 ↓ 1.8 45,944 1

CTE Scan on linked_line (cost=0.00..1,503.66 rows=25,061 width=5,805) (actual time=8,243.851..8,368.834 rows=45,944 loops=1)

Planning time : 7.391 ms
Execution time : 23,756.238 ms