explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q3oP

Settings
# exclusive inclusive rows x rows loops node
1. 78,779.393 5,066,686.399 ↑ 1.0 98,424,491 1

Merge Left Join (cost=2.33..38,263,269.50 rows=98,439,863 width=24) (actual time=4.252..5,066,686.399 rows=98,424,491 loops=1)

  • Output: table0.subs_id, table1.last_topup_dt, table2.last_core_ppu_date, table2.last_data_ppu_date, table3.last_promo_register_dt
  • Merge Cond: (table0.subs_id = table3.subs_id)
  • Join Filter: $0
  • Buffers: shared hit=384988249 read=3093498
  • I/O Timings: read=4343346.008
2.          

Initplan (forMerge Left Join)

3. 0.124 0.124 ↑ 94,769,462.0 1 1

Seq Scan on dpacmdm.subscriber (cost=0.00..5,649,201.01 rows=94,769,462 width=0) (actual time=0.124..0.124 rows=1 loops=1)

  • Filter: (subscriber.cfu_id = ANY ('{4,5,8,10,27,106,107,108,109,110,111,112,114,115,116,117,119,124,129,132,134,136,147,177,178,179,187}'::integer[]))
  • Buffers: shared hit=257
4. 75,310.871 3,969,501.726 ↑ 1.0 98,424,491 1

Merge Left Join (cost=1.70..28,309,344.22 rows=98,439,863 width=20) (actual time=4.243..3,969,501.726 rows=98,424,491 loops=1)

  • Output: table0.subs_id, table1.last_topup_dt, table2.last_core_ppu_date, table2.last_data_ppu_date
  • Merge Cond: (table0.subs_id = table2.subs_id)
  • Buffers: shared hit=289321947 read=2494393
  • I/O Timings: read=3451614.215
5. 83,704.998 1,207,170.787 ↑ 1.0 98,424,491 1

Merge Left Join (cost=1.14..19,393,149.42 rows=98,439,863 width=12) (actual time=4.236..1,207,170.787 rows=98,424,491 loops=1)

  • Output: table0.subs_id, table1.last_topup_dt
  • Merge Cond: (table0.subs_id = table1.subs_id)
  • Buffers: shared hit=194587953 read=963059
  • I/O Timings: read=892484.398
6. 568,587.842 568,587.842 ↑ 1.0 98,424,491 1

Index Only Scan using subs_idx on dpacmdm.subscriber table0 (cost=0.57..8,793,133.33 rows=98,439,863 width=8) (actual time=4.219..568,587.842 rows=98,424,491 loops=1)

  • Output: table0.subs_id
  • Heap Fetches: 98424827
  • Buffers: shared hit=98800577 read=486659
  • I/O Timings: read=450341.454
7. 554,877.947 554,877.947 ↓ 1.0 95,423,818 1

Index Scan using pst_subs_idx on dpacmdm.prepaid_subs_topup table1 (cost=0.57..9,257,638.51 rows=95,298,888 width=12) (actual time=0.007..554,877.947 rows=95,423,818 loops=1)

  • Output: table1.days_from_last_topup, table1.last_topup_dt, table1.ave_topup_amt_3mos, table1.ave_month_topup_roll_90days, table1.t
  • Buffers: shared hit=95787376 read=476400
  • I/O Timings: read=442142.944
8. 2,687,020.068 2,687,020.068 ↓ 1.0 95,423,804 1

Index Scan using psu_subs_idx on dpacmdm.prepaid_subs_usage table2 (cost=0.57..7,555,954.68 rows=95,366,682 width=16) (actual time=0.005..2,687,020.068 rows=95,423,804 loops=1)

  • Output: table2.ave_dly_data_usage_3mos, table2.projected_data_use, table2.record_create_dttm, table2.subs_id, table2.msisdn, table2.last_core_ppu_date, table2.last_data_ppu_date, table2.data_usage_30days, table2.sum_data_usage_3mos, table2.sum_outbnd_voice_usage_30days
  • Buffers: shared hit=94733994 read=1531334
  • I/O Timings: read=2559129.817
9. 1,018,405.156 1,018,405.156 ↓ 1.0 95,423,824 1

Index Scan using psp_subs_idx on dpacmdm.prepaid_subs_promo table3 (cost=0.57..8,604,673.11 rows=95,326,533 width=12) (actual time=0.006..1,018,405.156 rows=95,423,824 loops=1)

  • Output: table3.sampler_expire_dt, table3.most_freq_avail_rewards_3mos, table3.alldayroamsurf_register_dt, table3.most_freq_used_alldayroamsurf
  • Buffers: shared hit=95666045 read=599105
  • I/O Timings: read=891731.793