explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pbyR

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 11.419 3,138.040 ↑ 1.0 1,087 1

Subquery Scan on dv (cost=99,722.07..528,915.67 rows=1,096 width=16) (actual time=106.529..3,138.040 rows=1,087 loops=1)

2.          

Initplan (forSubquery Scan)

3. 1.217 53.892 ↑ 1.0 1 1

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

4. 52.675 52.675 ↑ 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.445..52.675 rows=1,087 loops=1)

5.          

CTE dentalvisits

6. 4.024 48.016 ↑ 1.0 1,087 1

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

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

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

  • Hash Cond: (pv.service_id = ds.service_id)
8. 1.546 1.546 ↓ 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..1.546 rows=3,599 loops=1)

9. 0.013 0.135 ↑ 1.0 22 1

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

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

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

11. 0.029 0.116 ↑ 1.0 22 1

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

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

Seq Scan on ref_pm_service (cost=0.00..5.97 rows=22 width=29) (actual time=0.017..0.087 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.011 0.183 ↓ 1.3 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
14. 0.046 0.172 ↓ 1.3 21 1

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

  • Hash Cond: (d.district_id = mst_am_facility_address.district_id)
15. 0.022 0.022 ↑ 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.022 rows=128 loops=1)

16. 0.009 0.104 ↓ 1.3 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
17. 0.034 0.095 ↓ 1.3 21 1

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

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

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

19. 0.026 0.038 ↑ 1.0 23 1

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

  • Sort Key: mst_am_facility_address.facility_id
  • Sort Method: quicksort Memory: 26kB
20. 0.012 0.012 ↑ 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.012 rows=23 loops=1)

21. 0.009 0.015 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=4) (actual time=0.015..0.015 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 18.479 ↑ 1.0 1 1,087

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

25. 15.218 15.218 ↓ 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.014 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. 1.491 8.449 ↑ 1.0 1 497

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

27. 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_1 (cost=0.28..10.02 rows=1 width=8) (actual time=0.006..0.014 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.994 11.928 ↑ 1.0 1 497

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

29. 2.982 10.934 ↑ 1.0 1 497

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

  • Sort Key: trx_pm_patient_visit_2.visit_date_time
  • Sort Method: quicksort Memory: 25kB
30. 7.952 7.952 ↓ 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.008..0.016 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.306 47.403 ↑ 1.0 1 1

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

32. 47.097 47.097 ↑ 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.491..47.097 rows=1,087 loops=1)

33.          

CTE dentalvisits

34. 4.419 42.649 ↑ 1.0 1,087 1

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

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

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

  • Hash Cond: (pv_1.service_id = ds_1.service_id)
36. 0.881 0.881 ↓ 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.005..0.881 rows=3,599 loops=1)

37. 0.012 0.147 ↑ 1.0 22 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
38. 0.008 0.135 ↑ 1.0 22 1

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

39. 0.046 0.127 ↑ 1.0 22 1

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

  • Sort Key: ref_pm_service_1.service_id
  • Sort Method: quicksort Memory: 26kB
40. 0.081 0.081 ↑ 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.023..0.081 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.011 0.203 ↓ 1.3 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
42. 0.050 0.192 ↓ 1.3 21 1

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

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

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

44. 0.012 0.117 ↓ 1.3 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
45. 0.032 0.105 ↓ 1.3 21 1

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

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

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

47. 0.034 0.050 ↑ 1.0 23 1

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

  • Sort Key: mst_am_facility_address_1.facility_id
  • Sort Method: quicksort Memory: 26kB
48. 0.016 0.016 ↑ 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.006..0.016 rows=23 loops=1)

49. 0.007 0.014 ↑ 1.0 16 1

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

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

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

51.          

SubPlan (forHash Join)

52. 2.174 14.131 ↑ 1.0 1 1,087

Aggregate (cost=10.02..10.03 rows=1 width=8) (actual time=0.013..0.013 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.006..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. 1.491 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.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_4 (cost=0.28..10.02 rows=1 width=8) (actual time=0.006..0.013 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 12.425 ↑ 1.0 1 497

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

57. 3.479 11.928 ↑ 1.0 1 497

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

  • Sort Key: trx_pm_patient_visit_5.visit_date_time
  • Sort Method: quicksort Memory: 25kB
58. 8.449 8.449 ↓ 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.008..0.017 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. 82.821 82.821 ↑ 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.917..82.821 rows=1,087 loops=1)

60.          

CTE dentalvisits

61. 12.255 75.101 ↑ 1.0 1,087 1

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

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

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

  • Hash Cond: (pv_2.service_id = ds_2.service_id)
63. 1.348 1.348 ↓ 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.036..1.348 rows=3,599 loops=1)

64. 0.013 0.188 ↑ 1.0 22 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
65. 0.009 0.175 ↑ 1.0 22 1

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

66. 0.037 0.166 ↑ 1.0 22 1

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

  • Sort Key: ref_pm_service_2.service_id
  • Sort Method: quicksort Memory: 26kB
67. 0.129 0.129 ↑ 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.037..0.129 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.010 0.234 ↓ 1.3 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
69. 0.044 0.224 ↓ 1.3 21 1

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

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

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

71. 0.010 0.132 ↓ 1.3 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
72. 0.031 0.122 ↓ 1.3 21 1

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

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

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

74. 0.028 0.053 ↑ 1.0 23 1

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

  • Sort Key: mst_am_facility_address_2.facility_id
  • Sort Method: quicksort Memory: 26kB
75. 0.025 0.025 ↑ 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.014..0.025 rows=23 loops=1)

76. 0.011 0.031 ↑ 1.0 16 1

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

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

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

78.          

SubPlan (forHash Join)

79. 5.435 29.349 ↑ 1.0 1 1,087

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

80. 23.914 23.914 ↓ 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.014..0.022 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. 1.988 10.437 ↑ 1.0 1 497

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

82. 8.449 8.449 ↓ 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.009..0.017 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. 1.491 16.401 ↑ 1.0 1 497

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

84. 5.467 14.910 ↑ 1.0 1 497

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

  • Sort Key: trx_pm_patient_visit_8.visit_date_time
  • Sort Method: quicksort Memory: 25kB
85. 9.443 9.443 ↓ 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.010..0.019 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. 3.261 1,418.535 ↑ 1.0 1 1,087

Result (cost=130.51..130.52 rows=1 width=0) (actual time=1.304..1.305 rows=1 loops=1,087)

88.          

Initplan (forResult)

89. 8.696 1,415.274 ↑ 1.0 1 1,087

Aggregate (cost=130.50..130.51 rows=1 width=3) (actual time=1.302..1.302 rows=1 loops=1,087)

90. 1,406.578 1,406.578 ↓ 0.0 0 1,087

Seq Scan on trx_bm_bill (cost=0.00..130.50 rows=1 width=3) (actual time=1.228..1.294 rows=0 loops=1,087)

  • Filter: ((is_canceled IS FALSE) AND (visit_id = dv.visit_id))
  • Rows Removed by Filter: 2637
91. 7.609 1,389.186 ↑ 1.0 1 1,087

Aggregate (cost=130.50..130.51 rows=1 width=3) (actual time=1.278..1.278 rows=1 loops=1,087)

92. 1,381.577 1,381.577 ↓ 0.0 0 1,087

Seq Scan on trx_bm_bill trx_bm_bill_1 (cost=0.00..130.50 rows=1 width=3) (actual time=1.206..1.271 rows=0 loops=1,087)

  • Filter: ((is_canceled IS FALSE) AND (visit_id = dv.visit_id))
  • Rows Removed by Filter: 2637
93. 0.864 134.784 ↑ 1.0 1 96

Aggregate (cost=130.50..130.51 rows=1 width=3) (actual time=1.404..1.404 rows=1 loops=96)

94. 133.920 133.920 ↑ 1.0 1 96

Seq Scan on trx_bm_bill trx_bm_bill_2 (cost=0.00..130.50 rows=1 width=3) (actual time=0.471..1.395 rows=1 loops=96)

  • Filter: ((is_canceled IS FALSE) AND (visit_id = dv.visit_id))
  • Rows Removed by Filter: 2636
Planning time : 9.601 ms
Execution time : 3,140.440 ms