explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mIA9 : Optimization for: plan #iVdh

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 8.546 1,688.108 ↑ 179.5 75 1

GroupAggregate (cost=1,680,890.02..1,685,103.54 rows=13,466 width=87) (actual time=1,679.425..1,688.108 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.earnmonth
2. 4.057 1,679.562 ↑ 4.7 2,849 1

Sort (cost=1,680,890.02..1,680,923.73 rows=13,484 width=348) (actual time=1,679.383..1,679.562 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.earnmonth
  • Sort Method: quicksort Memory: 853kB
3. 653.835 1,675.505 ↑ 4.7 2,849 1

Hash Left Join (cost=1,486.64..1,679,965.08 rows=13,484 width=348) (actual time=83.877..1,675.505 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. 654.815 1,011.230 ↑ 4.7 2,849 1

Hash Left Join (cost=1,243.32..1,677,898.75 rows=13,484 width=225) (actual time=73.269..1,011.230 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 346.112 ↑ 4.7 2,849 1

Gather (cost=1,000.00..1,675,832.42 rows=13,484 width=97) (actual time=62.804..346.112 rows=2,849 loops=1)

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

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

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

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,662kB
8. 3.337 4.327 ↓ 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.019..4.327 rows=18,016 loops=1)

  • Recheck Cond: (curve = 'claim'::bpchar)
  • Heap Blocks: exact=227
9. 0.990 0.990 ↓ 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.990..0.990 rows=18,016 loops=1)

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

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

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,662kB
11. 3.291 4.237 ↓ 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=0.971..4.237 rows=18,016 loops=1)

  • Recheck Cond: (curve = 'claim'::bpchar)
  • Heap Blocks: exact=227
12. 0.946 0.946 ↓ 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.946..0.946 rows=18,016 loops=1)

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