Re: query speed question

Christopher Condit <condit(at)sdsc(dot)edu> wrote:
>
> I have two tables that are georeferenced (although in this case I'm not using PostGIS) that I need to join.
> A ( lat | lon | depth | value)
> |A| = 1,100,000
>
> B ( lat | lon | attributes)
> |B| = 14,000,000
>
> A is a special case because the lat / lon values are all at half degree intervals (ie 33.5, -12.5). The lat/lons in B are arbitrary.
> I've written a function in B called getSpecialLat(latitude) and getSpecialLon(longitude) to calculate the correct A latitude and built an index on both functions.
>
> Here's the query that I'm trying, but it's rather slow:
> SELECT B.* FROM B,
> (SELECT lat, lon FROM A WHERE value > 0 AND value < 2 AND depth = 0) AS foo
> WHERE getSpecialLat(B.lat) = foo.lat AND getSpecialLon(B.lon) = foo.lon
>
> "Nested Loop (cost=3569.88..32055.02 rows=1414 width=422)"
> " -> Index Scan using A_valueidx on A (cost=0.00..555.26 rows=6 width=16)"
> " Index Cond: ((value > 0) AND (value < 2))"
> " Filter: (depth = 0)"
> " -> Bitmap Heap Scan on B (cost=3569.88..5029.48 rows=424 width=422)"
> " Recheck Cond: ((getSpecialLon((B.lon)::numeric) = A.lon) AND (getSpecialLat((B.lat)::numeric) = A.lat))"
> " -> BitmapAnd (cost=3569.88..3569.88 rows=424 width=0)"
> " -> Bitmap Index Scan on Blonidx (cost=0.00..1760.38 rows=84859 width=0)"
> " Index Cond: (getSpecialLon((B.lon)::numeric) = A.lon)"
> " -> Bitmap Index Scan on Blatidx (cost=0.00..1766.81 rows=84859 width=0)"
> " Index Cond: (getSpeicalLat((B.latitude)::numeric) = A.lat)"
>
> Am I missing something in terms of speeding up this query?

I'd be interested to see if the query rewritten as a JOIN would be faster.

--
Bill Moran
http://www.potentialtech.com