explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3A2T

Settings
# exclusive inclusive rows x rows loops node
1. 162.105 25,676.868 ↑ 91.4 648 1

Merge Right Join (cost=871,774.51..904,340.74 rows=59,196 width=143) (actual time=20,931.167..25,676.868 rows=648 loops=1)

  • Merge Cond: ((pr.mr_no)::text = (r.mr_no)::text)
2. 430.520 18,426.033 ↓ 2.1 1,361,769 1

GroupAggregate (cost=839,276.68..862,714.95 rows=657,731 width=18) (actual time=13,845.920..18,426.033 rows=1,361,769 loops=1)

3. 12,792.233 17,995.513 ↑ 1.0 2,211,501 1

Sort (cost=839,276.68..844,897.00 rows=2,248,128 width=18) (actual time=13,845.907..17,995.513 rows=2,211,501 loops=1)

  • Sort Key: pr.mr_no
  • Sort Method: external merge Disk: 56400kB
4. 1,960.094 5,203.280 ↑ 1.0 2,212,302 1

Hash Join (cost=144,604.61..575,091.24 rows=2,248,128 width=18) (actual time=1,335.404..5,203.280 rows=2,212,302 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
5. 1,908.397 1,908.397 ↓ 1.0 3,238,847 1

Seq Scan on bill b (cost=0.00..337,355.34 rows=3,201,734 width=14) (actual time=0.012..1,908.397 rows=3,238,847 loops=1)

6. 495.992 1,334.789 ↓ 1.0 2,248,444 1

Hash (cost=101,134.01..101,134.01 rows=2,248,128 width=27) (actual time=1,334.789..1,334.789 rows=2,248,444 loops=1)

  • Buckets: 16384 Batches: 32 Memory Usage: 4177kB
7. 838.797 838.797 ↓ 1.0 2,248,444 1

Seq Scan on patient_registration pr (cost=0.00..101,134.01 rows=2,248,128 width=27) (actual time=0.010..838.797 rows=2,248,444 loops=1)

  • Filter: (visit_type <> 'i'::bpchar)
  • Rows Removed by Filter: 12117
8. 0.111 7,088.730 ↓ 36.0 648 1

Materialize (cost=32,497.83..32,516.25 rows=18 width=111) (actual time=7,084.181..7,088.730 rows=648 loops=1)

9. 2.755 7,088.619 ↓ 36.0 648 1

Merge Left Join (cost=32,497.83..32,516.21 rows=18 width=111) (actual time=7,084.176..7,088.619 rows=648 loops=1)

  • Merge Cond: ((r.mr_no)::text = (setoffs.mr_no)::text)
10. 1.762 6,962.280 ↓ 648.0 648 1

Sort (cost=15.98..15.99 rows=1 width=79) (actual time=6,962.170..6,962.280 rows=648 loops=1)

  • Sort Key: r.mr_no
  • Sort Method: quicksort Memory: 75kB
11. 1.461 6,960.518 ↓ 648.0 648 1

HashAggregate (cost=5.31..15.96 rows=1 width=15) (actual time=17.096..6,960.518 rows=648 loops=1)

12. 0.034 6.017 ↓ 1,133.0 1,133 1

Nested Loop (cost=0.85..5.30 rows=1 width=15) (actual time=0.085..6.017 rows=1,133 loops=1)

13. 0.318 0.318 ↓ 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.052..0.318 rows=1,133 loops=1)

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

Index Scan using receipts_pkey on receipts r (cost=0.43..2.65 rows=1 width=27) (actual time=0.005..0.005 rows=1 loops=1,133)

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

SubPlan (for HashAggregate)

16. 0.648 3,488.832 ↑ 1.0 1 648

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

17. 418.608 3,488.184 ↓ 2.0 2 648

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

18. 132.840 132.840 ↓ 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.205 rows=1,133 loops=648)

  • Index Cond: (((entity_type)::text = 'visit_type'::text) AND ((entity_id)::text = 'i'::text))
19. 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
20. 0.000 3,464.208 ↑ 1.0 1 648

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

21. 393.984 3,464.208 ↓ 0.0 0 648

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

22. 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))
23. 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
24. 33.557 123.584 ↓ 3.0 10,747 1

Sort (cost=32,481.85..32,490.94 rows=3,638 width=47) (actual time=121.991..123.584 rows=10,747 loops=1)

  • Sort Key: setoffs.mr_no
  • Sort Method: quicksort Memory: 1107kB
25. 0.888 90.027 ↓ 3.1 11,156 1

Subquery Scan on setoffs (cost=32,193.92..32,266.68 rows=3,638 width=47) (actual time=87.582..90.027 rows=11,156 loops=1)

26. 6.931 89.139 ↓ 3.1 11,156 1

HashAggregate (cost=32,193.92..32,230.30 rows=3,638 width=18) (actual time=87.580..89.139 rows=11,156 loops=1)

27. 13.598 82.208 ↓ 1.1 14,111 1

Nested Loop (cost=0.86..32,131.75 rows=12,433 width=18) (actual time=0.075..82.208 rows=14,111 loops=1)

28. 8.025 8.025 ↑ 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.034..8.025 rows=12,117 loops=1)

  • Index Cond: (visit_type = 'i'::bpchar)
29. 60.585 60.585 ↑ 1.0 1 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=1 loops=12,117)

  • Index Cond: ((visit_id)::text = (pr_1.patient_id)::text)
Total runtime : 25,686.651 ms