explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iVdh

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.995 1,399.783 ↑ 179.5 75 1

GroupAggregate (cost=1,691,971.12..1,696,825.13 rows=13,466 width=217) (actual time=1,394.747..1,399.783 rows=75 loops=1)

  • Group Key: bi_tfs_frequency_detail_level.sg_con_dealer, bi_tfs_frequency_detail_level.sg_dlr_company, bi_tfs_frequency_detail_level.sg_dlr_state, bi_tfs_frequency_detail_level.dollars, bi_tfs_frequency_detail_level.earnmonth, bi_tfs_frequency_detail_level.contracts, bi_tfs_frequency_detail_level.written_prem, bi_tfs_frequency_detail_level.activecount, bi_tfs_frequency_detail_level.inforceprem, bi_tfs_frequency_detail_level.cancels, bi_tfs_frequency_detail_level.canprem, bi_tfs_frequency_detail_level.clms, bi_tfs_frequency_detail_level.mobile_clm_cnt, bi_tfs_frequency_detail_level.treplace, bi_tfs_frequency_detail_level.wreplace, bi_tfs_frequency_detail_level.wrepair, bi_tfs_frequency_detail_level.treplacecost, bi_tfs_frequency_detail_level.wreplacecost, bi_tfs_frequency_detail_level.wrepaircost, bi_tfs_frequency_detail_level._1_wheelrepair, bi_tfs_frequency_detail_level._2_wheelrepair, bi_tfs_frequency_detail_level._3_wheelrepair, bi_tfs_frequency_detail_level._4_wheelrepair
2. 5.355 1,394.788 ↑ 4.7 2,849 1

Sort (cost=1,691,971.12..1,692,004.83 rows=13,484 width=478) (actual time=1,394.609..1,394.788 rows=2,849 loops=1)

  • Sort Key: bi_tfs_frequency_detail_level.sg_dlr_company, bi_tfs_frequency_detail_level.sg_dlr_state, bi_tfs_frequency_detail_level.dollars, bi_tfs_frequency_detail_level.earnmonth, bi_tfs_frequency_detail_level.contracts, bi_tfs_frequency_detail_level.written_prem, bi_tfs_frequency_detail_level.activecount, bi_tfs_frequency_detail_level.inforceprem, bi_tfs_frequency_detail_level.cancels, bi_tfs_frequency_detail_level.canprem, bi_tfs_frequency_detail_level.clms, bi_tfs_frequency_detail_level.mobile_clm_cnt, bi_tfs_frequency_detail_level.treplace, bi_tfs_frequency_detail_level.wreplace, bi_tfs_frequency_detail_level.wrepair, bi_tfs_frequency_detail_level.treplacecost, bi_tfs_frequency_detail_level.wreplacecost, bi_tfs_frequency_detail_level.wrepaircost, bi_tfs_frequency_detail_level._1_wheelrepair, bi_tfs_frequency_detail_level._2_wheelrepair, bi_tfs_frequency_detail_level._3_wheelrepair, bi_tfs_frequency_detail_level._4_wheelrepair
  • Sort Method: quicksort Memory: 853kB
3. 1.586 1,389.433 ↑ 4.7 2,849 1

Nested Loop Left Join (cost=1,000.84..1,691,046.19 rows=13,484 width=478) (actual time=119.908..1,389.433 rows=2,849 loops=1)

4. 1.941 1,379.300 ↑ 4.7 2,849 1

Nested Loop Left Join (cost=1,000.42..1,683,439.30 rows=13,484 width=353) (actual time=119.892..1,379.300 rows=2,849 loops=1)

5. 0.000 1,368.812 ↑ 4.7 2,849 1

Gather (cost=1,000.00..1,675,832.42 rows=13,484 width=225) (actual time=119.862..1,368.812 rows=2,849 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
6. 1,380.322 1,380.322 ↑ 5.5 407 7 / 7

Parallel Seq Scan on bi_tfs_frequency_detail_level (cost=0.00..1,673,484.02 rows=2,247 width=225) (actual time=70.436..1,380.322 rows=407 loops=7)

  • Filter: (sg_con_dealer = 'CCC02856'::bpchar)
  • Rows Removed by Filter: 6,326,394
7. 8.547 8.547 ↓ 0.0 0 2,849

Index Scan using bi_toyota_earnings_temp_indx on bi_toyota_earnings_temp e1 (cost=0.42..0.55 rows=1 width=172) (actual time=0.003..0.003 rows=0 loops=2,849)

  • Index Cond: ((curve = 'claim'::bpchar) AND (vehclass = (bi_tfs_frequency_detail_level.tfsmodelclass)::bpchar) AND (bi_tfs_frequency_detail_level.product_term >= termlo) AND (bi_tfs_frequency_detail_level.product_term <= termhi) AND ((bi_tfs_frequency_detail_level.earn_day1)::numeric >= expdayslo) AND ((bi_tfs_frequency_detail_level.earn_day1)::numeric <= expdayshi) AND (bi_tfs_frequency_detail_level.effective_date >= effdatefrom) AND (bi_tfs_frequency_detail_level.effective_date <= effdateto))
  • Filter: ((product)::text = bi_tfs_frequency_detail_level.product_type)
8. 8.547 8.547 ↓ 0.0 0 2,849

Index Scan using bi_toyota_earnings_temp_indx on bi_toyota_earnings_temp e2 (cost=0.42..0.55 rows=1 width=172) (actual time=0.003..0.003 rows=0 loops=2,849)

  • Index Cond: ((curve = 'claim'::bpchar) AND (vehclass = (bi_tfs_frequency_detail_level.tfsmodelclass)::bpchar) AND (bi_tfs_frequency_detail_level.product_term >= termlo) AND (bi_tfs_frequency_detail_level.product_term <= termhi) AND ((bi_tfs_frequency_detail_level.earn_days2)::numeric >= expdayslo) AND ((bi_tfs_frequency_detail_level.earn_days2)::numeric <= expdayshi) AND (bi_tfs_frequency_detail_level.effective_date >= effdatefrom) AND (bi_tfs_frequency_detail_level.effective_date <= effdateto))
  • Filter: ((product)::text = bi_tfs_frequency_detail_level.product_type)
Planning time : 0.564 ms
Execution time : 1,400.055 ms