I’m running PostgreSQL 9.6 with PostGIS 2.3.3
I’m trying to make this rather critical query go faster (looking for users within 1000 meters of a location), but I’m having troubles setting up the right indexes.
Could someone point me in the right direction?
users
are 200k rows, locations
are 1200 rows, user_push_tokens
are 155k rows
users table:
create table users ( id serial not null constraint users_pkey primary key, (an additional 20-ish columns), geo_point geometry(Point,4326) );` create unique index users_id on users (id); create index users_geo_point_idx on users using gist(geo_point);
user_push_tokens table:
create table user_push_tokens ( user_id integer not null constraint push_tokens_user_id_fkey references users on delete cascade, push_token varchar(255) not null, push_provider varchar(64) not null, app varchar(64) default 'app'::character varying not null, id integer default nextval('user_push_tokens_id_seq'::regclass) not null constraint user_push_tokens_pkey primary key, active boolean default true not null, ); create index trinity_unique_index on user_push_tokens (user_id, app, push_token); create index user_push_tokens_token_fetch_idx on user_push_tokens (user_id, app, active); create index user_id_index on user_push_tokens (user_id);
locations table:
create table locations ( id integer default nextval('locations_id_seq'::regclass) not null constraint locations_pkey primary key, (another 25 columns), geo_point geometry(Point,4326) ); create unique index locations_id_key on locations (id); create index locations_geo_point_idx on locations using gist(geo_point);
The query
EXPLAIN ANALYSE SELECT u.id AS user_id, upt.push_provider, upt.push_token FROM users u JOIN locations l ON l.id = 3896 JOIN user_push_tokens upt ON upt.user_id = u.id AND upt.active = true AND upt.app = 'app' WHERE ST_DistanceSphere(u.geo_point, l.geo_point) <= 1000;
results in
Nested Loop (cost=26087.06..63658.87 rows=30605 width=107) (actual time=353.304..887.371 rows=2498 loops=1) Join Filter: (_st_distance(geography(u.geo_point), geography(l.geo_point), '0'::double precision, false) <= '1000'::double precision) Rows Removed by Join Filter: 89539 -> Index Scan using locations_id_key on locations l (cost=0.28..8.29 rows=1 width=32) (actual time=0.009..0.014 rows=1 loops=1) Index Cond: (id = 3896) -> Hash Join (cost=26086.78..39090.07 rows=91815 width=139) (actual time=352.437..657.228 rows=92037 loops=1) Hash Cond: (upt.user_id = u.id) -> Seq Scan on user_push_tokens upt (cost=0.00..7162.82 rows=91815 width=107) (actual time=0.032..103.512 rows=92037 loops=1) Filter: (active AND ((app)::text = 'app'::text)) Rows Removed by Filter: 62437 -> Hash (cost=22114.46..22114.46 rows=195546 width=36) (actual time=352.199..352.199 rows=195589 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 3563kB -> Seq Scan on users u (cost=0.00..22114.46 rows=195546 width=36) (actual time=0.014..214.976 rows=195589 loops=1)
More readable explain output
Thank you for your time.