#	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

GroupAggregate  (cost=3943083.56..3949688.55 rows=54429 width=364) (actual time=74920.707..75072.723 rows=53210 loops=1)
  Group Key: t.rebate_id, t.period, t.sku_id, t.beneficiary, t.sold_to_point_id
  ->  Sort  (cost=3943083.56..3943681.37 rows=239126 width=228) (actual time=74920.674..74942.698 rows=53338 loops=1)
        Sort Key: t.rebate_id, t.period, t.sku_id, t.beneficiary, t.sold_to_point_id
        Sort Method: quicksort  Memory: 8301kB
        ->  Subquery Scan on t  (cost=3881292.87..3906401.10 rows=239126 width=228) (actual time=74574.526..74790.721 rows=53338 loops=1)
              ->  WindowAgg  (cost=3881292.87..3904009.84 rows=239126 width=248) (actual time=74574.523..74750.552 rows=53338 loops=1)
                    ->  Sort  (cost=3881292.87..3881890.68 rows=239126 width=159) (actual time=74574.485..74595.360 rows=53338 loops=1)
                          Sort Key: emrc.rebate_id, emrc.period
                          Sort Method: quicksort  Memory: 9037kB
                          ->  Hash Left Join  (cost=1354.26..3848919.50 rows=239126 width=159) (actual time=1155.087..74513.894 rows=53338 loops=1)
                                Hash Cond: (sv.sku_id = (xmm.sku_id)::text)
                                Join Filter: (emrc.rebate_type = 4)
                                Rows Removed by Join Filter: 53338
                                ->  Hash Anti Join  (cost=471.42..3847408.95 rows=239126 width=153) (actual time=1141.871..74444.050 rows=53338 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]))
                                      ->  Nested Loop  (cost=469.86..3843789.30 rows=239165 width=157) (actual time=1141.824..74397.372 rows=53338 loops=1)
                                            ->  Index Scan using emrc_rebate_type_idx on emrc  (cost=0.28..292.95 rows=7438 width=218) (actual time=0.014..9.820 rows=7438 loops=1)
                                            ->  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=7438)
                                                  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
                                                  ->  BitmapAnd  (cost=469.57..469.57 rows=98 width=0) (actual time=9.671..9.671 rows=0 loops=7438)
                                                        ->  Bitmap Index Scan on sales_versions_period_version_name_idx  (cost=0.00..202.93 rows=19977 width=0) (actual time=4.764..4.764 rows=54938 loops=7438)
                                                              Index Cond: ((period = emrc."from") AND (version_name = emrc.required_view))
                                                        ->  BitmapOr  (cost=266.40..266.40 rows=32823 width=0) (actual time=4.032..4.032 rows=0 loops=7438)
                                                              ->  Bitmap Index Scan on sales_versions_sold_to_point_id_idx  (cost=0.00..11.63 rows=1050 width=0) (actual time=0.034..0.034 rows=344 loops=7438)
                                                                    Index Cond: (sold_to_point_id = (emrc.marketable_id)::text)
                                                              ->  Bitmap Index Scan on sales_versions_sold_to_group_id_idx  (cost=0.00..92.10 rows=11859 width=0) (actual time=0.294..0.294 rows=4520 loops=7438)
                                                                    Index Cond: (sold_to_group_id = (emrc.marketable_id)::text)
                                                              ->  Bitmap Index Scan on sales_versions_nestle_planning_account_id_idx  (cost=0.00..74.59 rows=9421 width=0) (actual time=3.699..3.699 rows=54655 loops=7438)
                                                                    Index Cond: (nestle_planning_account_id = (emrc.marketable_id)::text)
                                                              ->  Bitmap Index Scan on sales_versions_sold_to_point_id_idx  (cost=0.00..88.08 rows=10493 width=0) (actual time=0.000..0.000 rows=0 loops=7438)
                                                                    Index Cond: (sold_to_point_id = ANY (emrc.custom_customer_grouping_sold_to_ids))
                                      ->  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
                                            ->  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)
                                ->  Hash  (cost=742.93..742.93 rows=11193 width=12) (actual time=13.191..13.192 rows=11193 loops=1)
                                      Buckets: 16384  Batches: 1  Memory Usage: 613kB
                                      ->  Seq Scan on xin_material_masters xmm  (cost=0.00..742.93 rows=11193 width=12) (actual time=0.006..7.440 rows=11193 loops=1)
Planning Time: 33.511 ms
JIT:
  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
Execution Time: 75099.747 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	2,372.722 ms	3.2 %
Bitmap Index Scan	5	65,387.458 ms	87.1 %
BitmapAnd	1	6,508.250 ms	8.7 %
BitmapOr	1	37.190 ms	0.0 %
GroupAggregate	1	130.025 ms	0.2 %
Hash	2	5.768 ms	0.0 %
Hash Anti Join	1	46.644 ms	0.1 %
Hash Left Join	1	56.652 ms	0.1 %
Index Scan	1	9.820 ms	0.0 %
Nested Loop	1	81.932 ms	0.1 %
Seq Scan	2	7.458 ms	0.0 %
Sort	2	233.443 ms	0.3 %
Subquery Scan	1	40.169 ms	0.1 %
WindowAgg	1	155.192 ms	0.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
emrc	1	9.820 ms	0.0 %
Index Scan	1	9.820 ms	100.0 %
markets_exceptions	1	0.018 ms	0.0 %
Seq Scan	1	0.018 ms	100.0 %
sales_versions	1	2,372.722 ms	3.2 %
Bitmap Heap Scan	1	2,372.722 ms	100.0 %
xin_material_masters	1	7.440 ms	0.0 %
Seq Scan	1	7.440 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kxbx : Optimization for: plan #jOls

Color mode:

Visible columns:

Optimization path:

Per node type stats

Per table stats