explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pmx

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 62.985 ↓ 0.0 0 4,199

Hash Right Join (cost=58671.96..58753.16 rows=1 width=141) (actual time=26192.555..26192.555 rows=0 loops=1)2 (cost=0.43..8.45 rows=1 width=15) (actual time=0.015..0.015 rows=0 loops=4,199)

  • Hash Cond: ((pr_1.mr_no)::text = (r.mr_no)::text) Index Cond: (((mr_no)::text = (r.mr_no)::text) AND (is_deposit = true))
  • -> HashAggregate (cost=29311.91..29346.03 rows=3412 width=13) (never executed)ized)::bpchar <> 'Y'::bpchar))
2. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..29,294.85 rows=3,412 width=13) (never executed)

3. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_ip_deposit_set_off_idx on bill b_1 (cost=0.43..305.41 rows=3664 width=16) (never executed)(cost=0.43..4.45 rows=1 width=10) (cost=0..0 rows=0 width=0) (never executed)

  • Index Cond: (ip_deposit_set_off > 0::numeric)(entity_type)::text = 'visit_type'::text)
4. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.43..7.90 rows=1 width=23) (never executed)

5. 0.000 0.000 ↓ 0.0 0

Index Cond: ((patient_id)::text = (b_1.visit_id)::text)) (cost=0..0 rows=0 width=0) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Filter: (visit_type <> 'i'::bpchar)85 rows=252 width=13) (cost=0..0 rows=0 width=0) (never executed)

7. 0.001 26,192.550 ↓ 0.0 0 1

Hash (cost=29,360.04..29,360.04 rows=1 width=109) (actual time=26,192.550..26,192.550 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
8. 0.001 26,192.549 ↓ 0.0 0 1

Nested Loop Left Join (cost=29,351.82..29,360.04 rows=1 width=109) (actual time=26,192.549..26,192.549 rows=0 loops=1)

  • Join Filter: ((r.mr_no)::text = (pr_2.mr_no)::text)
9. 0.017 26,192.548 ↓ 0.0 0 1

Merge Join (cost=55.71..55.74 rows=1 width=77) (actual time=26,192.548..26,192.548 rows=0 loops=1)

  • Merge Cond: ((pr.mr_no)::text = (r.mr_no)::text)
10. 0.006 0.049 ↑ 1.0 1 1

Sort (cost=16.92..16.92 rows=1 width=13) (actual time=0.048..0.049 rows=1 loops=1)

  • Sort Key: pr.mr_no
  • Sort Method: quicksort Memory: 25kB
11. 0.005 0.043 ↑ 1.0 1 1

Nested Loop (cost=0.86..16.91 rows=1 width=13) (actual time=0.041..0.043 rows=1 loops=1)

12. 0.022 0.022 ↑ 1.0 1 1

Index Scan using bill_pkey on bill b (cost=0.43..8.45 rows=1 width=16) (actual time=0.021..0.022 rows=1 loops=1)

  • Index Cond: ((bill_no)::text = '19/20MIN0050497'::text)
13. 0.016 0.016 ↑ 1.0 1 1

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..8.45 rows=1 width=23) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
14. 21.617 26,192.482 ↓ 29.0 29 1

Sort (cost=38.80..38.80 rows=1 width=74) (actual time=26,192.479..26,192.482 rows=29 loops=1)

  • Sort Key: r.mr_no
  • Sort Method: quicksort Memory: 519kB
15. 10.876 26,170.865 ↓ 4,199.0 4,199 1

HashAggregate (cost=12.91..38.78 rows=1 width=10) (actual time=60.513..26,170.865 rows=4,199 loops=1)

16. 4.864 54.806 ↓ 5,950.0 5,950 1

Nested Loop (cost=0.86..12.91 rows=1 width=10) (actual time=0.031..54.806 rows=5,950 loops=1)

17. 2.342 2.342 ↓ 5,950.0 5,950 1

Index Scan using receipt_usage_entity_type_idx on receipt_usage (cost=0.43..4.45 rows=1 width=10) (actual time=0.020..2.342 rows=5,950 loops=1)

  • Index Cond: ((entity_type)::text = 'visit_type'::text)
  • Filter: ((entity_id)::text = 'i'::text)
18. 47.600 47.600 ↑ 1.0 1 5,950

Index Scan using receipts_pkey on receipts r (cost=0.43..8.45 rows=1 width=20) (actual time=0.008..0.008 rows=1 loops=5,950)

  • Index Cond: ((receipt_id)::text = (receipt_usage.receipt_id)::text)
  • Filter: is_deposit
19.          

SubPlan (for HashAggregate)

20. 8.398 26,033.800 ↑ 1.0 1 4,199

Aggregate (cost=12.92..12.93 rows=1 width=5) (actual time=6.199..6.200 rows=1 loops=4,199)

21. 9,468.913 26,025.402 ↑ 1.0 1 4,199

Nested Loop (cost=0.86..12.91 rows=1 width=5) (actual time=2.380..6.198 rows=1 loops=4,199)

  • Join Filter: ((r_1.receipt_id)::text = (receipt_usage_1.receipt_id)::text)
  • Rows Removed by Join Filter: 11577
22. 75.582 75.582 ↓ 2.0 2 4,199

Index Scan using idx_receipts_mrno_isdeposit on receipts r_1 (cost=0.43..8.45 rows=1 width=15) (actual time=0.016..0.018 rows=2 loops=4,199)

  • Index Cond: (((mr_no)::text = (r.mr_no)::text) AND (is_deposit = true))
  • Filter: (is_deposit AND ((realized)::bpchar = 'Y'::bpchar))
23. 16,480.907 16,480.907 ↓ 5,950.0 5,950 8,171

Index Scan using receipt_usage_entity_type_idx on receipt_usage receipt_usage_1 (cost=0.43..4.45 rows=1 width=10) (actual time=0.014..2.017 rows=5,950 loops=8,171)

  • Index Cond: ((entity_type)::text = 'visit_type'::text)
  • Filter: ((entity_id)::text = 'i'::text)
24. 4.199 71.383 ↑ 1.0 1 4,199

Aggregate (cost=12.92..12.93 rows=1 width=5) (actual time=0.017..0.017 rows=1 loops=4,199)

25. 4.199 67.184 ↓ 0.0 0 4,199

Nested Loop (cost=0.86..12.91 rows=1 width=5) (actual time=0.016..0.016 rows=0 loops=4,199)

  • Join Filter: ((r_2.receipt_id)::text = (receipt_usage_2.receipt_id)::text)
26. 62.985 62.985 ↓ 0.0 0 4,199

Index Scan using idx_receipts_mrno_isdeposit on receipts r_2 (cost=0.43..8.45 rows=1 width=15) (actual time=0.015..0.015 rows=0 loops=4,199)

  • Index Cond: (((mr_no)::text = (r.mr_no)::text) AND (is_deposit = true))
  • Filter: (is_deposit AND ((realized)::bpchar <> 'Y'::bpchar))
  • Rows Removed by Filter: 2
27. 0.000 0.000 ↓ 0.0 0

Index Scan using receipt_usage_entity_type_idx on receipt_usage receipt_usage_2 (cost=0.43..4.45 rows=1 width=10) (never executed)

  • Index Cond: ((entity_type)::text = 'visit_type'::text)
  • Filter: ((entity_id)::text = 'i'::text)
28. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=29,296.11..29,298.63 rows=252 width=13) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..29,294.85 rows=252 width=13) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_ip_deposit_set_off_idx on bill b_2 (cost=0.43..305.41 rows=3,664 width=16) (never executed)

  • Index Cond: (ip_deposit_set_off > 0::numeric)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_pkey on patient_registration pr_2 (cost=0.43..7.90 rows=1 width=23) (never executed)

  • Index Cond: ((patient_id)::text = (b_2.visit_id)::text)
  • Filter: (visit_type = 'i'::bpchar)
Total runtime : 26,192.888 ms