Indexes for combined time and location data

by dtech   Last Updated July 17, 2019 09:06 AM - source

I have a multi-TB table with geolocation data for a certain item id and timestamp.

It is partitioned by time with a partition for every month (~500GB), and clustered (including the partitions) on the item id and time index.

  itemid integer not null,
  ts timestamp not null,
  pos geometry(Point,4326) not null,
  otherdata varchar not null -- Stand in for other columns
) partition by range(ts);

CREATE INDEX ON data (itemid, ts);
CREATE INDEX ON data USING brin (itemid, ts, pos) with (pages_per_range = 8);

    FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');

    FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');

This performs very well for our most common queries, where the data of one or a small number of items is queried for a range of time (several hours to days).

A secondary type of query we would like to do is all items in or near a location in a certain time range. Our intention is that the brin index can be used for this since (item_id,ts) is highly correlated with pos.

Something like this:

select itemid, ts, pos, otherdata from data
where ts >= '2019-02-02 00:00:00' AND ts < '2019-02-03 14:00:00'
AND st_within(pos,  st_setsrid(
                st_geomfromgeojson('{"coordinates":"coordinates":[[[-74.05059814453125,40.6920928987952],[-73.92013549804688,40.6920928987952],[-73.92013549804688,40.79405848578324],[-74.05059814453125,40.79405848578324],[-74.05059814453125,40.6920928987952]]],"type": "Polygon"}'),
              ) :: geometry)

We see however that the normal btree index is used for this, and the queries take too long to be useful (multiple minutes to over an hour)

What is a way to enable these queries?

  • Is there a combined time and geospatial index?
  • Is there something that can be done to make the brin index being used?
  • Is the best way partitioning on a smaller timeframe like a day and creating a geo index for each partition?

Related Questions

Make PostgreSQL perform SELECT queries in memory

Updated September 03, 2017 22:06 PM

Postgres gist slow index f_unaccent

Updated April 09, 2018 20:06 PM

Optimizing ORDER BY in a full text search query

Updated October 26, 2018 14:06 PM