Thread
-
Not enough memory for complex join
Oleg Broytmann <phd@sun.med.ru> — 1999-03-04T11:49:46Z
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 just GOSUB without RETURN. -
Re: [HACKERS] Not enough memory for complex join
Hannu Krosing <hannu@trust.ee> — 1999-03-04T13:14:25Z
Oleg Broytmann wrote: > > 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 :((( What does EXPLAIN say ? -------------------- Hannu
-
Re: [HACKERS] Not enough memory for complex join
Oleg Broytmann <phd@sun.med.ru> — 1999-03-04T13:15:53Z
On Thu, 4 Mar 1999, Hannu Krosing wrote: > > 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 :((( > > What does EXPLAIN say ? EXPLAIN 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 ; NOTICE: QUERY PLAN: Nested Loop (cost=0.00 size=1 width=18) -> Nested Loop (cost=0.00 size=1 width=14) -> Merge Join (cost=0.00 size=1 width=10) -> Seq Scan (cost=0.00 size=0 width=0) -> Sort (cost=0.00 size=0 width=0) -> Seq Scan on districts d (cost=0.00 size=0 width=4) -> Seq Scan (cost=0.00 size=0 width=0) -> Sort (cost=0.00 size=0 width=0) -> Seq Scan on shops sh (cost=0.00 size=0 width=6) -> Seq Scan on central cn (cost=0.00 size=0 width=4) -> Seq Scan on positions p (cost=0.00 size=0 width=4) EXPLAIN > -------------------- > Hannu > Oleg. ---- Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net Programmers don't die, they just GOSUB without RETURN. -
Re: [HACKERS] Not enough memory for complex join
Vadim Mikheev <vadim@krs.ru> — 1999-03-05T02:03:00Z
Oleg Broytmann wrote: > > Nested Loop (cost=0.00 size=1 width=18) > -> Nested Loop (cost=0.00 size=1 width=14) > -> Merge Join (cost=0.00 size=1 width=10) > -> Seq Scan (cost=0.00 size=0 width=0) > -> Sort (cost=0.00 size=0 width=0) > -> Seq Scan on districts d (cost=0.00 size=0 width=4) > -> Seq Scan (cost=0.00 size=0 width=0) > -> Sort (cost=0.00 size=0 width=0) > -> Seq Scan on shops sh (cost=0.00 size=0 width=6) > -> Seq Scan on central cn (cost=0.00 size=0 width=4) > -> Seq Scan on positions p (cost=0.00 size=0 width=4) ^^^^^^ vacuum... Vadim -
Re: [HACKERS] Not enough memory for complex join
Oleg Broytmann <phd@sun.med.ru> — 1999-03-05T09:50:49Z
Hi! On Fri, 5 Mar 1999, Vadim Mikheev wrote: > Oleg Broytmann wrote: > > > > Nested Loop (cost=0.00 size=1 width=18) > > -> Nested Loop (cost=0.00 size=1 width=14) > > -> Merge Join (cost=0.00 size=1 width=10) > > -> Seq Scan (cost=0.00 size=0 width=0) > > -> Sort (cost=0.00 size=0 width=0) > > -> Seq Scan on districts d (cost=0.00 size=0 width=4) > > -> Seq Scan (cost=0.00 size=0 width=0) > > -> Sort (cost=0.00 size=0 width=0) > > -> Seq Scan on shops sh (cost=0.00 size=0 width=6) > > -> Seq Scan on central cn (cost=0.00 size=0 width=4) > > -> Seq Scan on positions p (cost=0.00 size=0 width=4) > ^^^^^^ > vacuum... I didn't think it could be of any help. I have a copy of this database on my local computer. I dump db on server and put it on local computer every other day, so I thiink VACUUM is unneccessary here. Anyway, I tried to VACUUM the db. No, the query didn't execute - postgres ate all memory and died. Any other idea, anyone? > Vadim Oleg. ---- Oleg Broytmann National Research Surgery Centre http://sun.med.ru/~phd/ Programmers don't die, they just GOSUB without RETURN. -
Re: [HACKERS] Not enough memory for complex join
Marc G. Fournier <scrappy@hub.org> — 1999-03-05T17:42:31Z
On Fri, 5 Mar 1999, Oleg Broytmann wrote: > Hi! > > On Fri, 5 Mar 1999, Vadim Mikheev wrote: > > Oleg Broytmann wrote: > > > > > > Nested Loop (cost=0.00 size=1 width=18) > > > -> Nested Loop (cost=0.00 size=1 width=14) > > > -> Merge Join (cost=0.00 size=1 width=10) > > > -> Seq Scan (cost=0.00 size=0 width=0) > > > -> Sort (cost=0.00 size=0 width=0) > > > -> Seq Scan on districts d (cost=0.00 size=0 width=4) > > > -> Seq Scan (cost=0.00 size=0 width=0) > > > -> Sort (cost=0.00 size=0 width=0) > > > -> Seq Scan on shops sh (cost=0.00 size=0 width=6) > > > -> Seq Scan on central cn (cost=0.00 size=0 width=4) > > > -> Seq Scan on positions p (cost=0.00 size=0 width=4) > > ^^^^^^ > > vacuum... > > I didn't think it could be of any help. I have a copy of this database > on my local computer. I dump db on server and put it on local computer > every other day, so I thiink VACUUM is unneccessary here. Try 'vacuum analyze'...vacuum, rom my understanding, just cleans out the database of old records...reloading the db from scratch effectively has that already done. 'vacuum analyze' adjusts statistics that don't get changed on a load, that determins, to a large extet, how the optimizaer runs things... Marc G. Fournier Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org -
Re: [HACKERS] Not enough memory for complex join
Vadim Mikheev <vadim@krs.ru> — 1999-03-06T09:57:10Z
Oleg Broytmann wrote: > > Hi! > > On Fri, 5 Mar 1999, Vadim Mikheev wrote: > > Oleg Broytmann wrote: > > > > > > Nested Loop (cost=0.00 size=1 width=18) > > > -> Nested Loop (cost=0.00 size=1 width=14) > > > -> Merge Join (cost=0.00 size=1 width=10) > > > -> Seq Scan (cost=0.00 size=0 width=0) > > > -> Sort (cost=0.00 size=0 width=0) > > > -> Seq Scan on districts d (cost=0.00 size=0 width=4) > > > -> Seq Scan (cost=0.00 size=0 width=0) > > > -> Sort (cost=0.00 size=0 width=0) > > > -> Seq Scan on shops sh (cost=0.00 size=0 width=6) > > > -> Seq Scan on central cn (cost=0.00 size=0 width=4) > > > -> Seq Scan on positions p (cost=0.00 size=0 width=4) > > ^^^^^^ > > vacuum... > > I didn't think it could be of any help. I have a copy of this database > on my local computer. I dump db on server and put it on local computer > every other day, so I thiink VACUUM is unneccessary here. > Anyway, I tried to VACUUM the db. No, the query didn't execute - > postgres ate all memory and died. EXPLAIN after vacuum? There was MergerJoin before vacuum => 2 sorts, but sorting eats some memory, this is why I suggested vacuum.. And, btw, please re-post your query/table definition... Vadim
-
Re: [HACKERS] Not enough memory for complex join
Oleg Broytmann <phd@sun.med.ru> — 1999-03-09T08:59:17Z
Hello! On Fri, 5 Mar 1999, The Hermit Hacker wrote: > > > vacuum... > > > > I didn't think it could be of any help. I have a copy of this database > > on my local computer. I dump db on server and put it on local computer > > every other day, so I thiink VACUUM is unneccessary here. > > Try 'vacuum analyze'...vacuum, rom my understanding, just cleans out the > database of old records...reloading the db from scratch effectively has > that already done. 'vacuum analyze' adjusts statistics that don't get > changed on a load, that determins, to a large extet, how the optimizaer > runs things... Ha, then I stuck on another bug. VACUUM ANALYZE failed on glibc2 with --enable-locale. I reported this in "VACUUM ANALYZE problem" thread. Look into mail archive for February :((( > Marc G. Fournier > Systems Administrator @ hub.org > primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org Oleg. ---- Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net Programmers don't die, they just GOSUB without RETURN.