Make the API / searches faster by using Postgres extensions instead of "get km" function. about dinesafe

[nomatteus]

Did a bunch of experimentation, and at first, when I used these extensions, it took about twice as long to execute the same queries! That was unexpected.

But did a bunch more testing and refining and found that the solution is to create a new earth_coord of earth type column, and then store the result of ll_to_earth(latlng[1], latlng[0]) in that column, effectively caching it. (I tried creating an index on this but it didn't seem to help... I think i tried create index test_index on establishments using gist (ll_to_earth(latlng[1], latlng[0]));).

When using the earth_coord column, and the earthdistance module queries, query time was reduced by an order of magnitude, from 1330ms to 120ms! And compared to the original query (which takes approx 666ms), it is 5 times as fast.

Original Query (approx)

dinesafe_dev=# explain analyze select get_distance_km(43.650992, -79.476959, latlng[0], latlng[1]) as distance_accurate, e.id, e.latest_name FROM establishments e ORDER BY distance_accurate asc limit 30;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=8191.28..8191.35 rows=30 width=38) (actual time=666.447..666.455 rows=30 loops=1)
   ->  Sort  (cost=8191.28..8252.51 rows=24492 width=38) (actual time=666.446..666.448 rows=30 loops=1)
         Sort Key: (get_distance_km(43.650992::double precision, (-79.476959)::double precision, latlng[0], latlng[1]))
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on establishments e  (cost=0.00..7467.92 rows=24492 width=38) (actual time=0.665..654.259 rows=24492 loops=1)
 Total runtime: 666.485 ms
(6 rows)

Time: 666.910 ms

Slow Query

dinesafe_dev=# explain analyze select earth_distance(ll_to_earth(43.650992, -79.476959), ll_to_earth(latlng[1], latlng[0])) as distance_accurate, e.id, e.latest_name FROM establishments e ORDER BY distance_accurate asc limit 30;
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=14375.51..14375.58 rows=30 width=38) (actual time=1394.592..1394.601 rows=30 loops=1)
   ->  Sort  (cost=14375.51..14436.74 rows=24492 width=38) (actual time=1394.590..1394.594 rows=30 loops=1)
         Sort Key: (sec_to_gc(cube_distance('(842836.826907544, -4537356.65805289, 4402618.27519211)'::cube, (ll_to_earth(latlng[1], latlng[0]))::cube)))
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on establishments e  (cost=0.00..13652.15 rows=24492 width=38) (actual time=1.127..1381.054 rows=24492 loops=1)
 Total runtime: 1394.624 ms
(6 rows)

Time: 1395.575 ms

Fast Query

dinesafe_dev=# explain analyze select earth_distance(ll_to_earth(43.650992, -79.476959), earth_coord) as distance_accurate, e.id, e.latest_name FROM establishments e ORDER BY distance_accurate asc limit 30;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=8252.51..8252.58 rows=30 width=78) (actual time=119.631..119.637 rows=30 loops=1)
   ->  Sort  (cost=8252.51..8313.74 rows=24492 width=78) (actual time=119.630..119.633 rows=30 loops=1)
         Sort Key: (sec_to_gc(cube_distance('(842836.826907544, -4537356.65805289, 4402618.27519211)'::cube, (earth_coord)::cube)))
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on establishments e  (cost=0.00..7529.15 rows=24492 width=78) (actual time=0.985..112.004 rows=24492 loops=1)
 Total runtime: 119.657 ms
(6 rows)

Time: 120.548 ms