explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3QEd

Settings
# exclusive inclusive rows x rows loops node
1. 14.732 292,648.105 ↓ 3,230.0 3,230 1

Nested Loop Left Join (cost=16.38..331.65 rows=1 width=138) (actual time=122.755..292,648.105 rows=3,230 loops=1)

2. 16.415 271,040.823 ↓ 3,230.0 3,230 1

Nested Loop Left Join (cost=15.52..109.56 rows=1 width=106) (actual time=122.667..271,040.823 rows=3,230 loops=1)

3. 40.962 255,565.628 ↓ 3,230.0 3,230 1

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

4. 3.753 41.356 ↓ 4,487.0 4,487 1

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

5. 1.707 1.707 ↓ 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.027..1.707 rows=4,487 loops=1)

  • Index Cond: (((entity_type)::text = 'visit_type'::text) AND ((entity_id)::text = 'i'::text))
6. 35.896 35.896 ↑ 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.008 rows=1 loops=4,487)

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

SubPlan (for HashAggregate)

8. 22.610 128,392.500 ↑ 1.0 1 3,230

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

9. 7,102.770 128,369.890 ↑ 1.0 1 3,230

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

10. 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_1 (cost=0.43..6.20 rows=1 width=10) (actual time=0.026..1.648 rows=4,487 loops=3,230)

  • Index Cond: (((entity_type)::text = 'visit_type'::text) AND ((entity_id)::text = 'i'::text))
11. 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
12. 12.920 127,090.810 ↑ 1.0 1 3,230

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

13. 5,852.760 127,077.890 ↓ 0.0 0 3,230

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

14. 5,281.050 5,281.050 ↓ 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.023..1.635 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_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
16. 16.150 15,458.780 ↑ 1.0 1 3,230

GroupAggregate (cost=0.86..65.50 rows=1 width=13) (actual time=4.786..4.786 rows=1 loops=3,230)

17. 28.960 15,442.630 ↑ 1.0 1 3,230

Nested Loop (cost=0.86..65.48 rows=1 width=13) (actual time=3.607..4.781 rows=1 loops=3,230)

18. 12,464.570 12,464.570 ↑ 1.0 1 3,230

Index Scan using patient_registration_mr_no_index on patient_registration pr (cost=0.43..49.83 rows=1 width=22) (actual time=2.675..3.859 rows=1 loops=3,230)

  • Index Cond: ((r.mr_no)::text = (mr_no)::text)
  • Filter: (visit_type = 'i'::bpchar)
  • Rows Removed by Filter: 8
19. 2,949.100 2,949.100 ↑ 1.0 1 3,830

Index Scan using bill_visit_id_idx on bill b (cost=0.43..15.65 rows=1 width=15) (actual time=0.635..0.770 rows=1 loops=3,830)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
  • Filter: (ip_deposit_set_off > 0::numeric)
  • Rows Removed by Filter: 1
20. 6.460 21,592.550 ↓ 0.0 0 3,230

GroupAggregate (cost=0.86..222.07 rows=1 width=13) (actual time=6.685..6.685 rows=0 loops=3,230)

21. 54.206 21,586.090 ↓ 0.0 0 3,230

Nested Loop (cost=0.86..222.06 rows=1 width=13) (actual time=6.683..6.683 rows=0 loops=3,230)

22. 106.590 106.590 ↑ 1.4 8 3,230

Index Scan using patient_registration_mr_no_index on patient_registration pr_1 (cost=0.43..49.83 rows=11 width=22) (actual time=0.018..0.033 rows=8 loops=3,230)

  • Index Cond: ((r.mr_no)::text = (mr_no)::text)
  • Filter: (visit_type <> 'i'::bpchar)
  • Rows Removed by Filter: 1
23. 21,425.294 21,425.294 ↓ 0.0 0 26,321

Index Scan using bill_visit_id_idx on bill b_1 (cost=0.43..15.65 rows=1 width=15) (actual time=0.814..0.814 rows=0 loops=26,321)

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