#	exclusive	inclusive	rows x	rows	loops	node
1.	0.004	59,359.539	↑ 1.0	1	1	→ Limit (cost=526,205.26..526,205.26 rows=1 width=158) (actual time=59,359.538..59,359.539 rows=1 loops=1)
2.	654.313	59,359.535	↑ 17,561.0	1	1	→ Sort (cost=526,205.26..526,249.16 rows=17,561 width=158) (actual time=59,359.535..59,359.535 rows=1 loops=1) Sort Key: v.visit_date_time Sort Method: top-N heapsort Memory: 25kB
3.	2,835.391	58,705.222	↓ 70.5	1,237,942	1	→ HashAggregate (cost=524,932.08..526,117.45 rows=17,561 width=158) (actual time=57,848.788..58,705.222 rows=1,237,942 loops=1) Group Key: v.visit_date_time, (v.visit_date_time)::date, p.name, CASE WHEN (v.arrival_date_time IS NOT NULL) THEN (min(e.encounter_start) - v.arrival_date_time) ELSE NULL::interval END, (v.arrival_date_time)::time without time zone, CASE WHEN (v.arrival_date_time IS NOT NULL) THEN (v.visit_date_time - v.arrival_date_time) ELSE NULL::interval END, (v.visit_date_time)::time without time zone, CASE WHEN (min(e.encounter_start) IS NOT NULL) THEN (min(e.encounter_start) - v.visit_date_time) ELSE NULL::interval END, (a.appointment_date)::time without time zone, CASE WHEN (a.appointment_date IS NOT NULL) THEN (min(e.encounter_start) - a.appointment_date) ELSE NULL::interval END, CASE WHEN ((v.visit_date_time)::date = (min(e.encounter_start))::date) THEN min(e.encounter_start) ELSE NULL::timestamp without time zone END, CASE WHEN (v.checkout_date_time IS NOT NULL) THEN (v.checkout_date_time - min(e.encounter_start)) ELSE NULL::interval END, (v.checkout_date_time)::time without time zone, CASE WHEN (((v.visit_date_time)::date = (min(e.encounter_start))::date) AND ((v.visit_date_time)::date = (max(e.encounter_end))::date)) THEN (max(e.encounter_end) - min(e.encounter_start)) ELSE NULL::interval END, CASE WHEN ((v.visit_date_time)::date = (max(e.encounter_end))::date) THEN max(e.encounter_end) ELSE NULL::timestamp without time zone END, v.checkout_date_time, v.visit_id, p.pc_id, v.clinic_id, addr.state_id, addr.district_id, f.facility_name, st.state_name, dt.district_name, rs.service_id, rs.service_name
4.	6,817.627	55,869.831	↓ 70.5	1,237,942	1	→ HashAggregate (cost=522,605.25..523,790.62 rows=17,561 width=158) (actual time=53,108.403..55,869.831 rows=1,237,942 loops=1) Group Key: v.visit_date_time, p.name, a.appointment_date, v.arrival_date_time, v.checkout_date_time, v.visit_id, p.pc_id, v.clinic_id, addr.state_id, addr.district_id, f.facility_name, st.state_name, dt.district_name, rs.service_id
5.	914.709	49,052.204	↓ 190.7	3,349,242	1	→ Hash Join (cost=11.11..521,419.88 rows=17,561 width=158) (actual time=25,982.728..49,052.204 rows=3,349,242 loops=1) Hash Cond: (addr.facility_id = f.facility_id)
6.	543.681	48,137.476	↓ 190.7	3,349,242	1	→ Merge Join (cost=9.55..521,176.86 rows=17,561 width=143) (actual time=25,982.687..48,137.476 rows=3,349,242 loops=1) Merge Cond: (st.state_id = addr.state_id)
7.	0.065	0.065	↑ 2.7	6	1	→ Index Scan using ref_co_state_pkey on ref_co_state st (cost=0.14..6.38 rows=16 width=13) (actual time=0.004..0.065 rows=6 loops=1)
8.	1,599.015	47,593.730	↓ 190.7	3,349,242	1	→ Materialize (cost=7.84..532,534.86 rows=17,561 width=134) (actual time=25,982.638..47,593.730 rows=3,349,242 loops=1)
9.	11,759.216	45,994.715	↓ 190.7	3,349,242	1	→ Nested Loop (cost=7.84..532,490.95 rows=17,561 width=134) (actual time=25,982.626..45,994.715 rows=3,349,242 loops=1) Join Filter: (addr.facility_id = v.clinic_id) Rows Removed by Join Filter: 89657847
10.	0.573	1.434	↓ 1.1	29	1	→ Nested Loop (cost=0.14..43.95 rows=26 width=21) (actual time=0.027..1.434 rows=29 loops=1) Join Filter: (addr.district_id = dt.district_id) Rows Removed by Join Filter: 3683
11.	0.339	0.339	↓ 1.1	29	1	→ Index Scan using mst_am_facility_address_state_id_district_id_idx on mst_am_facility_address addr (cost=0.14..6.53 rows=26 width=12) (actual time=0.004..0.339 rows=29 loops=1)
12.	0.485	0.522	↓ 1.5	128	29	→ Materialize (cost=0.00..3.32 rows=88 width=13) (actual time=0.001..0.018 rows=128 loops=29)
13.	0.037	0.037	↓ 1.5	128	1	→ Seq Scan on ref_co_district dt (cost=0.00..2.88 rows=88 width=13) (actual time=0.006..0.037 rows=128 loops=1)
14.	12,323.981	34,234.065	↓ 195.0	3,207,141	29	→ Materialize (cost=7.71..526,072.24 rows=16,451 width=113) (actual time=0.005..1,180.485 rows=3,207,141 loops=29)
15.	1,024.072	21,910.084	↓ 195.0	3,207,141	1	→ Nested Loop (cost=7.71..525,989.99 rows=16,451 width=113) (actual time=0.036..21,910.084 rows=3,207,141 loops=1)
16.	1,968.598	17,678.871	↓ 195.0	3,207,141	1	→ Nested Loop Left Join (cost=7.57..523,193.23 rows=16,451 width=92) (actual time=0.032..17,678.871 rows=3,207,141 loops=1)
17.	2,937.860	15,710.273	↓ 195.0	3,207,141	1	→ Nested Loop (cost=7.14..515,247.07 rows=16,451 width=92) (actual time=0.031..15,710.273 rows=3,207,141 loops=1)
18.	3,263.957	6,358.131	↓ 195.0	3,207,141	1	→ Merge Join (cost=6.72..507,517.04 rows=16,451 width=72) (actual time=0.024..6,358.131 rows=3,207,141 loops=1) Merge Cond: (v.visit_id = e.visit_id) Join Filter: ((e.encounter_end)::date = (v.visit_date_time)::date) Rows Removed by Join Filter: 134134
19.	894.746	894.746	↑ 1.1	1,363,164	1	→ Index Scan using trx_pm_patient_visit_visit_id_idx on trx_pm_patient_visit v (cost=0.43..134,859.16 rows=1,563,463 width=56) (actual time=0.005..894.746 rows=1,363,164 loops=1)
20.	2,199.428	2,199.428	↓ 1.0	3,341,275	1	→ Index Scan using trx_pm_visit_encounter_visit_id_idx on trx_pm_visit_encounter e (cost=0.43..302,997.66 rows=3,290,245 width=24) (actual time=0.007..2,199.428 rows=3,341,275 loops=1)
21.	6,414.282	6,414.282	↑ 1.0	1	3,207,141	→ Index Scan using mst_pm_patient_patient_id_idx on mst_pm_patient p (cost=0.42..0.46 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=3,207,141) Index Cond: (patient_id = v.patient_id)
22.	0.000	0.000	↓ 0.0	0	3,207,141	→ Index Scan using mst_pm_appointment_pkey on mst_pm_appointment a (cost=0.42..0.47 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=3,207,141) Index Cond: (v.appointment_id = appointment_id)
23.	3,207.141	3,207.141	↑ 1.0	1	3,207,141	→ Index Scan using ref_pm_service_pkey on ref_pm_service rs (cost=0.14..0.16 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=3,207,141) Index Cond: (service_id = v.service_id)
24.	0.011	0.019	↓ 1.1	28	1	→ Hash (cost=1.25..1.25 rows=25 width=23) (actual time=0.019..0.019 rows=28 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB
25.	0.008	0.008	↓ 1.1	28	1	→ Seq Scan on mst_am_facility f (cost=0.00..1.25 rows=25 width=23) (actual time=0.004..0.008 rows=28 loops=1)

 Limit  (cost=526205.26..526205.26 rows=1 width=158) (actual time=59359.538..59359.539 rows=1 loops=1)
   ->  Sort  (cost=526205.26..526249.16 rows=17561 width=158) (actual time=59359.535..59359.535 rows=1 loops=1)
         Sort Key: v.visit_date_time
         Sort Method: top-N heapsort  Memory: 25kB
         ->  HashAggregate  (cost=524932.08..526117.45 rows=17561 width=158) (actual time=57848.788..58705.222 rows=1237942 loops=1)
               Group Key: v.visit_date_time, (v.visit_date_time)::date, p.name, CASE WHEN (v.arrival_date_time IS NOT NULL) THEN (min(e.encounter_start) - v.arrival_date_time) ELSE NULL::interval END, (v.arrival_date_time)::time without time zone, CASE WHEN (v.arrival_date_time IS NOT NULL) THEN (v.visit_date_time - v.arrival_date_time) ELSE NULL::interval END, (v.visit_date_time)::time without time zone, CASE WHEN (min(e.encounter_start) IS NOT NULL) THEN (min(e.encounter_start) - v.visit_date_time) ELSE NULL::interval END, (a.appointment_date)::time without time zone, CASE WHEN (a.appointment_date IS NOT NULL) THEN (min(e.encounter_start) - a.appointment_date) ELSE NULL::interval END, CASE WHEN ((v.visit_date_time)::date = (min(e.encounter_start))::date) THEN min(e.encounter_start) ELSE NULL::timestamp without time zone END, CASE WHEN (v.checkout_date_time IS NOT NULL) THEN (v.checkout_date_time - min(e.encounter_start)) ELSE NULL::interval END, (v.checkout_date_time)::time without time zone, CASE WHEN (((v.visit_date_time)::date = (min(e.encounter_start))::date) AND ((v.visit_date_time)::date = (max(e.encounter_end))::date)) THEN (max(e.encounter_end) - min(e.encounter_start)) ELSE NULL::interval END, CASE WHEN ((v.visit_date_time)::date = (max(e.encounter_end))::date) THEN max(e.encounter_end) ELSE NULL::timestamp without time zone END, v.checkout_date_time, v.visit_id, p.pc_id, v.clinic_id, addr.state_id, addr.district_id, f.facility_name, st.state_name, dt.district_name, rs.service_id, rs.service_name
               ->  HashAggregate  (cost=522605.25..523790.62 rows=17561 width=158) (actual time=53108.403..55869.831 rows=1237942 loops=1)
                     Group Key: v.visit_date_time, p.name, a.appointment_date, v.arrival_date_time, v.checkout_date_time, v.visit_id, p.pc_id, v.clinic_id, addr.state_id, addr.district_id, f.facility_name, st.state_name, dt.district_name, rs.service_id
                     ->  Hash Join  (cost=11.11..521419.88 rows=17561 width=158) (actual time=25982.728..49052.204 rows=3349242 loops=1)
                           Hash Cond: (addr.facility_id = f.facility_id)
                           ->  Merge Join  (cost=9.55..521176.86 rows=17561 width=143) (actual time=25982.687..48137.476 rows=3349242 loops=1)
                                 Merge Cond: (st.state_id = addr.state_id)
                                 ->  Index Scan using ref_co_state_pkey on ref_co_state st  (cost=0.14..6.38 rows=16 width=13) (actual time=0.004..0.065 rows=6 loops=1)
                                 ->  Materialize  (cost=7.84..532534.86 rows=17561 width=134) (actual time=25982.638..47593.730 rows=3349242 loops=1)
                                       ->  Nested Loop  (cost=7.84..532490.95 rows=17561 width=134) (actual time=25982.626..45994.715 rows=3349242 loops=1)
                                             Join Filter: (addr.facility_id = v.clinic_id)
                                             Rows Removed by Join Filter: 89657847
                                             ->  Nested Loop  (cost=0.14..43.95 rows=26 width=21) (actual time=0.027..1.434 rows=29 loops=1)
                                                   Join Filter: (addr.district_id = dt.district_id)
                                                   Rows Removed by Join Filter: 3683
                                                   ->  Index Scan using mst_am_facility_address_state_id_district_id_idx on mst_am_facility_address addr  (cost=0.14..6.53 rows=26 width=12) (actual time=0.004..0.339 rows=29 loops=1)
                                                   ->  Materialize  (cost=0.00..3.32 rows=88 width=13) (actual time=0.001..0.018 rows=128 loops=29)
                                                         ->  Seq Scan on ref_co_district dt  (cost=0.00..2.88 rows=88 width=13) (actual time=0.006..0.037 rows=128 loops=1)
                                             ->  Materialize  (cost=7.71..526072.24 rows=16451 width=113) (actual time=0.005..1180.485 rows=3207141 loops=29)
                                                   ->  Nested Loop  (cost=7.71..525989.99 rows=16451 width=113) (actual time=0.036..21910.084 rows=3207141 loops=1)
                                                         ->  Nested Loop Left Join  (cost=7.57..523193.23 rows=16451 width=92) (actual time=0.032..17678.871 rows=3207141 loops=1)
                                                               ->  Nested Loop  (cost=7.14..515247.07 rows=16451 width=92) (actual time=0.031..15710.273 rows=3207141 loops=1)
                                                                     ->  Merge Join  (cost=6.72..507517.04 rows=16451 width=72) (actual time=0.024..6358.131 rows=3207141 loops=1)
                                                                           Merge Cond: (v.visit_id = e.visit_id)
                                                                           Join Filter: ((e.encounter_end)::date = (v.visit_date_time)::date)
                                                                           Rows Removed by Join Filter: 134134
                                                                           ->  Index Scan using trx_pm_patient_visit_visit_id_idx on trx_pm_patient_visit v  (cost=0.43..134859.16 rows=1563463 width=56) (actual time=0.005..894.746 rows=1363164 loops=1)
                                                                           ->  Index Scan using trx_pm_visit_encounter_visit_id_idx on trx_pm_visit_encounter e  (cost=0.43..302997.66 rows=3290245 width=24) (actual time=0.007..2199.428 rows=3341275 loops=1)
                                                                     ->  Index Scan using mst_pm_patient_patient_id_idx on mst_pm_patient p  (cost=0.42..0.46 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=3207141)
                                                                           Index Cond: (patient_id = v.patient_id)
                                                               ->  Index Scan using mst_pm_appointment_pkey on mst_pm_appointment a  (cost=0.42..0.47 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=3207141)
                                                                     Index Cond: (v.appointment_id = appointment_id)
                                                         ->  Index Scan using ref_pm_service_pkey on ref_pm_service rs  (cost=0.14..0.16 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=3207141)
                                                               Index Cond: (service_id = v.service_id)
                           ->  Hash  (cost=1.25..1.25 rows=25 width=23) (actual time=0.019..0.019 rows=28 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 2kB
                                 ->  Seq Scan on mst_am_facility f  (cost=0.00..1.25 rows=25 width=23) (actual time=0.004..0.008 rows=28 loops=1)
 Planning time: 9.289 ms
 Execution time: 59528.256 ms
(44 rows)

Per node type stats

node type	count	sum of times	% of query
Hash	1	0.011 ms	0.0 %
Hash Join	1	914.709 ms	1.5 %
HashAggregate	2	9,653.018 ms	16.3 %
Index Scan	7	12,716.001 ms	21.4 %
Limit	1	0.004 ms	0.0 %
Materialize	3	13,923.481 ms	23.5 %
Merge Join	2	3,807.638 ms	6.4 %
Nested Loop	4	15,721.721 ms	26.5 %
Nested Loop Left Join	1	1,968.598 ms	3.3 %
Seq Scan	2	0.045 ms	0.0 %
Sort	1	654.313 ms	1.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
mst_am_facility	1	0.008 ms	0.0 %
Seq Scan	1	0.008 ms	100.0 %
mst_am_facility_address	1	0.339 ms	0.0 %
Index Scan	1	0.339 ms	100.0 %
mst_pm_appointment	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
mst_pm_patient	1	6,414.282 ms	10.8 %
Index Scan	1	6,414.282 ms	100.0 %
ref_co_district	1	0.037 ms	0.0 %
Seq Scan	1	0.037 ms	100.0 %
ref_co_state	1	0.065 ms	0.0 %
Index Scan	1	0.065 ms	100.0 %
ref_pm_service	1	3,207.141 ms	5.4 %
Index Scan	1	3,207.141 ms	100.0 %
trx_pm_patient_visit	1	894.746 ms	1.5 %
Index Scan	1	894.746 ms	100.0 %
trx_pm_visit_encounter	1	2,199.428 ms	3.7 %
Index Scan	1	2,199.428 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VnZL

Color mode:

Visible columns:

Optimization(s) for this plan:

Per node type stats

Per table stats