explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BQJm

Settings
# exclusive inclusive rows x rows loops node
1. 14,582.719 59,103.984 ↓ 1.0 2,456,700 1

Merge Right Join (cost=1,532,802.98..2,348,144.83 rows=2,352,887 width=104) (actual time=32,389.839..59,103.984 rows=2,456,700 loops=1)

  • Merge Cond: (((dc.test_id)::text = (pcont.activity_id)::text) AND ((dc.bed_type)::text = (pc.bed_type)::text) AND ((dc.org_name)::text = (pc.org_id)::text))
2. 18,164.379 18,944.810 ↑ 1.0 3,613,247 1

Sort (cost=722,286.95..731,320.07 rows=3,613,247 width=31) (actual time=14,866.023..18,944.810 rows=3,613,247 loops=1)

  • Sort Key: dc.test_id, dc.bed_type, dc.org_name
  • Sort Method: external merge Disk: 146192kB
3. 780.431 780.431 ↑ 1.0 3,613,247 1

Seq Scan on diagnostic_charges dc (cost=0.00..69,366.47 rows=3,613,247 width=31) (actual time=0.720..780.431 rows=3,613,247 loops=1)

4. 971.427 25,576.455 ↓ 1.0 2,456,700 1

Materialize (cost=810,516.03..933,355.50 rows=2,352,887 width=97) (actual time=17,518.721..25,576.455 rows=2,456,700 loops=1)

5. 659.353 24,605.028 ↓ 1.0 2,456,700 1

Merge Left Join (cost=810,516.03..927,473.28 rows=2,352,887 width=97) (actual time=17,518.706..24,605.028 rows=2,456,700 loops=1)

  • Merge Cond: (((pcont.activity_id)::text = (ec.equip_id)::text) AND ((pc.bed_type)::text = (ec.bed_type)::text) AND ((pc.org_id)::text = (ec.org_id)::text))
6. 814.516 23,469.981 ↓ 1.0 2,456,700 1

Merge Left Join (cost=795,337.75..891,854.09 rows=2,352,887 width=91) (actual time=17,187.935..23,469.981 rows=2,456,700 loops=1)

  • Merge Cond: (((pcont.activity_id)::text = (oc.op_id)::text) AND ((pc.bed_type)::text = (oc.bed_type)::text) AND ((pc.org_id)::text = (oc.org_id)::text))
7. 1,112.266 18,803.131 ↓ 1.0 2,456,700 1

Merge Left Join (cost=696,041.31..751,056.10 rows=2,352,887 width=73) (actual time=14,183.737..18,803.131 rows=2,456,700 loops=1)

  • Merge Cond: (((pcont.activity_id)::text = (smc.service_id)::text) AND ((pc.bed_type)::text = (smc.bed_type)::text) AND ((pc.org_id)::text = (smc.org_id)::text))
8. 13,124.939 13,815.401 ↓ 1.0 2,456,700 1

Sort (cost=575,281.92..581,164.13 rows=2,352,887 width=66) (actual time=11,155.694..13,815.401 rows=2,456,700 loops=1)

  • Sort Key: pcont.activity_id, pc.bed_type, pc.org_id
  • Sort Method: external merge Disk: 192800kB
9. 581.784 690.462 ↓ 1.0 2,456,700 1

Hash Join (cost=71.26..36,759.38 rows=2,352,887 width=66) (actual time=5.471..690.462 rows=2,456,700 loops=1)

  • Hash Cond: (pc.package_id = pm.package_id)
10. 107.851 107.851 ↑ 1.0 580,644 1

Seq Scan on package_charges pc (cost=0.00..15,968.44 rows=580,644 width=30) (actual time=0.042..107.851 rows=580,644 loops=1)

11. 0.192 0.827 ↓ 1.0 651 1

Hash (cost=63.46..63.46 rows=624 width=48) (actual time=0.827..0.827 rows=651 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
12. 0.336 0.635 ↓ 1.0 651 1

Hash Join (cost=13.79..63.46 rows=624 width=48) (actual time=0.271..0.635 rows=651 loops=1)

  • Hash Cond: (pcont.package_id = pmmap.package_id)
13. 0.130 0.130 ↑ 1.0 686 1

Seq Scan on package_contents pcont (cost=0.00..40.86 rows=686 width=40) (actual time=0.009..0.130 rows=686 loops=1)

14. 0.023 0.169 ↑ 1.0 141 1

Hash (cost=12.03..12.03 rows=141 width=12) (actual time=0.169..0.169 rows=141 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
15. 0.044 0.146 ↑ 1.0 141 1

Hash Join (cost=4.17..12.03 rows=141 width=12) (actual time=0.071..0.146 rows=141 loops=1)

  • Hash Cond: (pm.package_id = pmmap.pack_master_id)
16. 0.057 0.057 ↑ 1.5 141 1

Seq Scan on pack_master pm (cost=0.00..5.65 rows=212 width=4) (actual time=0.010..0.057 rows=141 loops=1)

  • Filter: (type = 'P'::bpchar)
  • Rows Removed by Filter: 14
17. 0.027 0.045 ↑ 1.0 141 1

Hash (cost=2.41..2.41 rows=141 width=8) (actual time=0.045..0.045 rows=141 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
18. 0.018 0.018 ↑ 1.0 141 1

Seq Scan on temp_pack_master_packages_mapping pmmap (cost=0.00..2.41 rows=141 width=8) (actual time=0.003..0.018 rows=141 loops=1)

19. 88.370 3,875.464 ↓ 1.1 815,860 1

Materialize (cost=120,759.39..124,445.39 rows=737,200 width=32) (actual time=3,028.032..3,875.464 rows=815,860 loops=1)

20. 3,597.098 3,787.094 ↑ 1.0 737,200 1

Sort (cost=120,759.39..122,602.39 rows=737,200 width=32) (actual time=3,027.991..3,787.094 rows=737,200 loops=1)

  • Sort Key: smc.service_id, smc.bed_type, smc.org_id
  • Sort Method: external merge Disk: 31296kB
21. 189.996 189.996 ↑ 1.0 737,200 1

Seq Scan on service_master_charges smc (cost=0.00..13,633.00 rows=737,200 width=32) (actual time=0.621..189.996 rows=737,200 loops=1)

22. 73.727 3,852.334 ↓ 1.2 665,000 1

Materialize (cost=99,296.44..102,089.44 rows=558,600 width=43) (actual time=3,004.192..3,852.334 rows=665,000 loops=1)

23. 3,598.715 3,778.607 ↑ 1.0 558,600 1

Sort (cost=99,296.44..100,692.94 rows=558,600 width=43) (actual time=3,002.617..3,778.607 rows=558,600 loops=1)

  • Sort Key: oc.op_id, oc.bed_type, oc.org_id
  • Sort Method: external merge Disk: 30424kB
24. 179.892 179.892 ↑ 1.0 558,600 1

Seq Scan on operation_charges oc (cost=0.00..11,604.00 rows=558,600 width=43) (actual time=0.632..179.892 rows=558,600 loops=1)

25. 12.634 475.694 ↑ 1.0 117,800 1

Materialize (cost=15,178.28..15,767.28 rows=117,800 width=31) (actual time=330.766..475.694 rows=117,800 loops=1)

26. 434.138 463.060 ↑ 1.0 117,800 1

Sort (cost=15,178.28..15,472.78 rows=117,800 width=31) (actual time=330.763..463.060 rows=117,800 loops=1)

  • Sort Key: ec.equip_id, ec.bed_type, ec.org_id
  • Sort Method: external merge Disk: 4840kB
27. 28.922 28.922 ↑ 1.0 117,800 1

Seq Scan on equipement_charges ec (cost=0.00..2,435.00 rows=117,800 width=31) (actual time=0.015..28.922 rows=117,800 loops=1)