Re: [HACKERS] Problem with complex query

Oleg Broytmann <phd@sun.med.ru> 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:
> pqReadData() -- backend closed the channel unexpectedly.
>    Is it I just have not enough memory or bug?

What version are you running?  Also, does it act the same if you try to
EXPLAIN that same query?  If EXPLAIN fails then the problem is in the
plan/optimize stage, not actual execution of the query.

This kinda sounds like the optimizer problems that Bruce has fixed for
6.5, but I don't recall anyone reporting serious problems with only
4 tables in the query --- you had to get up to 7 or 8 or so before
it really went nuts.
        regards, tom lane