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 just GOSUB 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'::timespanbut 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'::timespanbut 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.
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 just GOSUB 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 just GOSUB without RETURN.