explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4qid

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

Insert on temp_package_content_charges (cost=805,436.88..1,460,254.49 rows=2,219,069 width=90) (actual time=42,547.468..42,547.468 rows=0 loops=1)

2. 2,263.811 32,423.014 ↑ 1.2 1,874,183 1

Subquery Scan on *SELECT* (cost=805,436.88..1,460,254.49 rows=2,219,069 width=90) (actual time=17,061.758..32,423.014 rows=1,874,183 loops=1)

3. 9,721.766 30,159.203 ↑ 1.2 1,874,183 1

Merge Join (cost=805,436.88..1,415,873.11 rows=2,219,069 width=73) (actual time=17,061.726..30,159.203 rows=1,874,183 loops=1)

  • Merge Cond: ((pc.package_id = pm.package_id) AND ((pc.bed_type)::text = (dcl.bed_type)::text) AND ((pc.org_id)::text = (dcl.org_name)::text))
4. 1,595.284 1,721.774 ↑ 1.0 580,644 1

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

  • Sort Key: pc.package_id, pc.bed_type, pc.org_id
  • Sort Method: external merge Disk: 23992kB
5. 126.490 126.490 ↑ 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.003..126.490 rows=580,644 loops=1)

6. 277.821 18,715.663 ↑ 1.2 1,882,733 1

Materialize (cost=711,811.63..723,048.26 rows=2,247,326 width=71) (actual time=15,822.916..18,715.663 rows=1,882,733 loops=1)

7. 13,937.345 18,437.842 ↑ 1.2 1,882,733 1

Sort (cost=711,811.63..717,429.94 rows=2,247,326 width=71) (actual time=15,822.910..18,437.842 rows=1,882,733 loops=1)

  • Sort Key: pm.package_id, dcl.bed_type, dcl.org_name
  • Sort Method: external merge Disk: 154576kB
8. 2,474.921 4,500.497 ↑ 1.2 1,882,733 1

Hash Join (cost=145,064.42..198,193.23 rows=2,247,326 width=71) (actual time=2,049.603..4,500.497 rows=1,882,733 loops=1)

  • Hash Cond: ((pcont.activity_id)::text = (dcl.test_id)::text)
9. 0.742 1.076 ↓ 1.0 651 1

Hash Join (cost=13.26..40.93 rows=624 width=48) (actual time=0.142..1.076 rows=651 loops=1)

  • Hash Cond: (pcont.package_id = pmmap.package_id)
10. 0.213 0.213 ↑ 1.0 686 1

Seq Scan on temp_package_contents pcont (cost=0.00..18.86 rows=686 width=40) (actual time=0.008..0.213 rows=686 loops=1)

11. 0.019 0.121 ↑ 1.0 141 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
12. 0.039 0.102 ↑ 1.0 141 1

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

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

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

14. 0.017 0.036 ↑ 1.0 141 1

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

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

16. 1,253.434 2,024.500 ↑ 1.0 3,811,607 1

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

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

Seq Scan on diagnostic_charges dcl (cost=0.00..71,350.07 rows=3,811,607 width=31) (actual time=1.006..771.066 rows=3,811,607 loops=1)