How to create a PostgreSQL index that includes Latitude/Longitude (using GIST), but also regular fields
earthdistance, indexing, postgresql
Solution
You will probably need the btree_gist extension to create the index to include the tenant_id field, which appears to have existed since at least Postgres 8,.4.
CREATE EXTENSION btree_gist;
CREATE INDEX job_locations_lat_lng ON job_locations USING gist(tenant_id, ll_to_earth(latitude, longitude));
Problem
I have a table of locations that users of my app need to be able to search in. Baically "locations within x miles of me" I'm using a query like this: ``` select * from job_locations where earth_box(ll_to_earth(51.5, -0.085), 5000) @> ll_to_earth(latitude, longitude) and earth_distance(ll_to_earth(51.5, -0.085), ll_to_earth(latitude, longitude)) < 5000; ``` And an index like this: ``` CREATE INDEX job_locations_lat_lng on job_locations USING gist(ll_to_earth(latitude, longitude)); ``` However, this is a multi-tenant system, where all tables have a "tenant_id" field, and we always filter by that too. So ideally, I'd be able to have both tenant_id and ll_to_earth(lat, lng) in the index, which should presumably make searching much faster. Is this possible? How do I create that index? Thanks! Daniel