Re: query speed question

On 3 Sep 2009, at 23:11, Christopher Condit 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.

My guess is that those functions round lat and lon values to their
nearest half-degree interval counterpart as in table A?
I assume you marked that function immutable?
Is the return type indeed a numeric, as there are some explicit casts
in the query plan?

> 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?

Hard to tell without knowing where most time gets spent. An EXPLAIN
ANALYSE would tell.

You could try comparing integers instead of numerics, simply by
multiplying your half-degree values by 10 (or by 2) and cast them to
int. Integer comparisons are typically faster than numerics. It's hard
to tell whether that does indeed take up a significant amount of time
without the above ;)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4aa0f4d811866722913219!