querying jsonb for arrays inside a hash - Mailing list pgsql-performance

explain analyze

select count(*) from products where product_attributes @> '{"upsell":["true"]}' and site_id = '1';

                                                                   QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=1382.92..1382.93 rows=1 width=0) (actual time=410.498..410.499 rows=1 loops=1)

   ->  Bitmap Heap Scan on products  (cost=46.94..1382.52 rows=157 width=0) (actual time=31.747..363.145 rows=45165 loops=1)

         Recheck Cond: (product_attributes @> '{"upsell": ["true"]}'::jsonb)

         Filter: (site_id = '1'::text)

         Rows Removed by Filter: 90330

         Heap Blocks: exact=12740

         ->  Bitmap Index Scan on products_attributes_idx  (cost=0.00..46.90 rows=386 width=0) (actual time=29.585..29.585 rows=135843 loops=1)

               Index Cond: (product_attributes @> '{"upsell": ["true"]}'::jsonb)

 Planning time: 0.851 ms

 Execution time: 410.825 ms

(10 rows)

Time: 413.172 ms

explain analyze

select count(*) from products where product_attributes @> '{"upsell": 1}' and site_id = '1';

                                                               QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=1382.92..1382.93 rows=1 width=0) (actual time=0.110..0.111 rows=1 loops=1)

   ->  Bitmap Heap Scan on products  (cost=46.94..1382.52 rows=157 width=0) (actual time=0.107..0.107 rows=0 loops=1)

         Recheck Cond: (product_attributes @> '{"upsell": 1}'::jsonb)

         Filter: (site_id = '1'::text)

         ->  Bitmap Index Scan on products_attributes_idx  (cost=0.00..46.90 rows=386 width=0) (actual time=0.105..0.105 rows=0 loops=1)

               Index Cond: (product_attributes @> '{"upsell": 1}'::jsonb)

 Planning time: 0.091 ms

 Execution time: 0.140 ms

(8 rows)

Time: 1.264 ms