6

We have 7,512,694 records in our table,

Columns:

id, name, lat (numeric), lon (numeric), location (geometry(Point,4326))

Indexes we created: create index on table using gist(location);

and of course rest of the columns are also indexed.

Requirement: We need to get places that are in 100m distance of a given point. The queries I am using right now are taking more than 100s (yes seconds:()

These are the queries I have tried so far:

Query 1:

EXPLAIN ANALYZE
SELECT name FROM businesses
WHERE ST_Distance(location::geography, 'SRID=4326;POINT(-121.696176 38.15399)'::geography) < 100 ;

Result:

Seq Scan on businesses  (cost=0.00..2997214.91 rows=2504231 width=543) (actual time=5982.917..120110.083 rows=6 loops=1)
    Filter: (_st_distance((location)::geography, '0101000020E6100000A510C8258E6C5EC09DF4BEF1B5134340'::geography, '0'::double precision, true) < '100'::double precision)
    Rows Removed by Filter: 7512688
Planning time: 0.138 ms
Execution time: 120110.129 ms
(5 rows)

Query 2:

explain analyze SELECT name FROM businesses
WHERE ST_DWithin( ST_Transform(ST_GeomFromText('POINT(-121.696176 38.15399)',4326),26986), ST_Transform(location,26986), 100);

Result:

Sort  (cost=3104972.93..3105223.35 rows=100169 width=543) (actual time=120216.612..120216.619 rows=6 loops=1)
Sort Key: name
Sort Method: quicksort  Memory: 27kB
->  Seq Scan on businesses  (cost=0.00..3072341.85 rows=100169 width=543) (actual time=39426.948..120216.532 rows=6 loops=1)
    Filter: ((st_transform(location, 26986) && '01030000206A6900000100000005000000E758D42C93364EC16028183E1ADA3941E758D42C93364EC16028183EE2DA3941E758D42C2F364EC16028183EE2DA3941E758D42C2F364EC16028183E1ADA3941E758D42C93364EC16028183E1ADA3941'::geometry) AND ('01010000206A690000E758D42C61364EC16028183E7EDA3941'::geometry && st_expand(st_transform(location, 26986), '100'::double precision)) AND _st_dwithin('01010000206A690000E758D42C61364EC16028183E7EDA3941'::geometry, st_transform(location, 26986), '100'::double precision))
    Rows Removed by Filter: 7512688

What method can I use to decrease the query time?

1 Answer 1

6

You're not getting any benefit from your spatial index, because you're storing your locations as geometry(Point, 4326) but querying them either as geography (Query 1) or as geometry(Point, 26986) (Query 2).

Any of the following changes would fix this:

Query 1:

Switch your geometry column to type geography

ALTER TABLE businesses ALTER COLUMN location
  SET DATA TYPE geography USING geography(location);

Or, add a geography column, populate it based on location, and maybe set up a trigger to maintain this in the future

ALTER TABLE businesses ADD COLUMN geog geography;
UPDATE businesses SET geog = location::geography;

Or, create a functional index on location casted to geography (slower than the above two options):

CREATE INDEX ON businesses USING gist(geography(location));

Query 2:

Store your geometry in the projection you're using for queries:

ALTER TABLE businesses ALTER COLUMN location 
  SET DATA TYPE geometry(Point, 26986) USING ST_Transform(location, 26986);

Or, create a functional index on your transformed geometry (slower than the above option):

CREATE INDEX ON businesses USING gist(ST_Transform(location, 26986));

Which of these solutions is the best depends on your particular situation. In general, distance calculations using geometry will be faster than calculations using geography, and indexes on a stored column will be faster than functional indexes.

5
  • Thanks so much for the elaborate answer, I am updating the column with geometry 26986 as you suggested for Query 2. It will take some time because of the large dataset. I'll get back to you as soon as it is done. Commented Sep 19, 2016 at 13:48
  • its done, should I create an index again on the location column now? Commented Sep 19, 2016 at 15:08
  • Update: without creating the index again, I am getting the result in 10s now :D Commented Sep 19, 2016 at 15:19
  • Infact 2 secs with st_dwithin and 10s with st_distance Commented Sep 19, 2016 at 15:46
  • 2
    How many results in the return? For a small result set, a "within distance" query should return in under 100ms. Maybe drop and re-create the index. Also, since you re-wrote the whole table, consider running VACUUM FULL on it. Commented Sep 19, 2016 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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