I need help optimizing time-based queries on a relatively small table.
The table looks like this:
time int | tag varchar(21) | val decimal
I also have a index like such
create index _index on table (time, tag)
And queries looks like this:
select * from table where time >= 1554599241 and time <= 1554599251 and tag = ANY('{aaa,bbb,ccc}')
Is there any way for me to reduce query times?
Example
select * from table where time >= 1554020803 and time <= 1554625603 and tag = any('{ryeb1UQ2Gm,HkGM18QnGX,rkxE1872fQ,rybS18XnGm,B1f8JUmnzX,BkuJ873M7,HyO6Z9OTMQ,BkmCbqOaG7,HJpCWcdpfm,rkGgAb9upGm,ry7JM9Oaz7,BkOkf5OTzm,B1ejy8Xhz7,Syp1G9uTzm,HkMlJMcupzQ,rycJLm3zQ,SyxFJLmnGm,Sy_RZ9u6Gm,BkfJy8QnMQ,Sy76-qupGm}')
produces this explain:
Bitmap Heap Scan on table (cost=20795.15..520342.18 rows=81781 width=69) (actual time=72.530..674.270 rows=224012 loops=1)
Recheck Cond: (("time" >= 1554020803) AND ("time" <= 1554625603))
Filter: ((tag)::text = ANY ('{ryeb1UQ2Gm,HkGM18QnGX,rkxE1872fQ,rybS18XnGm,B1f8JUmnzX,BkuJ873M7,HyO6Z9OTMQ,BkmCbqOaG7,HJpCWcdpfm,rkGgAb9upGm,ry7JM9Oaz7,BkOkf5OTzm,B1ejy8Xhz7,Syp1G9uTzm,HkMlJMcupzQ,rycJLm3zQ,SyxFJLmnGm,Sy_RZ9u6Gm,BkfJy8QnMQ,Sy76-qupGm}'::text[]))
Rows Removed by Filter: 896063
Heap Blocks: exact=7229
-> Bitmap Index Scan on _test_index (cost=0.00..20774.71 rows=817814 width=0) (actual time=71.430..71.430 rows=1120075 loops=1)
Index Cond: (("time" >= 1554020803) AND ("time" <= 1554625603))
Planning time: 0.137 ms
Execution time: 682.200 ms
Best Answer
Try an index on
(tag, time)
. With B-tree indexes, you generally want the equality-constrained columns to be in the index before the inequality-constrained columns.