explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HHlW

Settings
# exclusive inclusive rows x rows loops node
1. 27.486 4,360.398 ↑ 8.1 29,538 1

Append (cost=756,083.96..770,464.22 rows=239,671 width=5,805) (actual time=2,303.930..4,360.398 rows=29,538 loops=1)

2.          

CTE tp_25

3. 39.033 2,371.254 ↑ 1.7 29,538 1

Unique (cost=169,565.06..169,811.78 rows=49,344 width=279) (actual time=2,303.926..2,371.254 rows=29,538 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.013 0.013 ↑ 1.0 1 1

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

7. 87.686 2,332.199 ↑ 1.1 44,819 1

Sort (cost=169,563.79..169,687.15 rows=49,344 width=279) (actual time=2,303.924..2,332.199 rows=44,819 loops=1)

  • Sort Key: li.id
  • Sort Method: quicksort Memory: 20196kB
8. 109.993 2,244.513 ↑ 1.1 44,819 1

Hash Join (cost=30,161.21..165,717.28 rows=49,344 width=279) (actual time=896.061..2,244.513 rows=44,819 loops=1)

  • Hash Cond: (v.treatment_plan_id = tp.id)
9. 53.986 1,664.173 ↑ 1.1 44,819 1

Hash Left Join (cost=28,801.90..39,949.41 rows=49,344 width=248) (actual time=873.841..1,664.173 rows=44,819 loops=1)

  • Hash Cond: (v.id = ce.visit_id)
10. 54.339 1,582.893 ↑ 1.1 44,819 1

Hash Left Join (cost=27,078.45..37,499.33 rows=49,344 width=240) (actual time=846.510..1,582.893 rows=44,819 loops=1)

  • Hash Cond: (cli.claim_id = c.id)
11. 143.466 1,508.281 ↑ 1.1 44,819 1

Hash Join (cost=25,789.27..34,544.79 rows=49,344 width=232) (actual time=826.214..1,508.281 rows=44,819 loops=1)

  • Hash Cond: (li.visit_id = v.id)
12. 249.230 1,248.797 ↓ 3.2 205,604 1

Hash Join (cost=18,785.98..25,840.28 rows=63,442 width=210) (actual time=710.135..1,248.797 rows=205,604 loops=1)

  • Hash Cond: (li.chargeable_item_id = ci.id)
13. 170.258 966.260 ↓ 3.2 205,604 1

Hash Right Join (cost=17,196.97..22,110.10 rows=63,442 width=78) (actual time=676.812..966.260 rows=205,604 loops=1)

  • Hash Cond: (te.line_item_id = li.id)
14. 55.296 131.270 ↑ 1.0 24,259 1

Hash Join (cost=953.52..5,626.57 rows=24,445 width=12) (actual time=12.017..131.270 rows=24,259 loops=1)

  • Hash Cond: (te.transaction_id = t.id)
15. 64.013 64.013 ↑ 1.0 65,209 1

Index Scan using idx_transaction_entries_line_item_id on transaction_entries te (cost=0.29..3,695.46 rows=65,209 width=8) (actual time=0.023..64.013 rows=65,209 loops=1)

  • Index Cond: (line_item_id IS NOT NULL)
16. 5.044 11.961 ↑ 1.0 8,586 1

Hash (cost=674.18..674.18 rows=8,586 width=12) (actual time=11.961..11.961 rows=8,586 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 497kB
17. 6.230 6.917 ↑ 1.0 8,586 1

Bitmap Heap Scan on transactions t (cost=129.40..674.18 rows=8,586 width=12) (actual time=0.725..6.917 rows=8,586 loops=1)

  • Recheck Cond: ((type)::text = ANY ('{"Add row correction",Invoice,"Add row adjustment"}'::text[]))
  • Heap Blocks: exact=255
18. 0.687 0.687 ↑ 1.0 8,586 1

Bitmap Index Scan on idx_transactions_type (cost=0.00..127.26 rows=8,586 width=0) (actual time=0.687..0.687 rows=8,586 loops=1)

  • Index Cond: ((type)::text = ANY ('{"Add row correction",Invoice,"Add row adjustment"}'::text[]))
19. 151.923 664.732 ↓ 3.2 205,604 1

Hash (cost=14,181.58..14,181.58 rows=63,442 width=70) (actual time=664.732..664.732 rows=205,604 loops=1)

  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 24775kB
20. 183.641 512.809 ↓ 3.2 205,604 1

Hash Right Join (cost=11,899.82..14,181.58 rows=63,442 width=70) (actual time=304.809..512.809 rows=205,604 loops=1)

  • Hash Cond: (cli.line_item_id = li.id)
21. 24.445 24.445 ↑ 1.0 40,817 1

Seq Scan on claims_line_items cli (cost=0.00..1,745.51 rows=40,817 width=8) (actual time=0.013..24.445 rows=40,817 loops=1)

22. 142.481 304.723 ↓ 3.0 190,323 1

Hash (cost=9,837.95..9,837.95 rows=63,442 width=66) (actual time=304.723..304.723 rows=190,323 loops=1)

  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 22610kB
23. 162.242 162.242 ↓ 3.0 190,323 1

Seq Scan on line_items li (cost=0.00..9,837.95 rows=63,442 width=66) (actual time=0.025..162.242 rows=190,323 loops=1)

  • Filter: (is_active AND (created_at >= $2))
  • Rows Removed by Filter: 12414
24. 3.794 33.307 ↓ 1.0 5,443 1

Hash (cost=1,412.18..1,412.18 rows=5,441 width=140) (actual time=33.307..33.307 rows=5,443 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 963kB
25. 6.596 29.513 ↓ 1.0 5,443 1

Hash Right Join (cost=522.63..1,412.18 rows=5,441 width=140) (actual time=7.320..29.513 rows=5,443 loops=1)

  • Hash Cond: (ciic.chargeable_item_id = ci.id)
26. 8.703 15.628 ↑ 1.0 5,266 1

Merge Join (cost=0.56..712.39 rows=5,266 width=10) (actual time=0.020..15.628 rows=5,266 loops=1)

  • Merge Cond: (ic.id = ciic.insurance_code_id)
27. 3.455 3.455 ↑ 1.0 5,259 1

Index Scan using insurance_codes_pkey on insurance_codes ic (cost=0.28..266.20 rows=5,259 width=10) (actual time=0.009..3.455 rows=5,259 loops=1)

28. 3.470 3.470 ↑ 1.0 5,266 1

Index Scan using idx_chargeable_items_insurance_codes_insurance_code_id on chargeable_items_insurance_codes ciic (cost=0.28..261.90 rows=5,266 width=8) (actual time=0.007..3.470 rows=5,266 loops=1)

29. 3.593 7.289 ↑ 1.0 5,441 1

Hash (cost=345.23..345.23 rows=5,441 width=134) (actual time=7.289..7.289 rows=5,441 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 932kB
30. 3.696 3.696 ↑ 1.0 5,441 1

Seq Scan on chargeable_items ci (cost=0.00..345.23 rows=5,441 width=134) (actual time=0.006..3.696 rows=5,441 loops=1)

31. 16.356 116.018 ↑ 2.3 25,939 1

Hash (cost=5,105.71..5,105.71 rows=58,387 width=26) (actual time=116.018..116.018 rows=25,939 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2011kB
32. 57.818 99.662 ↑ 2.3 25,939 1

Hash Join (cost=1.52..5,105.71 rows=58,387 width=26) (actual time=0.031..99.662 rows=25,939 loops=1)

  • Hash Cond: (v.visit_status_id = vs.id)
33. 41.826 41.826 ↑ 1.0 75,069 1

Seq Scan on visits v (cost=0.00..3,071.07 rows=75,069 width=21) (actual time=0.006..41.826 rows=75,069 loops=1)

34. 0.007 0.018 ↑ 1.0 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.011 0.011 ↑ 1.0 7 1

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

  • Filter: ((name)::text <> ALL ('{PreExisting,Historical}'::text[]))
  • Rows Removed by Filter: 2
36. 9.556 20.273 ↑ 1.0 16,195 1

Hash (cost=762.85..762.85 rows=16,195 width=12) (actual time=20.273..20.273 rows=16,195 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 888kB
37. 10.717 10.717 ↑ 1.0 16,195 1

Seq Scan on claims c (cost=0.00..762.85 rows=16,195 width=12) (actual time=0.012..10.717 rows=16,195 loops=1)

38. 11.609 27.294 ↑ 1.1 16,519 1

Hash (cost=1,109.20..1,109.20 rows=18,900 width=12) (actual time=27.294..27.294 rows=16,519 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 966kB
39. 15.685 15.685 ↓ 1.1 19,917 1

Seq Scan on calendar_events ce (cost=0.00..1,109.20 rows=18,900 width=12) (actual time=0.021..15.685 rows=19,917 loops=1)

  • Filter: ((appointment_status_id IS NULL) OR (appointment_status_id <> $1))
  • Rows Removed by Filter: 2489
40. 10.853 22.157 ↑ 1.0 18,837 1

Hash (cost=747.11..747.11 rows=18,837 width=18) (actual time=22.157..22.157 rows=18,837 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1146kB
41. 11.304 11.304 ↑ 1.0 18,837 1

Seq Scan on treatment_plans tp (cost=0.00..747.11 rows=18,837 width=18) (actual time=0.010..11.304 rows=18,837 loops=1)

42.          

SubPlan (forHash Join)

43. 224.095 448.190 ↑ 1.0 1 44,819

Aggregate (cost=2.45..2.48 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=44,819)

44. 224.095 224.095 ↓ 1.2 5 44,819

Index Only Scan using idx_visits_treatment_plan_id on visits v2 (cost=0.29..2.44 rows=4 width=4) (actual time=0.002..0.005 rows=5 loops=44,819)

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

CTE linked_line

46. 0.001 1,927.165 ↓ 0.0 0 1

Unique (cost=585,302.54..586,272.18 rows=190,327 width=279) (actual time=1,927.165..1,927.165 rows=0 loops=1)

47.          

Initplan (forUnique)

48. 0.000 0.000 ↓ 0.0 0

Seq Scan on appointment_statuses appointment_statuses_1 (cost=0.00..1.23 rows=1 width=4) (never executed)

  • Filter: ((name)::text = 'Delete'::text)
49. 0.013 1,927.164 ↓ 0.0 0 1

Sort (cost=585,301.32..585,786.14 rows=193,928 width=279) (actual time=1,927.164..1,927.164 rows=0 loops=1)

  • Sort Key: li_1.id
  • Sort Method: quicksort Memory: 25kB
50. 0.063 1,927.151 ↓ 0.0 0 1

Hash Join (cost=65,854.18..568,269.43 rows=193,928 width=279) (actual time=1,927.151..1,927.151 rows=0 loops=1)

  • Hash Cond: (v_1.treatment_plan_id = tp_1.id)
51. 0.000 1,826.158 ↓ 0.0 0 1

Hash Left Join (cost=58,265.19..72,005.33 rows=74,016 width=248) (actual time=1,826.158..1,826.158 rows=0 loops=1)

  • Hash Cond: (v_1.id = ce_1.visit_id)
52. 0.002 1,826.158 ↓ 0.0 0 1

Hash Left Join (cost=56,541.74..69,191.93 rows=74,016 width=240) (actual time=1,826.158..1,826.158 rows=0 loops=1)

  • Hash Cond: (cli_1.claim_id = c_1.id)
53. 84.267 1,826.156 ↓ 0.0 0 1

Hash Join (cost=55,252.56..65,404.70 rows=74,016 width=232) (actual time=1,826.156..1,826.156 rows=0 loops=1)

  • Hash Cond: (li_1.visit_id = v_1.id)
54. 211.130 1,627.935 ↓ 1.7 160,785 1

Merge Anti Join (cost=48,249.27..55,849.57 rows=95,164 width=210) (actual time=770.498..1,627.935 rows=160,785 loops=1)

  • Merge Cond: (li_1.id = tp_25_2.line_item_id)
55. 232.579 1,365.770 ↓ 1.1 205,604 1

Merge Left Join (cost=41,442.12..46,930.16 rows=190,327 width=210) (actual time=734.388..1,365.770 rows=205,604 loops=1)

  • Merge Cond: (li_1.id = cli_1.line_item_id)
56. 235.104 1,097.951 ↑ 1.0 190,323 1

Merge Left Join (cost=41,441.83..42,917.73 rows=190,327 width=206) (actual time=734.377..1,097.951 rows=190,323 loops=1)

  • Merge Cond: (li_1.id = te_1.line_item_id)
57. 301.066 708.636 ↑ 1.0 190,323 1

Sort (cost=34,033.55..34,509.37 rows=190,327 width=198) (actual time=594.186..708.636 rows=190,323 loops=1)

  • Sort Key: li_1.id
  • Sort Method: quicksort Memory: 62319kB
58. 226.615 407.570 ↑ 1.0 190,323 1

Hash Join (cost=1,589.02..17,343.66 rows=190,327 width=198) (actual time=34.259..407.570 rows=190,323 loops=1)

  • Hash Cond: (li_1.chargeable_item_id = ci_1.id)
59. 146.758 146.758 ↑ 1.0 190,323 1

Seq Scan on line_items li_1 (cost=0.00..9,331.11 rows=190,327 width=66) (actual time=0.011..146.758 rows=190,323 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 12414
60. 4.068 34.197 ↓ 1.0 5,443 1

Hash (cost=1,412.18..1,412.18 rows=5,441 width=140) (actual time=34.197..34.197 rows=5,443 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 963kB
61. 6.486 30.129 ↓ 1.0 5,443 1

Hash Right Join (cost=522.63..1,412.18 rows=5,441 width=140) (actual time=7.666..30.129 rows=5,443 loops=1)

  • Hash Cond: (ciic_1.chargeable_item_id = ci_1.id)
62. 8.855 16.040 ↑ 1.0 5,266 1

Merge Join (cost=0.56..712.39 rows=5,266 width=10) (actual time=0.026..16.040 rows=5,266 loops=1)

  • Merge Cond: (ic_1.id = ciic_1.insurance_code_id)
63. 3.669 3.669 ↑ 1.0 5,259 1

Index Scan using insurance_codes_pkey on insurance_codes ic_1 (cost=0.28..266.20 rows=5,259 width=10) (actual time=0.013..3.669 rows=5,259 loops=1)

64. 3.516 3.516 ↑ 1.0 5,266 1

Index Scan using idx_chargeable_items_insurance_codes_insurance_code_id on chargeable_items_insurance_codes ciic_1 (cost=0.28..261.90 rows=5,266 width=8) (actual time=0.008..3.516 rows=5,266 loops=1)

65. 3.891 7.603 ↑ 1.0 5,441 1

Hash (cost=345.23..345.23 rows=5,441 width=134) (actual time=7.603..7.603 rows=5,441 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 932kB
66. 3.712 3.712 ↑ 1.0 5,441 1

Seq Scan on chargeable_items ci_1 (cost=0.00..345.23 rows=5,441 width=134) (actual time=0.007..3.712 rows=5,441 loops=1)

67. 28.986 154.211 ↑ 1.0 24,259 1

Sort (cost=7,408.28..7,469.39 rows=24,445 width=12) (actual time=140.185..154.211 rows=24,259 loops=1)

  • Sort Key: te_1.line_item_id
  • Sort Method: quicksort Memory: 1906kB
68. 52.992 125.225 ↑ 1.0 24,259 1

Hash Join (cost=953.52..5,626.57 rows=24,445 width=12) (actual time=11.977..125.225 rows=24,259 loops=1)

  • Hash Cond: (te_1.transaction_id = t_1.id)
69. 60.305 60.305 ↑ 1.0 65,209 1

Index Scan using idx_transaction_entries_line_item_id on transaction_entries te_1 (cost=0.29..3,695.46 rows=65,209 width=8) (actual time=0.016..60.305 rows=65,209 loops=1)

  • Index Cond: (line_item_id IS NOT NULL)
70. 5.055 11.928 ↑ 1.0 8,586 1

Hash (cost=674.18..674.18 rows=8,586 width=12) (actual time=11.928..11.928 rows=8,586 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 497kB
71. 6.193 6.873 ↑ 1.0 8,586 1

Bitmap Heap Scan on transactions t_1 (cost=129.40..674.18 rows=8,586 width=12) (actual time=0.716..6.873 rows=8,586 loops=1)

  • Recheck Cond: ((type)::text = ANY ('{"Add row correction",Invoice,"Add row adjustment"}'::text[]))
  • Heap Blocks: exact=255
72. 0.680 0.680 ↑ 1.0 8,586 1

Bitmap Index Scan on idx_transactions_type (cost=0.00..127.26 rows=8,586 width=0) (actual time=0.680..0.680 rows=8,586 loops=1)

  • Index Cond: ((type)::text = ANY ('{"Add row correction",Invoice,"Add row adjustment"}'::text[]))
73. 35.240 35.240 ↑ 1.0 40,817 1

Index Scan using idx_claims_line_items_line_item_id on claims_line_items cli_1 (cost=0.29..2,285.03 rows=40,817 width=8) (actual time=0.008..35.240 rows=40,817 loops=1)

74. 32.428 51.035 ↑ 1.7 29,538 1

Sort (cost=6,807.15..6,930.51 rows=49,344 width=4) (actual time=34.569..51.035 rows=29,538 loops=1)

  • Sort Key: tp_25_2.line_item_id
  • Sort Method: quicksort Memory: 2153kB
75. 18.607 18.607 ↑ 1.7 29,538 1

CTE Scan on tp_25 tp_25_2 (cost=0.00..2,960.64 rows=49,344 width=4) (actual time=0.002..18.607 rows=29,538 loops=1)

76. 16.772 113.954 ↑ 2.3 25,939 1

Hash (cost=5,105.71..5,105.71 rows=58,387 width=26) (actual time=113.954..113.954 rows=25,939 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2011kB
77. 56.664 97.182 ↑ 2.3 25,939 1

Hash Join (cost=1.52..5,105.71 rows=58,387 width=26) (actual time=0.040..97.182 rows=25,939 loops=1)

  • Hash Cond: (v_1.visit_status_id = vs_1.id)
78. 40.495 40.495 ↑ 1.0 75,069 1

Seq Scan on visits v_1 (cost=0.00..3,071.07 rows=75,069 width=21) (actual time=0.010..40.495 rows=75,069 loops=1)

79. 0.010 0.023 ↑ 1.0 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 0.013 0.013 ↑ 1.0 7 1

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

  • Filter: ((name)::text <> ALL ('{PreExisting,Historical}'::text[]))
  • Rows Removed by Filter: 2
81. 0.000 0.000 ↓ 0.0 0

Hash (cost=762.85..762.85 rows=16,195 width=12) (never executed)

82. 0.000 0.000 ↓ 0.0 0

Seq Scan on claims c_1 (cost=0.00..762.85 rows=16,195 width=12) (never executed)

83. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,109.20..1,109.20 rows=18,900 width=12) (never executed)

84. 0.000 0.000 ↓ 0.0 0

Seq Scan on calendar_events ce_1 (cost=0.00..1,109.20 rows=18,900 width=12) (never executed)

  • Filter: ((appointment_status_id IS NULL) OR (appointment_status_id <> $5))
85. 18.910 100.930 ↑ 1.7 29,538 1

Hash (cost=5,985.31..5,985.31 rows=49,344 width=22) (actual time=100.930..100.930 rows=29,538 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2161kB
86. 40.716 82.020 ↑ 1.7 29,538 1

Hash Join (cost=1,359.31..5,985.31 rows=49,344 width=22) (actual time=22.937..82.020 rows=29,538 loops=1)

  • Hash Cond: (tp_25_1.treatment_plan_id = tp_1.id)
87. 18.508 18.508 ↑ 1.7 29,538 1

CTE Scan on tp_25 tp_25_1 (cost=0.00..2,960.64 rows=49,344 width=4) (actual time=0.001..18.508 rows=29,538 loops=1)

88. 11.321 22.796 ↑ 1.0 18,837 1

Hash (cost=747.11..747.11 rows=18,837 width=18) (actual time=22.796..22.796 rows=18,837 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1146kB
89. 11.475 11.475 ↑ 1.0 18,837 1

Seq Scan on treatment_plans tp_1 (cost=0.00..747.11 rows=18,837 width=18) (actual time=0.018..11.475 rows=18,837 loops=1)

90.          

SubPlan (forHash Join)

91. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.45..2.48 rows=1 width=8) (never executed)

92. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_visits_treatment_plan_id on visits v2_1 (cost=0.29..2.44 rows=4 width=4) (never executed)

  • Index Cond: (treatment_plan_id = v_1.treatment_plan_id)
  • Heap Fetches: 0
93. 2,405.745 2,405.745 ↑ 1.7 29,538 1

CTE Scan on tp_25 (cost=0.00..2,960.64 rows=49,344 width=5,805) (actual time=2,303.929..2,405.745 rows=29,538 loops=1)

94. 1,927.167 1,927.167 ↓ 0.0 0 1

CTE Scan on linked_line (cost=0.00..11,419.62 rows=190,327 width=5,805) (actual time=1,927.167..1,927.167 rows=0 loops=1)

Planning time : 7.978 ms
Execution time : 4,375.243 ms