Xjyuk

I have a table with over 50M records. One of the fields is COLOR_CODE. I've set an index on the column COLOR_CODE like this:

"mytable_colorcode_idx" btree (color_code)

I'm noticing that when I run the query below, the execution time is higher

SELECT count(total_amount) FROM mytable 
WHERE color_code in ('red','green') and sale_date = '1970'

However, the execution time is faster with OR clause:

SELECT count(total_amount) FROM mytable 
WHERE color_code = 'red' or color_code = 'green' and sale_date = '1970'

Query plan for IN

explain analyze SELECT count(total_amount) FROM mytable 
WHERE color_code in ('red','green') and sale_date = '1970'
 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=2074238.07..2074238.08 rows=1 width=8) (actual time=63520.150..63520.150 rows=1 loops=1)
 -> Bitmap Heap Scan on mytable (cost=53504.73..2069923.27 rows=1725919 width=6) (actual time=3509.920..63080.519 rows=1727037 loops=1)
 Recheck Cond: ((color_code)::text = ANY ('red,green'::text[]))
 Rows Removed by Index Recheck: 5067635
 Filter: (sale_date = 1970)
 Heap Blocks: exact=38679 lossy=496680
 -> Bitmap Index Scan on mytable_colorcode_idx (cost=0.00..53073.26 rows=1725919 width=0) (actual time=3501.777..3501.777 rows=1727037 loops=1)
 Index Cond: ((color_code)::text = ANY ('red,green'::text[]))
 Planning time: 0.165 ms
 Execution time: 63524.100 ms
(10 rows)

Query plan for OR

explain analyze SELECT count(total_amount) FROM mytable 
 WHERE color_code = 'red' or color_code = 'green' and sale_date = '1970'

 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=2081265.36..2081265.37 rows=1 width=8) (actual time=18895.998..18895.998 rows=1 loops=1)
 -> Bitmap Heap Scan on mytable (cost=56223.06..2076956.39 rows=1723588 width=6) (actual time=161.335..18468.146 rows=1727037 loops=1)
 Recheck Cond: (((color_code)::text = 'red'::text) OR ((color_code)::text = 'green'::text))
 Rows Removed by Index Recheck: 5067635
 Filter: (((color_code)::text = 'red'::text) OR (((color_code)::text = 'green'::text) AND (sale_date = 1970)))
 Heap Blocks: exact=38679 lossy=496680
 -> BitmapOr (cost=56223.06..56223.06 rows=1725919 width=0) (actual time=153.683..153.684 rows=0 loops=1)
 -> Bitmap Index Scan on mytable_colorcode_idx (cost=0.00..663.35 rows=20655 width=0) (actual time=3.935..3.935 rows=26768 loops=1)
 Index Cond: ((color_code)::text = 'red'::text)
 -> Bitmap Index Scan on mytable_colorcode_idx (cost=0.00..54697.91 rows=1705264 width=0) (actual time=149.745..149.746 rows=1700269 loops=1)
 Index Cond: ((color_code)::text = 'green'::text)
 Planning time: 0.162 ms
 Execution time: 18896.785 ms
(13 rows)

Update

If I add an index (color_code, total_count, and sale_date) I notice that no index is used at all. Rather it does a partial scan.

"mytable_color_total_count_sale_Date_idx" btree (color_code, total_count, sale_date) 



 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate (cost=2099755.26..2099755.27 rows=1 width=8) (actual time=97066.585..97066.586 rows=1 loops=1)
 -> Gather (cost=2099755.04..2099755.25 rows=2 width=8) (actual time=97063.512..97069.838 rows=3 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 -> Partial Aggregate (cost=2098755.04..2098755.05 rows=1 width=8) (actual time=97061.531..97061.532 rows=1 loops=3)
 -> Parallel Seq Scan on mytable (cost=0.00..2096119.69 rows=1054140 width=6) (actual time=27782.491..96730.232 rows=841604 loops=3)
 Filter: ((sale_date = 1970) AND ((color_code)::text = ANY ('red,green'::text[])))
 Rows Removed by Filter: 4196103
 Planning time: 0.161 ms
 Execution time: 97069.896 ms
(10 rows)

Question

Is there a way I can optimize by IN clause query other than turning it into an OR clause?

You can't compare performance of:

WHERE color_code in ('red','green') and sale_date = '1970'

with:

WHERE color_code = 'red' or color_code = 'green' and sale_date = '1970'

because they are not logically equivalent (will return different results). A trivial example:

 with T (color_code, sale_date) as ( 
 values ('red', '1970'), ('green','1969')
 ) 
 select * from T 
 where color_code in ('green', 'red') 
 and sale_date = '1970';

 color_code | sale_date 
------------+-----------
 red | 1970
(1 row)

However:

with T (color_code, sale_date) as ( 
 values ('red', '1970'), ('green','1969')
) 
select * from T 
where color_code = 'green' or color_code = 'red' 
 and sale_date = '1970';

color_code | sale_date 
------------+-----------
 red | 1970
 green | 1969
(2 rows)

For the comparison to be meaningful, you need to change your query to:

select * from T 
where (color_code = 'green' or color_code = 'red') 
 and sale_date = '1970';

That said, I would suggest an index like:

CREATE INDEX ... ON ... (sale_date, color_code)

I think the difference in timing you see is simply a caching effect, based on which query you ran first. It is probably not a real difference caused by how you specify the query (Although as Lennart described, your queries are not really equivalent as you are missing parentheses around the OR part--although all your rows seem to meet sale_date = '1970' anyway, so this difference is important in general, but doesn't make a difference in the exact example)

There some things you can do that might make both specifications of this query faster.

For one, look at this line:

 Heap Blocks: exact=38679 lossy=496680

This means that your work_mem is not large enough to hold the entire bitmap. All of those lossy blocks need to have every row in them rechecked, which takes time. Increasing work_mem would prevent this and should speed up the query. Ideally the lossy blocks would drop to zero (at which point the 'lossy' label wouldn't be displayed anymore.

Second, having an index on mytable (color_code, sale_date, total_count) could allow for index-only scans, because all needed data would be in the index and it wouldn't have to visit the table at all (assuming the table is kept well-vacuumed).

These are mutually exclusive: if you do the index-only scan rather than the bitmap scan, then work_mem no longer matters.