What index to choose for a range of dates?

Question

PostgreSQL

Have a request

SELECT *
FROM table
WHERE id > 100
AND date >= ДАТА
AND date <  ДАТА
ORDER BY id
LIMIT 1000

What are the correct indexes to make it work quickly?

Answer 1

It is quite possible to get by with partitioning tables by date, then the standard btree should work quite quickly.
https://postgrespro.ru/docs/postgresql/14/ddl-part...