EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
WITH temp_stock_history AS (
    SELECT MIN(date) AS date, product_id
    FROM testing.stock_history SH_
    WHERE SH_.date >= '2024-01-18 12:00+0100'
    GROUP BY SH_.product_id
)
SELECT COUNT(id)
FROM testing.stock_history SH
WHERE (date, product_id) IN (
    SELECT date, product_id
    FROM temp_stock_history
);

                                                                                                      
QUERY PLAN                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=624582.46..624582.47 rows=1 width=8) (actual time=55718.704..55718.867 rows=1 loops=1)
   Output: count(sh.id)
   Buffers: shared hit=4004 read=313651, temp read=138025 written=140224
   ->  Gather  (cost=624582.24..624582.45 rows=2 width=8) (actual time=55718.678..55718.847 rows=3 loops=1)
         ->  Partial Aggregate  (cost=623582.24..623582.25 rows=1 width=8) (actual time=55473.355..55473.366 rows=1 loops=3)
               ->  Hash Join  (cost=38467.42..623580.84 rows=562 width=8) (actual time=40909.238..55464.326 rows=59341 loops=3)
                     ->  Parallel Seq Scan on testing.stock_history sh  (cost=0.00..491830.58 rows=17768158 width=24) (actual time=2.414..25400.057 rows=14215230 loops=3)
                     ->  Hash  (cost=36921.66..36921.66 rows=103051 width=16) (actual time=2972.388..2972.392 rows=166060 loops=3)
                           ->  HashAggregate  (cost=32040.93..35891.15 rows=103051 width=16) (actual time=2149.388..2720.948 rows=166060 loops=3)
                                 ->  Index Only Scan using stock_history_date_product_id_idx on testing.stock_history sh_  (cost=0.56..13543.65 rows=288738 width=16) (actual time=3.315..383.314 rows=315682 loops=3)
 Execution Time: 55835.100 ms
(76 rows)


                                                                                           QUERY PLAN                                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=151878.19..151878.20 rows=1 width=8) (actual time=2257.709..2257.713 rows=1 loops=1)
   Output: count(sh.id)
   Buffers: shared hit=831576, temp read=770 written=2385
   CTE temp_stock_history
     ->  HashAggregate  (cost=32040.93..35891.15 rows=103051 width=16) (actual time=379.824..534.225 rows=166060 loops=1)
           Group Key: sh_.product_id
           ->  Index Only Scan using stock_history_date_product_id_idx on testing.stock_history sh_  (cost=0.56..13543.65 rows=288738 width=16) (actual time=0.093..110.797 rows=315682 loops=1)
                 Index Cond: (sh_.date >= '2024-01-18 11:00:00+00'::timestamp with time zone)
   ->  Nested Loop  (cost=2576.84..89334.80 rows=10660895 width=8) (actual time=765.940..2224.865 rows=178022 loops=1)
         Buffers: shared hit=831576, temp read=770 written=2385
         ->  HashAggregate  (cost=2576.28..2679.33 rows=10305 width=16) (actual time=765.793..893.761 rows=166060 loops=1)
               Group Key: temp_stock_history.date, temp_stock_history.product_id
               ->  CTE Scan on temp_stock_history  (cost=0.00..2061.02 rows=103051 width=16) (actual time=379.830..641.721 rows=166060 loops=1)
         ->  Index Scan using stock_history_date_product_id_idx on testing.stock_history sh  (cost=0.56..8.40 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=166060)
               Index Cond: ((sh.date = temp_stock_history.date) AND (sh.product_id = temp_stock_history.product_id))
 Execution Time: 2269.782 ms
(38 rows)