explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RsXJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 255,429.236 ↓ 0.0 0 1

Nested Loop Left Join (cost=17.23..348.56 rows=1 width=141) (actual time=255,429.236..255,429.236 rows=0 loops=1)

2. 0.002 255,429.236 ↓ 0.0 0 1

Nested Loop Left Join (cost=16.38..126.47 rows=1 width=109) (actual time=255,429.236..255,429.236 rows=0 loops=1)

3. 6.614 255,429.234 ↓ 0.0 0 1

Nested Loop (cost=15.52..60.95 rows=1 width=77) (actual time=255,429.234..255,429.234 rows=0 loops=1)

  • Join Filter: ((pr.mr_no)::text = (r.mr_no)::text)
  • Rows Removed by Join Filter: 3230
4. 0.008 0.074 ↑ 1.0 1 1

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

5. 0.038 0.038 ↑ 1.0 1 1

Index Scan using bill_pkey on bill b (cost=0.43..8.45 rows=1 width=15) (actual time=0.035..0.038 rows=1 loops=1)

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

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

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
7. 34.143 255,422.546 ↓ 3,230.0 3,230 1

HashAggregate (cost=14.66..44.03 rows=1 width=10) (actual time=125.267..255,422.546 rows=3,230 loops=1)

8. 1.656 43.983 ↓ 4,487.0 4,487 1

Nested Loop (cost=0.86..14.66 rows=1 width=10) (actual time=0.058..43.983 rows=4,487 loops=1)

9. 1.944 1.944 ↓ 4,487.0 4,487 1

Index Scan using idx_receipt_usage_entitytype_entityid on receipt_usage (cost=0.43..6.20 rows=1 width=10) (actual time=0.032..1.944 rows=4,487 loops=1)

  • Index Cond: (((entity_type)::text = 'visit_type'::text) AND ((entity_id)::text = 'i'::text))
10. 40.383 40.383 ↑ 1.0 1 4,487

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

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

SubPlan (for HashAggregate)

12. 22.610 127,966.140 ↑ 1.0 1 3,230

Aggregate (cost=14.67..14.68 rows=1 width=5) (actual time=39.617..39.618 rows=1 loops=3,230)

13. 6,753.930 127,943.530 ↑ 1.0 1 3,230

Nested Loop (cost=0.86..14.66 rows=1 width=5) (actual time=18.706..39.611 rows=1 loops=3,230)

14. 5,245.520 5,245.520 ↓ 4,487.0 4,487 3,230

Index Scan using idx_receipt_usage_entitytype_entityid on receipt_usage receipt_usage_1 (cost=0.43..6.20 rows=1 width=10) (actual time=0.022..1.624 rows=4,487 loops=3,230)

  • Index Cond: (((entity_type)::text = 'visit_type'::text) AND ((entity_id)::text = 'i'::text))
15. 115,944.080 115,944.080 ↓ 0.0 0 14,493,010

Index Scan using receipts_pkey on receipts r_1 (cost=0.43..8.46 rows=1 width=15) (actual time=0.008..0.008 rows=0 loops=14,493,010)

  • 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
16. 12.920 127,378.280 ↑ 1.0 1 3,230

Aggregate (cost=14.67..14.68 rows=1 width=5) (actual time=39.435..39.436 rows=1 loops=3,230)

17. 6,098.240 127,365.360 ↓ 0.0 0 3,230

Nested Loop (cost=0.86..14.66 rows=1 width=5) (actual time=39.432..39.432 rows=0 loops=3,230)

18. 5,323.040 5,323.040 ↓ 4,487.0 4,487 3,230

Index Scan using idx_receipt_usage_entitytype_entityid on receipt_usage receipt_usage_2 (cost=0.43..6.20 rows=1 width=10) (actual time=0.022..1.648 rows=4,487 loops=3,230)

  • Index Cond: (((entity_type)::text = 'visit_type'::text) AND ((entity_id)::text = 'i'::text))
19. 115,944.080 115,944.080 ↓ 0.0 0 14,493,010

Index Scan using receipts_pkey on receipts r_2 (cost=0.43..8.46 rows=1 width=15) (actual time=0.008..0.008 rows=0 loops=14,493,010)

  • 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
20. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.86..65.50 rows=1 width=13) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..65.48 rows=1 width=13) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_mr_no_index on patient_registration pr_1 (cost=0.43..49.83 rows=1 width=22) (never executed)

  • Index Cond: ((r.mr_no)::text = (mr_no)::text)
  • Filter: (visit_type = 'i'::bpchar)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_visit_id_idx on bill b_1 (cost=0.43..15.65 rows=1 width=15) (never executed)

  • Index Cond: ((visit_id)::text = (pr_1.patient_id)::text)
  • Filter: (ip_deposit_set_off > 0::numeric)
24. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.86..222.07 rows=1 width=13) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..222.06 rows=1 width=13) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_mr_no_index on patient_registration pr_2 (cost=0.43..49.83 rows=11 width=22) (never executed)

  • Filter: (visit_type <> 'i'::bpchar)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_visit_id_idx on bill b_2 (cost=0.43..15.65 rows=1 width=15) (never executed)

  • Index Cond: ((visit_id)::text = (pr_2.patient_id)::text)
  • Filter: (ip_deposit_set_off > 0::numeric)
Total runtime : 255,429.662 ms