explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kb12

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 23,286.631 ↑ 1.0 1 1

Aggregate (cost=796,682.81..796,682.82 rows=1 width=9) (actual time=23,286.631..23,286.631 rows=1 loops=1)

2. 0.079 23,286.622 ↑ 24.0 1 1

Hash Left Join (cost=373,530.24..796,682.51 rows=24 width=1,774) (actual time=13,442.871..23,286.622 rows=1 loops=1)

  • Hash Cond: ((b.bill_no)::text = (isr.billno)::text)
3. 0.646 23,286.543 ↑ 24.0 1 1

Hash Left Join (cost=373,519.56..796,665.56 rows=24 width=1,268) (actual time=13,442.792..23,286.543 rows=1 loops=1)

  • Hash Cond: ((b.visit_id)::text = (phc.customer_id)::text)
4. 0.034 23,285.897 ↑ 24.0 1 1

Hash Left Join (cost=373,465.53..796,611.39 rows=24 width=1,258) (actual time=13,442.146..23,285.897 rows=1 loops=1)

  • Hash Cond: ((r.counter)::text = (c.counter_id)::text)
5. 0.004 23,285.863 ↑ 24.0 1 1

Nested Loop (cost=373,464.39..796,609.92 rows=24 width=1,235) (actual time=13,442.114..23,285.863 rows=1 loops=1)

6. 0.017 0.436 ↑ 1.0 1 1

Hash Right Join (cost=8.70..9.92 rows=1 width=36) (actual time=0.424..0.436 rows=1 loops=1)

  • Hash Cond: ((s.salutation_id)::text = (p.salutation)::text)
7. 0.006 0.006 ↑ 1.0 15 1

Seq Scan on salutation_master s (cost=0.00..1.15 rows=15 width=14) (actual time=0.002..0.006 rows=15 loops=1)

8. 0.002 0.413 ↑ 1.0 1 1

Hash (cost=8.69..8.69 rows=1 width=40) (actual time=0.413..0.413 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
9. 0.411 0.411 ↑ 1.0 1 1

Index Scan using patient_details_pkey on patient_details p (cost=0.42..8.69 rows=1 width=40) (actual time=0.410..0.411 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = '19012513'::text)
  • Filter: patient_confidentiality_check(COALESCE(patient_group, 0), mr_no)
10. 265.368 23,285.423 ↑ 24.0 1 1

Hash Join (cost=373,455.69..796,599.77 rows=24 width=1,208) (actual time=13,441.686..23,285.423 rows=1 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
11. 551.552 23,019.415 ↑ 1.0 1,709,848 1

Hash Left Join (cost=373,410.56..790,140.60 rows=1,710,345 width=1,195) (actual time=6,613.063..23,019.415 rows=1,709,848 loops=1)

  • Hash Cond: (r.currency_id = fc.currency_id)
12. 953.578 22,467.850 ↑ 1.0 1,709,848 1

Hash Join (cost=373,397.86..768,342.09 rows=1,710,345 width=679) (actual time=6,613.053..22,467.850 rows=1,709,848 loops=1)

  • Hash Cond: (r.payment_mode_id = pm.mode_id)
13. 565.492 21,514.272 ↑ 1.0 1,709,848 1

Hash Left Join (cost=373,396.65..744,823.65 rows=1,710,345 width=656) (actual time=6,613.038..21,514.272 rows=1,709,848 loops=1)

  • Hash Cond: ((r.receipt_id)::text = (receipt_usage.receipt_id)::text)
14. 12,961.143 20,948.759 ↑ 1.0 1,709,848 1

Hash Join (cost=373,383.33..738,396.52 rows=1,710,345 width=647) (actual time=6,612.988..20,948.759 rows=1,709,848 loops=1)

  • Hash Cond: ((r.receipt_id)::text = (br.receipt_no)::text)
15. 1,057.612 1,380.387 ↑ 1.0 1,709,848 1

Hash Left Join (cost=1.11..76,657.12 rows=1,710,358 width=518) (actual time=0.009..1,380.387 rows=1,709,848 loops=1)

  • Hash Cond: (r.card_type_id = ctm.card_type_id)
16. 322.771 322.771 ↑ 1.0 1,709,848 1

Seq Scan on receipts r (cost=0.00..53,138.58 rows=1,710,358 width=509) (actual time=0.003..322.771 rows=1,709,848 loops=1)

17. 0.000 0.004 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=13) (actual time=0.004..0.004 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
18. 0.004 0.004 ↑ 1.0 5 1

Seq Scan on card_type_master ctm (cost=0.00..1.05 rows=5 width=13) (actual time=0.002..0.004 rows=5 loops=1)

19. 898.541 6,607.228 ↑ 1.0 1,709,848 1

Hash (cost=318,596.90..318,596.90 rows=1,710,345 width=129) (actual time=6,607.228..6,607.228 rows=1,709,848 loops=1)

  • Buckets: 1024 Batches: 512 Memory Usage: 345kB
20. 723.354 5,708.677 ↑ 1.0 1,709,848 1

Hash Right Join (cost=294,979.63..318,596.90 rows=1,710,345 width=129) (actual time=4,985.295..5,708.677 rows=1,709,848 loops=1)

  • Hash Cond: ((bcn.credit_note_bill_no)::text = (br.bill_no)::text)
21. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on bill_credit_notes bcn (cost=0.00..15.00 rows=500 width=48) (actual time=0.002..0.002 rows=0 loops=1)

22. 817.202 4,985.288 ↑ 1.0 1,709,848 1

Hash (cost=250,216.32..250,216.32 rows=1,710,345 width=81) (actual time=4,985.288..4,985.288 rows=1,709,848 loops=1)

  • Buckets: 1024 Batches: 256 Memory Usage: 680kB
23. 1,842.153 4,168.086 ↑ 1.0 1,709,848 1

Hash Join (cost=125,260.97..250,216.32 rows=1,710,345 width=81) (actual time=1,490.374..4,168.086 rows=1,709,848 loops=1)

  • Hash Cond: ((br.bill_no)::text = (b.bill_no)::text)
24. 835.996 835.996 ↑ 1.0 1,709,848 1

Seq Scan on bill_receipts br (cost=0.00..45,953.45 rows=1,710,345 width=53) (actual time=0.042..835.996 rows=1,709,848 loops=1)

25. 600.984 1,489.937 ↓ 1.0 1,667,493 1

Hash (cost=93,046.43..93,046.43 rows=1,666,043 width=28) (actual time=1,489.937..1,489.937 rows=1,667,493 loops=1)

  • Buckets: 2048 Batches: 128 Memory Usage: 793kB
26. 888.953 888.953 ↓ 1.0 1,667,493 1

Seq Scan on bill b (cost=0.00..93,046.43 rows=1,666,043 width=28) (actual time=0.009..888.953 rows=1,667,493 loops=1)

27. 0.001 0.033 ↓ 0.0 0 1

Hash (cost=13.32..13.32 rows=1 width=27) (actual time=0.033..0.033 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
28. 0.032 0.032 ↓ 0.0 0 1

Index Scan using receipt_usage_entity_type_idx on receipt_usage (cost=0.43..13.32 rows=1 width=27) (actual time=0.032..0.032 rows=0 loops=1)

  • Index Cond: ((entity_type)::text = ANY ('{package_id,visit_type,bill_type}'::text[]))
29. 0.003 0.010 ↓ 1.4 13 1

Hash (cost=1.09..1.09 rows=9 width=27) (actual time=0.010..0.010 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
30. 0.007 0.007 ↓ 1.4 13 1

Seq Scan on payment_mode_master pm (cost=0.00..1.09 rows=9 width=27) (actual time=0.003..0.007 rows=13 loops=1)

31. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=11.20..11.20 rows=120 width=520) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
32. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on foreign_currency fc (cost=0.00..11.20 rows=120 width=520) (actual time=0.001..0.001 rows=0 loops=1)

33. 0.001 0.021 ↑ 11.0 1 1

Hash (cost=45.00..45.00 rows=11 width=23) (actual time=0.021..0.021 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
34. 0.020 0.020 ↑ 11.0 1 1

Index Scan using patient_registration_mr_no_index on patient_registration pr (cost=0.42..45.00 rows=11 width=23) (actual time=0.019..0.020 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = '19012513'::text)
35. 0.004 0.013 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=31) (actual time=0.013..0.013 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
36. 0.009 0.009 ↑ 1.0 6 1

Seq Scan on counters c (cost=0.00..1.06 rows=6 width=31) (actual time=0.008..0.009 rows=6 loops=1)

37. 0.356 0.640 ↑ 1.0 1,735 1

Hash (cost=32.35..32.35 rows=1,735 width=19) (actual time=0.640..0.640 rows=1,735 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 87kB
38. 0.284 0.284 ↑ 1.0 1,735 1

Seq Scan on store_retail_customers phc (cost=0.00..32.35 rows=1,735 width=19) (actual time=0.004..0.284 rows=1,735 loops=1)

39. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=10.30..10.30 rows=30 width=584) (actual time=0.000..0.000 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
40. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on incoming_sample_registration isr (cost=0.00..10.30 rows=30 width=584) (actual time=0.000..0.000 rows=0 loops=1)