explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZVRd

Settings
# exclusive inclusive rows x rows loops node
1. 10,396.859 42,666.495 ↓ 0.0 0 1

Insert on temp_package_content_charges (cost=650,860.84..1,124,958.40 rows=1,601,223 width=90) (actual time=42,666.495..42,666.495 rows=0 loops=1)

2. 2,317.589 32,269.636 ↓ 1.2 1,874,183 1

Subquery Scan on *SELECT* (cost=650,860.84..1,124,958.40 rows=1,601,223 width=90) (actual time=16,799.050..32,269.636 rows=1,874,183 loops=1)

3. 9,794.826 29,952.047 ↓ 1.2 1,874,183 1

Merge Join (cost=650,860.84..1,092,933.94 rows=1,601,223 width=73) (actual time=16,799.019..29,952.047 rows=1,874,183 loops=1)

  • Merge Cond: ((pc.package_id = pm.package_id) AND ((pc.bed_type)::text = (dc.bed_type)::text) AND ((pc.org_id)::text = (dc.org_name)::text))
4. 1,596.419 1,716.680 ↑ 1.0 580,644 1

Sort (cost=93,625.25..95,076.86 rows=580,644 width=30) (actual time=1,221.728..1,716.680 rows=580,644 loops=1)

  • Sort Key: pc.package_id, pc.bed_type, pc.org_id
  • Sort Method: external merge Disk: 23992kB
5. 120.261 120.261 ↑ 1.0 580,644 1

Seq Scan on package_charges pc (cost=0.00..10,246.44 rows=580,644 width=30) (actual time=0.002..120.261 rows=580,644 loops=1)

6. 279.362 18,440.541 ↓ 1.2 1,882,733 1

Materialize (cost=557,235.59..565,338.93 rows=1,620,668 width=71) (actual time=15,577.079..18,440.541 rows=1,882,733 loops=1)

7. 13,791.597 18,161.179 ↓ 1.2 1,882,733 1

Sort (cost=557,235.59..561,287.26 rows=1,620,668 width=71) (actual time=15,577.073..18,161.179 rows=1,882,733 loops=1)

  • Sort Key: pm.package_id, dc.bed_type, dc.org_name
  • Sort Method: external merge Disk: 154576kB
8. 2,323.334 4,369.582 ↓ 1.2 1,882,733 1

Hash Join (cost=145,064.42..190,652.12 rows=1,620,668 width=71) (actual time=2,061.750..4,369.582 rows=1,882,733 loops=1)

  • Hash Cond: ((pcont.activity_id)::text = (dc.test_id)::text)
9. 0.401 0.945 ↓ 1.1 515 1

Hash Join (cost=13.26..41.91 rows=450 width=48) (actual time=0.209..0.945 rows=515 loops=1)

  • Hash Cond: (pcont.package_id = pmmap.package_id)
10. 0.378 0.378 ↓ 1.1 541 1

Seq Scan on temp_package_contents pcont (cost=0.00..22.29 rows=495 width=40) (actual time=0.011..0.378 rows=541 loops=1)

  • Filter: (((activity_type)::text = 'Laboratory'::text) OR ((activity_type)::text = 'Radiology'::text))
  • Rows Removed by Filter: 145
11. 0.029 0.166 ↑ 1.0 141 1

Hash (cost=11.50..11.50 rows=141 width=12) (actual time=0.166..0.166 rows=141 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
12. 0.063 0.137 ↑ 1.0 141 1

Hash Join (cost=4.17..11.50 rows=141 width=12) (actual time=0.081..0.137 rows=141 loops=1)

  • Hash Cond: (pm.package_id = pmmap.pack_master_id)
13. 0.030 0.030 ↑ 1.4 155 1

Seq Scan on pack_master pm (cost=0.00..5.12 rows=212 width=4) (actual time=0.004..0.030 rows=155 loops=1)

14. 0.029 0.044 ↑ 1.0 141 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
15. 0.015 0.015 ↑ 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.004..0.015 rows=141 loops=1)

16. 1,262.648 2,045.303 ↑ 1.0 3,811,607 1

Hash (cost=71,350.07..71,350.07 rows=3,811,607 width=31) (actual time=2,045.303..2,045.303 rows=3,811,607 loops=1)

  • Buckets: 2048 Batches: 2048 (originally 256) Memory Usage: 1025kB
17. 782.655 782.655 ↑ 1.0 3,811,607 1

Seq Scan on diagnostic_charges dc (cost=0.00..71,350.07 rows=3,811,607 width=31) (actual time=0.233..782.655 rows=3,811,607 loops=1)