Postgresql – How to optimize time based data

postgresql

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.