|
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? Thanks, -Chris |
