Combining traditional and spatial indices in Postgres

Question

I have timestamped location data.

I want Postgres to efficiently execute queries that are bounded in time and space. e.g.

select * 
from tracking_tags
where ts >= '1990-01-01T00:00:00.000Z'
and ts < '2000-01-01T00:00:00.000Z'
and lat > 40.0
and lat < 50.0
and long < 0.0
and long > -10.0

How should I approach this from an indexing point of view?

I am confused because I think I might need to choose between a normal b-tree index on ts and a GIST index on lat/long POINTs, but I need a composite index (or possibly two).

Assume a decade of data, with a thousand records per day.

(P.S. Apologies for nonsense SQL, I haven't yet switched from MySQL to Postgres - but this is a Postgres question.)

This is not a postgis query. You are just doing comparisons of decimals and there aren't any geometry or geography fields here. — e4c5
– e4c5, Commented May 9, 2016 at 23:09

Ezequiel Tolnay · Accepted Answer · 2016-05-10 02:48:43Z

2

Indexes for this particular table schema could vary greatly depending on what what information you need to fetch.

For example, the query below would likely use the index effectively

CREATE INDEX ON tracking_tags USING gist (point(lat,long), ts);

SELECT * 
FROM tracking_tags
WHERE point(lat,long) <@ box(point(40,-10),point(50,0)) AND
      ts <@ tstzrange'[1990-01-01,2000-01-01)' AND
      lat NOT IN (40, 50) AND long NOT IN (-10, 0);

answered May 10, 2016 at 2:48

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fadedbee Over a year ago

What do the "lat/long NOT IN..." do and why are they needed in addition to the "<@ box"?

Ezequiel Tolnay Over a year ago

@chrisdew the operator point <@ box is optimised in gist indexes, but it's inclusive. Your sample query requires exclusive, so this uses a fast indexed inclusive approach first, and then filters out any points that are actually touching the box's frame.

Jakub Kania · Accepted Answer · 2016-05-09 21:29:39Z

2

The btree_gist extension allows you to make a gist index on timestamps which makes it possible to combine them with PostGIS indexes. PostgreSQL also can use multiple indexes in one query. You'll have to test and see which combination performs the best.

answered May 9, 2016 at 21:29

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

Combining traditional and spatial indices in Postgres

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related