explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9p6U : Optimization for: plan #YheV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 43.242 1,991.669 ↑ 179.5 75 1

GroupAggregate (cost=1,680,890.02..1,686,721.58 rows=13,466 width=241) (actual time=1,949.005..1,991.669 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. 7.077 1,948.427 ↑ 4.7 2,849 1

Sort (cost=1,680,890.02..1,680,923.73 rows=13,484 width=476) (actual time=1,948.156..1,948.427 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: 966kB
3. 670.049 1,941.350 ↑ 4.7 2,849 1

Hash Left Join (cost=1,486.64..1,679,965.08 rows=13,484 width=476) (actual time=226.785..1,941.350 rows=2,849 loops=1)

  • Hash Cond: ((bi_tfs_frequency_detail_level.tfsmodelclass_left = (e2.vehclass)::text) AND (bi_tfs_frequency_detail_level.product_type = (e2.product)::text))
  • Join Filter: ((bi_tfs_frequency_detail_level.product_term >= e2.termlo) AND (bi_tfs_frequency_detail_level.product_term <= e2.termhi) AND (bi_tfs_frequency_detail_level.effective_date >= e2.effdatefrom) AND (bi_tfs_frequency_detail_level.effective_date <= e2.effdateto) AND ((bi_tfs_frequency_detail_level.earn_days2)::numeric >= e2.expdayslo) AND ((bi_tfs_frequency_detail_level.earn_days2)::numeric <= e2.expdayshi))
  • Rows Removed by Join Filter: 2,384,626
4. 680.517 1,260.391 ↑ 4.7 2,849 1

Hash Left Join (cost=1,243.32..1,677,898.75 rows=13,484 width=351) (actual time=215.735..1,260.391 rows=2,849 loops=1)

  • Hash Cond: ((bi_tfs_frequency_detail_level.tfsmodelclass_left = (e1.vehclass)::text) AND (bi_tfs_frequency_detail_level.product_type = (e1.product)::text))
  • Join Filter: ((bi_tfs_frequency_detail_level.product_term >= e1.termlo) AND (bi_tfs_frequency_detail_level.product_term <= e1.termhi) AND (bi_tfs_frequency_detail_level.effective_date >= e1.effdatefrom) AND (bi_tfs_frequency_detail_level.effective_date <= e1.effdateto) AND ((bi_tfs_frequency_detail_level.earn_day1)::numeric >= e1.expdayslo) AND ((bi_tfs_frequency_detail_level.earn_day1)::numeric <= e1.expdayshi))
  • Rows Removed by Join Filter: 2,384,576
5. 0.000 568.990 ↑ 4.7 2,849 1

Gather (cost=1,000.00..1,675,832.42 rows=13,484 width=223) (actual time=204.702..568.990 rows=2,849 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
6. 1,624.975 1,624.975 ↑ 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=223) (actual time=73.413..1,624.975 rows=407 loops=7)

  • Filter: (sg_con_dealer = 'CCC02856'::bpchar)
  • Rows Removed by Filter: 6,326,394
7. 6.179 10.884 ↓ 68.0 18,016 1

Hash (cost=239.34..239.34 rows=265 width=172) (actual time=10.884..10.884 rows=18,016 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,662kB
8. 3.609 4.705 ↓ 68.0 18,016 1

Bitmap Heap Scan on bi_toyota_earnings_temp e1 (cost=5.77..239.34 rows=265 width=172) (actual time=1.128..4.705 rows=18,016 loops=1)

  • Recheck Cond: (curve = 'claim'::bpchar)
  • Heap Blocks: exact=227
9. 1.096 1.096 ↓ 68.0 18,016 1

Bitmap Index Scan on bi_toyota_earnings_temp_indx (cost=0.00..5.70 rows=265 width=0) (actual time=1.096..1.096 rows=18,016 loops=1)

  • Index Cond: (curve = 'claim'::bpchar)
10. 6.279 10.910 ↓ 68.0 18,016 1

Hash (cost=239.34..239.34 rows=265 width=172) (actual time=10.910..10.910 rows=18,016 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,662kB
11. 3.662 4.631 ↓ 68.0 18,016 1

Bitmap Heap Scan on bi_toyota_earnings_temp e2 (cost=5.77..239.34 rows=265 width=172) (actual time=1.000..4.631 rows=18,016 loops=1)

  • Recheck Cond: (curve = 'claim'::bpchar)
  • Heap Blocks: exact=227
12. 0.969 0.969 ↓ 68.0 18,016 1

Bitmap Index Scan on bi_toyota_earnings_temp_indx (cost=0.00..5.70 rows=265 width=0) (actual time=0.969..0.969 rows=18,016 loops=1)

  • Index Cond: (curve = 'claim'::bpchar)
Planning time : 0.639 ms
Execution time : 1,992.097 ms