I'm trying to optimize my geolocation query for a table of addresses of ~862k rows within a search radius using earth_box
. My first initial query is not too terrible:
explain analyze SELECT
id
FROM
location
WHERE
earth_box(ll_to_earth(40.65130101, -73.83367812), 25000) @> ll_to_earth(latitude, longitude)
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on location (cost=46.97..3385.08 rows=863 width=16) (actual time=11.430..49.406 rows=29407 loops=1)
Recheck Cond: ('(1322317.9173587903, -4672693.781456112, 4130081.708818594),(1372317.8853516562, -4622693.8134632455, 4180081.6768114604)'::cube @> (ll_to_earth(latitude, longitude))::cube)
Heap Blocks: exact=22479
-> Bitmap Index Scan on location_gist_lat_lon_idx (cost=0.00..46.76 rows=863 width=0) (actual time=7.942..7.943 rows=29407 loops=1)
Index Cond: ((ll_to_earth(latitude, longitude))::cube <@ '(1322317.9173587903, -4672693.781456112, 4130081.708818594),(1372317.8853516562, -4622693.8134632455, 4180081.6768114604)'::cube)
Planning Time: 0.711 ms
Execution Time: 51.018 ms
However, when I want to refine my search to get more accurate results with earth_distance
the execution time dramatically increases by 10x:
=> explain analyze SELECT
id
FROM
location
WHERE
earth_box(ll_to_earth(40.65130101, -73.83367812), 25000) @> ll_to_earth(latitude, longitude)
AND earth_distance(ll_to_earth(40.65130101, -73.83367812),
ll_to_earth(latitude, longitude)) < 25000 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on location (cost=46.83..3820.75 rows=288 width=16) (actual time=11.264..537.018 rows=24898 loops=1)
Recheck Cond: ('(1322317.9173587903, -4672693.781456112, 4130081.708818594),(1372317.8853516562, -4622693.8134632455, 4180081.6768114604)'::cube @> (ll_to_earth(latitude, longitude))::cube)
Filter: (sec_to_gc(cube_distance('(1347317.9013552233, -4647693.797459679, 4155081.692815027)'::cube, (ll_to_earth(latitude, longitude))::cube)) < '25000'::double precision)
Rows Removed by Filter: 4509
Heap Blocks: exact=22479
-> Bitmap Index Scan on location_gist_lat_lon_idx (cost=0.00..46.76 rows=863 width=0) (actual time=7.358..7.358 rows=29407 loops=1)
Index Cond: ((ll_to_earth(latitude, longitude))::cube <@ '(1322317.9173587903, -4672693.781456112, 4130081.708818594),(1372317.8853516562, -4622693.8134632455, 4180081.6768114604)'::cube)
Planning Time: 0.901 ms
Execution Time: 539.113 ms
(9 rows)
My table schema (excluded some columns):
Table "provider.location"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+-------------------
id | uuid | | not null |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP
can_delete | boolean | | |
name | text | | |
address | text | | |
address_line_1 | text | | |
address_line_2 | text | | |
city | text | | |
state | text | | |
street | text | | |
zip | text | | |
confidence | int | | |
google_maps_link | text | | |
is_coe | boolean | | |
latitude | double precision | | |
longitude | double precision | | |
I also have a GiST index created for the lat/lon:
"location_gist_lat_lon_idx" gist (ll_to_earth(latitude, longitude))
I'm wondering what is it that I'm missing that's making the additional query execution time increase by 10x?
My postgres 13.5 instance has the following specs:
CPU: 4vCPU
Memory: 16GB
SDD: 250GB
This question is related to How can I speed-up my query on geo-location processes, however after following the suggested answer it didn't seem to improve my performance.