[postgis-users] help optimize distance calculation

[Sky]

Interesting...

I did not set the 'max mindistance' any bit smaller than the grid
extent because I need a value for each and every grid point... thanks
for clarification on how that works.

Additionally, you were correct about me using only one grid point in
my sample explain analyse query. I ran it again using 400,000 of the
grid points instead of 1:

"Limit  (cost=100000054.08..121909083.44 rows=400000 width=204)
(actual time=18.286..4661607.457 rows=400000 loops=1)"
"  ->  GroupAggregate  (cost=100000054.08..319090347.66 rows=4000000
width=204) (actual time=18.280..4660429.481 rows=400000 loops=1)"
"        ->  Nested Loop  (cost=100000054.08..275250347.66
rows=8756000000 width=204) (actual time=0.072..2935273.363
rows=875600001 loops=1)"
"              ->  Index Scan using grid_pkey on grid
(cost=0.00..130293.58 rows=4000000 width=104) (actual
time=0.040..3647.327 rows=400001 loops=1)"
"              ->  Materialize  (cost=100000054.08..100000075.97
rows=2189 width=100) (actual time=0.002..2.475 rows=2189
loops=400001)"
"                    ->  Seq Scan on features f
(cost=100000000.00..100000051.89 rows=2189 width=100) (actual
time=0.013..3.388 rows=2189 loops=1)"
"Total runtime: 4662416.175 ms"


Again, the query doesn't seem to use the ao.the_geom index... correct?
Here is the query I used:

  explain analyse select ao.gid, min(distance(f.the_geom,ao.the_geom))
from features f, ao ao group by ao.gid limit 400000

This query should use the grid_inx_geo... yeah?

Thanks again for your assistance Paul,
Sky