[postgis-users] help optimize distance calculation

[Paul Ramsey]

Judging by your index condition in your query, you are restricting
your test to just one grid cell (gid = 1?) and PostgreSQL doesn't use
indexes willynilly, it uses them when it thinks they will help. You
only have 2000 features in the other candidate table, PostgreSQL is
guessing that a seqscan will be faster than an index scan for this
case.

You are doing a query that isn't so little, it's (at minimum) 2
million distance calculations (and that's if you only calculate the
minimum distance, in order to *find* the minimum distance you have to
do several more distance tests at least). Indexes makes things much
faster if they can drastically reduce the candidate sets.  In your
case, your "max mindistance" would have to be a good deal smaller than
your overall grid extent to start to achieve that.

P.

On Wed, Jan 28, 2009 at 8:30 AM, Sky <shlyon+postgis at gmail.com> wrote:
> Paul (and others),
>
> I appreciate the suggestion, but that actually ended up making the
> query take significantly longer!
>
> I created an index on both of the 'the_geom' columns:
>  create index features_idx_geo on features using gist(the_geom)
>  create index grid_idx_geo on grid using gist(the_geom)
> ...thinking that this should speed up my query, then I run vacuum
> analyze grid, features, but when I run explain analyze [my select
> statement] I don't see any indication that my idxs are being used...
>
> "GroupAggregate  (cost=0.00..93.45 rows=1 width=204) (actual
> time=12.732..12.734 rows=1 loops=1)"
> "  ->  Nested Loop  (cost=0.00..82.49 rows=2189 width=204) (actual
> time=0.091..8.384 rows=2189 loops=1)"
> "        ->  Index Scan using grid_pkey on grid (cost=0.00..8.71
> rows=1 width=104) (actual time=0.054..0.060 rows=1 loops=1)"
> "              Index Cond: (gid = 1)"
> "        ->  Seq Scan on features f  (cost=0.00..51.89 rows=2189
> width=100) (actual time=0.015..3.027 rows=2189 loops=1)"
> "Total runtime: 12.872 ms"
>
> What's up with that? Shouldn't I see some sort of grid/feature_idx_geo
> in the analysis??
>
> I'd really love other suggestions as how to optimize or just speed up
> a little this distance calculation that I'm trying to perform.
>
> Thanks,
> Sky
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>