explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hCce

Settings
# exclusive inclusive rows x rows loops node
1. 0.119 8,048.441 ↓ 648.0 648 1

Hash Right Join (cost=378,385.09..378,390.45 rows=1 width=143) (actual time=8,048.334..8,048.441 rows=648 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (r.mr_no)::text)
2. 0.009 921.109 ↓ 0.0 0 1

HashAggregate (cost=346,207.30..346,209.55 rows=225 width=18) (actual time=921.109..921.109 rows=0 loops=1)

3. 0.054 921.100 ↓ 0.0 0 1

Nested Loop (cost=0.43..346,206.17 rows=225 width=18) (actual time=921.100..921.100 rows=0 loops=1)

4. 917.062 917.062 ↓ 2.1 664 1

Seq Scan on bill b (cost=0.00..345,359.67 rows=320 width=14) (actual time=486.617..917.062 rows=664 loops=1)

  • Filter: (ip_deposit_set_off > 0::numeric)
  • Rows Removed by Filter: 3238183
5. 3.984 3.984 ↓ 0.0 0 664

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..2.64 rows=1 width=27) (actual time=0.006..0.006 rows=0 loops=664)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
  • Filter: (visit_type <> 'i'::bpchar)
  • Rows Removed by Filter: 1
6. 0.129 7,127.213 ↓ 648.0 648 1

Hash (cost=32,177.78..32,177.78 rows=1 width=111) (actual time=7,127.213..7,127.213 rows=648 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
7. 0.717 7,127.084 ↓ 648.0 648 1

Merge Left Join (cost=32,177.76..32,177.78 rows=1 width=111) (actual time=7,126.298..7,127.084 rows=648 loops=1)

  • Merge Cond: ((r.mr_no)::text = (setoffs.mr_no)::text)
8. 1.725 7,047.995 ↓ 648.0 648 1

Sort (cost=14.89..14.89 rows=1 width=79) (actual time=7,047.952..7,047.995 rows=648 loops=1)

  • Sort Key: r.mr_no
  • Sort Method: quicksort Memory: 75kB
9. 1.749 7,046.270 ↓ 648.0 648 1

HashAggregate (cost=4.21..14.87 rows=1 width=15) (actual time=37.823..7,046.270 rows=648 loops=1)

10. 1.162 13.073 ↓ 1,133.0 1,133 1

Nested Loop (cost=0.85..4.21 rows=1 width=15) (actual time=0.043..13.073 rows=1,133 loops=1)

11. 0.581 0.581 ↓ 1,133.0 1,133 1

Index Scan using idx_receipt_usage_entitytype_entityid on receipt_usage (cost=0.43..2.65 rows=1 width=12) (actual time=0.029..0.581 rows=1,133 loops=1)

  • Index Cond: (((entity_type)::text = 'visit_type'::text) AND ((entity_id)::text = 'i'::text))
12. 11.330 11.330 ↑ 1.0 1 1,133

Index Only Scan using idx_receipts_receiptid_mrno_isdeposit on receipts r (cost=0.43..1.55 rows=1 width=27) (actual time=0.010..0.010 rows=1 loops=1,133)

  • Index Cond: ((receipt_id = (receipt_usage.receipt_id)::text) AND (is_deposit = true))
  • Filter: is_deposit
  • Heap Fetches: 0
13.          

SubPlan (for HashAggregate)

14. 1.296 3,526.416 ↑ 1.0 1 648

Aggregate (cost=5.31..5.32 rows=1 width=5) (actual time=5.442..5.442 rows=1 loops=648)

15. 454.248 3,525.120 ↓ 2.0 2 648

Nested Loop (cost=0.85..5.31 rows=1 width=5) (actual time=2.630..5.440 rows=2 loops=648)

16. 134.136 134.136 ↓ 1,133.0 1,133 648

Index Scan using idx_receipt_usage_entitytype_entityid on receipt_usage receipt_usage_1 (cost=0.43..2.65 rows=1 width=12) (actual time=0.014..0.207 rows=1,133 loops=648)

  • Index Cond: (((entity_type)::text = 'visit_type'::text) AND ((entity_id)::text = 'i'::text))
17. 2,936.736 2,936.736 ↓ 0.0 0 734,184

Index Scan using receipts_pkey on receipts r_1 (cost=0.43..2.65 rows=1 width=17) (actual time=0.004..0.004 rows=0 loops=734,184)

  • Index Cond: ((receipt_id)::text = (receipt_usage_1.receipt_id)::text)
  • Filter: (is_deposit AND ((realized)::bpchar = 'Y'::bpchar) AND ((mr_no)::text = (r.mr_no)::text))
  • Rows Removed by Filter: 1
18. 0.000 3,505.032 ↑ 1.0 1 648

Aggregate (cost=5.31..5.32 rows=1 width=5) (actual time=5.409..5.409 rows=1 loops=648)

19. 434.808 3,505.032 ↓ 0.0 0 648

Nested Loop (cost=0.85..5.31 rows=1 width=5) (actual time=5.409..5.409 rows=0 loops=648)

20. 133.488 133.488 ↓ 1,133.0 1,133 648

Index Scan using idx_receipt_usage_entitytype_entityid on receipt_usage receipt_usage_2 (cost=0.43..2.65 rows=1 width=12) (actual time=0.014..0.206 rows=1,133 loops=648)

  • Index Cond: (((entity_type)::text = 'visit_type'::text) AND ((entity_id)::text = 'i'::text))
21. 2,936.736 2,936.736 ↓ 0.0 0 734,184

Index Scan using receipts_pkey on receipts r_2 (cost=0.43..2.65 rows=1 width=17) (actual time=0.004..0.004 rows=0 loops=734,184)

  • Index Cond: ((receipt_id)::text = (receipt_usage_2.receipt_id)::text)
  • Filter: (is_deposit AND ((realized)::bpchar <> 'Y'::bpchar) AND ((mr_no)::text = (r.mr_no)::text))
  • Rows Removed by Filter: 1
22. 1.428 78.372 ↓ 619.0 619 1

Sort (cost=32,162.87..32,162.88 rows=1 width=47) (actual time=78.340..78.372 rows=619 loops=1)

  • Sort Key: setoffs.mr_no
  • Sort Method: quicksort Memory: 73kB
23. 0.046 76.944 ↓ 619.0 619 1

Subquery Scan on setoffs (cost=32,162.84..32,162.86 rows=1 width=47) (actual time=76.809..76.944 rows=619 loops=1)

24. 0.401 76.898 ↓ 619.0 619 1

HashAggregate (cost=32,162.84..32,162.85 rows=1 width=18) (actual time=76.808..76.898 rows=619 loops=1)

25. 8.371 76.497 ↓ 664.0 664 1

Nested Loop (cost=0.86..32,162.84 rows=1 width=18) (actual time=3.136..76.497 rows=664 loops=1)

26. 7.541 7.541 ↑ 1.0 12,117 1

Index Scan using idx_patient_registration_visit_type on patient_registration pr_1 (cost=0.43..1,292.97 rows=12,433 width=27) (actual time=0.032..7.541 rows=12,117 loops=1)

  • Index Cond: (visit_type = 'i'::bpchar)
27. 60.585 60.585 ↓ 0.0 0 12,117

Index Scan using bill_visit_id_idx on bill b_1 (cost=0.43..2.47 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=12,117)

  • Index Cond: ((visit_id)::text = (pr_1.patient_id)::text)
  • Filter: (ip_deposit_set_off > 0::numeric)
  • Rows Removed by Filter: 1
Total runtime : 8,048.739 ms