explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VKxg

Settings
# exclusive inclusive rows x rows loops node
1. 130.578 75,705.680 ↑ 1.0 53,210 1

GroupAggregate (cost=3,943,019.99..3,949,624.98 rows=54,429 width=364) (actual time=75,551.246..75,705.680 rows=53,210 loops=1)

  • Output: t.rebate_id, t.period, t.sku_id, t.beneficiary, t.sold_to_point_id, ''::text, ''::text, ''::text, ''::text, sum(t.forecast_units), sum(t.forecast_dollars), sum(t.forecast_trade_spend), timezone('America/Chicago'::text, now())
  • Group Key: t.rebate_id, t.period, t.sku_id, t.beneficiary, t.sold_to_point_id
  • Buffers: shared hit=4187056, local hit=155
  • Functions: 42
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 17.070 ms, Inlining 15.671 ms, Optimization 628.525 ms, Emission 421.354 ms, Total 1082.621 ms
2. 153.117 75,575.102 ↑ 4.5 53,338 1

Sort (cost=3,943,019.99..3,943,617.80 rows=239,126 width=228) (actual time=75,551.206..75,575.102 rows=53,338 loops=1)

  • Output: t.rebate_id, t.period, t.sku_id, t.beneficiary, t.sold_to_point_id, t.forecast_units, t.forecast_dollars, t.forecast_trade_spend
  • Sort Key: t.rebate_id, t.period, t.sku_id, t.beneficiary, t.sold_to_point_id
  • Sort Method: quicksort Memory: 8301kB
  • Buffers: shared hit=4187056, local hit=155
3. 41.085 75,421.985 ↑ 4.5 53,338 1

Subquery Scan on t (cost=3,881,229.30..3,906,337.53 rows=239,126 width=228) (actual time=75,203.881..75,421.985 rows=53,338 loops=1)

  • Output: t.rebate_id, t.period, t.sku_id, t.beneficiary, t.sold_to_point_id, t.forecast_units, t.forecast_dollars, t.forecast_trade_spend
  • Buffers: shared hit=4187056, local hit=155
4. 155.878 75,380.900 ↑ 4.5 53,338 1

WindowAgg (cost=3,881,229.30..3,903,946.27 rows=239,126 width=248) (actual time=75,203.879..75,380.900 rows=53,338 loops=1)

  • Output: emrc.rebate_id, (emrc.period)::text, COALESCE(sv.sku_id, 'UNKNOWN'::text), COALESCE(CASE WHEN (emrc.beneficiary_definer = 0) THEN sv.sold_to_group_id WHEN (emrc.beneficiary_definer = 1) THEN sv.sold_to_group_id WHEN (emrc.beneficiary_definer = 2) THEN (emrc.beneficiary_if_someone_else)::text ELSE NULL::text END, (CASE WHEN ((emrc.marketable_type)::text = 'SoldtoGroup'::text) THEN emrc.marketable_id ELSE NULL::character varying END)::text, 'SONAR-UNKNOWN'::text), COALESCE(sv.sold_to_point_id, ''::text), (sv.volume * '1'::numeric), (sv.revenue * '1'::numeric), CASE WHEN (emrc.rebate_type = 0) THEN ((sv.volume * '1'::numeric) * emrc.value) WHEN (emrc.rebate_type = 1) THEN (((sv.revenue * '1'::numeric) * emrc.value) / '100'::numeric) WHEN (emrc.rebate_type = 2) THEN ((emrc.lsrma_amount)::numeric * COALESCE((COALESCE(sv.revenue, '0'::numeric) / NULLIF(sum(sv.revenue) OVER (?), '0'::numeric)), ('1'::numeric / (count(*) OVER (?))::numeric))) WHEN (emrc.rebate_type = 3) THEN ((sv.volume * '1'::numeric) * ((sv.revenue / NULLIF(sv.volume, '0'::numeric)) - emrc.net_price)) WHEN (emrc.rebate_type = 4) THEN ((emrc.value * sv.volume) * xmm.net_weight_lb_per_buom) ELSE NULL::numeric END, NULL::timestamp without time zone, emrc.rebate_id, emrc.period
  • Buffers: shared hit=4187056, local hit=155
5. 82.375 75,225.022 ↑ 4.5 53,338 1

Sort (cost=3,881,229.30..3,881,827.11 rows=239,126 width=159) (actual time=75,203.835..75,225.022 rows=53,338 loops=1)

  • Output: emrc.rebate_id, emrc.period, sv.sku_id, emrc.beneficiary_definer, sv.sold_to_group_id, emrc.beneficiary_if_someone_else, emrc.marketable_type, emrc.marketable_id, sv.sold_to_point_id, sv.volume, sv.revenue, emrc.rebate_type, emrc.value, emrc.lsrma_amount, emrc.net_price, xmm.net_weight_lb_per_buom
  • Sort Key: emrc.rebate_id, emrc.period
  • Sort Method: quicksort Memory: 9037kB
  • Buffers: shared hit=4187056, local hit=155
6. 58.591 75,142.647 ↑ 4.5 53,338 1

Hash Left Join (cost=1,353.98..3,848,855.93 rows=239,126 width=159) (actual time=1,102.260..75,142.647 rows=53,338 loops=1)

  • Output: emrc.rebate_id, emrc.period, sv.sku_id, emrc.beneficiary_definer, sv.sold_to_group_id, emrc.beneficiary_if_someone_else, emrc.marketable_type, emrc.marketable_id, sv.sold_to_point_id, sv.volume, sv.revenue, emrc.rebate_type, emrc.value, emrc.lsrma_amount, emrc.net_price, xmm.net_weight_lb_per_buom
  • Inner Unique: true
  • Hash Cond: (sv.sku_id = (xmm.sku_id)::text)
  • Join Filter: (emrc.rebate_type = 4)
  • Rows Removed by Join Filter: 53338
  • Buffers: shared hit=4187056, local hit=155
7. 48.489 75,070.874 ↑ 4.5 53,338 1

Hash Anti Join (cost=471.14..3,847,345.38 rows=239,126 width=153) (actual time=1,089.055..75,070.874 rows=53,338 loops=1)

  • Output: emrc.rebate_id, emrc.period, emrc.beneficiary_definer, emrc.beneficiary_if_someone_else, emrc.marketable_type, emrc.marketable_id, emrc.rebate_type, emrc.value, emrc.lsrma_amount, emrc.net_price, sv.sku_id, sv.sold_to_group_id, sv.sold_to_point_id, sv.volume, sv.revenue
  • 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]))
  • Buffers: shared hit=4186425, local hit=155
8. 84.130 75,022.348 ↑ 4.5 53,338 1

Nested Loop (cost=469.57..3,843,725.72 rows=239,165 width=157) (actual time=1,089.004..75,022.348 rows=53,338 loops=1)

  • Output: emrc.rebate_id, emrc.period, emrc.beneficiary_definer, emrc.beneficiary_if_someone_else, emrc.marketable_type, emrc.marketable_id, emrc.rebate_type, emrc.value, emrc.lsrma_amount, emrc.net_price, emrc.event_id, sv.sku_id, sv.sold_to_group_id, sv.sold_to_point_id, sv.volume, sv.revenue
  • Buffers: shared hit=4186424, local hit=155
9. 7.806 7.806 ↑ 1.0 7,438 1

Seq Scan on pg_temp_3.emrc (cost=0.00..229.38 rows=7,438 width=218) (actual time=0.012..7.806 rows=7,438 loops=1)

  • Output: emrc.event_type, emrc.system_of_record, emrc.beneficiary_definer, emrc.beneficiary_if_someone_else, emrc.required_view, emrc.company_of_origin_in_scope, emrc.marketable_id, emrc.marketable_type, emrc.marketable_sold_to_point_id, emrc.marketable_sold_to_group_id, emrc.marketable_ship_to_point_id, emrc.marketable_ship_to_group_id, emrc.custom_customer_grouping_sold_to_ids, emrc.net_price, emrc.value, emrc.lob_constraint, emrc.rebate_type, emrc.materialable_id, emrc.sku_ids, emrc.event_id, emrc.ship_start, emrc.exec_start, emrc.ship_end, emrc.exec_end, emrc.rebate_id, emrc.period, emrc."from", emrc."to", emrc.day_count, emrc.lsrma_amount
  • Buffers: local hit=155
10. 2,424.788 74,930.412 ↓ 7.0 7 7,438

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

  • Output: sv.id, sv.version_name, sv.sold_to_point_id, sv.sku_id, sv.period, sv.volume, sv.revenue, sv.created_at, sv.updated_at, sv.promo_group_id, sv.brand_id, sv.product_level_2_id, sv.lob_id, sv.segment, sv.company_of_origin, sv.customer_group_id, sv.nestle_planning_account_id, sv.sold_to_group_id
  • Recheck Cond: ((sv.period = emrc."from") AND (sv.version_name = emrc.required_view) AND (((emrc.marketable_id)::text = sv.sold_to_group_id) OR ((emrc.marketable_id)::text = sv.sold_to_point_id) OR ((emrc.marketable_id)::text = sv.nestle_planning_account_id) OR (sv.sold_to_point_id = ANY (emrc.custom_customer_grouping_sold_to_ids))))
  • Filter: ((((emrc.lob_constraint)::text = 'none'::text) OR ((emrc.lob_constraint)::text = sv.lob_id)) AND ((sv.revenue <> '0'::numeric) OR (emrc.rebate_type = 2)) AND ((sv.period >= '2020-01-01'::date) OR ((sv.period < '2020-01-01'::date) AND (sv.company_of_origin = emrc.company_of_origin_in_scope))) AND (((emrc.materialable_id)::text = sv.sku_id) OR ((emrc.materialable_id)::text = sv.promo_group_id) OR ((emrc.materialable_id)::text = sv.brand_id) OR ((emrc.materialable_id)::text = sv.product_level_2_id) OR ((emrc.materialable_id)::text = sv.lob_id) OR (sv.sku_id = ANY ((emrc.sku_ids)::text[]))))
  • Rows Removed by Filter: 201
  • Heap Blocks: exact=1316736
  • Buffers: shared hit=4186424
11. 6,545.440 72,505.624 ↓ 0.0 0 7,438

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

  • Buffers: shared hit=2869688
12. 35,806.532 35,806.532 ↓ 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.814..4.814 rows=54,938 loops=7,438)

  • Index Cond: ((sv.period = emrc."from") AND (sv.version_name = emrc.required_view))
  • Buffers: shared hit=1136925
13. 37.190 30,153.652 ↓ 0.0 0 7,438

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

  • Buffers: shared hit=1732763
14. 2,223.962 2,223.962 ↑ 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.299..0.299 rows=4,520 loops=7,438)

  • Index Cond: (sv.sold_to_group_id = (emrc.marketable_id)::text)
  • Buffers: shared hit=114120
15. 238.016 238.016 ↑ 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.032..0.032 rows=344 loops=7,438)

  • Index Cond: (sv.sold_to_point_id = (emrc.marketable_id)::text)
  • Buffers: shared hit=39489
16. 27,654.484 27,654.484 ↓ 5.8 54,655 7,438

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

  • Index Cond: (sv.nestle_planning_account_id = (emrc.marketable_id)::text)
  • Buffers: shared hit=1579154
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: (sv.sold_to_point_id = ANY (emrc.custom_customer_grouping_sold_to_ids))
18. 0.017 0.037 ↑ 1.0 25 1

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

  • Output: me.event_id, me.marketable_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
19. 0.020 0.020 ↑ 1.0 25 1

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

  • Output: me.event_id, me.marketable_id
  • Buffers: shared hit=1
20. 5.721 13.182 ↑ 1.0 11,193 1

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

  • Output: xmm.net_weight_lb_per_buom, xmm.sku_id
  • Buckets: 16384 Batches: 1 Memory Usage: 613kB
  • Buffers: shared hit=631
21. 7.461 7.461 ↑ 1.0 11,193 1

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

  • Output: xmm.net_weight_lb_per_buom, xmm.sku_id
  • Buffers: shared hit=631
Planning time : 31.914 ms
Execution time : 75,742.675 ms