Postgres create Index im running out with ideas for creating postgres index for a better performance.
This example below is a selection of a big table with many entries for a diary to person relation.
usually, i create an index just of person_id with btree but this has a less impact of the performance. How i should create the right index for this example?
select * from diary2person where now() between valid_from and valid_until and person_id = ?
thanks in advance!
regards Lars
The way to go is using a GIST index. This sort of index helps checking if a value is contained within a range.
Because you want to filter also by person_id, you will need to install the btree_gist extension. In addition, you should convert the valid_from and valid_until columns to a single tstzrange column, which is a range column that holds timestamp with time zone range limits.
After doing that, you can create an index on person_id and the new range column which you can call valid_range:
CREATE INDEX ON diary2person USING GIST (person_id, valid_range);

Good luck!