explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4hBHj

Settings
# exclusive inclusive rows x rows loops node
1. 41,467.430 111,765.603 ↓ 0.0 0 1

Insert on package_content_charges (cost=1,532,802.98..2,406,967.01 rows=2,352,887 width=90) (actual time=111,765.603..111,765.603 rows=0 loops=1)

2. 4,491.075 70,298.173 ↓ 1.0 2,456,700 1

Subquery Scan on *SELECT* (cost=1,532,802.98..2,406,967.01 rows=2,352,887 width=90) (actual time=34,454.382..70,298.173 rows=2,456,700 loops=1)

3. 17,807.226 65,807.098 ↓ 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=34,447.625..65,807.098 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))
4. 19,284.246 20,099.059 ↑ 1.0 3,613,247 1

Sort (cost=722,286.95..731,320.07 rows=3,613,247 width=31) (actual time=15,947.504..20,099.059 rows=3,613,247 loops=1)

  • Sort Key: dc.test_id, dc.bed_type, dc.org_name
  • Sort Method: external merge Disk: 146192kB
5. 814.813 814.813 ↑ 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.986..814.813 rows=3,613,247 loops=1)

6. 1,056.500 27,900.813 ↓ 1.0 2,456,700 1

Materialize (cost=810,516.03..933,355.50 rows=2,352,887 width=97) (actual time=18,500.036..27,900.813 rows=2,456,700 loops=1)

7. 782.346 26,844.313 ↓ 1.0 2,456,700 1

Merge Left Join (cost=810,516.03..927,473.28 rows=2,352,887 width=97) (actual time=18,500.031..26,844.313 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))
8. 927.740 25,545.019 ↓ 1.0 2,456,700 1

Merge Left Join (cost=795,337.75..891,854.09 rows=2,352,887 width=91) (actual time=18,173.487..25,545.019 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))
9. 1,405.958 20,997.872 ↓ 1.0 2,456,700 1

Merge Left Join (cost=696,041.31..751,056.10 rows=2,352,887 width=73) (actual time=15,392.776..20,997.872 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))
10. 15,078.516 15,787.660 ↓ 1.0 2,456,700 1

Sort (cost=575,281.92..581,164.13 rows=2,352,887 width=66) (actual time=12,403.629..15,787.660 rows=2,456,700 loops=1)

  • Sort Key: pcont.activity_id, pc.bed_type, pc.org_id
  • Sort Method: external merge Disk: 192800kB
11. 592.620 709.144 ↓ 1.0 2,456,700 1

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

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

13. 0.154 1.079 ↓ 1.0 651 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
14. 0.464 0.925 ↓ 1.0 651 1

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

  • Hash Cond: (pcont.package_id = pmmap.package_id)
15. 0.269 0.269 ↑ 1.0 686 1

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

16. 0.014 0.192 ↑ 1.0 141 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
17. 0.059 0.178 ↑ 1.0 141 1

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

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

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

  • Filter: (type = 'P'::bpchar)
  • Rows Removed by Filter: 14
19. 0.020 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
20. 0.016 0.016 ↑ 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.016 rows=141 loops=1)

21. 91.780 3,804.254 ↓ 1.1 815,860 1

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

22. 3,531.512 3,712.474 ↑ 1.0 737,200 1

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

  • Sort Key: smc.service_id, smc.bed_type, smc.org_id
  • Sort Method: external merge Disk: 31296kB
23. 180.962 180.962 ↑ 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=1.684..180.962 rows=737,200 loops=1)

24. 79.589 3,619.407 ↓ 1.2 665,000 1

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

25. 3,382.042 3,539.818 ↑ 1.0 558,600 1

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

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

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

27. 14.572 516.948 ↑ 1.0 117,800 1

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

28. 464.457 502.376 ↑ 1.0 117,800 1

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

  • Sort Key: ec.equip_id, ec.bed_type, ec.org_id
  • Sort Method: external merge Disk: 4840kB
29. 37.919 37.919 ↑ 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.036..37.919 rows=117,800 loops=1)

Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint package_content_charges_package_content_id_fkey 25,195.698 ms 2456700 0.010 ms
for constraint package_content_charges_org_id_fkey 54,039.633 ms 2456700 0.022 ms