explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2fxL : Optimization for: Optimization for: sss; plan #icTc; plan #fViG

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 42.053 416,087.885 ↓ 0.0 0 1

Insert on d_tmp_trans_hd_rpt28 (cost=19,135,705.35..20,982,273.10 rows=25,469,900 width=183) (actual time=416,087.885..416,087.885 rows=0 loops=1)

2. 0.458 416,045.832 ↑ 279,889.0 91 1

Subquery Scan on *SELECT* (cost=19,135,705.35..20,982,273.10 rows=25,469,900 width=183) (actual time=269,645.769..416,045.832 rows=91 loops=1)

3. 115,761.794 416,045.374 ↑ 279,889.0 91 1

GroupAggregate (cost=19,135,705.35..20,409,200.35 rows=25,469,900 width=163) (actual time=269,645.744..416,045.374 rows=91 loops=1)

4. 287,282.747 300,283.580 ↑ 2.4 10,606,540 1

Sort (cost=19,135,705.35..19,199,380.10 rows=25,469,900 width=163) (actual time=269,645.513..300,283.580 rows=10,606,540 loops=1)

  • Sort Key: b.cell_name, b.region, b.longitude, b.latitude, a_1.service_type, a_1.transfer_status, a_1.cell_id
  • Sort Method: external merge Disk: 679696kB
5. 3,327.017 13,000.833 ↑ 2.4 10,606,540 1

Nested Loop Left Join (cost=5,110,134.22..5,555,973.52 rows=25,469,900 width=163) (actual time=7,429.122..13,000.833 rows=10,606,540 loops=1)

6. 1,293.175 9,673.816 ↑ 2.4 5,303,270 1

Merge Left Join (cost=5,110,134.22..5,237,598.75 rows=12,734,950 width=163) (actual time=7,429.103..9,673.816 rows=5,303,270 loops=1)

  • Merge Cond: ((a_1.cell_id)::text = ((b.cell)::text))
7. 4,398.254 8,380.641 ↑ 2.4 5,303,270 1

Sort (cost=5,109,091.41..5,140,928.79 rows=12,734,950 width=125) (actual time=7,429.096..8,380.641 rows=5,303,270 loops=1)

  • Sort Key: a_1.cell_id
  • Sort Method: external sort Disk: 298368kB
8. 3,982.387 3,982.387 ↑ 2.4 5,303,270 1

Seq Scan on b_rbt_trans_hd_rpt28 a_1 (cost=0.00..298,105.50 rows=12,734,950 width=125) (actual time=333.757..3,982.387 rows=5,303,270 loops=1)

9. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,042.81..1,071.56 rows=11,502 width=44) (never executed)

  • Sort Key: ((b.cell)::text)
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on m_cell_id b (cost=0.00..267.02 rows=11,502 width=44) (never executed)

11. 0.000 0.000 ↑ 1.0 2 5,303,270

Materialize (cost=0.00..1.03 rows=2 width=0) (actual time=0.000..0.000 rows=2 loops=5,303,270)

12. 0.011 0.011 ↑ 1.0 2 1

Seq Scan on dim_currency c (cost=0.00..1.02 rows=2 width=0) (actual time=0.011..0.011 rows=2 loops=1)