explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O3dQ

Settings
# exclusive inclusive rows x rows loops node
1. 430,123.505 536,478.855 ↓ 0.0 0 1

Update on package_content_charges (cost=967,127.80..2,063,905.99 rows=2,022,558 width=365) (actual time=536,478.855..536,478.855 rows=0 loops=1)

2. 66,271.636 106,355.350 ↑ 1.1 1,882,493 1

Hash Join (cost=967,127.80..2,063,905.99 rows=2,022,558 width=365) (actual time=21,112.896..106,355.350 rows=1,882,493 loops=1)

  • Hash Cond: ("*SELECT* 1".content_charge_id = package_content_charges.content_charge_id)
3. 165.738 30,958.087 ↑ 1.1 1,882,493 1

Append (cost=667,290.77..1,539,639.60 rows=2,022,558 width=68) (actual time=11,591.005..30,958.087 rows=1,882,493 loops=1)

4. 349.281 14,640.604 ↓ 1.1 1,494,373 1

Subquery Scan on *SELECT* 1 (cost=667,290.77..732,927.21 rows=1,338,971 width=68) (actual time=11,591.005..14,640.604 rows=1,494,373 loops=1)

5. 1,571.112 14,291.323 ↓ 1.1 1,494,373 1

Merge Join (cost=667,290.77..719,537.50 rows=1,338,971 width=18) (actual time=11,591.002..14,291.323 rows=1,494,373 loops=1)

  • Merge Cond: ((tmap.package_id = pcc.package_id) AND ((tmap.org_id)::text = (pcc.org_id)::text) AND ((tmap.bed_type)::text = (pcc.bed_type)::text))
6. 685.071 865.632 ↑ 1.0 380,578 1

Sort (cost=66,100.36..67,062.19 rows=384,732 width=24) (actual time=795.154..865.632 rows=380,578 loops=1)

  • Sort Key: tmap.package_id, tmap.org_id, tmap.bed_type
  • Sort Method: external sort Disk: 14280kB
7. 180.561 180.561 ↑ 1.0 383,997 1

Seq Scan on temp_package_charge_mapping tmap (cost=0.00..14,624.75 rows=384,732 width=24) (actual time=0.014..180.561 rows=383,997 loops=1)

  • Filter: (total_calculated_package_charge > 0::numeric)
  • Rows Removed by Filter: 146863
8. 210.529 11,854.579 ↑ 1.4 1,815,400 1

Materialize (cost=601,189.37..613,641.33 rows=2,490,392 width=34) (actual time=10,795.832..11,854.579 rows=1,815,400 loops=1)

9. 10,278.643 11,644.050 ↑ 1.4 1,815,400 1

Sort (cost=601,189.37..607,415.35 rows=2,490,392 width=34) (actual time=10,795.827..11,644.050 rows=1,815,400 loops=1)

  • Sort Key: pcc.package_id, pcc.org_id, pcc.bed_type
  • Sort Method: external merge Disk: 104896kB
10. 1,365.407 1,365.407 ↑ 1.0 2,445,351 1

Seq Scan on package_content_charges pcc (cost=0.00..132,312.35 rows=2,490,392 width=34) (actual time=1.384..1,365.407 rows=2,445,351 loops=1)

  • Filter: (charge > 0::numeric)
  • Rows Removed by Filter: 831389
11. 95.331 16,151.745 ↑ 1.8 388,120 1

Subquery Scan on *SELECT* 2 (cost=757,227.18..806,712.39 rows=683,587 width=68) (actual time=13,705.122..16,151.745 rows=388,120 loops=1)

12. 685.987 16,056.414 ↑ 1.8 388,120 1

Merge Join (cost=757,227.18..799,876.52 rows=683,587 width=13) (actual time=13,705.121..16,056.414 rows=388,120 loops=1)

  • Merge Cond: ((pcc_1.package_id = tmap_1.package_id) AND ((pcc_1.org_id)::text = (tmap_1.org_id)::text) AND ((pcc_1.bed_type)::text = (tmap_1.bed_type)::text))
13. 13,727.110 14,986.440 ↑ 1.4 2,456,701 1

Sort (cost=727,067.71..735,437.38 rows=3,347,868 width=29) (actual time=13,382.695..14,986.440 rows=2,456,701 loops=1)

  • Sort Key: pcc_1.package_id, pcc_1.org_id, pcc_1.bed_type
  • Sort Method: external merge Disk: 125064kB
14. 1,259.330 1,259.330 ↑ 1.0 3,276,740 1

Seq Scan on package_content_charges pcc_1 (cost=0.00..123,942.68 rows=3,347,868 width=29) (actual time=0.020..1,259.330 rows=3,276,740 loops=1)

15. 35.226 383.987 ↓ 2.7 388,119 1

Materialize (cost=30,158.07..30,888.62 rows=146,110 width=24) (actual time=322.405..383.987 rows=388,119 loops=1)

16. 185.688 348.761 ↓ 1.0 146,863 1

Sort (cost=30,158.07..30,523.35 rows=146,110 width=24) (actual time=322.401..348.761 rows=146,863 loops=1)

  • Sort Key: tmap_1.package_id, tmap_1.org_id, tmap_1.bed_type
  • Sort Method: external sort Disk: 5120kB
17. 163.073 163.073 ↓ 1.0 146,863 1

Seq Scan on temp_package_charge_mapping tmap_1 (cost=0.00..14,624.75 rows=146,110 width=24) (actual time=0.010..163.073 rows=146,863 loops=1)

  • Filter: (total_calculated_package_charge = 0::numeric)
  • Rows Removed by Filter: 383997
18. 6,844.754 9,125.627 ↑ 1.0 3,276,740 1

Hash (cost=123,942.68..123,942.68 rows=3,347,868 width=301) (actual time=9,125.627..9,125.627 rows=3,276,740 loops=1)

  • Buckets: 1024 Batches: 2048 Memory Usage: 170kB
19. 2,280.873 2,280.873 ↑ 1.0 3,276,740 1

Seq Scan on package_content_charges (cost=0.00..123,942.68 rows=3,347,868 width=301) (actual time=0.039..2,280.873 rows=3,276,740 loops=1)

Total runtime : 536,579.885 ms