Merge join exhausting swap space

Started by Martin Weinbergalmost 25 years ago3 messagesgeneral
Jump to latest
#1Martin Weinberg
weinberg@osprey.astro.umass.edu

We have a database with two-dimensional spatial data. The following
query is makes a table of separation between pairs of points:

create table close_test as
select a.cntr as a_cntr,b.cntr as b_cntr
from may14_goodsrc as a, may14_goodsrc as b
where a.decl between b.decl+.00001 and b.decl+.030
and a.ra between b.ra-.040 and b.ra+.040
and a.scan=b.scan;

The two coordinates are ra and decl. The variable scan is further
restriction to data obtained at nearly the same time.

As long as the input table (in this case, may14_goodsrc) is small
enough it works fine. For large input tables, postgres exhausts
all swap space and crashes. Explain on the select gives:

explain
select a.cntr as a_cntr,b.cntr as b_cntr
from may14_goodsrc as a, may14_goodsrc as b
where a.decl between b.decl+.00001 and b.decl+.030
and a.ra between b.ra-.040 and b.ra+.040
and a.scan=b.scan
NOTICE: QUERY PLAN:

Merge Join (cost=1174722.06..1335057.36 rows=332564991 width=44)
-> Sort (cost=587361.03..587361.03 rows=2466697 width=22)
-> Seq Scan on may14_goodsrc b (cost=0.00..202999.97 rows=2466697
width=22)
-> Sort (cost=587361.03..587361.03 rows=2466697 width=22)
-> Seq Scan on may14_goodsrc a (cost=0.00..202999.97 rows=2466697
width=22)

Is there an obvious work around for this?

Thanks,

Martin

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Martin Weinberg (#1)
Re: Merge join exhausting swap space

Martin Weinberg <weinberg@osprey.astro.umass.edu> writes:

As long as the input table (in this case, may14_goodsrc) is small
enough it works fine. For large input tables, postgres exhausts
all swap space and crashes.

What Postgres version?

If it's 7.0, try 7.1.

regards, tom lane

#3Martin Weinberg
weinberg@osprey.astro.umass.edu
In reply to: Tom Lane (#2)
Re: Merge join exhausting swap space

Tom Lane wrote on Tue, 12 Jun 2001 19:28:14 EDT

Martin Weinberg <weinberg@osprey.astro.umass.edu> writes:

As long as the input table (in this case, may14_goodsrc) is small
enough it works fine. For large input tables, postgres exhausts
all swap space and crashes.

What Postgres version?

If it's 7.0, try 7.1.

Thanks Tom! We are still testing but 7.1 seems to have fixed the
problem (yes, it was 7.0). There are probably better ways of doing
two-point correlation functions. I suppose what we really need here
is some sort of spatial hash.

--Martin