#	exclusive	inclusive	rows x	rows	loops	node
1.	0.000	46,087.868	↑ 1.8	13	1	→ GroupAggregate (cost=3,314,014.19..3,314,019.40 rows=23 width=408) (actual time=46,087.722..46,087.868 rows=13 loops=1) Group Key: tpa.tpa_name, hcm.center_name
2.	0.053	46,087.693	↑ 1.6	92	1	→ Sort (cost=3,314,014.19..3,314,014.55 rows=145 width=329) (actual time=46,087.688..46,087.693 rows=92 loops=1) Sort Key: tpa.tpa_name, hcm.center_name Sort Method: quicksort Memory: 37kB
3.	2.611	46,087.640	↑ 1.6	92	1	→ Hash Right Join (cost=3,313,319.52..3,314,008.98 rows=145 width=329) (actual time=46,086.670..46,087.640 rows=92 loops=1) Hash Cond: ((bc.claim_id)::text = (ipa.claim_id)::text)
4.	5.400	871.670	↓ 1.1	32,480	1	→ Unique (cost=258,144.22..258,433.91 rows=28,969 width=21) (actual time=863.483..871.670 rows=32,480 loops=1)
5.	134.411	866.270	↓ 1.1	32,591	1	→ Sort (cost=258,144.22..258,216.65 rows=28,969 width=21) (actual time=863.481..866.270 rows=32,591 loops=1) Sort Key: b.insurance_deduction, bc.claim_id, pr.center_id Sort Method: quicksort Memory: 3315kB
6.	11.464	731.859	↓ 1.1	32,591	1	→ Gather (cost=1,000.86..255,997.30 rows=28,969 width=21) (actual time=4.354..731.859 rows=32,591 loops=1) Workers Planned: 2 Workers Launched: 2
7.	720.395	720.395	↑ 1.1	10,864	3	→ Nested Loop (cost=0.86..252,100.40 rows=12,070 width=21) (actual time=1.245..720.395 rows=10,864 loops=3) -> Nested Loop (cost=0.43..243180.46 rows=15241 width=21) (actual time=1.163..600.358 rows=12961 loops=3 -> Parallel Seq Scan on patient_registration pr (cost=0.00..163502.46 rows=13576 width=20) (actual Filter: ((use_drg)::text = 'Y'::text) Rows Removed by Filter: 1097418 -> Index Scan using bill_visit_id_idx on bill b (cost=0.43..5.86 rows=1 width=32) (actual time=0.0 Index Cond: ((visit_id)::text = (pr.patient_id)::text) -> Index Scan using bill_claim_bill_no_idx on bill_claim bc (cost=0.43..0.58 rows=1 width=29) (actual ti Index Cond: ((bill_no)::text = (b.bill_no)::text)
8.	45,213.359	45,213.359	↓ 92.0	92	1	→ Hash (cost=3,055,175.28..3,055,175.28 rows=1 width=326) (actual time=45,213.359..45,213.359 rows=92 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 19kB
9.	0.000	45,213.320	↓ 92.0	92	1	→ Nested Loop Left Join (cost=3,055,167.20..3,055,175.28 rows=1 width=326) (actual time=45,212.095..45,213.320 rows=92 loops=1)
10.	0.078	45,213.228	↓ 92.0	92	1	→ Nested Loop Left Join (cost=3,055,167.06..3,055,175.13 rows=1 width=112) (actual time=45,212.088..45,213.228 rows=92 loops=1)
11.	0.520	45,212.598	↓ 92.0	92	1	→ GroupAggregate (cost=3,055,166.63..3,055,166.67 rows=1 width=123) (actual time=45,212.065..45,212.598 rows=92 loops=1) Group Key: tpa.tpa_name, ipa.claim_id, b_1.visit_id
12.	0.764	45,212.078	↓ 838.0	838	1	→ Sort (cost=3,055,166.63..3,055,166.64 rows=1 width=69) (actual time=45,212.039..45,212.078 rows=838 loops=1) Sort Key: tpa.tpa_name, ipa.claim_id, b_1.visit_id Sort Method: quicksort Memory: 142kB
13.	0.271	45,211.314	↓ 838.0	838	1	→ Nested Loop Semi Join (cost=2,415,836.40..3,055,166.62 rows=1 width=69) (actual time=37,857.458..45,211.314 rows=838 loops=1) Join Filter: ((insurance_submission_history.claim_id)::text = (insurance_payment_allocation.claim_id)::text)
14.	0.000	45,206.015	↓ 838.0	838	1	→ Nested Loop Semi Join (cost=2,415,835.84..3,055,154.93 rows=1 width=105) (actual time=37,857.438..45,206.015 rows=838 loops=1)
15.	0.329	45,197.658	↓ 838.0	838	1	→ Nested Loop Left Join (cost=2,415,834.98..3,055,148.06 rows=1 width=100) (actual time=37,857.405..45,197.658 rows=838 loops=1)
16.	0.600	45,193.139	↓ 838.0	838	1	→ Nested Loop Left Join (cost=2,415,834.55..3,055,147.37 rows=1 width=100) (actual time=37,857.389..45,193.139 rows=838 loops=1)
17.	0.029	45,188.384	↓ 831.0	831	1	→ Nested Loop Left Join (cost=2,415,834.12..3,055,146.86 rows=1 width=99) (actual time=37,857.374..45,188.384 rows=831 loops=1)
18.	0.068	45,183.369	↓ 831.0	831	1	→ Nested Loop (cost=2,415,833.56..3,055,145.95 rows=1 width=96) (actual time=37,857.356..45,183.369 rows=831 loops=1)
19.	1,414.074	45,180.808	↓ 831.0	831	1	→ Hash Join (cost=2,415,833.29..3,055,145.66 rows=1 width=84) (actual time=37,857.323..45,180.808 rows=831 loops=1) Hash Cond: (((insurance_submission_history.claim_id)::text = (ipa.claim_id)::text) AND ((insurance_submission_history.activity_id)::text = (ipa.charge_id)::text))
20.	5,002.225	43,753.701	↑ 1.0	10,634,583	1	→ Unique (cost=2,395,425.42..2,821,633.66 rows=10,655,206 width=149) (actual time=34,294.112..43,753.701 rows=10,634,583 loops=1)
21.	37,360.592	38,751.476	↑ 1.0	10,655,206	1	→ Sort (cost=2,395,425.42..2,422,063.43 rows=10,655,206 width=149) (actual time=34,294.111..38,751.476 rows=10,655,206 loops=1) Sort Method: external merge Disk: 1708152kB
22.	1,390.884	1,390.884	↑ 1.0	10,655,206	1	→ Seq Scan on insurance_submission_history (cost=0.00..350,470.06 rows=10,655,206 width=149) (actual time=0.013..1,390.884 rows=10,655,206 loops=1)
23.	0.251	13.033	↑ 36.9	709	1	→ Hash (cost=20,015.16..20,015.16 rows=26,181 width=45) (actual time=13.033..13.033 rows=709 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 310kB
24.	5.229	12.782	↑ 36.9	709	1	→ Gather (cost=1,000.43..20,015.16 rows=26,181 width=45) (actual time=5.932..12.782 rows=709 loops=1) Workers Planned: 1 Workers Launched: 1
25.	0.693	7.553	↑ 43.5	354	2	→ Nested Loop (cost=0.43..16,397.06 rows=15,401 width=45) (actual time=2.759..7.553 rows=354 loops=2)
26.	6.844	6.844	↑ 2.9	40	2	→ Parallel Seq Scan on insurance_remittance ir (cost=0.00..2,632.58 rows=115 width=4) (actual time=2.297..6.844 rows=40 loops=2) Filter: ((received_date >= '2018-09-01'::date) AND (received_date <= '2018-11-30'::date)) Rows Removed by Filter: 45213
27.	0.016	0.016	↑ 102.1	9	80	→ Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=0.43..110.50 rows=919 width=41) (actual time=0.014..0.016 rows=9 loops=80) Index Cond: (remittance_id = ir.remittance_id)
28.	2.493	2.493	↑ 1.0	1	831	→ Index Scan using tpa_master_pkey on tpa_master tpa (cost=0.27..0.29 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=831) Index Cond: ((tpa_id)::text = (insurance_submission_history.sponsor_id)::text)
29.	4.986	4.986	↑ 1.0	1	831	→ Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc (cost=0.56..0.89 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=831) Index Cond: ((insurance_submission_history.activity_id)::text = (charge_id)::text) Filter: ((claim_id)::text = (insurance_submission_history.claim_id)::text)
30.	4.155	4.155	↑ 1.0	1	831	→ Index Scan using bill_claim_claim_idx on bill_claim bc_1 (cost=0.43..0.50 rows=1 width=29) (actual time=0.005..0.005 rows=1 loops=831) Index Cond: ((claim_id)::text = (bcc.claim_id)::text)
31.	4.190	4.190	↑ 1.0	1	838	→ Index Scan using bill_pkey on bill b_1 (cost=0.43..0.69 rows=1 width=29) (actual time=0.005..0.005 rows=1 loops=838) Index Cond: ((bill_no)::text = (bc_1.bill_no)::text)
32.	0.838	8.380	↑ 1.0	1	838	→ Nested Loop (cost=0.85..6.86 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=838) Join Filter: ((insurance_submission_history.submission_batch_id)::text = (isb.submission_batch_id)::text)
33.	5.028	5.028	↑ 1.0	1	838	→ Index Scan using idx_claim_id_ins_sub_history on insurance_submission_history ish1 (cost=0.43..6.41 rows=1 width=23) (actual time=0.006..0.006 rows=1 loops=838) Index Cond: ((claim_id)::text = (insurance_submission_history.claim_id)::text) Filter: ((insurance_submission_history.submission_batch_id)::text = (submission_batch_id)::text) Rows Removed by Filter: 3
34.	2.514	2.514	↑ 1.0	1	838	→ Index Only Scan using insurance_submission_batch_pkey on insurance_submission_batch isb (cost=0.42..0.44 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=838) Index Cond: (submission_batch_id = (ish1.submission_batch_id)::text) Heap Fetches: 838
35.	5.028	5.028	↑ 1.0	1	838	→ Index Scan using idx_insurance_payment_allocation_claim_id on insurance_payment_allocation (cost=0.56..11.68 rows=1 width=18) (actual time=0.006..0.006 rows=1 loops=838) Index Cond: ((claim_id)::text = (ipa.claim_id)::text) Filter: (ipa.remittance_id = remittance_id) Rows Removed by Filter: 3
36.	0.552	0.552	↑ 1.0	1	92	→ Index Scan using patient_registration_pkey on patient_registration pr1 (cost=0.43..8.45 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=92) Index Cond: ((patient_id)::text = (b_1.visit_id)::text)
37.	0.092	0.092	↑ 1.0	1	92	→ Index Scan using center_pkey on hospital_center_master hcm (cost=0.14..0.16 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=92) Index Cond: (center_id = pr1.center_id)

 QUERY PLAN                                                     
                                                                                                                                                 
                                                                                                                                                 
                             
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
 GroupAggregate  (cost=3314014.19..3314019.40 rows=23 width=408) (actual time=46087.722..46087.868 rows=13 loops=1)
   Group Key: tpa.tpa_name, hcm.center_name
   ->  Sort  (cost=3314014.19..3314014.55 rows=145 width=329) (actual time=46087.688..46087.693 rows=92 loops=1)
         Sort Key: tpa.tpa_name, hcm.center_name
         Sort Method: quicksort  Memory: 37kB
         ->  Hash Right Join  (cost=3313319.52..3314008.98 rows=145 width=329) (actual time=46086.670..46087.640 rows=92 loops=1)
               Hash Cond: ((bc.claim_id)::text = (ipa.claim_id)::text)
               ->  Unique  (cost=258144.22..258433.91 rows=28969 width=21) (actual time=863.483..871.670 rows=32480 loops=1)
                     ->  Sort  (cost=258144.22..258216.65 rows=28969 width=21) (actual time=863.481..866.270 rows=32591 loops=1)
                           Sort Key: b.insurance_deduction, bc.claim_id, pr.center_id
                           Sort Method: quicksort  Memory: 3315kB
                           ->  Gather  (cost=1000.86..255997.30 rows=28969 width=21) (actual time=4.354..731.859 rows=32591 loops=1)
                                 Workers Planned: 2
                                 Workers Launched: 2
                                 ->  Nested Loop  (cost=0.86..252100.40 rows=12070 width=21) (actual time=1.245..720.395 rows=10864 loops=3)
                                       ->  Nested Loop  (cost=0.43..243180.46 rows=15241 width=21) (actual time=1.163..600.358 rows=12961 loops=3
)
                                             ->  Parallel Seq Scan on patient_registration pr  (cost=0.00..163502.46 rows=13576 width=20) (actual
 time=1.066..501.113 rows=10820 loops=3)
                                                   Filter: ((use_drg)::text = 'Y'::text)
                                                   Rows Removed by Filter: 1097418
                                             ->  Index Scan using bill_visit_id_idx on bill b  (cost=0.43..5.86 rows=1 width=32) (actual time=0.0
08..0.008 rows=1 loops=32461)
                                                   Index Cond: ((visit_id)::text = (pr.patient_id)::text)
                                       ->  Index Scan using bill_claim_bill_no_idx on bill_claim bc  (cost=0.43..0.58 rows=1 width=29) (actual ti
me=0.009..0.009 rows=1 loops=38883)
                                             Index Cond: ((bill_no)::text = (b.bill_no)::text)
               ->  Hash  (cost=3055175.28..3055175.28 rows=1 width=326) (actual time=45213.359..45213.359 rows=92 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 19kB
->  Nested Loop Left Join  (cost=3055167.20..3055175.28 rows=1 width=326) (actual time=45212.095..45213.320 rows=92 loops=1)
                           ->  Nested Loop Left Join  (cost=3055167.06..3055175.13 rows=1 width=112) (actual time=45212.088..45213.228 rows=92 loops=1)
                                 ->  GroupAggregate  (cost=3055166.63..3055166.67 rows=1 width=123) (actual time=45212.065..45212.598 rows=92 loops=1)
                                       Group Key: tpa.tpa_name, ipa.claim_id, b_1.visit_id
                                       ->  Sort  (cost=3055166.63..3055166.64 rows=1 width=69) (actual time=45212.039..45212.078 rows=838 loops=1)
                                             Sort Key: tpa.tpa_name, ipa.claim_id, b_1.visit_id
                                             Sort Method: quicksort  Memory: 142kB
                                             ->  Nested Loop Semi Join  (cost=2415836.40..3055166.62 rows=1 width=69) (actual time=37857.458..45211.314 rows=838 loops=1)
                                                   Join Filter: ((insurance_submission_history.claim_id)::text = (insurance_payment_allocation.claim_id)::text)
                                                   ->  Nested Loop Semi Join  (cost=2415835.84..3055154.93 rows=1 width=105) (actual time=37857.438..45206.015 rows=838 loops=1)
                                                         ->  Nested Loop Left Join  (cost=2415834.98..3055148.06 rows=1 width=100) (actual time=37857.405..45197.658 rows=838 loops=1)
                                                               ->  Nested Loop Left Join  (cost=2415834.55..3055147.37 rows=1 width=100) (actual time=37857.389..45193.139 rows=838 loops=1)
                                                                     ->  Nested Loop Left Join  (cost=2415834.12..3055146.86 rows=1 width=99) (actual time=37857.374..45188.384 rows=831 loops=1)
                                                                           ->  Nested Loop  (cost=2415833.56..3055145.95 rows=1 width=96) (actual time=37857.356..45183.369 rows=831 loops=1)
                                                                                 ->  Hash Join  (cost=2415833.29..3055145.66 rows=1 width=84) (actual time=37857.323..45180.808 rows=831 loops=1)
                                                                                       Hash Cond: (((insurance_submission_history.claim_id)::text = (ipa.claim_id)::text) AND ((insurance_submission_history.activity_id)::text = (ipa.charge_id)::text))
                                                                                       ->  Unique  (cost=2395425.42..2821633.66 rows=10655206 width=149) (actual time=34294.112..43753.701 rows=10634583 loops=1)
                                                                                             ->  Sort  (cost=2395425.42..2422063.43 rows=10655206 width=149) (actual time=34294.111..38751.476 rows=10655206 loops=1)
 Sort Key: insurance_submission_history.submission_batch_id, insurance_submission_history.claim_id, insurance_submission_history.activity_id, insurance_submission_history.item_code, insurance_submission_history.amount, insurance_submission_history.insurance_claim_amount, insurance_submission_history.charge_head, insurance_submission_history.act_description, insurance_submission_history.act_quantity, insurance_submission_history.clinician_id, insurance_submission_history.member_id, insurance_submission_history.insurance_co, insurance_submission_history.sponsor_id, insurance_submission_history.resubmission, insurance_submission_history.patient_share
                                                                                                   Sort Method: external merge  Disk: 1708152kB
                                                                                                   ->  Seq Scan on insurance_submission_history  (cost=0.00..350470.06 rows=10655206 width=149) (actual time=0.013..1390.884 rows=10655206 loops=1)
                                                                                       ->  Hash  (cost=20015.16..20015.16 rows=26181 width=45) (actual time=13.033..13.033 rows=709 loops=1)
                                                                                             Buckets: 32768  Batches: 1  Memory Usage: 310kB
                                                                                             ->  Gather  (cost=1000.43..20015.16 rows=26181 width=45) (actual time=5.932..12.782 rows=709 loops=1)
                                                                                                   Workers Planned: 1
                                                                                                   Workers Launched: 1
                                                                                                   ->  Nested Loop  (cost=0.43..16397.06 rows=15401 width=45) (actual time=2.759..7.553 rows=354 loops=2)
                                                                                                         ->  Parallel Seq Scan on insurance_remittance ir  (cost=0.00..2632.58 rows=115 width=4) (actual time=2.297..6.844 rows=40 loops=2)
                                                                                                               Filter: ((received_date >= '2018-09-01'::date) AND (received_date <= '2018-11-30'::date))
                                                                                                               Rows Removed by Filter: 45213
                                                                                                         ->  Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa  (cost=0.43..110.50 rows=919 width=41) (actual time=0.014..0.016 rows=9 loops=80)
                                                                                                               Index Cond: (remittance_id = ir.remittance_id)
                                                                                 ->  Index Scan using tpa_master_pkey on tpa_master tpa  (cost=0.27..0.29 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=831)
                                                                                       Index Cond: ((tpa_id)::text = (insurance_submission_history.sponsor_id)::text)
                                                                           ->  Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc  (cost=0.56..0.89 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=831)
                                                                                 Index Cond: ((insurance_submission_history.activity_id)::text = (charge_id)::text)
                                                                                 Filter: ((claim_id)::text = (insurance_submission_history.claim_id)::text)
 Rows Removed by Filter: 0
                                                                     ->  Index Scan using bill_claim_claim_idx on bill_claim bc_1  (cost=0.43..0.50 rows=1 width=29) (actual time=0.005..0.005 rows=1 loops=831)
                                                                           Index Cond: ((claim_id)::text = (bcc.claim_id)::text)
                                                               ->  Index Scan using bill_pkey on bill b_1  (cost=0.43..0.69 rows=1 width=29) (actual time=0.005..0.005 rows=1 loops=838)
                                                                     Index Cond: ((bill_no)::text = (bc_1.bill_no)::text)
                                                         ->  Nested Loop  (cost=0.85..6.86 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=838)
                                                               Join Filter: ((insurance_submission_history.submission_batch_id)::text = (isb.submission_batch_id)::text)
                                                               ->  Index Scan using idx_claim_id_ins_sub_history on insurance_submission_history ish1  (cost=0.43..6.41 rows=1 width=23) (actual time=0.006..0.006 rows=1 loops=838)
                                                                     Index Cond: ((claim_id)::text = (insurance_submission_history.claim_id)::text)
                                                                     Filter: ((insurance_submission_history.submission_batch_id)::text = (submission_batch_id)::text)
                                                                     Rows Removed by Filter: 3
                                                               ->  Index Only Scan using insurance_submission_batch_pkey on insurance_submission_batch isb  (cost=0.42..0.44 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=838)
                                                                     Index Cond: (submission_batch_id = (ish1.submission_batch_id)::text)
                                                                     Heap Fetches: 838
                                                   ->  Index Scan using idx_insurance_payment_allocation_claim_id on insurance_payment_allocation  (cost=0.56..11.68 rows=1 width=18) (actual time=0.006..0.006 rows=1 loops=838)
                                                         Index Cond: ((claim_id)::text = (ipa.claim_id)::text)
                                                         Filter: (ipa.remittance_id = remittance_id)
                                                         Rows Removed by Filter: 3
                                 ->  Index Scan using patient_registration_pkey on patient_registration pr1  (cost=0.43..8.45 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=92)
                                       Index Cond: ((patient_id)::text = (b_1.visit_id)::text)
                           ->  Index Scan using center_pkey on hospital_center_master hcm  (cost=0.14..0.16 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=92)
                                 Index Cond: (center_id = pr1.center_id)
 Planning time: 8.391 ms
 Execution time: 46281.170 ms
(86 rows)

Per node type stats

node type	count	sum of times	% of query
Gather	2	16.693 ms	0.0 %
GroupAggregate	2	0.520 ms	0.0 %
Hash	2	45,213.610 ms	98.1 %
Hash Join	1	1,414.074 ms	3.1 %
Hash Right Join	1	2.611 ms	0.0 %
Index Only Scan	1	2.514 ms	0.0 %
Index Scan	9	26.540 ms	0.1 %
Nested Loop	4	721.994 ms	1.6 %
Nested Loop Left Join	5	1.036 ms	0.0 %
Nested Loop Semi Join	2	0.271 ms	0.0 %
Parallel Seq Scan	1	6.844 ms	0.0 %
Seq Scan	1	1,390.884 ms	3.0 %
Sort	4	37,495.820 ms	81.4 %
Unique	2	5,007.625 ms	10.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
bill	1	4.190 ms	0.0 %
Index Scan	1	4.190 ms	100.0 %
bill_charge_claim	1	4.986 ms	0.0 %
Index Scan	1	4.986 ms	100.0 %
bill_claim	1	4.155 ms	0.0 %
Index Scan	1	4.155 ms	100.0 %
hospital_center_master	1	0.092 ms	0.0 %
Index Scan	1	0.092 ms	100.0 %
insurance_payment_allocation	2	5.044 ms	0.0 %
Index Scan	2	5.044 ms	100.0 %
insurance_remittance	1	6.844 ms	0.0 %
Parallel Seq Scan	1	6.844 ms	100.0 %
insurance_submission_batch	1	2.514 ms	0.0 %
Index Only Scan	1	2.514 ms	100.0 %
insurance_submission_history	2	1,395.912 ms	3.0 %
Index Scan	1	5.028 ms	0.4 %
Seq Scan	1	1,390.884 ms	99.6 %
patient_registration	1	0.552 ms	0.0 %
Index Scan	1	0.552 ms	100.0 %
tpa_master	1	2.493 ms	0.0 %
Index Scan	1	2.493 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F2jC

Color mode:

Visible columns:

Per node type stats

Per table stats