Thread
-
Re: [HACKERS] Problem with complex query
Tom Lane <tgl@sss.pgh.pa.us> — 1999-05-09T17:49:05Z
Bruce Momjian <maillist@candle.pha.pa.us> writes: >> SELECT DISTINCT p.subsec_id >> FROM central cn, shops sh, districts d, positions p >> WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id >> AND d.city_id = %d AND cn.pos_id = p.pos_id >> AND cn.date_i >= current_date - '7 days'::timespan >> >> While running postgres slowly eats all swap space (30 Meg) and aborts: > Not sure how to comment on this. Is 6.5beta any better? Probably not :-(. My guess is that the expression "current_date - '7 days'::timespan" is being re-evaluated at each tuple, and since we don't yet have intra-statement space recovery, the palloc'd space just grows and grows. Oleg, can you try evaluating that expression on the application side and sending over a constant instead? I think being able to recover palloc'd space after every few tuples will have to be a top priority for 6.6; we've seen too many complaints that trace back to this sort of thing. regards, tom lane
-
Re: [HACKERS] Problem with complex query
Oleg Broytmann <phd@sun.med.ru> — 1999-05-10T10:34:27Z
Hello! Tom, I want to remind you that you looked into my database and found the problem was that central.shop_id was int4 but shops.shop_id int2. After making all fields identical most of the problem was fixed. I just rerun the query now - and it worked! On Sun, 9 May 1999, Tom Lane wrote: > Bruce Momjian <maillist@candle.pha.pa.us> writes: > >> SELECT DISTINCT p.subsec_id > >> FROM central cn, shops sh, districts d, positions p > >> WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id > >> AND d.city_id = %d AND cn.pos_id = p.pos_id > >> AND cn.date_i >= current_date - '7 days'::timespan > >> > >> While running postgres slowly eats all swap space (30 Meg) and aborts: > > > Not sure how to comment on this. Is 6.5beta any better? > > Probably not :-(. My guess is that the expression "current_date - > '7 days'::timespan" is being re-evaluated at each tuple, and since > we don't yet have intra-statement space recovery, the palloc'd space > just grows and grows. Oleg, can you try evaluating that expression > on the application side and sending over a constant instead? > > I think being able to recover palloc'd space after every few tuples > will have to be a top priority for 6.6; we've seen too many complaints > that trace back to this sort of thing. > > regards, tom lane > Oleg. ---- Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net Programmers don't die, they just GOSUB without RETURN.