explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E4Xx

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,872.853 ↓ 0.0 0 1

Insert on temp_package_content_charges (cost=291,073.25..339,503.47 rows=145,566 width=90) (actual time=4,872.853..4,872.853 rows=0 loops=1)

2. 0.001 4,872.851 ↓ 0.0 0 1

Subquery Scan on *SELECT* (cost=291,073.25..339,503.47 rows=145,566 width=90) (actual time=4,872.851..4,872.851 rows=0 loops=1)

3. 0.002 4,872.850 ↓ 0.0 0 1

Merge Join (cost=291,073.25..336,592.15 rows=145,566 width=73) (actual time=4,872.850..4,872.850 rows=0 loops=1)

  • Merge Cond: ((pmmap.package_id = pcont.package_id) AND ((pc.bed_type)::text = (dc.bed_type)::text) AND ((pc.org_id)::text = (dc.org_name)::text))
4. 1,040.166 1,266.916 ↑ 528,199.0 1 1

Sort (cost=91,783.17..93,103.66 rows=528,199 width=30) (actual time=1,266.916..1,266.916 rows=1 loops=1)

  • Sort Key: pmmap.package_id, pc.bed_type, pc.org_id
  • Sort Method: external merge Disk: 22040kB
5. 153.669 226.750 ↓ 1.0 531,240 1

Hash Join (cost=13.26..16,298.95 rows=528,199 width=30) (actual time=0.230..226.750 rows=531,240 loops=1)

  • Hash Cond: (pc.package_id = pm.package_id)
6. 72.875 72.875 ↑ 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.004..72.875 rows=580,644 loops=1)

7. 0.036 0.206 ↑ 1.0 141 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
8. 0.058 0.170 ↑ 1.0 141 1

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

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

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

10. 0.033 0.065 ↑ 1.0 141 1

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

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

12. 0.005 3,605.932 ↓ 0.0 0 1

Materialize (cost=199,290.08..200,100.42 rows=162,067 width=63) (actual time=3,605.932..3,605.932 rows=0 loops=1)

13. 0.010 3,605.927 ↓ 0.0 0 1

Sort (cost=199,290.08..199,695.25 rows=162,067 width=63) (actual time=3,605.927..3,605.927 rows=0 loops=1)

  • Sort Key: pcont.package_id, dc.bed_type, dc.org_name
  • Sort Method: quicksort Memory: 25kB
14. 1,569.426 3,605.917 ↓ 0.0 0 1

Hash Join (cost=145,051.16..173,079.24 rows=162,067 width=63) (actual time=3,605.917..3,605.917 rows=0 loops=1)

  • Hash Cond: ((pcont.activity_id)::text = (dc.test_id)::text)
15. 0.149 0.149 ↑ 1.0 45 1

Seq Scan on temp_package_contents pcont (cost=0.00..20.58 rows=45 width=40) (actual time=0.013..0.149 rows=45 loops=1)

  • Filter: ((activity_type)::text = 'Service'::text)
  • Rows Removed by Filter: 641
16. 1,251.617 2,036.342 ↑ 1.0 3,811,607 1

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

  • Buckets: 2048 Batches: 2048 (originally 256) Memory Usage: 1025kB
17. 784.725 784.725 ↑ 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=1.165..784.725 rows=3,811,607 loops=1)

Total runtime : 4,893.306 ms