Обычно делают counter_id in (6001, 15998) а не толпу or.
explain analyze select max(time) from params where counter_id in (6001, 15998);
Aggregate (cost=18.52..18.53 rows=1 width=8) (actual time=1.586..1.586 rows=1 loops=1)
-> Index Only Scan using params_counter_id_time_idx on params (cost=0.56..18.51 rows=4 width=8) (actual time=0.769..1.566 rows=12 loops=1)
Index Cond: (counter_id = ANY ('{6001,15998}'::bigint[]))
Heap Fetches: 2
Planning time: 50.191 ms
Execution time: 1.933 ms
Тут на порядок поменьше данных, это просто виртуалка и pg ещё 9.4
or две минуты исполнения тоже не даёт:
Aggregate (cost=25.20..25.21 rows=1 width=8) (actual time=38.797..38.797 rows=1 loops=1)
-> Bitmap Heap Scan on params (cost=9.16..25.19 rows=4 width=8) (actual time=0.362..38.749 rows=12 loops=1)
Recheck Cond: ((counter_id = 6001) OR (counter_id = 15998))
Heap Blocks: exact=12
-> BitmapOr (cost=9.16..9.16 rows=4 width=0) (actual time=0.042..0.042 rows=0 loops=1)
-> Bitmap Index Scan on params_counter_id_time_idx (cost=0.00..4.58 rows=2 width=0) (actual time=0.031..0.031 rows=6 loops=1)
Index Cond: (counter_id = 6001)
-> Bitmap Index Scan on params_counter_id_time_idx (cost=0.00..4.58 rows=2 width=0) (actual time=0.009..0.009 rows=6 loops=1)
Index Cond: (counter_id = 15998)
Planning time: 0.247 ms
Execution time: 39.408 ms
Попробуйте сделать analyze params;
Может, у планировщика статистика старая.
