explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DHoQ : Optimization for: plan #pbyR

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 5.786 179.664 ↑ 1.0 1,087 1

Subquery Scan on dv (cost=99,722.07..127,113.85 rows=1,096 width=16) (actual time=108.867..179.664 rows=1,087 loops=1)

2.          

Initplan (forSubquery Scan)

3. 1.375 54.094 ↑ 1.0 1 1

Aggregate (cost=33,261.69..33,261.71 rows=1 width=40) (actual time=54.094..54.094 rows=1 loops=1)

4. 52.719 52.719 ↑ 1.0 1,087 1

CTE Scan on dentalvisits dentalvisits_1 (cost=33,209.63..33,237.03 rows=1,096 width=1,749) (actual time=0.380..52.719 rows=1,087 loops=1)

5.          

CTE dentalvisits

6. 6.783 47.841 ↑ 1.0 1,087 1

Hash Join (cost=15.05..33,209.63 rows=1,096 width=244) (actual time=0.374..47.841 rows=1,087 loops=1)

  • Hash Cond: (pv.clinic_id = mst_am_facility_address.facility_id)
7. 3.186 4.207 ↑ 1.4 1,087 1

Hash Join (cost=7.02..177.01 rows=1,576 width=236) (actual time=0.127..4.207 rows=1,087 loops=1)

  • Hash Cond: (pv.service_id = ds.service_id)
8. 0.918 0.918 ↓ 1.0 3,599 1

Seq Scan on trx_pm_patient_visit pv (cost=0.00..140.81 rows=3,581 width=236) (actual time=0.003..0.918 rows=3,599 loops=1)

9. 0.006 0.103 ↑ 1.0 22 1

Hash (cost=6.74..6.74 rows=22 width=4) (actual time=0.103..0.103 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
10. 0.008 0.097 ↑ 1.0 22 1

Subquery Scan on ds (cost=6.47..6.74 rows=22 width=4) (actual time=0.088..0.097 rows=22 loops=1)

11. 0.019 0.089 ↑ 1.0 22 1

Sort (cost=6.47..6.52 rows=22 width=29) (actual time=0.086..0.089 rows=22 loops=1)

  • Sort Key: ref_pm_service.service_id
  • Sort Method: quicksort Memory: 26kB
12. 0.070 0.070 ↑ 1.0 22 1

Seq Scan on ref_pm_service (cost=0.00..5.97 rows=22 width=29) (actual time=0.016..0.070 rows=22 loops=1)

  • Filter: ((service_code)::text = ANY ('{DPHC,DHCO,DSH,DAN,DHCI,DTL,DORT,DOCC,DORV,DPCC,DPRV,DRES,DRCC,DRSV,DPEV,SNDV,DCC,DPER,DOSV,DPH}'::text[]))
  • Rows Removed by Filter: 63
13. 0.005 0.169 ↓ 1.3 21 1

Hash (cost=7.84..7.84 rows=16 width=12) (actual time=0.169..0.169 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
14. 0.053 0.164 ↓ 1.3 21 1

Hash Join (cost=3.90..7.84 rows=16 width=12) (actual time=0.125..0.164 rows=21 loops=1)

  • Hash Cond: (d.district_id = mst_am_facility_address.district_id)
15. 0.021 0.021 ↑ 1.0 128 1

Seq Scan on ref_co_district d (cost=0.00..3.29 rows=129 width=4) (actual time=0.005..0.021 rows=128 loops=1)

16. 0.006 0.090 ↓ 1.3 21 1

Hash (cost=3.70..3.70 rows=16 width=12) (actual time=0.090..0.090 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
17. 0.027 0.084 ↓ 1.3 21 1

Hash Join (cost=3.11..3.70 rows=16 width=12) (actual time=0.063..0.084 rows=21 loops=1)

  • Hash Cond: (mst_am_facility_address.state_id = s.state_id)
18. 0.006 0.046 ↑ 1.1 21 1

Unique (cost=1.75..1.87 rows=23 width=12) (actual time=0.037..0.046 rows=21 loops=1)

19. 0.027 0.040 ↑ 1.0 23 1

Sort (cost=1.75..1.81 rows=23 width=12) (actual time=0.037..0.040 rows=23 loops=1)

  • Sort Key: mst_am_facility_address.facility_id
  • Sort Method: quicksort Memory: 26kB
20. 0.013 0.013 ↑ 1.0 23 1

Seq Scan on mst_am_facility_address (cost=0.00..1.23 rows=23 width=12) (actual time=0.003..0.013 rows=23 loops=1)

21. 0.005 0.011 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=4) (actual time=0.011..0.011 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
22. 0.006 0.006 ↑ 1.0 16 1

Seq Scan on ref_co_state s (cost=0.00..1.16 rows=16 width=4) (actual time=0.003..0.006 rows=16 loops=1)

23.          

SubPlan (forHash Join)

24. 3.261 16.305 ↑ 1.0 1 1,087

Aggregate (cost=10.02..10.03 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1,087)

25. 13.044 13.044 ↓ 2.0 2 1,087

Index Scan using trx_pm_patient_visit_patient_id_idx on trx_pm_patient_visit (cost=0.28..10.02 rows=1 width=8) (actual time=0.007..0.012 rows=2 loops=1,087)

  • Index Cond: (patient_id = pv.patient_id)
  • Filter: ((clinic_id = pv.clinic_id) AND (date_part('year'::text, visit_date_time) = date_part('year'::text, pv.visit_date_time)))
  • Rows Removed by Filter: 3
26. 0.994 7.455 ↑ 1.0 1 497

Aggregate (cost=10.02..10.03 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=497)

27. 6.461 6.461 ↓ 4.0 4 497

Index Scan using trx_pm_patient_visit_patient_id_idx on trx_pm_patient_visit trx_pm_patient_visit_1 (cost=0.28..10.02 rows=1 width=8) (actual time=0.005..0.013 rows=4 loops=497)

  • Index Cond: (patient_id = pv.patient_id)
  • Filter: ((clinic_id = pv.clinic_id) AND (date_part('year'::text, visit_date_time) = date_part('year'::text, pv.visit_date_time)))
  • Rows Removed by Filter: 4
28. 0.497 12.922 ↑ 1.0 1 497

Limit (cost=10.03..10.04 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=497)

29. 3.479 12.425 ↑ 1.0 1 497

Sort (cost=10.03..10.04 rows=1 width=8) (actual time=0.025..0.025 rows=1 loops=497)

  • Sort Key: trx_pm_patient_visit_2.visit_date_time
  • Sort Method: quicksort Memory: 25kB
30. 8.946 8.946 ↓ 4.0 4 497

Index Scan using trx_pm_patient_visit_patient_id_idx on trx_pm_patient_visit trx_pm_patient_visit_2 (cost=0.28..10.02 rows=1 width=8) (actual time=0.009..0.018 rows=4 loops=497)

  • Index Cond: (patient_id = pv.patient_id)
  • Filter: ((clinic_id = pv.clinic_id) AND (date_part('year'::text, visit_date_time) = date_part('year'::text, pv.visit_date_time)))
  • Rows Removed by Filter: 4
31. 0.283 54.032 ↑ 1.0 1 1

Aggregate (cost=33,250.73..33,250.74 rows=1 width=8) (actual time=54.032..54.032 rows=1 loops=1)

32. 53.749 53.749 ↑ 1.0 1,087 1

CTE Scan on dentalvisits dentalvisits_2 (cost=33,209.63..33,237.03 rows=1,096 width=1,749) (actual time=0.426..53.749 rows=1,087 loops=1)

33.          

CTE dentalvisits

34. 4.004 48.170 ↑ 1.0 1,087 1

Hash Join (cost=15.05..33,209.63 rows=1,096 width=244) (actual time=0.413..48.170 rows=1,087 loops=1)

  • Hash Cond: (pv_1.clinic_id = mst_am_facility_address_1.facility_id)
35. 3.030 3.940 ↑ 1.4 1,087 1

Hash Join (cost=7.02..177.01 rows=1,576 width=236) (actual time=0.163..3.940 rows=1,087 loops=1)

  • Hash Cond: (pv_1.service_id = ds_1.service_id)
36. 0.782 0.782 ↓ 1.0 3,599 1

Seq Scan on trx_pm_patient_visit pv_1 (cost=0.00..140.81 rows=3,581 width=236) (actual time=0.003..0.782 rows=3,599 loops=1)

37. 0.008 0.128 ↑ 1.0 22 1

Hash (cost=6.74..6.74 rows=22 width=4) (actual time=0.128..0.128 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
38. 0.005 0.120 ↑ 1.0 22 1

Subquery Scan on ds_1 (cost=6.47..6.74 rows=22 width=4) (actual time=0.112..0.120 rows=22 loops=1)

39. 0.042 0.115 ↑ 1.0 22 1

Sort (cost=6.47..6.52 rows=22 width=29) (actual time=0.111..0.115 rows=22 loops=1)

  • Sort Key: ref_pm_service_1.service_id
  • Sort Method: quicksort Memory: 26kB
40. 0.073 0.073 ↑ 1.0 22 1

Seq Scan on ref_pm_service ref_pm_service_1 (cost=0.00..5.97 rows=22 width=29) (actual time=0.020..0.073 rows=22 loops=1)

  • Filter: ((service_code)::text = ANY ('{DPHC,DHCO,DSH,DAN,DHCI,DTL,DORT,DOCC,DORV,DPCC,DPRV,DRES,DRCC,DRSV,DPEV,SNDV,DCC,DPER,DOSV,DPH}'::text[]))
  • Rows Removed by Filter: 63
41. 0.009 0.158 ↓ 1.3 21 1

Hash (cost=7.84..7.84 rows=16 width=12) (actual time=0.158..0.158 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
42. 0.038 0.149 ↓ 1.3 21 1

Hash Join (cost=3.90..7.84 rows=16 width=12) (actual time=0.114..0.149 rows=21 loops=1)

  • Hash Cond: (d_1.district_id = mst_am_facility_address_1.district_id)
43. 0.027 0.027 ↑ 1.0 128 1

Seq Scan on ref_co_district d_1 (cost=0.00..3.29 rows=129 width=4) (actual time=0.007..0.027 rows=128 loops=1)

44. 0.008 0.084 ↓ 1.3 21 1

Hash (cost=3.70..3.70 rows=16 width=12) (actual time=0.084..0.084 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
45. 0.024 0.076 ↓ 1.3 21 1

Hash Join (cost=3.11..3.70 rows=16 width=12) (actual time=0.056..0.076 rows=21 loops=1)

  • Hash Cond: (mst_am_facility_address_1.state_id = s_1.state_id)
46. 0.009 0.041 ↑ 1.1 21 1

Unique (cost=1.75..1.87 rows=23 width=12) (actual time=0.032..0.041 rows=21 loops=1)

47. 0.019 0.032 ↑ 1.0 23 1

Sort (cost=1.75..1.81 rows=23 width=12) (actual time=0.030..0.032 rows=23 loops=1)

  • Sort Key: mst_am_facility_address_1.facility_id
  • Sort Method: quicksort Memory: 26kB
48. 0.013 0.013 ↑ 1.0 23 1

Seq Scan on mst_am_facility_address mst_am_facility_address_1 (cost=0.00..1.23 rows=23 width=12) (actual time=0.004..0.013 rows=23 loops=1)

49. 0.006 0.011 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=4) (actual time=0.011..0.011 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
50. 0.005 0.005 ↑ 1.0 16 1

Seq Scan on ref_co_state s_1 (cost=0.00..1.16 rows=16 width=4) (actual time=0.003..0.005 rows=16 loops=1)

51.          

SubPlan (forHash Join)

52. 3.261 15.218 ↑ 1.0 1 1,087

Aggregate (cost=10.02..10.03 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1,087)

53. 11.957 11.957 ↓ 2.0 2 1,087

Index Scan using trx_pm_patient_visit_patient_id_idx on trx_pm_patient_visit trx_pm_patient_visit_3 (cost=0.28..10.02 rows=1 width=8) (actual time=0.007..0.011 rows=2 loops=1,087)

  • Index Cond: (patient_id = pv_1.patient_id)
  • Filter: ((clinic_id = pv_1.clinic_id) AND (date_part('year'::text, visit_date_time) = date_part('year'::text, pv_1.visit_date_time)))
  • Rows Removed by Filter: 3
54. 0.994 7.952 ↑ 1.0 1 497

Aggregate (cost=10.02..10.03 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=497)

55. 6.958 6.958 ↓ 4.0 4 497

Index Scan using trx_pm_patient_visit_patient_id_idx on trx_pm_patient_visit trx_pm_patient_visit_4 (cost=0.28..10.02 rows=1 width=8) (actual time=0.006..0.014 rows=4 loops=497)

  • Index Cond: (patient_id = pv_1.patient_id)
  • Filter: ((clinic_id = pv_1.clinic_id) AND (date_part('year'::text, visit_date_time) = date_part('year'::text, pv_1.visit_date_time)))
  • Rows Removed by Filter: 4
56. 0.497 16.898 ↑ 1.0 1 497

Limit (cost=10.03..10.04 rows=1 width=8) (actual time=0.034..0.034 rows=1 loops=497)

57. 4.970 16.401 ↑ 1.0 1 497

Sort (cost=10.03..10.04 rows=1 width=8) (actual time=0.033..0.033 rows=1 loops=497)

  • Sort Key: trx_pm_patient_visit_5.visit_date_time
  • Sort Method: quicksort Memory: 25kB
58. 11.431 11.431 ↓ 4.0 4 497

Index Scan using trx_pm_patient_visit_patient_id_idx on trx_pm_patient_visit trx_pm_patient_visit_5 (cost=0.28..10.02 rows=1 width=8) (actual time=0.013..0.023 rows=4 loops=497)

  • Index Cond: (patient_id = pv_1.patient_id)
  • Filter: ((clinic_id = pv_1.clinic_id) AND (date_part('year'::text, visit_date_time) = date_part('year'::text, pv_1.visit_date_time)))
  • Rows Removed by Filter: 4
59. 54.402 54.402 ↑ 1.0 1,087 1

CTE Scan on dentalvisits (cost=33,209.63..33,237.03 rows=1,096 width=1,749) (actual time=0.599..54.402 rows=1,087 loops=1)

60.          

CTE dentalvisits

61. 5.708 50.161 ↑ 1.0 1,087 1

Hash Join (cost=15.05..33,209.63 rows=1,096 width=244) (actual time=0.589..50.161 rows=1,087 loops=1)

  • Hash Cond: (pv_2.clinic_id = mst_am_facility_address_2.facility_id)
62. 3.607 4.682 ↑ 1.4 1,087 1

Hash Join (cost=7.02..177.01 rows=1,576 width=236) (actual time=0.190..4.682 rows=1,087 loops=1)

  • Hash Cond: (pv_2.service_id = ds_2.service_id)
63. 0.919 0.919 ↓ 1.0 3,599 1

Seq Scan on trx_pm_patient_visit pv_2 (cost=0.00..140.81 rows=3,581 width=236) (actual time=0.009..0.919 rows=3,599 loops=1)

64. 0.015 0.156 ↑ 1.0 22 1

Hash (cost=6.74..6.74 rows=22 width=4) (actual time=0.156..0.156 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
65. 0.007 0.141 ↑ 1.0 22 1

Subquery Scan on ds_2 (cost=6.47..6.74 rows=22 width=4) (actual time=0.132..0.141 rows=22 loops=1)

66. 0.040 0.134 ↑ 1.0 22 1

Sort (cost=6.47..6.52 rows=22 width=29) (actual time=0.132..0.134 rows=22 loops=1)

  • Sort Key: ref_pm_service_2.service_id
  • Sort Method: quicksort Memory: 26kB
67. 0.094 0.094 ↑ 1.0 22 1

Seq Scan on ref_pm_service ref_pm_service_2 (cost=0.00..5.97 rows=22 width=29) (actual time=0.023..0.094 rows=22 loops=1)

  • Filter: ((service_code)::text = ANY ('{DPHC,DHCO,DSH,DAN,DHCI,DTL,DORT,DOCC,DORV,DPCC,DPRV,DRES,DRCC,DRSV,DPEV,SNDV,DCC,DPER,DOSV,DPH}'::text[]))
  • Rows Removed by Filter: 63
68. 0.009 0.200 ↓ 1.3 21 1

Hash (cost=7.84..7.84 rows=16 width=12) (actual time=0.200..0.200 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
69. 0.052 0.191 ↓ 1.3 21 1

Hash Join (cost=3.90..7.84 rows=16 width=12) (actual time=0.149..0.191 rows=21 loops=1)

  • Hash Cond: (d_2.district_id = mst_am_facility_address_2.district_id)
70. 0.028 0.028 ↑ 1.0 128 1

Seq Scan on ref_co_district d_2 (cost=0.00..3.29 rows=129 width=4) (actual time=0.007..0.028 rows=128 loops=1)

71. 0.013 0.111 ↓ 1.3 21 1

Hash (cost=3.70..3.70 rows=16 width=12) (actual time=0.111..0.111 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
72. 0.035 0.098 ↓ 1.3 21 1

Hash Join (cost=3.11..3.70 rows=16 width=12) (actual time=0.077..0.098 rows=21 loops=1)

  • Hash Cond: (mst_am_facility_address_2.state_id = s_2.state_id)
73. 0.007 0.050 ↑ 1.1 21 1

Unique (cost=1.75..1.87 rows=23 width=12) (actual time=0.040..0.050 rows=21 loops=1)

74. 0.030 0.043 ↑ 1.0 23 1

Sort (cost=1.75..1.81 rows=23 width=12) (actual time=0.040..0.043 rows=23 loops=1)

  • Sort Key: mst_am_facility_address_2.facility_id
  • Sort Method: quicksort Memory: 26kB
75. 0.013 0.013 ↑ 1.0 23 1

Seq Scan on mst_am_facility_address mst_am_facility_address_2 (cost=0.00..1.23 rows=23 width=12) (actual time=0.005..0.013 rows=23 loops=1)

76. 0.006 0.013 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=4) (actual time=0.013..0.013 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
77. 0.007 0.007 ↑ 1.0 16 1

Seq Scan on ref_co_state s_2 (cost=0.00..1.16 rows=16 width=4) (actual time=0.004..0.007 rows=16 loops=1)

78.          

SubPlan (forHash Join)

79. 2.174 15.218 ↑ 1.0 1 1,087

Aggregate (cost=10.02..10.03 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1,087)

80. 13.044 13.044 ↓ 2.0 2 1,087

Index Scan using trx_pm_patient_visit_patient_id_idx on trx_pm_patient_visit trx_pm_patient_visit_6 (cost=0.28..10.02 rows=1 width=8) (actual time=0.008..0.012 rows=2 loops=1,087)

  • Index Cond: (patient_id = pv_2.patient_id)
  • Filter: ((clinic_id = pv_2.clinic_id) AND (date_part('year'::text, visit_date_time) = date_part('year'::text, pv_2.visit_date_time)))
  • Rows Removed by Filter: 3
81. 0.994 7.455 ↑ 1.0 1 497

Aggregate (cost=10.02..10.03 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=497)

82. 6.461 6.461 ↓ 4.0 4 497

Index Scan using trx_pm_patient_visit_patient_id_idx on trx_pm_patient_visit trx_pm_patient_visit_7 (cost=0.28..10.02 rows=1 width=8) (actual time=0.006..0.013 rows=4 loops=497)

  • Index Cond: (patient_id = pv_2.patient_id)
  • Filter: ((clinic_id = pv_2.clinic_id) AND (date_part('year'::text, visit_date_time) = date_part('year'::text, pv_2.visit_date_time)))
  • Rows Removed by Filter: 4
83. 0.994 16.898 ↑ 1.0 1 497

Limit (cost=10.03..10.04 rows=1 width=8) (actual time=0.034..0.034 rows=1 loops=497)

84. 4.970 15.904 ↑ 1.0 1 497

Sort (cost=10.03..10.04 rows=1 width=8) (actual time=0.032..0.032 rows=1 loops=497)

  • Sort Key: trx_pm_patient_visit_8.visit_date_time
  • Sort Method: quicksort Memory: 25kB
85. 10.934 10.934 ↓ 4.0 4 497

Index Scan using trx_pm_patient_visit_patient_id_idx on trx_pm_patient_visit trx_pm_patient_visit_8 (cost=0.28..10.02 rows=1 width=8) (actual time=0.008..0.022 rows=4 loops=497)

  • Index Cond: (patient_id = pv_2.patient_id)
  • Filter: ((clinic_id = pv_2.clinic_id) AND (date_part('year'::text, visit_date_time) = date_part('year'::text, pv_2.visit_date_time)))
  • Rows Removed by Filter: 4
86.          

SubPlan (forSubquery Scan)

87. 1.087 7.609 ↑ 1.0 1 1,087

Result (cost=8.31..8.32 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1,087)

88.          

Initplan (forResult)

89. 3.261 6.522 ↑ 1.0 1 1,087

Aggregate (cost=8.30..8.31 rows=1 width=3) (actual time=0.005..0.006 rows=1 loops=1,087)

90. 3.261 3.261 ↓ 0.0 0 1,087

Index Scan using trx_bm_bill_visit_id_idx on trx_bm_bill (cost=0.28..8.30 rows=1 width=3) (actual time=0.003..0.003 rows=0 loops=1,087)

  • Index Cond: (visit_id = dv.visit_id)
  • Filter: (is_canceled IS FALSE)
91. 1.087 3.261 ↑ 1.0 1 1,087

Aggregate (cost=8.30..8.31 rows=1 width=3) (actual time=0.003..0.003 rows=1 loops=1,087)

92. 2.174 2.174 ↓ 0.0 0 1,087

Index Scan using trx_bm_bill_visit_id_idx on trx_bm_bill trx_bm_bill_1 (cost=0.28..8.30 rows=1 width=3) (actual time=0.002..0.002 rows=0 loops=1,087)

  • Index Cond: (visit_id = dv.visit_id)
  • Filter: (is_canceled IS FALSE)
93. 0.192 0.480 ↑ 1.0 1 96

Aggregate (cost=8.30..8.31 rows=1 width=3) (actual time=0.005..0.005 rows=1 loops=96)

94. 0.288 0.288 ↑ 1.0 1 96

Index Scan using trx_bm_bill_visit_id_idx on trx_bm_bill trx_bm_bill_2 (cost=0.28..8.30 rows=1 width=3) (actual time=0.002..0.003 rows=1 loops=96)

  • Index Cond: (visit_id = dv.visit_id)
  • Filter: (is_canceled IS FALSE)