Merge join exhausting swap space

Поиск
Список
Период
Сортировка
От Martin Weinberg
Тема Merge join exhausting swap space
Дата
Msg-id 200106122149.RAA03524@osprey.astro.umass.edu
обсуждение исходный текст
Ответы Re: Merge join exhausting swap space
Список pgsql-general
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



В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: PHP's pg_connect() and non-standard location of the socket (was: REPOST: redefining location of the socket file /tmp/.s.PGSQL.5432)
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: abs() does not exists..