I have a table that contains accumulated sales of many products over time. The schema is as follows:
CREATE TABLE IF NOT EXISTS public.accumulated_sales
(
id bigint NOT NULL,
sale_date daterange NOT NULL,
units_sold integer,
sales_amount real,
sales_amount_usd real,
category_id integer NOT NULL,
CONSTRAINT accumulated_sales_new_pkey PRIMARY KEY (id, sale_date, category_id),
CONSTRAINT accumulated_sales_new_id_fkey FOREIGN KEY (id)
REFERENCES public.item_new (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
) PARTITION BY LIST (category_id);
There is a hierarchy of categories and the table is partitioned by lists of category_id so that each partition has related categories together. sale_date represents (date_product_had_sales, next_date_product_had_sales). For the current latest entry, next_date_product_had_sales is left as '2400-01-1'.
The table has millions of records and about 3 years of history. We now allow the user to build a weekly report where they can see the sales of each product grouped by week for an entire year, filtered by a category (which can have many category leaf nodes).
The query being generated to solve an example of that report is this:
select sale.id, sale.week, sale.sales_amount, sale.sales_amount_usd, sale.units_sold from (
select id, sale_date, case when lower(sale_date) > '2024-12-29' then date_part('week',lower(sale_date)) else null end as week, sales_amount - COALESCE(LAG(sales_amount) over w, 0) as sales_amount, sales_amount_usd - COALESCE(LAG(sales_amount_usd) over w, 0) as sales_amount_usd, units_sold - COALESCE(LAG(units_sold) over w, 0) as units_sold
from accumulated_sales
where not sale_date @> daterange('2024-12-29'::date, '2400-01-01'::date) and sale_date @> ANY (array ['2024-12-29'::date, '2025-01-05'::date, '2025-01-12'::date, '2025-01-19'::date, '2025-01-26'::date, '2025-02-02'::date, '2025-02-09'::date, '2025-02-16'::date, '2025-02-23'::date, '2025-03-02'::date, '2025-03-09'::date, '2025-03-16'::date, '2025-03-23'::date, '2025-03-30'::date, '2025-04-06'::date, '2025-04-13'::date, '2025-04-20'::date, '2025-04-27'::date, '2025-05-04'::date, '2025-05-11'::date, '2025-05-18'::date, '2025-05-25'::date, '2025-06-01'::date, '2025-06-08'::date, '2025-06-15'::date, '2025-06-22'::date, '2025-06-29'::date, '2025-07-06'::date, '2025-07-13'::date, '2025-07-20'::date, '2025-07-27'::date, '2025-08-03'::date, '2025-08-10'::date, '2025-08-17'::date, '2025-08-24'::date, '2025-08-31'::date, '2025-09-07'::date, '2025-09-14'::date, '2025-09-21'::date, '2025-09-28'::date, '2025-10-05'::date, '2025-10-12'::date, '2025-10-19'::date, '2025-10-26'::date, '2025-11-02'::date, '2025-11-09'::date, '2025-11-16'::date, '2025-11-23'::date, '2025-11-30'::date, '2025-12-07'::date, '2025-12-14'::date, '2025-12-21'::date, '2025-12-28'::date])
and category_id = ANY ('{7628,7632,7637,7640,7645,7646,7665,7667,7670,7674,8426,8430,8433,8446,8481,8425,8431,8440,8442,8437,11803,7672,8436,8439,8429,8432,8438,8443,8444,8445,8448,8798,8795,8796,8797,8800,8447,8802,8799,8801,8441,8428,8435,7676,7692,6837,7700,7702,7705,7707,7708,7710,6865,7762,7823,8557,8544,8545,7824,8560,8586,8590,8939,8943,9186,8592,8561,8562,8563,11663,11664,8502,8503,8504,8505,7763,8508,8511,8513,8506,8509,8515,8523,8525,7782,7800,8514,8517,8520,8542,8546,8838,8839,8842,8845,8548,8558,8559,8844,8846,8849,8851,8854,8867,9025,9026,9029,8868,8865,8866,8547,8847,8852,8855,8858,8526,8529,8540,8541,8543,6867,7793,7794,7795,8527,8532,8537,8539,8530,8518,8522,8528,8531,8533,8538,8534,6870,7812,7813,7815,6872,7796,7797,7799,7801,7703,7712,7720,8449,8452,8453,8454,7724,8490,8494,8496,8497,8455,7775,8460,8462,8465,8458,8463,8482,8495,8813,8814,8818,8821,8834,8484,8491,8493,8804,8807,8810,8456,8812,8806,8464,8805,8808,8803,8483,8485,8492,8809,8811,10514,8650,8498,8816,9010,8815,8841,8499,8820,8823,8835,8864,9185,8836,8837,8929,8930,8819,8817,8840,8843,8928,8848,8850,8857,8859,8860,8861,8853,8863,8862,7689,7698,8424,8427,8554,8434,7648,7649,7651,7671,7673,8422,8423,8421}')
WINDOW w as (partition by id order by sale_date asc rows between 1 preceding and CURRENT ROW)
) sale
where week is not null
order by sale.sales_amount desc nulls last
This is taking about 2 minutes. I did not suspect at first that it should take less time, but after seeing that all the time was spent at the bitmap index scan, after trying set enable_bitmapscan to off the query improves drastically to 7 seconds. I don't understand why it takes so long and would appreciate any guidance on how to proceed.
This is the full query plan:
"Subquery Scan on sale (cost=717818.54..1605714.24 rows=5228589 width=28) (actual time=100176.480..101019.963 rows=554610 loops=1)"
" Output: sale.id, sale.week, sale.sales_amount, sale.sales_amount_usd, sale.units_sold"
" Filter: (sale.week IS NOT NULL)"
" Rows Removed by Filter: 104972"
" Buffers: shared hit=7264261"
" -> WindowAgg (cost=717818.54..1553165.61 rows=5254863 width=42) (actual time=100176.469..100951.151 rows=659582 loops=1)"
" Output: accumulated_sales.id, accumulated_sales.sale_date, CASE WHEN (lower(accumulated_sales.sale_date) > '2024-12-29'::date) THEN date_part('week'::text, (lower(accumulated_sales.sale_date))::timestamp without time zone) ELSE NULL::double precision END, (accumulated_sales.sales_amount - COALESCE(lag(accumulated_sales.sales_amount) OVER (?), '0'::real)), (accumulated_sales.sales_amount_usd - COALESCE(lag(accumulated_sales.sales_amount_usd) OVER (?), '0'::real)), (accumulated_sales.units_sold - COALESCE(lag(accumulated_sales.units_sold) OVER (?), 0))"
" Buffers: shared hit=7264261"
" -> Gather Merge (cost=717818.54..1329833.93 rows=5254863 width=34) (actual time=100176.454..100443.751 rows=659582 loops=1)"
" Output: accumulated_sales.id, accumulated_sales.sale_date, accumulated_sales.sales_amount, accumulated_sales.sales_amount_usd, accumulated_sales.units_sold"
" Workers Planned: 2"
" Workers Launched: 2"
" Buffers: shared hit=7264261"
" -> Sort (cost=716818.51..722292.33 rows=2189526 width=34) (actual time=100162.203..100200.002 rows=219861 loops=3)"
" Output: accumulated_sales.id, accumulated_sales.sale_date, accumulated_sales.sales_amount, accumulated_sales.sales_amount_usd, accumulated_sales.units_sold"
" Sort Key: accumulated_sales.id, accumulated_sales.sale_date"
" Sort Method: quicksort Memory: 20813kB"
" Buffers: shared hit=7264261"
" Worker 0: actual time=100147.835..100182.249 rows=193827 loops=1"
" Sort Method: quicksort Memory: 18259kB"
" Buffers: shared hit=23738"
" Worker 1: actual time=100163.081..100203.509 rows=231065 loops=1"
" Sort Method: quicksort Memory: 20586kB"
" Buffers: shared hit=7174773"
" -> Parallel Append (cost=5187.00..452025.48 rows=2189526 width=34) (actual time=66736.111..100046.021 rows=219861 loops=3)"
" Buffers: shared hit=7264225"
" Worker 0: actual time=99892.620..100042.222 rows=193827 loops=1"
" Buffers: shared hit=23720"
" Worker 1: actual time=99893.715..100042.945 rows=231065 loops=1"
" Buffers: shared hit=7174755"
" -> Parallel Bitmap Heap Scan on public.accumulated_sales_new_mla1574_2 accumulated_sales_1 (cost=111378.88..434315.36 rows=2188137 width=34) (actual time=99615.149..99747.595 rows=219853 loops=3)"
" Output: accumulated_sales_1.id, accumulated_sales_1.sale_date, accumulated_sales_1.sales_amount, accumulated_sales_1.sales_amount_usd, accumulated_sales_1.units_sold"
" Recheck Cond: ((accumulated_sales_1.sale_date @> ANY ('{2024-12-29,2025-01-05,2025-01-12,2025-01-19,2025-01-26,2025-02-02,2025-02-09,2025-02-16,2025-02-23,2025-03-02,2025-03-09,2025-03-16,2025-03-23,2025-03-30,2025-04-06,2025-04-13,2025-04-20,2025-04-27,2025-05-04,2025-05-11,2025-05-18,2025-05-25,2025-06-01,2025-06-08,2025-06-15,2025-06-22,2025-06-29,2025-07-06,2025-07-13,2025-07-20,2025-07-27,2025-08-03,2025-08-10,2025-08-17,2025-08-24,2025-08-31,2025-09-07,2025-09-14,2025-09-21,2025-09-28,2025-10-05,2025-10-12,2025-10-19,2025-10-26,2025-11-02,2025-11-09,2025-11-16,2025-11-23,2025-11-30,2025-12-07,2025-12-14,2025-12-21,2025-12-28}'::date[])) AND (accumulated_sales_1.category_id = ANY ('{7628,7632,7637,7640,7645,7646,7665,7667,7670,7674,8426,8430,8433,8446,8481,8425,8431,8440,8442,8437,11803,7672,8436,8439,8429,8432,8438,8443,8444,8445,8448,8798,8795,8796,8797,8800,8447,8802,8799,8801,8441,8428,8435,7676,7692,6837,7700,7702,7705,7707,7708,7710,6865,7762,7823,8557,8544,8545,7824,8560,8586,8590,8939,8943,9186,8592,8561,8562,8563,11663,11664,8502,8503,8504,8505,7763,8508,8511,8513,8506,8509,8515,8523,8525,7782,7800,8514,8517,8520,8542,8546,8838,8839,8842,8845,8548,8558,8559,8844,8846,8849,8851,8854,8867,9025,9026,9029,8868,8865,8866,8547,8847,8852,8855,8858,8526,8529,8540,8541,8543,6867,7793,7794,7795,8527,8532,8537,8539,8530,8518,8522,8528,8531,8533,8538,8534,6870,7812,7813,7815,6872,7796,7797,7799,7801,7703,7712,7720,8449,8452,8453,8454,7724,8490,8494,8496,8497,8455,7775,8460,8462,8465,8458,8463,8482,8495,8813,8814,8818,8821,8834,8484,8491,8493,8804,8807,8810,8456,8812,8806,8464,8805,8808,8803,8483,8485,8492,8809,8811,10514,8650,8498,8816,9010,8815,8841,8499,8820,8823,8835,8864,9185,8836,8837,8929,8930,8819,8817,8840,8843,8928,8848,8850,8857,8859,8860,8861,8853,8863,8862,7689,7698,8424,8427,8554,8434,7648,7649,7651,7671,7673,8422,8423,8421}'::integer[])))"
" Filter: (NOT (accumulated_sales_1.sale_date @> '[2024-12-29,2400-01-01)'::daterange))"
" Rows Removed by Filter: 54885"
" Heap Blocks: exact=26100"
" Buffers: shared hit=7224575"
" Worker 0: actual time=99475.935..99607.875 rows=193827 loops=1"
" Buffers: shared hit=23720"
" Worker 1: actual time=99893.714..100024.961 rows=231065 loops=1"
" Buffers: shared hit=7174755"
" -> Bitmap Index Scan on accumulated_sales_new_mla1574_2_sale_date_category_id_idx (cost=0.00..110065.42 rows=5317382 width=0) (actual time=99877.002..99877.003 rows=15833113 loops=1)"
" Index Cond: ((accumulated_sales_1.sale_date @> ANY ('{2024-12-29,2025-01-05,2025-01-12,2025-01-19,2025-01-26,2025-02-02,2025-02-09,2025-02-16,2025-02-23,2025-03-02,2025-03-09,2025-03-16,2025-03-23,2025-03-30,2025-04-06,2025-04-13,2025-04-20,2025-04-27,2025-05-04,2025-05-11,2025-05-18,2025-05-25,2025-06-01,2025-06-08,2025-06-15,2025-06-22,2025-06-29,2025-07-06,2025-07-13,2025-07-20,2025-07-27,2025-08-03,2025-08-10,2025-08-17,2025-08-24,2025-08-31,2025-09-07,2025-09-14,2025-09-21,2025-09-28,2025-10-05,2025-10-12,2025-10-19,2025-10-26,2025-11-02,2025-11-09,2025-11-16,2025-11-23,2025-11-30,2025-12-07,2025-12-14,2025-12-21,2025-12-28}'::date[])) AND (accumulated_sales_1.category_id = ANY ('{7628,7632,7637,7640,7645,7646,7665,7667,7670,7674,8426,8430,8433,8446,8481,8425,8431,8440,8442,8437,11803,7672,8436,8439,8429,8432,8438,8443,8444,8445,8448,8798,8795,8796,8797,8800,8447,8802,8799,8801,8441,8428,8435,7676,7692,6837,7700,7702,7705,7707,7708,7710,6865,7762,7823,8557,8544,8545,7824,8560,8586,8590,8939,8943,9186,8592,8561,8562,8563,11663,11664,8502,8503,8504,8505,7763,8508,8511,8513,8506,8509,8515,8523,8525,7782,7800,8514,8517,8520,8542,8546,8838,8839,8842,8845,8548,8558,8559,8844,8846,8849,8851,8854,8867,9025,9026,9029,8868,8865,8866,8547,8847,8852,8855,8858,8526,8529,8540,8541,8543,6867,7793,7794,7795,8527,8532,8537,8539,8530,8518,8522,8528,8531,8533,8538,8534,6870,7812,7813,7815,6872,7796,7797,7799,7801,7703,7712,7720,8449,8452,8453,8454,7724,8490,8494,8496,8497,8455,7775,8460,8462,8465,8458,8463,8482,8495,8813,8814,8818,8821,8834,8484,8491,8493,8804,8807,8810,8456,8812,8806,8464,8805,8808,8803,8483,8485,8492,8809,8811,10514,8650,8498,8816,9010,8815,8841,8499,8820,8823,8835,8864,9185,8836,8837,8929,8930,8819,8817,8840,8843,8928,8848,8850,8857,8859,8860,8861,8853,8863,8862,7689,7698,8424,8427,8554,8434,7648,7649,7651,7671,7673,8422,8423,8421}'::integer[])))"
" Buffers: shared hit=7146217"
" Worker 1: actual time=99877.002..99877.003 rows=15833113 loops=1"
" Buffers: shared hit=7146217"
" -> Parallel Bitmap Heap Scan on public.accumulated_sales_new_default accumulated_sales_2 (cost=5187.00..6762.49 rows=1961 width=34) (actual time=419.339..419.360 rows=12 loops=2)"
" Output: accumulated_sales_2.id, accumulated_sales_2.sale_date, accumulated_sales_2.sales_amount, accumulated_sales_2.sales_amount_usd, accumulated_sales_2.units_sold"
" Recheck Cond: ((accumulated_sales_2.sale_date @> ANY ('{2024-12-29,2025-01-05,2025-01-12,2025-01-19,2025-01-26,2025-02-02,2025-02-09,2025-02-16,2025-02-23,2025-03-02,2025-03-09,2025-03-16,2025-03-23,2025-03-30,2025-04-06,2025-04-13,2025-04-20,2025-04-27,2025-05-04,2025-05-11,2025-05-18,2025-05-25,2025-06-01,2025-06-08,2025-06-15,2025-06-22,2025-06-29,2025-07-06,2025-07-13,2025-07-20,2025-07-27,2025-08-03,2025-08-10,2025-08-17,2025-08-24,2025-08-31,2025-09-07,2025-09-14,2025-09-21,2025-09-28,2025-10-05,2025-10-12,2025-10-19,2025-10-26,2025-11-02,2025-11-09,2025-11-16,2025-11-23,2025-11-30,2025-12-07,2025-12-14,2025-12-21,2025-12-28}'::date[])) AND (accumulated_sales_2.category_id = ANY ('{7628,7632,7637,7640,7645,7646,7665,7667,7670,7674,8426,8430,8433,8446,8481,8425,8431,8440,8442,8437,11803,7672,8436,8439,8429,8432,8438,8443,8444,8445,8448,8798,8795,8796,8797,8800,8447,8802,8799,8801,8441,8428,8435,7676,7692,6837,7700,7702,7705,7707,7708,7710,6865,7762,7823,8557,8544,8545,7824,8560,8586,8590,8939,8943,9186,8592,8561,8562,8563,11663,11664,8502,8503,8504,8505,7763,8508,8511,8513,8506,8509,8515,8523,8525,7782,7800,8514,8517,8520,8542,8546,8838,8839,8842,8845,8548,8558,8559,8844,8846,8849,8851,8854,8867,9025,9026,9029,8868,8865,8866,8547,8847,8852,8855,8858,8526,8529,8540,8541,8543,6867,7793,7794,7795,8527,8532,8537,8539,8530,8518,8522,8528,8531,8533,8538,8534,6870,7812,7813,7815,6872,7796,7797,7799,7801,7703,7712,7720,8449,8452,8453,8454,7724,8490,8494,8496,8497,8455,7775,8460,8462,8465,8458,8463,8482,8495,8813,8814,8818,8821,8834,8484,8491,8493,8804,8807,8810,8456,8812,8806,8464,8805,8808,8803,8483,8485,8492,8809,8811,10514,8650,8498,8816,9010,8815,8841,8499,8820,8823,8835,8864,9185,8836,8837,8929,8930,8819,8817,8840,8843,8928,8848,8850,8857,8859,8860,8861,8853,8863,8862,7689,7698,8424,8427,8554,8434,7648,7649,7651,7671,7673,8422,8423,8421}'::integer[])))"
" Filter: (NOT (accumulated_sales_2.sale_date @> '[2024-12-29,2400-01-01)'::daterange))"
" Heap Blocks: exact=25"
" Buffers: shared hit=39650"
" Worker 0: actual time=416.681..416.681 rows=0 loops=1"
" -> Bitmap Index Scan on accumulated_sales_new_default_sale_date_category_id_idx (cost=0.00..5185.59 rows=3684 width=0) (actual time=421.956..421.957 rows=483 loops=1)"
" Index Cond: ((accumulated_sales_2.sale_date @> ANY ('{2024-12-29,2025-01-05,2025-01-12,2025-01-19,2025-01-26,2025-02-02,2025-02-09,2025-02-16,2025-02-23,2025-03-02,2025-03-09,2025-03-16,2025-03-23,2025-03-30,2025-04-06,2025-04-13,2025-04-20,2025-04-27,2025-05-04,2025-05-11,2025-05-18,2025-05-25,2025-06-01,2025-06-08,2025-06-15,2025-06-22,2025-06-29,2025-07-06,2025-07-13,2025-07-20,2025-07-27,2025-08-03,2025-08-10,2025-08-17,2025-08-24,2025-08-31,2025-09-07,2025-09-14,2025-09-21,2025-09-28,2025-10-05,2025-10-12,2025-10-19,2025-10-26,2025-11-02,2025-11-09,2025-11-16,2025-11-23,2025-11-30,2025-12-07,2025-12-14,2025-12-21,2025-12-28}'::date[])) AND (accumulated_sales_2.category_id = ANY ('{7628,7632,7637,7640,7645,7646,7665,7667,7670,7674,8426,8430,8433,8446,8481,8425,8431,8440,8442,8437,11803,7672,8436,8439,8429,8432,8438,8443,8444,8445,8448,8798,8795,8796,8797,8800,8447,8802,8799,8801,8441,8428,8435,7676,7692,6837,7700,7702,7705,7707,7708,7710,6865,7762,7823,8557,8544,8545,7824,8560,8586,8590,8939,8943,9186,8592,8561,8562,8563,11663,11664,8502,8503,8504,8505,7763,8508,8511,8513,8506,8509,8515,8523,8525,7782,7800,8514,8517,8520,8542,8546,8838,8839,8842,8845,8548,8558,8559,8844,8846,8849,8851,8854,8867,9025,9026,9029,8868,8865,8866,8547,8847,8852,8855,8858,8526,8529,8540,8541,8543,6867,7793,7794,7795,8527,8532,8537,8539,8530,8518,8522,8528,8531,8533,8538,8534,6870,7812,7813,7815,6872,7796,7797,7799,7801,7703,7712,7720,8449,8452,8453,8454,7724,8490,8494,8496,8497,8455,7775,8460,8462,8465,8458,8463,8482,8495,8813,8814,8818,8821,8834,8484,8491,8493,8804,8807,8810,8456,8812,8806,8464,8805,8808,8803,8483,8485,8492,8809,8811,10514,8650,8498,8816,9010,8815,8841,8499,8820,8823,8835,8864,9185,8836,8837,8929,8930,8819,8817,8840,8843,8928,8848,8850,8857,8859,8860,8861,8853,8863,8862,7689,7698,8424,8427,8554,8434,7648,7649,7651,7671,7673,8422,8423,8421}'::integer[])))"
" Buffers: shared hit=39625"
"Settings: effective_cache_size = '16123168kB', jit = 'off', work_mem = '128MB', random_page_cost = '1'"
"Query Identifier: -7833921758881389012"
"Planning Time: 1.636 ms"
"Execution Time: 101045.218 ms"
And this is the plan after disabling bitmap scan:
"Subquery Scan on sale (cost=998088.78..1885984.48 rows=5228589 width=28) (actual time=5412.324..6255.307 rows=554610 loops=1)"
" Output: sale.id, sale.week, sale.sales_amount, sale.sales_amount_usd, sale.units_sold"
" Filter: (sale.week IS NOT NULL)"
" Rows Removed by Filter: 104972"
" Buffers: shared hit=199 read=138773"
" I/O Timings: shared read=465.453"
" -> WindowAgg (cost=998088.78..1833435.85 rows=5254863 width=42) (actual time=5412.311..6183.154 rows=659582 loops=1)"
" Output: accumulated_sales.id, accumulated_sales.sale_date, CASE WHEN (lower(accumulated_sales.sale_date) > '2024-12-29'::date) THEN date_part('week'::text, (lower(accumulated_sales.sale_date))::timestamp without time zone) ELSE NULL::double precision END, (accumulated_sales.sales_amount - COALESCE(lag(accumulated_sales.sales_amount) OVER (?), '0'::real)), (accumulated_sales.sales_amount_usd - COALESCE(lag(accumulated_sales.sales_amount_usd) OVER (?), '0'::real)), (accumulated_sales.units_sold - COALESCE(lag(accumulated_sales.units_sold) OVER (?), 0))"
" Buffers: shared hit=199 read=138773"
" I/O Timings: shared read=465.453"
" -> Gather Merge (cost=998088.78..1610104.17 rows=5254863 width=34) (actual time=5412.298..5671.779 rows=659582 loops=1)"
" Output: accumulated_sales.id, accumulated_sales.sale_date, accumulated_sales.sales_amount, accumulated_sales.sales_amount_usd, accumulated_sales.units_sold"
" Workers Planned: 2"
" Workers Launched: 2"
" Buffers: shared hit=199 read=138773"
" I/O Timings: shared read=465.453"
" -> Sort (cost=997088.76..1002562.57 rows=2189526 width=34) (actual time=5403.808..5439.135 rows=219861 loops=3)"
" Output: accumulated_sales.id, accumulated_sales.sale_date, accumulated_sales.sales_amount, accumulated_sales.sales_amount_usd, accumulated_sales.units_sold"
" Sort Key: accumulated_sales.id, accumulated_sales.sale_date"
" Sort Method: quicksort Memory: 20165kB"
" Buffers: shared hit=199 read=138773"
" I/O Timings: shared read=465.453"
" Worker 0: actual time=5397.962..5432.593 rows=215412 loops=1"
" Sort Method: quicksort Memory: 19608kB"
" Buffers: shared hit=100 read=46682"
" I/O Timings: shared read=158.324"
" Worker 1: actual time=5401.701..5436.999 rows=219841 loops=1"
" Sort Method: quicksort Memory: 19885kB"
" Buffers: shared hit=98 read=46408"
" I/O Timings: shared read=150.375"
" -> Parallel Append (cost=0.58..732295.73 rows=2189526 width=34) (actual time=1.878..5286.082 rows=219861 loops=3)"
" Buffers: shared hit=63 read=138773"
" I/O Timings: shared read=465.453"
" Worker 0: actual time=3.063..5282.736 rows=215412 loops=1"
" Buffers: shared hit=32 read=46682"
" I/O Timings: shared read=158.324"
" Worker 1: actual time=0.181..5281.860 rows=219841 loops=1"
" Buffers: shared hit=30 read=46408"
" I/O Timings: shared read=150.375"
" -> Parallel Seq Scan on public.accumulated_sales_new_mla1574_2 accumulated_sales_1 (cost=0.58..711715.76 rows=2188137 width=34) (actual time=0.507..5249.633 rows=219853 loops=3)"
" Output: accumulated_sales_1.id, accumulated_sales_1.sale_date, accumulated_sales_1.sales_amount, accumulated_sales_1.sales_amount_usd, accumulated_sales_1.units_sold"
" Filter: ((NOT (accumulated_sales_1.sale_date @> '[2024-12-29,2400-01-01)'::daterange)) AND (accumulated_sales_1.category_id = ANY ('{7628,7632,7637,7640,7645,7646,7665,7667,7670,7674,8426,8430,8433,8446,8481,8425,8431,8440,8442,8437,11803,7672,8436,8439,8429,8432,8438,8443,8444,8445,8448,8798,8795,8796,8797,8800,8447,8802,8799,8801,8441,8428,8435,7676,7692,6837,7700,7702,7705,7707,7708,7710,6865,7762,7823,8557,8544,8545,7824,8560,8586,8590,8939,8943,9186,8592,8561,8562,8563,11663,11664,8502,8503,8504,8505,7763,8508,8511,8513,8506,8509,8515,8523,8525,7782,7800,8514,8517,8520,8542,8546,8838,8839,8842,8845,8548,8558,8559,8844,8846,8849,8851,8854,8867,9025,9026,9029,8868,8865,8866,8547,8847,8852,8855,8858,8526,8529,8540,8541,8543,6867,7793,7794,7795,8527,8532,8537,8539,8530,8518,8522,8528,8531,8533,8538,8534,6870,7812,7813,7815,6872,7796,7797,7799,7801,7703,7712,7720,8449,8452,8453,8454,7724,8490,8494,8496,8497,8455,7775,8460,8462,8465,8458,8463,8482,8495,8813,8814,8818,8821,8834,8484,8491,8493,8804,8807,8810,8456,8812,8806,8464,8805,8808,8803,8483,8485,8492,8809,8811,10514,8650,8498,8816,9010,8815,8841,8499,8820,8823,8835,8864,9185,8836,8837,8929,8930,8819,8817,8840,8843,8928,8848,8850,8857,8859,8860,8861,8853,8863,8862,7689,7698,8424,8427,8554,8434,7648,7649,7651,7671,7673,8422,8423,8421}'::integer[])) AND (accumulated_sales_1.sale_date @> ANY ('{2024-12-29,2025-01-05,2025-01-12,2025-01-19,2025-01-26,2025-02-02,2025-02-09,2025-02-16,2025-02-23,2025-03-02,2025-03-09,2025-03-16,2025-03-23,2025-03-30,2025-04-06,2025-04-13,2025-04-20,2025-04-27,2025-05-04,2025-05-11,2025-05-18,2025-05-25,2025-06-01,2025-06-08,2025-06-15,2025-06-22,2025-06-29,2025-07-06,2025-07-13,2025-07-20,2025-07-27,2025-08-03,2025-08-10,2025-08-17,2025-08-24,2025-08-31,2025-09-07,2025-09-14,2025-09-21,2025-09-28,2025-10-05,2025-10-12,2025-10-19,2025-10-26,2025-11-02,2025-11-09,2025-11-16,2025-11-23,2025-11-30,2025-12-07,2025-12-14,2025-12-21,2025-12-28}'::date[])))"
" Rows Removed by Filter: 5266315"
" Buffers: shared hit=33 read=137379"
" I/O Timings: shared read=445.129"
" Worker 0: actual time=1.298..5240.878 rows=215401 loops=1"
" Buffers: shared hit=2 read=46047"
" I/O Timings: shared read=149.403"
" Worker 1: actual time=0.180..5263.549 rows=219841 loops=1"
" Buffers: shared hit=30 read=46408"
" I/O Timings: shared read=150.375"
" -> Parallel Seq Scan on public.accumulated_sales_new_default accumulated_sales_2 (cost=0.58..9632.33 rows=1961 width=34) (actual time=2.725..26.222 rows=12 loops=2)"
" Output: accumulated_sales_2.id, accumulated_sales_2.sale_date, accumulated_sales_2.sales_amount, accumulated_sales_2.sales_amount_usd, accumulated_sales_2.units_sold"
" Filter: ((NOT (accumulated_sales_2.sale_date @> '[2024-12-29,2400-01-01)'::daterange)) AND (accumulated_sales_2.category_id = ANY ('{7628,7632,7637,7640,7645,7646,7665,7667,7670,7674,8426,8430,8433,8446,8481,8425,8431,8440,8442,8437,11803,7672,8436,8439,8429,8432,8438,8443,8444,8445,8448,8798,8795,8796,8797,8800,8447,8802,8799,8801,8441,8428,8435,7676,7692,6837,7700,7702,7705,7707,7708,7710,6865,7762,7823,8557,8544,8545,7824,8560,8586,8590,8939,8943,9186,8592,8561,8562,8563,11663,11664,8502,8503,8504,8505,7763,8508,8511,8513,8506,8509,8515,8523,8525,7782,7800,8514,8517,8520,8542,8546,8838,8839,8842,8845,8548,8558,8559,8844,8846,8849,8851,8854,8867,9025,9026,9029,8868,8865,8866,8547,8847,8852,8855,8858,8526,8529,8540,8541,8543,6867,7793,7794,7795,8527,8532,8537,8539,8530,8518,8522,8528,8531,8533,8538,8534,6870,7812,7813,7815,6872,7796,7797,7799,7801,7703,7712,7720,8449,8452,8453,8454,7724,8490,8494,8496,8497,8455,7775,8460,8462,8465,8458,8463,8482,8495,8813,8814,8818,8821,8834,8484,8491,8493,8804,8807,8810,8456,8812,8806,8464,8805,8808,8803,8483,8485,8492,8809,8811,10514,8650,8498,8816,9010,8815,8841,8499,8820,8823,8835,8864,9185,8836,8837,8929,8930,8819,8817,8840,8843,8928,8848,8850,8857,8859,8860,8861,8853,8863,8862,7689,7698,8424,8427,8554,8434,7648,7649,7651,7671,7673,8422,8423,8421}'::integer[])) AND (accumulated_sales_2.sale_date @> ANY ('{2024-12-29,2025-01-05,2025-01-12,2025-01-19,2025-01-26,2025-02-02,2025-02-09,2025-02-16,2025-02-23,2025-03-02,2025-03-09,2025-03-16,2025-03-23,2025-03-30,2025-04-06,2025-04-13,2025-04-20,2025-04-27,2025-05-04,2025-05-11,2025-05-18,2025-05-25,2025-06-01,2025-06-08,2025-06-15,2025-06-22,2025-06-29,2025-07-06,2025-07-13,2025-07-20,2025-07-27,2025-08-03,2025-08-10,2025-08-17,2025-08-24,2025-08-31,2025-09-07,2025-09-14,2025-09-21,2025-09-28,2025-10-05,2025-10-12,2025-10-19,2025-10-26,2025-11-02,2025-11-09,2025-11-16,2025-11-23,2025-11-30,2025-12-07,2025-12-14,2025-12-21,2025-12-28}'::date[])))"
" Rows Removed by Filter: 83436"
" Buffers: shared hit=30 read=1394"
" I/O Timings: shared read=20.324"
" Worker 0: actual time=3.062..23.904 rows=11 loops=1"
" Buffers: shared hit=30 read=635"
" I/O Timings: shared read=8.921"
"Settings: effective_cache_size = '16123168kB', jit = 'off', work_mem = '128MB', enable_bitmapscan = 'off', random_page_cost = '1'"
"Query Identifier: -7833921758881389012"
"Planning Time: 1.675 ms"
"Execution Time: 6282.181 ms"
