explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XOPt

Settings
# exclusive inclusive rows x rows loops node
1. 99.400 31,821.378 ↓ 1.3 106,240 1

Append (cost=350,939.98..355,948.48 rows=83,475 width=5,805) (actual time=21,546.261..31,821.378 rows=106,240 loops=1)

2.          

CTE tp_25

3. 43.872 21,618.842 ↓ 1.2 40,546 1

Unique (cost=140,882.79..141,049.74 rows=33,390 width=822) (actual time=21,546.255..21,618.842 rows=40,546 loops=1)

4.          

Initplan (forUnique)

5. 0.015 0.015 ↑ 1.0 1 1

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

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

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

7. 82.103 21,574.938 ↓ 1.3 42,595 1

Sort (cost=140,881.52..140,965.00 rows=33,390 width=822) (actual time=21,546.254..21,574.938 rows=42,595 loops=1)

  • Sort Key: li.id
  • Sort Method: quicksort Memory: 16678kB
8. 129.750 21,492.835 ↓ 1.3 42,595 1

Hash Join (cost=46,538.59..138,372.75 rows=33,390 width=822) (actual time=5,940.716..21,492.835 rows=42,595 loops=1)

  • Hash Cond: (v.treatment_plan_id = tp.id)
9. 51.519 6,228.626 ↓ 1.3 42,595 1

Hash Left Join (cost=45,845.65..50,907.55 rows=33,390 width=792) (actual time=5,926.741..6,228.626 rows=42,595 loops=1)

  • Hash Cond: (cli.claim_id = c.id)
10. 73.353 5,856.411 ↓ 1.3 42,595 1

Hash Right Join (cost=43,156.65..47,091.63 rows=33,390 width=784) (actual time=5,605.899..5,856.411 rows=42,595 loops=1)

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

Seq Scan on calendar_events ce (cost=0.00..2,937.81 rows=51,802 width=12) (actual time=0.038..177.265 rows=46,188 loops=1)

  • Filter: ((appointment_status_id IS NULL) OR (appointment_status_id <> $1))
  • Rows Removed by Filter: 13406
12. 40.343 5,605.793 ↓ 1.3 42,595 1

Hash (cost=42,071.48..42,071.48 rows=33,390 width=776) (actual time=5,605.793..5,605.793 rows=42,595 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 9320kB
13. 62.671 5,565.450 ↓ 1.3 42,595 1

Hash Join (cost=21,806.58..42,071.48 rows=33,390 width=776) (actual time=5,141.415..5,565.450 rows=42,595 loops=1)

  • Hash Cond: (v.visit_status_id = vs.id)
14. 79.976 5,502.758 ↓ 1.5 63,770 1

Hash Join (cost=21,805.06..40,907.27 rows=42,930 width=771) (actual time=5,141.386..5,502.758 rows=63,770 loops=1)

  • Hash Cond: (li.visit_id = v.id)
15. 75.511 5,288.195 ↓ 1.5 63,770 1

Hash Join (cost=17,180.87..34,834.25 rows=42,930 width=757) (actual time=5,006.739..5,288.195 rows=63,770 loops=1)

  • Hash Cond: (li.chargeable_item_id = ci.id)
16. 102.445 5,204.904 ↓ 1.5 63,770 1

Hash Right Join (cost=16,949.89..33,154.39 rows=42,930 width=599) (actual time=4,998.938..5,204.904 rows=63,770 loops=1)

  • Hash Cond: (te.line_item_id = li.id)
17. 192.899 4,347.026 ↓ 1.0 81,462 1

Hash Join (cost=4,181.10..19,588.98 rows=80,255 width=12) (actual time=1,669.341..4,347.026 rows=81,462 loops=1)

  • Hash Cond: (te.transaction_id = t.id)
18. 2,485.071 2,485.071 ↓ 1.0 226,406 1

Index Scan using idx_transaction_entries_line_item_id on transaction_entries te (cost=0.42..12,153.70 rows=225,853 width=8) (actual time=0.019..2,485.071 rows=226,406 loops=1)

  • Index Cond: (line_item_id IS NOT NULL)
19. 25.353 1,669.056 ↑ 1.0 37,722 1

Hash (cost=2,954.72..2,954.72 rows=37,722 width=12) (actual time=1,669.056..1,669.056 rows=37,722 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2133kB
20. 1,253.703 1,643.703 ↑ 1.0 37,722 1

Bitmap Heap Scan on transactions t (cost=507.60..2,954.72 rows=37,722 width=12) (actual time=390.156..1,643.703 rows=37,722 loops=1)

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

Bitmap Index Scan on idx_transactions_type (cost=0.00..498.17 rows=37,722 width=0) (actual time=390.000..390.000 rows=37,722 loops=1)

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

Hash (cost=11,373.57..11,373.57 rows=42,930 width=591) (actual time=755.433..755.433 rows=63,770 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 6891kB
23. 89.014 707.952 ↓ 1.5 63,770 1

Hash Right Join (cost=7,924.28..11,373.57 rows=42,930 width=591) (actual time=511.317..707.952 rows=63,770 loops=1)

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

Seq Scan on claims_line_items cli (cost=0.00..2,634.61 rows=62,687 width=8) (actual time=0.008..263.042 rows=62,687 loops=1)

25. 45.990 355.896 ↓ 1.4 61,721 1

Hash (cost=6,529.06..6,529.06 rows=42,930 width=587) (actual time=355.896..355.896 rows=61,721 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 6610kB
26. 309.906 309.906 ↓ 1.4 61,721 1

Seq Scan on line_items li (cost=0.00..6,529.06 rows=42,930 width=587) (actual time=0.059..309.906 rows=61,721 loops=1)

  • Filter: (is_active AND (created_at >= $2))
  • Rows Removed by Filter: 77573
27. 0.615 7.780 ↑ 1.0 765 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 158kB
28. 0.989 7.165 ↑ 1.0 765 1

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

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

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

30. 0.465 4.517 ↑ 1.0 738 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
31. 0.905 4.052 ↑ 1.0 738 1

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

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

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

33. 0.435 1.720 ↑ 1.0 738 1

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

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

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

35. 37.884 134.587 ↑ 1.0 61,315 1

Hash (cost=2,631.45..2,631.45 rows=61,315 width=18) (actual time=134.587..134.587 rows=61,315 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3622kB
36. 96.703 96.703 ↑ 1.0 61,315 1

Seq Scan on visits v (cost=0.00..2,631.45 rows=61,315 width=18) (actual time=0.005..96.703 rows=61,315 loops=1)

37. 0.005 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.016 0.016 ↑ 1.0 7 1

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

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

Hash (cost=1,720.24..1,720.24 rows=29,808 width=12) (actual time=320.696..320.696 rows=29,808 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1654kB
40. 302.513 302.513 ↑ 1.0 29,808 1

Seq Scan on claims c (cost=0.00..1,720.24 rows=29,808 width=12) (actual time=0.011..302.513 rows=29,808 loops=1)

41. 5.694 13.234 ↑ 1.0 9,615 1

Hash (cost=380.45..380.45 rows=9,615 width=17) (actual time=13.234..13.234 rows=9,615 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 611kB
42. 7.540 7.540 ↑ 1.0 9,615 1

Seq Scan on treatment_plans tp (cost=0.00..380.45 rows=9,615 width=17) (actual time=0.008..7.540 rows=9,615 loops=1)

43.          

SubPlan (forHash Join)

44. 7,113.365 15,121.225 ↑ 1.0 1 42,595

Aggregate (cost=2.53..2.56 rows=1 width=8) (actual time=0.355..0.355 rows=1 loops=42,595)

45. 8,007.860 8,007.860 ↓ 54.5 327 42,595

Index Only Scan using idx_visits_treatment_plan_id on visits v2 (cost=0.29..2.52 rows=6 width=4) (actual time=0.004..0.188 rows=327 loops=42,595)

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

CTE linked_line

47. 97.288 9,975.405 ↓ 1.3 65,694 1

Unique (cost=79,908.84..209,890.24 rows=50,085 width=822) (actual time=2,211.067..9,975.405 rows=65,694 loops=1)

48.          

Initplan (forUnique)

49. 0.008 0.008 ↑ 1.0 1 1

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

  • Filter: ((name)::text = 'Delete'::text)
  • Rows Removed by Filter: 6
50. 245.731 9,878.109 ↓ 1.5 73,218 1

Merge Anti Join (cost=79,907.62..209,763.80 rows=50,085 width=822) (actual time=2,211.064..9,878.109 rows=73,218 loops=1)

  • Merge Cond: (li_1.id = tp_25_1.line_item_id)
51. 208.526 2,242.860 ↓ 1.2 115,813 1

Sort (cost=75,395.44..75,645.86 rows=100,170 width=805) (actual time=2,165.229..2,242.860 rows=115,813 loops=1)

  • Sort Key: li_1.id
  • Sort Method: quicksort Memory: 40918kB
52. 138.608 2,034.334 ↓ 1.2 115,813 1

Hash Left Join (cost=38,664.21..67,075.27 rows=100,170 width=805) (actual time=1,009.723..2,034.334 rows=115,813 loops=1)

  • Hash Cond: (cli_1.claim_id = c_1.id)
53. 164.861 1,859.259 ↓ 1.2 115,813 1

Hash Join (cost=35,975.21..61,005.53 rows=100,170 width=797) (actual time=973.116..1,859.259 rows=115,813 loops=1)

  • Hash Cond: (li_1.visit_id = v_1.id)
54. 161.926 1,337.042 ↓ 1.1 138,363 1

Hash Join (cost=21,882.35..43,424.82 rows=128,790 width=757) (actual time=615.498..1,337.042 rows=138,363 loops=1)

  • Hash Cond: (li_1.chargeable_item_id = ci_1.id)
55. 159.135 1,170.565 ↓ 1.1 138,363 1

Hash Right Join (cost=21,651.37..38,847.18 rows=128,790 width=599) (actual time=610.936..1,170.565 rows=138,363 loops=1)

  • Hash Cond: (te_1.line_item_id = li_1.id)
56. 184.379 452.132 ↓ 1.0 81,462 1

Hash Join (cost=4,181.10..19,588.98 rows=80,255 width=12) (actual time=51.121..452.132 rows=81,462 loops=1)

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

Index Scan using idx_transaction_entries_line_item_id on transaction_entries te_1 (cost=0.42..12,153.70 rows=225,853 width=8) (actual time=0.017..216.700 rows=226,406 loops=1)

  • Index Cond: (line_item_id IS NOT NULL)
58. 22.193 51.053 ↑ 1.0 37,722 1

Hash (cost=2,954.72..2,954.72 rows=37,722 width=12) (actual time=51.053..51.053 rows=37,722 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2133kB
59. 25.963 28.860 ↑ 1.0 37,722 1

Bitmap Heap Scan on transactions t_1 (cost=507.60..2,954.72 rows=37,722 width=12) (actual time=3.050..28.860 rows=37,722 loops=1)

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

Bitmap Index Scan on idx_transactions_type (cost=0.00..498.17 rows=37,722 width=0) (actual time=2.897..2.897 rows=37,722 loops=1)

  • Index Cond: ((type)::text = ANY ('{"Add row correction",Invoice,"Add row adjustment"}'::text[]))
61. 107.901 559.298 ↓ 1.1 138,363 1

Hash (cost=13,284.59..13,284.59 rows=128,790 width=591) (actual time=559.298..559.298 rows=138,363 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 16187kB
62. 181.907 451.397 ↓ 1.1 138,363 1

Merge Left Join (cost=0.71..13,284.59 rows=128,790 width=591) (actual time=0.028..451.397 rows=138,363 loops=1)

  • Merge Cond: (li_1.id = cli_1.line_item_id)
63. 135.411 135.411 ↑ 1.0 128,790 1

Index Scan using line_items_pkey on line_items li_1 (cost=0.42..7,729.35 rows=128,790 width=587) (actual time=0.016..135.411 rows=128,790 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 10504
64. 134.079 134.079 ↑ 1.0 62,687 1

Index Scan using idx_claims_line_items_line_item_id on claims_line_items cli_1 (cost=0.29..3,337.75 rows=62,687 width=8) (actual time=0.008..134.079 rows=62,687 loops=1)

65. 0.562 4.551 ↑ 1.0 765 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 158kB
66. 0.911 3.989 ↑ 1.0 765 1

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

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

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

68. 0.429 2.596 ↑ 1.0 738 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
69. 0.869 2.167 ↑ 1.0 738 1

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

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

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

71. 0.426 0.888 ↑ 1.0 738 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
72. 0.462 0.462 ↑ 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.012..0.462 rows=738 loops=1)

73. 40.535 357.356 ↓ 1.2 59,286 1

Hash (cost=12,542.97..12,542.97 rows=47,689 width=44) (actual time=357.356..357.356 rows=59,286 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 5044kB
74. 69.530 316.821 ↓ 1.2 59,286 1

Hash Left Join (cost=5,315.83..12,542.97 rows=47,689 width=44) (actual time=75.878..316.821 rows=59,286 loops=1)

  • Hash Cond: (v_1.id = ce_1.visit_id)
75. 67.249 183.091 ↓ 1.2 59,286 1

Hash Join (cost=694.46..6,596.01 rows=47,689 width=36) (actual time=11.425..183.091 rows=59,286 loops=1)

  • Hash Cond: (v_1.treatment_plan_id = tp_1.id)
76. 68.577 104.487 ↓ 1.2 59,286 1

Hash Join (cost=1.52..4,293.57 rows=47,689 width=23) (actual time=0.050..104.487 rows=59,286 loops=1)

  • Hash Cond: (v_1.visit_status_id = vs_1.id)
77. 35.886 35.886 ↑ 1.0 61,315 1

Seq Scan on visits v_1 (cost=0.00..2,631.45 rows=61,315 width=18) (actual time=0.015..35.886 rows=61,315 loops=1)

78. 0.008 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
79. 0.016 0.016 ↑ 1.0 7 1

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

  • Filter: ((name)::text <> ALL ('{PreExisting,Historical}'::text[]))
  • Rows Removed by Filter: 2
80. 5.594 11.355 ↑ 1.0 9,615 1

Hash (cost=380.45..380.45 rows=9,615 width=17) (actual time=11.355..11.355 rows=9,615 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 611kB
81. 5.761 5.761 ↑ 1.0 9,615 1

Seq Scan on treatment_plans tp_1 (cost=0.00..380.45 rows=9,615 width=17) (actual time=0.007..5.761 rows=9,615 loops=1)

82. 26.699 64.200 ↑ 1.2 43,792 1

Hash (cost=2,937.81..2,937.81 rows=51,802 width=12) (actual time=64.200..64.200 rows=43,792 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2394kB
83. 37.501 37.501 ↑ 1.1 46,188 1

Seq Scan on calendar_events ce_1 (cost=0.00..2,937.81 rows=51,802 width=12) (actual time=0.024..37.501 rows=46,188 loops=1)

  • Filter: ((appointment_status_id IS NULL) OR (appointment_status_id <> $5))
  • Rows Removed by Filter: 13406
84. 17.300 36.467 ↑ 1.0 29,808 1

Hash (cost=1,720.24..1,720.24 rows=29,808 width=12) (actual time=36.467..36.467 rows=29,808 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1654kB
85. 19.167 19.167 ↑ 1.0 29,808 1

Seq Scan on claims c_1 (cost=0.00..1,720.24 rows=29,808 width=12) (actual time=0.011..19.167 rows=29,808 loops=1)

86. 43.288 67.718 ↓ 1.2 40,546 1

Sort (cost=4,512.18..4,595.65 rows=33,390 width=4) (actual time=45.731..67.718 rows=40,546 loops=1)

  • Sort Key: tp_25_1.line_item_id
  • Sort Method: quicksort Memory: 3437kB
87. 24.430 24.430 ↓ 1.2 40,546 1

CTE Scan on tp_25 tp_25_1 (cost=0.00..2,003.40 rows=33,390 width=4) (actual time=0.003..24.430 rows=40,546 loops=1)

88.          

SubPlan (forMerge Anti Join)

89. 3,441.246 7,321.800 ↑ 1.0 1 73,218

Aggregate (cost=2.53..2.56 rows=1 width=8) (actual time=0.099..0.100 rows=1 loops=73,218)

90. 3,880.554 3,880.554 ↓ 15.2 91 73,218

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.053 rows=91 loops=73,218)

  • Index Cond: (treatment_plan_id = v_1.treatment_plan_id)
  • Heap Fetches: 6823
91. 21,666.096 21,666.096 ↓ 1.2 40,546 1

CTE Scan on tp_25 (cost=0.00..2,003.40 rows=33,390 width=5,805) (actual time=21,546.259..21,666.096 rows=40,546 loops=1)

92. 10,055.882 10,055.882 ↓ 1.3 65,694 1

CTE Scan on linked_line (cost=0.00..3,005.10 rows=50,085 width=5,805) (actual time=2,211.068..10,055.882 rows=65,694 loops=1)