explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kxbx : Optimization for: plan #jOls

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 130.025 75,072.723 ↑ 1.0 53,210 1

GroupAggregate (cost=3,943,083.56..3,949,688.55 rows=54,429 width=364) (actual time=74,920.707..75,072.723 rows=53,210 loops=1)

  • Group Key: t.rebate_id, t.period, t.sku_id, t.beneficiary, t.sold_to_point_id
  • Functions: 42
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 7.402 ms, Inlining 15.188 ms, Optimization 619.625 ms, Emission 410.698 ms, Total 1052.914 ms
2. 151.977 74,942.698 ↑ 4.5 53,338 1

Sort (cost=3,943,083.56..3,943,681.37 rows=239,126 width=228) (actual time=74,920.674..74,942.698 rows=53,338 loops=1)

  • Sort Key: t.rebate_id, t.period, t.sku_id, t.beneficiary, t.sold_to_point_id
  • Sort Method: quicksort Memory: 8301kB
3. 40.169 74,790.721 ↑ 4.5 53,338 1

Subquery Scan on t (cost=3,881,292.87..3,906,401.10 rows=239,126 width=228) (actual time=74,574.526..74,790.721 rows=53,338 loops=1)

4. 155.192 74,750.552 ↑ 4.5 53,338 1

WindowAgg (cost=3,881,292.87..3,904,009.84 rows=239,126 width=248) (actual time=74,574.523..74,750.552 rows=53,338 loops=1)

5. 81.466 74,595.360 ↑ 4.5 53,338 1

Sort (cost=3,881,292.87..3,881,890.68 rows=239,126 width=159) (actual time=74,574.485..74,595.360 rows=53,338 loops=1)

  • Sort Key: emrc.rebate_id, emrc.period
  • Sort Method: quicksort Memory: 9037kB
6. 56.652 74,513.894 ↑ 4.5 53,338 1

Hash Left Join (cost=1,354.26..3,848,919.50 rows=239,126 width=159) (actual time=1,155.087..74,513.894 rows=53,338 loops=1)

  • Hash Cond: (sv.sku_id = (xmm.sku_id)::text)
  • Join Filter: (emrc.rebate_type = 4)
  • Rows Removed by Join Filter: 53338
7. 46.644 74,444.050 ↑ 4.5 53,338 1

Hash Anti Join (cost=471.42..3,847,408.95 rows=239,126 width=153) (actual time=1,141.871..74,444.050 rows=53,338 loops=1)

  • Hash Cond: (emrc.event_id = me.event_id)
  • Join Filter: (me.marketable_id = ANY (ARRAY[sv.sold_to_group_id, sv.sold_to_point_id]))
8. 81.932 74,397.372 ↑ 4.5 53,338 1

Nested Loop (cost=469.86..3,843,789.30 rows=239,165 width=157) (actual time=1,141.824..74,397.372 rows=53,338 loops=1)

9. 9.820 9.820 ↑ 1.0 7,438 1

Index Scan using emrc_rebate_type_idx on emrc (cost=0.28..292.95 rows=7,438 width=218) (actual time=0.014..9.820 rows=7,438 loops=1)

10. 2,372.722 74,305.620 ↓ 7.0 7 7,438

Bitmap Heap Scan on sales_versions sv (cost=469.57..516.73 rows=1 width=83) (actual time=9.845..9.990 rows=7 loops=7,438)

  • Recheck Cond: ((period = emrc."from") AND (version_name = emrc.required_view) AND (((emrc.marketable_id)::text = sold_to_point_id) OR ((emrc.marketable_id)::text = sold_to_group_id) OR ((emrc.marketable_id)::text = nestle_planning_account_id) OR (sold_to_point_id = ANY (emrc.custom_customer_grouping_sold_to_ids))))
  • Filter: ((((emrc.lob_constraint)::text = 'none'::text) OR ((emrc.lob_constraint)::text = lob_id)) AND ((revenue <> '0'::numeric) OR (emrc.rebate_type = 2)) AND ((period >= '2020-01-01'::date) OR ((period < '2020-01-01'::date) AND (company_of_origin = emrc.company_of_origin_in_scope))) AND (((emrc.materialable_id)::text = sku_id) OR ((emrc.materialable_id)::text = promo_group_id) OR ((emrc.materialable_id)::text = brand_id) OR ((emrc.materialable_id)::text = product_level_2_id) OR ((emrc.materialable_id)::text = lob_id) OR (sku_id = ANY ((emrc.sku_ids)::text[]))))
  • Rows Removed by Filter: 201
  • Heap Blocks: exact=1316736
11. 6,508.250 71,932.898 ↓ 0.0 0 7,438

BitmapAnd (cost=469.57..469.57 rows=98 width=0) (actual time=9.671..9.671 rows=0 loops=7,438)

12. 35,434.632 35,434.632 ↓ 2.8 54,938 7,438

Bitmap Index Scan on sales_versions_period_version_name_idx (cost=0.00..202.93 rows=19,977 width=0) (actual time=4.764..4.764 rows=54,938 loops=7,438)

  • Index Cond: ((period = emrc."from") AND (version_name = emrc.required_view))
13. 37.190 29,990.016 ↓ 0.0 0 7,438

BitmapOr (cost=266.40..266.40 rows=32,823 width=0) (actual time=4.032..4.032 rows=0 loops=7,438)

14. 252.892 252.892 ↑ 3.1 344 7,438

Bitmap Index Scan on sales_versions_sold_to_point_id_idx (cost=0.00..11.63 rows=1,050 width=0) (actual time=0.034..0.034 rows=344 loops=7,438)

  • Index Cond: (sold_to_point_id = (emrc.marketable_id)::text)
15. 2,186.772 2,186.772 ↑ 2.6 4,520 7,438

Bitmap Index Scan on sales_versions_sold_to_group_id_idx (cost=0.00..92.10 rows=11,859 width=0) (actual time=0.294..0.294 rows=4,520 loops=7,438)

  • Index Cond: (sold_to_group_id = (emrc.marketable_id)::text)
16. 27,513.162 27,513.162 ↓ 5.8 54,655 7,438

Bitmap Index Scan on sales_versions_nestle_planning_account_id_idx (cost=0.00..74.59 rows=9,421 width=0) (actual time=3.699..3.699 rows=54,655 loops=7,438)

  • Index Cond: (nestle_planning_account_id = (emrc.marketable_id)::text)
17. 0.000 0.000 ↓ 0.0 0 7,438

Bitmap Index Scan on sales_versions_sold_to_point_id_idx (cost=0.00..88.08 rows=10,493 width=0) (actual time=0.000..0.000 rows=0 loops=7,438)

  • Index Cond: (sold_to_point_id = ANY (emrc.custom_customer_grouping_sold_to_ids))
18. 0.016 0.034 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=7) (actual time=0.034..0.034 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.018 0.018 ↑ 1.0 25 1

Seq Scan on markets_exceptions me (cost=0.00..1.25 rows=25 width=7) (actual time=0.007..0.018 rows=25 loops=1)

20. 5.752 13.192 ↑ 1.0 11,193 1

Hash (cost=742.93..742.93 rows=11,193 width=12) (actual time=13.191..13.192 rows=11,193 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 613kB
21. 7.440 7.440 ↑ 1.0 11,193 1

Seq Scan on xin_material_masters xmm (cost=0.00..742.93 rows=11,193 width=12) (actual time=0.006..7.440 rows=11,193 loops=1)

Planning time : 33.511 ms
Execution time : 75,099.747 ms