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?


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
  • Reduce query times from what? You'll need to give the current query times and plans and table update rates and patterns if you want spe ific performance advice. – Richard Huxton Apr 7 '19 at 7:17
  • The query runs in less than a second (nearly half a second). How fast do you need that to be? – a_horse_with_no_name Apr 7 '19 at 8:46

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.