1

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.

  • What are the possible values of app? – Evan Carroll Jan 20 '18 at 20:49
  • you also cut off the times in your explain analyze – Evan Carroll Jan 20 '18 at 21:08
2

ST_DWithin

ST_DistanceSphere(x,y)<t cannot use your spatial index. ST_DWithin can, depending on the selectivity. Instead use,

ST_DWithin(u.geo_point, l.geo_point, 1000);

Also, for testing purposes, you should remove the join to user_push_tokens and add it back once you get a better plan for the simpler query.

  • yes it'll use an index. – Evan Carroll Jan 20 '18 at 20:59
  • 1
    Hey! Thanks for replying. I finally found time to loop back around to this. I think you mean to use ST_DWithin in your second code block there. This small change made the query four times faster. Thanks! – Mattis Jan 24 '18 at 15:48
1

Schema changes

In addition to the mention by jjanes,

  • varchar(x) : Don't use varhcar(x) in PostgreSQL, unless you must or otherwise have good reason to. Reviewing your schema, all of these should be text. It's faster.

  • NOT NULL with default strings: What are the possible values here,

    app varchar(64) default 'app'::character varying not null,
    

    Your query has app = 'app'::text on the above, this is also not good

    active AND (app)::text = 'app'::text
    

    You probably want nullable and then just test if the value IS NULL

  • geography type: also you probably don't want

    geometry(Point,4326)
    

    instead, use

    geography(Point,4326)
    
  • Hey thanks Evan! Using geography instead of geometry (duh, should have been obvious) cut the query time down even more (~150-200 ms to ~100-120ms). I will look at your other points as soon as I can. Thanks for answering! – Mattis Jan 24 '18 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.