query speed question

Christopher Condit <condit@sdsc.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

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.

I can write it like this:
select b.*
from b join a on (getwoalatitude(b.latitude::numeric) = a.lat
and getwoalongitude(b.longitude::numeric) = a.lon)
where
a.value > 0 and a.value < 2 and a.depth = 0

which results in this plan:
"Nested Loop (cost=1387.20..13152982.35 rows=1625767 width=422)"
" -> Index Scan using a_depthidx on a_(cost=0.00..1464.07 rows=6897 width=16)"
" Index Cond: (depth = 0)"
" Filter: ((value > 0::numeric) AND (value < 2::numeric))"
" -> Bitmap Heap Scan on b (cost=1387.20..1686.37 rows=424 width=422)"
" Recheck Cond: ((getSpecialLon((b.lon)::numeric) = a.lon) AND (getSpecialLat((b.lat)::numeric) = a.lat))"
" -> BitmapAnd (cost=1387.20..1387.20 rows=424 width=0)"
" -> Bitmap Index Scan on Blonidx (cost=0.00..672.15 rows=84859 width=0)"
" Index Cond: (getSpecialLon((b.lon)::numeric) = a.lon)"
" -> Bitmap Index Scan on Blatidx (cost=0.00..672.36 rows=84859 width=0)"
" Index Cond: (getSpecialLat((b.lat)::numeric) = a.lat)"

However it's still taking ages to execute (over five minutes - I stopped it before it finished)

-Chris

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!

In response to Christopher Condit <condit@sdsc.edu>:

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.

I can write it like this:
select b.*
from b join a on (getwoalatitude(b.latitude::numeric) = a.lat
and getwoalongitude(b.longitude::numeric) = a.lon)
where
a.value > 0 and a.value < 2 and a.depth = 0

which results in this plan:
"Nested Loop (cost=1387.20..13152982.35 rows=1625767 width=422)"
" -> Index Scan using a_depthidx on a_(cost=0.00..1464.07 rows=6897 width=16)"
" Index Cond: (depth = 0)"
" Filter: ((value > 0::numeric) AND (value < 2::numeric))"
" -> Bitmap Heap Scan on b (cost=1387.20..1686.37 rows=424 width=422)"
" Recheck Cond: ((getSpecialLon((b.lon)::numeric) = a.lon) AND (getSpecialLat((b.lat)::numeric) = a.lat))"
" -> BitmapAnd (cost=1387.20..1387.20 rows=424 width=0)"
" -> Bitmap Index Scan on Blonidx (cost=0.00..672.15 rows=84859 width=0)"
" Index Cond: (getSpecialLon((b.lon)::numeric) = a.lon)"
" -> Bitmap Index Scan on Blatidx (cost=0.00..672.36 rows=84859 width=0)"
" Index Cond: (getSpecialLat((b.lat)::numeric) = a.lat)"

However it's still taking ages to execute (over five minutes - I stopped it before it finished)

Do you really expect that query to return 1.6M rows? I doubt it, since
the subselect version only returns 1400. If you do a vacuum analyze on
those two tables, does it speed either of the queries up?

Try "set enable_nestloop=off" and rerun the two queries. If that helps
and analyze didn't then perhaps you need to increase the statistics
target on those two tables, or perhaps you'll just have to use that
set command to force the planner to avoid a nested loop.

Hope one of these helps.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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.

I can write it like this:
select b.*
from b join a on (getwoalatitude(b.latitude::numeric) = a.lat
and getwoalongitude(b.longitude::numeric) = a.lon)
where
a.value > 0 and a.value < 2 and a.depth = 0

which results in this plan:
"Nested Loop (cost=1387.20..13152982.35 rows=1625767 width=422)"
" -> Index Scan using a_depthidx on a_(cost=0.00..1464.07 rows=6897

width=16)"

" Index Cond: (depth = 0)"
" Filter: ((value > 0::numeric) AND (value < 2::numeric))"
" -> Bitmap Heap Scan on b (cost=1387.20..1686.37 rows=424

width=422)"

" Recheck Cond: ((getSpecialLon((b.lon)::numeric) = a.lon) AND

(getSpecialLat((b.lat)::numeric) = a.lat))"

" -> BitmapAnd (cost=1387.20..1387.20 rows=424 width=0)"
" -> Bitmap Index Scan on Blonidx (cost=0.00..672.15

rows=84859 width=0)"

" Index Cond: (getSpecialLon((b.lon)::numeric) =

a.lon)"

" -> Bitmap Index Scan on Blatidx (cost=0.00..672.36

rows=84859 width=0)"

" Index Cond: (getSpecialLat((b.lat)::numeric) =

a.lat)"

However it's still taking ages to execute (over five minutes - I

stopped it before it finished)

Do you really expect that query to return 1.6M rows? I doubt it, since
the subselect version only returns 1400.

No - I do not expect it to return 1.6M...

If you do a vacuum analyze on
those two tables, does it speed either of the queries up?

Here are the new results (with explain analyze):

EXPLAIN ANALYZE SELECT B.* FROM B,
(SELECT lat, lon FROM A WHERE value > 0 AND value < 2 AND depth = 0) AS foo
WHERE getwoalatitude(B.latitude::numeric) = foo.lat AND getwoalongitude(B.longitude::numeric) = foo.lon

after vacuum analyze:
"Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420) (actual time=752983.201..941125.197 rows=226941 loops=1)"
" Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric))) AND (a.lon = (getwoalongitude((b.longitude)::numeric))))"
" -> Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual time=30.704..32.171 rows=2111 loops=1)"
" Sort Key: a.lat, a.lon"
" Sort Method: quicksort Memory: 212kB"
" -> Index Scan using depthidx on a (cost=0.00..1555.43 rows=7239 width=16) (actual time=0.533..24.631 rows=2111 loops=1)"
" Index Cond: (depth = 0)"
" Filter: ((value > 0::numeric) AND (value < 2::numeric))"
" -> Materialize (cost=17871190.21..18083338.96 rows=16971900 width=420) (actual time=751324.751..919278.574 rows=16963350 loops=1)"
" -> Sort (cost=17871190.21..17913619.96 rows=16971900 width=420) (actual time=751324.744..820522.604 rows=16963350 loops=1)"
" Sort Key: (getwoalatitude((b.latitude)::numeric)), (getwoalongitude((b.longitude)::numeric))"
" Sort Method: external merge Disk: 4599344kB"
" -> Seq Scan on b (cost=0.00..750696.00 rows=16971900 width=420) (actual time=1.781..229158.949 rows=16971901 loops=1)"
"Total runtime: 942295.914 ms"

EXPLAIN ANALYZE SELECT b.* FROM b JOIN a
ON (getwoalatitude(b.latitude::numeric) = a.lat AND getwoalongitude(b.longitude::numeric) = a.lon)
WHERE a.value > 0 AND a.value < 2 AND a.depth = 0

"Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420) (actual time=755255.801..906407.961 rows=226941 loops=1)"
" Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric))) AND (a.lon = (getwoalongitude((b.longitude)::numeric))))"
" -> Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual time=27.658..29.108 rows=2111 loops=1)"
" Sort Key: a.lat, a.lon"
" Sort Method: quicksort Memory: 212kB"
" -> Index Scan using depthidx on a (cost=0.00..1555.43 rows=7239 width=16) (actual time=0.467..21.646 rows=2111 loops=1)"
" Index Cond: (depth = 0)"
" Filter: ((value > 0::numeric) AND (value < 2::numeric))"
" -> Materialize (cost=17871190.21..18083338.96 rows=16971900 width=420) (actual time=753605.414..884549.890 rows=16963350 loops=1)"
" -> Sort (cost=17871190.21..17913619.96 rows=16971900 width=420) (actual time=753605.407..822844.299 rows=16963350 loops=1)"
" Sort Key: (getwoalatitude((b.latitude)::numeric)), (getwoalongitude((b.longitude)::numeric))"
" Sort Method: external merge Disk: 4599344kB"
" -> Seq Scan on b (cost=0.00..750696.00 rows=16971900 width=420) (actual time=0.095..229888.646 rows=16971901 loops=1)"
"Total runtime: 911284.022 ms"

Try "set enable_nestloop=off" and rerun the two queries. If that helps
and analyze didn't then perhaps you need to increase the statistics
target on those two tables, or perhaps you'll just have to use that
set command to force the planner to avoid a nested loop.

Hope one of these helps.

Thank, Bill, it's a bit faster. Any other thoughts?

-Chris

Hi Alban-

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?

Yes - the function is immutable. The return type is numeric, but I fixed the casting on input problem...

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.

Here are both queries (with explain analyze):

EXPLAIN ANALYZE SELECT B.* FROM B,
(SELECT lat, lon FROM A WHERE value > 0 AND value < 2 AND depth = 0) AS foo WHERE getwoalatitude(B.latitude::numeric) = foo.lat AND getwoalongitude(B.longitude::numeric) = foo.lon

after vacuum analyze:
"Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420) (actual time=752983.201..941125.197 rows=226941 loops=1)"
" Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric))) AND (a.lon = (getwoalongitude((b.longitude)::numeric))))"
" -> Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual time=30.704..32.171 rows=2111 loops=1)"
" Sort Key: a.lat, a.lon"
" Sort Method: quicksort Memory: 212kB"
" -> Index Scan using depthidx on a (cost=0.00..1555.43 rows=7239 width=16) (actual time=0.533..24.631 rows=2111 loops=1)"
" Index Cond: (depth = 0)"
" Filter: ((value > 0::numeric) AND (value < 2::numeric))"
" -> Materialize (cost=17871190.21..18083338.96 rows=16971900 width=420) (actual time=751324.751..919278.574 rows=16963350 loops=1)"
" -> Sort (cost=17871190.21..17913619.96 rows=16971900 width=420) (actual time=751324.744..820522.604 rows=16963350 loops=1)"
" Sort Key: (getwoalatitude((b.latitude)::numeric)), (getwoalongitude((b.longitude)::numeric))"
" Sort Method: external merge Disk: 4599344kB"
" -> Seq Scan on b (cost=0.00..750696.00 rows=16971900 width=420) (actual time=1.781..229158.949 rows=16971901 loops=1)"
"Total runtime: 942295.914 ms"

EXPLAIN ANALYZE SELECT b.* FROM b JOIN a ON (getwoalatitude(b.latitude::numeric) = a.lat AND getwoalongitude(b.longitude::numeric) = a.lon) WHERE a.value > 0 AND a.value < 2 AND a.depth = 0

"Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420) (actual time=755255.801..906407.961 rows=226941 loops=1)"
" Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric))) AND (a.lon = (getwoalongitude((b.longitude)::numeric))))"
" -> Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual time=27.658..29.108 rows=2111 loops=1)"
" Sort Key: a.lat, a.lon"
" Sort Method: quicksort Memory: 212kB"
" -> Index Scan using depthidx on a (cost=0.00..1555.43 rows=7239 width=16) (actual time=0.467..21.646 rows=2111 loops=1)"
" Index Cond: (depth = 0)"
" Filter: ((value > 0::numeric) AND (value < 2::numeric))"
" -> Materialize (cost=17871190.21..18083338.96 rows=16971900 width=420) (actual time=753605.414..884549.890 rows=16963350 loops=1)"
" -> Sort (cost=17871190.21..17913619.96 rows=16971900 width=420) (actual time=753605.407..822844.299 rows=16963350 loops=1)"
" Sort Key: (getwoalatitude((b.latitude)::numeric)), (getwoalongitude((b.longitude)::numeric))"
" Sort Method: external merge Disk: 4599344kB"
" -> Seq Scan on b (cost=0.00..750696.00 rows=16971900 width=420) (actual time=0.095..229888.646 rows=16971901 loops=1)"
"Total runtime: 911284.022 ms"

Any thoughts?

Thanks,
-Chris

On 10 Sep 2009, at 19:09, Christopher Condit wrote:

Here are both queries (with explain analyze):

EXPLAIN ANALYZE SELECT B.* FROM B,
(SELECT lat, lon FROM A WHERE value > 0 AND value < 2 AND depth = 0)
AS foo WHERE getwoalatitude(B.latitude::numeric) = foo.lat AND
getwoalongitude(B.longitude::numeric) = foo.lon

after vacuum analyze:
"Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420)
(actual time=752983.201..941125.197 rows=226941 loops=1)"
" Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric)))
AND (a.lon = (getwoalongitude((b.longitude)::numeric))))"
" -> Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual
time=30.704..32.171 rows=2111 loops=1)"
" Sort Key: a.lat, a.lon"
" Sort Method: quicksort Memory: 212kB"
" -> Index Scan using depthidx on a (cost=0.00..1555.43
rows=7239 width=16) (actual time=0.533..24.631 rows=2111 loops=1)"
" Index Cond: (depth = 0)"
" Filter: ((value > 0::numeric) AND (value <
2::numeric))"

Here you can see that the estimated row counts are way off compared to
the actual number of rows. That means the statistics for that table
aren't reliable; either you need to ANALYZE them or you need to
increase the statistics size of the columns involved.

This part of the query is the quicker part, but it may be possible
that the planner wouldn't have picked a merge-join with the remainder
if the estimates were closer to reality.

You can paste those queries in http://explain-analyze.info/, although
you'll need to strip the quotes from the plan (not sure where those
get added, psql doesn't add them).

" -> Materialize (cost=17871190.21..18083338.96 rows=16971900
width=420) (actual time=751324.751..919278.574 rows=16963350 loops=1)"
" -> Sort (cost=17871190.21..17913619.96 rows=16971900
width=420) (actual time=751324.744..820522.604 rows=16963350 loops=1)"
" Sort Key: (getwoalatitude((b.latitude)::numeric)),
(getwoalongitude((b.longitude)::numeric))"
" Sort Method: external merge Disk: 4599344kB"
" -> Seq Scan on b (cost=0.00..750696.00
rows=16971900 width=420) (actual time=1.781..229158.949
rows=16971901 loops=1)"
"Total runtime: 942295.914 ms"

Here's where most time is spent. Firstly, an external merge to disk is
going to be slow. I think the setting controlling the amount of memory
available for sorting is work_mem, you can try increasing that in the
client (set work_mem TO <some value>) before executing your query and
see what value is sufficient.

The other issue here is the sequential scan on table b. I think that's
caused by the planner needing to merge to disk though.

EXPLAIN ANALYZE SELECT b.* FROM b JOIN a ON (getwoalatitude
(b.latitude::numeric) = a.lat AND getwoalongitude
(b.longitude::numeric) = a.lon) WHERE a.value > 0 AND a.value < 2
AND a.depth = 0

"Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420)
(actual time=755255.801..906407.961 rows=226941 loops=1)"
" Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric)))
AND (a.lon = (getwoalongitude((b.longitude)::numeric))))"
" -> Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual
time=27.658..29.108 rows=2111 loops=1)"
" Sort Key: a.lat, a.lon"
" Sort Method: quicksort Memory: 212kB"
" -> Index Scan using depthidx on a (cost=0.00..1555.43
rows=7239 width=16) (actual time=0.467..21.646 rows=2111 loops=1)"
" Index Cond: (depth = 0)"
" Filter: ((value > 0::numeric) AND (value <
2::numeric))"
" -> Materialize (cost=17871190.21..18083338.96 rows=16971900
width=420) (actual time=753605.414..884549.890 rows=16963350 loops=1)"
" -> Sort (cost=17871190.21..17913619.96 rows=16971900
width=420) (actual time=753605.407..822844.299 rows=16963350 loops=1)"
" Sort Key: (getwoalatitude((b.latitude)::numeric)),
(getwoalongitude((b.longitude)::numeric))"
" Sort Method: external merge Disk: 4599344kB"
" -> Seq Scan on b (cost=0.00..750696.00
rows=16971900 width=420) (actual time=0.095..229888.646
rows=16971901 loops=1)"
"Total runtime: 911284.022 ms"

This plan is actually very similar to the original query's plan, it
has the same problems.

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,4aa939f812071577543529!