-- FIN STUDIO Scale Lab 2026 | products=5000000 | Q5: Facet counts: top 20 brands for the Q4 filter set (brand filter excluded)
-- params: ["/10/294/%",14649,449398,"{\"color\":\"plava\"}"]

SELECT p.brand_id, b.name, count(*)::int AS n
FROM products p
JOIN brands b ON b.id = p.brand_id
WHERE p.category_id IN (SELECT id FROM categories WHERE path LIKE $1)
  AND p.is_active AND p.stock > 0
  AND p.price_cents BETWEEN $2 AND $3
  AND p.attrs @> $4::jsonb
GROUP BY p.brand_id, b.name
ORDER BY n DESC, p.brand_id
LIMIT 20

Limit  (cost=3159.14..3159.19 rows=20 width=14) (actual time=85.112..85.115 rows=20 loops=1)
  Buffers: shared hit=1844 read=3
  ->  Sort  (cost=3159.14..3159.32 rows=72 width=14) (actual time=85.110..85.112 rows=20 loops=1)
        Sort Key: ((count(*))::integer) DESC, p.brand_id
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=1844 read=3
        ->  GroupAggregate  (cost=3155.60..3157.22 rows=72 width=14) (actual time=84.995..85.042 rows=242 loops=1)
              Group Key: p.brand_id, b.name
              Buffers: shared hit=1844 read=3
              ->  Sort  (cost=3155.60..3155.78 rows=72 width=10) (actual time=84.986..84.997 rows=374 loops=1)
                    Sort Key: p.brand_id, b.name
                    Sort Method: quicksort  Memory: 39kB
                    Buffers: shared hit=1844 read=3
                    ->  Nested Loop  (cost=2707.89..3153.38 rows=72 width=10) (actual time=26.916..84.940 rows=374 loops=1)
                          Buffers: shared hit=1844 read=3
                          ->  Nested Loop  (cost=2707.60..3131.68 rows=72 width=4) (actual time=26.901..84.553 rows=374 loops=1)
                                Buffers: shared hit=722 read=3
                                ->  Index Scan using categories_path_idx on categories  (cost=0.28..8.30 rows=1 width=4) (actual time=0.009..0.020 rows=4 loops=1)
                                      Index Cond: ((path ~>=~ '/10/294/'::text) AND (path ~<~ '/10/2940'::text))
                                      Filter: (path ~~ '/10/294/%'::text)
                                      Buffers: shared hit=6
                                ->  Bitmap Heap Scan on products p  (cost=2707.33..3122.49 rows=89 width=8) (actual time=20.932..21.124 rows=94 loops=4)
                                      Recheck Cond: ((category_id = categories.id) AND (price_cents >= 14649) AND (price_cents <= 449398) AND is_active AND (attrs @> '{"color": "plava"}'::jsonb))
                                      Filter: (stock > 0)
                                      Rows Removed by Filter: 18
                                      Heap Blocks: exact=445
                                      Buffers: shared hit=716 read=3
                                      ->  BitmapAnd  (cost=2707.33..2707.33 rows=105 width=0) (actual time=20.914..20.914 rows=0 loops=4)
                                            Buffers: shared hit=274
                                            ->  Bitmap Index Scan on products_cat_price_idx  (cost=0.00..40.77 rows=1307 width=0) (actual time=0.077..0.077 rows=1231 loops=4)
                                                  Index Cond: ((category_id = categories.id) AND (price_cents >= 14649) AND (price_cents <= 449398))
                                                  Buffers: shared hit=31
                                            ->  Bitmap Index Scan on products_attrs_idx  (cost=0.00..2630.51 rows=403121 width=0) (actual time=27.667..27.667 rows=419647 loops=3)
                                                  Index Cond: (attrs @> '{"color": "plava"}'::jsonb)
                                                  Buffers: shared hit=243
                          ->  Index Scan using brands_pkey on brands b  (cost=0.28..0.30 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=374)
                                Index Cond: (id = p.brand_id)
                                Buffers: shared hit=1122
Planning:
  Buffers: shared hit=18
Planning Time: 0.317 ms
Execution Time: 85.151 ms