Обсуждение: Not enough memory for complex join
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.
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
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=1width=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.00size=0 width=0) -> Sort (cost=0.00 size=0 width=0) -> Seq Scan onshops sh (cost=0.00 size=0 width=6) -> Seq Scan on central cn (cost=0.00 size=0 width=4) -> Seq Scan on positionsp (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 justGOSUB without RETURN.
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
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 justGOSUB without RETURN.
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
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
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
justGOSUB without RETURN.