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 по дате отправления: