add batch remove batch split batch show hidden batches hide batch
db<>fiddle
feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
1000 rows affected
961 rows affected
17897 rows affected
QUERY PLAN
GroupAggregate (cost=4542.85..4856.63 rows=961 width=12) (actual time=459.224..660.219 rows=854 loops=1)
  Group Key: sales_periods.product_id
  -> Sort (cost=4542.85..4644.24 rows=40556 width=8) (actual time=459.014..514.489 rows=243454 loops=1)
        Sort Key: sales_periods.product_id
        Sort Method: external merge Disk: 4320kB
        -> Nested Loop (cost=0.30..1438.77 rows=40556 width=8) (actual time=0.097..313.976 rows=243454 loops=1)
              -> Seq Scan on sales_periods (cost=0.00..17.00 rows=1000 width=12) (actual time=0.010..0.355 rows=1000 loops=1)
              -> Memoize (cost=0.30..1.53 rows=41 width=4) (actual time=0.014..0.257 rows=243 loops=1000)
                    Cache Key: sales_periods.since, sales_periods.till
                    Hits: 0 Misses: 1000 Evictions: 521 Overflows: 0 Memory Usage: 4097kB
                    -> Index Only Scan using calendar_pkey on calendar (cost=0.29..1.52 rows=41 width=4) (actual time=0.013..0.096 rows=243 loops=1000)
                          Index Cond: ((day >= sales_periods.since) AND (day <= sales_periods.till) AND (day >= '2021-04-14'::date) AND (day <= '2022-04-13'::date))
                          Heap Fetches: 0
Planning Time: 0.404 ms
Execution Time: 661.876 ms
QUERY PLAN
GroupAggregate (cost=9112.35..9414.27 rows=961 width=12) (actual time=460.265..617.176 rows=854 loops=1)
  Group Key: products.product_id
  -> Sort (cost=9112.35..9209.79 rows=38974 width=8) (actual time=460.106..523.763 rows=236802 loops=1)
        Sort Key: products.product_id
        Sort Method: external merge Disk: 4200kB
        -> Hash Semi Join (cost=29.79..6140.54 rows=38974 width=8) (actual time=0.410..367.131 rows=236802 loops=1)
              Hash Cond: (products.product_id = sales_periods.product_id)
              Join Filter: ((calendar.day >= sales_periods.since) AND (calendar.day <= sales_periods.till))
              Rows Removed by Join Filter: 118063
              -> Nested Loop (cost=0.29..4415.67 rows=350765 width=8) (actual time=0.052..163.928 rows=350765 loops=1)
                    -> Seq Scan on products (cost=0.00..14.61 rows=961 width=4) (actual time=0.014..11.283 rows=961 loops=1)
                    -> Materialize (cost=0.29..17.41 rows=365 width=4) (actual time=0.000..0.061 rows=365 loops=961)
                          -> Index Only Scan using calendar_pkey on calendar (cost=0.29..15.59 rows=365 width=4) (actual time=0.030..0.084 rows=365 loops=1)
                                Index Cond: ((day >= '2021-04-14'::date) AND (day <= '2022-04-13'::date))
                                Heap Fetches: 0
              -> Hash (cost=17.00..17.00 rows=1000 width=12) (actual time=0.336..0.337 rows=1000 loops=1)
                    Buckets: 1024 Batches: 1 Memory Usage: 51kB
                    -> Seq Scan on sales_periods (cost=0.00..17.00 rows=1000 width=12) (actual time=0.012..0.169 rows=1000 loops=1)
Planning Time: 0.600 ms
Execution Time: 618.032 ms
QUERY PLAN
HashAggregate (cost=6335.41..6345.02 rows=961 width=12) (actual time=453.081..453.245 rows=854 loops=1)
  Group Key: p.product_id
  Batches: 1 Memory Usage: 193kB
  -> Hash Semi Join (cost=29.79..6140.54 rows=38974 width=4) (actual time=0.391..341.287 rows=236802 loops=1)
        Hash Cond: (p.product_id = s.product_id)
        Join Filter: ((calendar.day >= s.since) AND (calendar.day <= s.till))
        Rows Removed by Join Filter: 118063
        -> Nested Loop (cost=0.29..4415.67 rows=350765 width=8) (actual time=0.045..172.665 rows=350765 loops=1)
              -> Seq Scan on products p (cost=0.00..14.61 rows=961 width=4) (actual time=0.012..0.273 rows=961 loops=1)
              -> Materialize (cost=0.29..17.41 rows=365 width=4) (actual time=0.000..0.038 rows=365 loops=961)
                    -> Index Only Scan using calendar_pkey on calendar (cost=0.29..15.59 rows=365 width=4) (actual time=0.027..0.081 rows=365 loops=1)
                          Index Cond: ((day >= '2021-04-14'::date) AND (day <= '2022-04-13'::date))
                          Heap Fetches: 0
        -> Hash (cost=17.00..17.00 rows=1000 width=12) (actual time=0.339..0.339 rows=1000 loops=1)
              Buckets: 1024 Batches: 1 Memory Usage: 51kB
              -> Seq Scan on sales_periods s (cost=0.00..17.00 rows=1000 width=12) (actual time=0.009..0.180 rows=1000 loops=1)
Planning Time: 0.397 ms
Execution Time: 453.351 ms
QUERY PLAN
HashAggregate (cost=6267.39..6277.00 rows=961 width=12) (actual time=461.520..461.689 rows=854 loops=1)
  Group Key: p.product_id
  Batches: 1 Memory Usage: 193kB
  -> Hash Semi Join (cost=33.41..6086.92 rows=36094 width=4) (actual time=0.468..385.362 rows=236802 loops=1)
        Hash Cond: (p.product_id = sales_periods.product_id)
        Join Filter: ((calendar.day >= sales_periods.since) AND (calendar.day <= sales_periods.till))
        Rows Removed by Join Filter: 77913
        -> Nested Loop (cost=0.29..4415.67 rows=350765 width=8) (actual time=0.046..177.505 rows=350765 loops=1)
              -> Seq Scan on products p (cost=0.00..14.61 rows=961 width=4) (actual time=0.013..0.278 rows=961 loops=1)
              -> Materialize (cost=0.29..17.41 rows=365 width=4) (actual time=0.000..0.069 rows=365 loops=961)
                    -> Index Only Scan using calendar_pkey on calendar (cost=0.29..15.59 rows=365 width=4) (actual time=0.027..0.079 rows=365 loops=1)
                          Index Cond: ((day >= '2021-04-14'::date) AND (day <= '2022-04-13'::date))
                          Heap Fetches: 0
        -> Hash (cost=22.00..22.00 rows=890 width=12) (actual time=0.414..0.415 rows=888 loops=1)
              Buckets: 1024 Batches: 1 Memory Usage: 47kB
              -> Seq Scan on sales_periods (cost=0.00..22.00 rows=890 width=12) (actual time=0.011..0.238 rows=888 loops=1)
                    Filter: ((since <= '2022-04-13'::date) AND (till >= '2021-04-14'::date))
                    Rows Removed by Filter: 112
Planning Time: 0.425 ms
Execution Time: 461.797 ms
QUERY PLAN
Subquery Scan on sub (cost=65.60..322.96 rows=864 width=8) (actual time=0.476..3.005 rows=854 loops=1)
  -> GroupAggregate (cost=65.60..89.68 rows=864 width=44) (actual time=0.474..2.268 rows=854 loops=1)
        Group Key: sales_periods.product_id
        -> Sort (cost=65.60..67.82 rows=890 width=12) (actual time=0.440..0.531 rows=888 loops=1)
              Sort Key: sales_periods.product_id
              Sort Method: quicksort Memory: 66kB
              -> Seq Scan on sales_periods (cost=0.00..22.00 rows=890 width=12) (actual time=0.014..0.215 rows=888 loops=1)
                    Filter: ((since <= '2022-04-13'::date) AND (till >= '2021-04-14'::date))
                    Rows Removed by Filter: 112
Planning Time: 0.357 ms
Execution Time: 3.093 ms