Postgresql join index for spatial and temporal parameters

Question

We have a table in which there are millions of rows with PostGIS geometries. The query we want to fulfill is: what are the most recent records that fall into the bounding geometry? The problem with this query is that we often will have a large number of elements that match the bounding box (which has a radius of 5 km), and Postgres will then need to double-check all returned elements in the bounding box to get their timestamp, then sort and return the latest version.

It looks like we need an index (composite?) That takes into account both the spatial GIST index and the timestamp. Is it possible? I tried several combinations in the CREATE INDEX step, and so far nothing has worked.

Answer 1

I would prefer to make two indexes, one spatial and one in the timestamp column. PostgreSQL can combine indexes pretty nicely, and there is no need to "check" the rows found. It can use indexes to get rows in geometry and sort them using another index.