explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mVVh

Settings
# exclusive inclusive rows x rows loops node
1. 125,616.056 523,110.593 ↑ 1.0 94,414,133 1

Hash Left Join (cost=2,561,005.73..24,271,622.14 rows=94,482,266 width=146) (actual time=140,970.580..523,110.593 rows=94,414,133 loops=1)

  • Output: stg_subscriber.subs_id, stg_subscriber.msisdn, stg_subscriber.record_create_dttm, stg_subscriber.most_freq_avail_rewards_3mos, stg_subscriber.alldayroamsurf_register_dt, stg_subscriber.alldayroamsurf_most_freq_avail_var, stg_sampler_availer.expiry_date, stg_subscriber.last_promo_register_dt, stg_sampler_availer.sampler_denom, stg_sampler_availer.sampler_svcid, stg_subscriber.last_promo_reg_name, stg_subscriber.availment_enhanced_12clmwks, stg_dpa_daily_promo.total_mds_promo_12clmwks, stg_dpa_daily_promo.mode_promo_amount_30days, stg_dpa_daily_promo.total_mds_promo_30days, stg_ragnarok_mds.total_mds_promo_6mos, stg_dpa_daily_promo.mode_data_promo_12clmwks, stg_dpa_daily_promo.mode_core_promo_12clmwks, stg_dpa_daily_promo.mode_promo_name_30days
  • Hash Cond: ((stg_subscriber.msisdn)::text = (stg_dpa_daily_promo.msisdn)::text)
  • Buffers: shared hit=7754968 read=574659, temp read=3368439 written=3364093
  • I/O Timings: read=489.833
2. 80,277.910 262,914.186 ↑ 1.0 94,414,133 1

Hash Left Join (cost=234,881.17..11,278,037.36 rows=94,482,266 width=97) (actual time=6,389.957..262,914.186 rows=94,414,133 loops=1)

  • Output: stg_subscriber.subs_id, stg_subscriber.msisdn, stg_subscriber.record_create_dttm, stg_subscriber.most_freq_avail_rewards_3mos, stg_subscriber.alldayroamsurf_register_dt, stg_subscriber.alldayroamsurf_most_freq_avail_var, stg_subscriber.last_promo_register_dt, stg_subscriber.last_promo_reg_name, stg_subscriber.availment_enhanced_12clmwks, stg_sampler_availer.expiry_date, stg_sampler_availer.sampler_denom, stg_sampler_availer.sampler_svcid, stg_ragnarok_mds.total_mds_promo_6mos
  • Hash Cond: ((stg_subscriber.msisdn)::text = (stg_sampler_availer.msisdn)::text)
  • Buffers: shared hit=7219672 read=39289, temp read=1973383 written=1973131
  • I/O Timings: read=159.545
3. 109,202.005 181,402.501 ↑ 1.0 94,414,133 1

Hash Left Join (cost=136,310.31..7,963,318.88 rows=94,482,266 width=82) (actual time=5,155.911..181,402.501 rows=94,414,133 loops=1)

  • Output: stg_subscriber.subs_id, stg_subscriber.msisdn, stg_subscriber.record_create_dttm, stg_subscriber.most_freq_avail_rewards_3mos, stg_subscriber.alldayroamsurf_register_dt, stg_subscriber.alldayroamsurf_most_freq_avail_var, stg_subscriber.last_promo_register_dt, stg_subscriber.last_promo_reg_name, stg_subscriber.availment_enhanced_12clmwks, stg_ragnarok_mds.total_mds_promo_6mos
  • Hash Cond: ((stg_subscriber.msisdn)::text = (stg_ragnarok_mds.msisdn)::text)
  • Buffers: shared hit=7172048 read=39289, temp read=986081 written=985955
  • I/O Timings: read=159.545
4. 67,044.930 67,044.930 ↑ 1.0 94,414,133 1

Seq Scan on dpastg.stg_subscriber (cost=0.00..4,784,305.20 rows=94,482,266 width=77) (actual time=0.135..67,044.930 rows=94,414,133 loops=1)

  • Output: stg_subscriber.subs_id, stg_subscriber.msisdn, stg_subscriber.record_create_dttm, stg_subscriber.most_freq_avail_rewards_3mos, stg_subscriber.alldayroamsurf_register_dt, stg_subscriber.alldayroamsurf_most_freq_avail_var, stg_subscriber.last_promo_register_dt, stg_subscriber.last_promo_reg_name, stg_subscriber.availment_enhanced_12clmwks
  • Filter: ((stg_subscriber.brand)::text = ANY ('{GHP-PREPAID,TM}'::text[]))
  • Rows Removed by Filter: 3005257
  • Buffers: shared hit=7132877
5. 1,173.463 5,155.566 ↓ 1.0 3,421,215 1

Hash (cost=73,500.25..73,500.25 rows=3,421,125 width=18) (actual time=5,155.566..5,155.566 rows=3,421,215 loops=1)

  • Output: stg_ragnarok_mds.total_mds_promo_6mos, stg_ragnarok_mds.msisdn
  • Buckets: 65536 Batches: 64 Memory Usage: 3115kB
  • Buffers: shared hit=39168 read=39289, temp written=15588
  • I/O Timings: read=159.545
6. 3,982.103 3,982.103 ↓ 1.0 3,421,215 1

Seq Scan on dpastg.stg_ragnarok_mds (cost=0.00..73,500.25 rows=3,421,125 width=18) (actual time=10.485..3,982.103 rows=3,421,215 loops=1)

  • Output: stg_ragnarok_mds.total_mds_promo_6mos, stg_ragnarok_mds.msisdn
  • Buffers: shared hit=39168 read=39289
  • I/O Timings: read=159.545
7. 748.377 1,233.775 ↓ 1.0 2,548,286 1

Hash (cost=49,298.16..49,298.16 rows=2,548,216 width=28) (actual time=1,233.775..1,233.775 rows=2,548,286 loops=1)

  • Output: stg_sampler_availer.expiry_date, stg_sampler_availer.sampler_denom, stg_sampler_availer.sampler_svcid, stg_sampler_availer.msisdn
  • Buckets: 65536 Batches: 64 Memory Usage: 2855kB
  • Buffers: shared hit=47624, temp written=14752
8. 485.398 485.398 ↓ 1.0 2,548,286 1

Seq Scan on dpastg.stg_sampler_availer (cost=0.00..49,298.16 rows=2,548,216 width=28) (actual time=0.135..485.398 rows=2,548,286 loops=1)

  • Output: stg_sampler_availer.expiry_date, stg_sampler_availer.sampler_denom, stg_sampler_availer.sampler_svcid, stg_sampler_availer.msisdn
  • Buffers: shared hit=47624
9. 26,370.530 134,580.351 ↑ 1.0 53,869,936 1

Hash (cost=1,074,069.36..1,074,069.36 rows=53,869,936 width=62) (actual time=134,580.351..134,580.351 rows=53,869,936 loops=1)

  • Output: stg_dpa_daily_promo.total_mds_promo_12clmwks, stg_dpa_daily_promo.mode_promo_amount_30days, stg_dpa_daily_promo.total_mds_promo_30days, stg_dpa_daily_promo.mode_data_promo_12clmwks, stg_dpa_daily_promo.mode_core_promo_12clmwks, stg_dpa_daily_promo.mode_promo_name_30days, stg_dpa_daily_promo.msisdn
  • Buckets: 65536 Batches: 2048 Memory Usage: 2360kB
  • Buffers: shared hit=535296 read=535370, temp written=394867
  • I/O Timings: read=330.288
10. 108,209.821 108,209.821 ↑ 1.0 53,869,936 1

Seq Scan on dpastg.stg_dpa_daily_promo (cost=0.00..1,074,069.36 rows=53,869,936 width=62) (actual time=13.953..108,209.821 rows=53,869,936 loops=1)

  • Output: stg_dpa_daily_promo.total_mds_promo_12clmwks, stg_dpa_daily_promo.mode_promo_amount_30days, stg_dpa_daily_promo.total_mds_promo_30days, stg_dpa_daily_promo.mode_data_promo_12clmwks, stg_dpa_daily_promo.mode_core_promo_12clmwks, stg_dpa_daily_promo.mode_promo_name_30days, stg_dpa_daily_promo.msisdn
  • Buffers: shared hit=535296 read=535370
  • I/O Timings: read=330.288