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.

CREATE TABLE data(
  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);

CREATE TABLE data_201901 PARTITION OF data
    FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');

CREATE TABLE data_201902 PARTITION OF data
    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"}'),
                4326
              ) :: 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