Not enough memory for complex join - Mailing list pgsql-hackers

From Oleg Broytmann
Subject Not enough memory for complex join
Date
Msg-id Pine.SOL2.3.96.SK.990304144214.9702A-100000@sun.med.ru
Whole thread Raw
List pgsql-hackers
Hello!
  A week ago I reported this, but haven't got any good help. I am trying
to repeat.
  Postgres 6.4.2 on Solaris. Query:
SELECT p.subsec_id  FROM  positions p, central cn, shops sh, districts d     WHERE cn.pos_id = p.pos_id AND d.city_id =
2    AND   cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id     AND   cn.date_i >= current_date - '7
days'::timespan
  I am not publishing here my tables, but the structure is obvious. There
is central database, which only knows shop_id, and shop only knows its
district, and district knows city. I want to count distinct p.subsec_id for
one city.  With the query, postgres eats all memory and dies.
  I played with the query, and found I can remove (AND d.city_id = 2). The
query executes pretty fast (my database is small). I tried to get
d.city_id:

SELECT p.subsec_id, d.city_id  FROM  positions p, central cn, shops sh, districts d     WHERE cn.pos_id = p.pos_id
AND  cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id     AND   cn.date_i >= current_date - '7 days'::timespan
 

but postgres eats all memory and dies :(((
  Memory leak? Other bugs?

Oleg.
----    Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net          Programmers don't die, they
justGOSUB without RETURN.
 



pgsql-hackers by date:

Previous
From: "David R. Favor"
Date:
Subject: PostgreSQL 6.4.2 on AIX 4.3.2: typeidTypeRelid error
Next
From: Hannu Krosing
Date:
Subject: Re: [HACKERS] Not enough memory for complex join