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.


Thu, 4 Mar 1999 14:49:46 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Not enough memory for complex join
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

Thu, 04 Mar 1999 15:14:25 +0200
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] Not enough memory for complex join
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.


Thu, 4 Mar 1999 16:15:53 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Not enough memory for complex join
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

Fri, 05 Mar 1999 09:03:00 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Not enough memory for complex join
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.


Fri, 5 Mar 1999 12:50:49 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Not enough memory for complex join
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 


Fri, 5 Mar 1999 13:42:31 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Not enough memory for complex join
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

Sat, 06 Mar 1999 16:57:10 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Not enough memory for complex join
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.


Tue, 9 Mar 1999 11:59:17 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Not enough memory for complex join