Re: [HACKERS] Problem with complex query

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