Hello!

Query:

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

While running postgres slowly eats all swap space (30 Meg) and aborts:

pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally before or
while processing the request.

   Is it I just have not enough memory or bug?

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


Wed, 24 Feb 1999 16:03:13 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Problem with complex query
Oleg Broytmann <phd@sun.med.ru> writes:
> SELECT DISTINCT p.subsec_id
>    FROM central cn, shops sh, districts d, positions p
>       WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
>       AND   d.city_id = %d AND cn.pos_id = p.pos_id
>       AND   cn.date_i >= current_date - '7 days'::timespan
> While running postgres slowly eats all swap space (30 Meg) and aborts:
> pqReadData() -- backend closed the channel unexpectedly.
>    Is it I just have not enough memory or bug?

What version are you running?  Also, does it act the same if you try to
EXPLAIN that same query?  If EXPLAIN fails then the problem is in the
plan/optimize stage, not actual execution of the query.

This kinda sounds like the optimizer problems that Bruce has fixed for
6.5, but I don't recall anyone reporting serious problems with only
4 tables in the query --- you had to get up to 7 or 8 or so before
it really went nuts.

			regards, tom lane

Wed, 24 Feb 1999 10:25:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Problem with complex query 
Hi!

On Wed, 24 Feb 1999, Tom Lane wrote:
> What version are you running?  Also, does it act the same if you try to

   6.4.2 on Sparc-solaris2.5.1

> EXPLAIN that same query?  If EXPLAIN fails then the problem is in the
> plan/optimize stage, not actual execution of the query.

   EXPLAIN works fine:

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

Unique  (cost=0.00 size=0 width=0)
  ->  Sort  (cost=0.00 size=0 width=0)
        ->  Nested Loop  (cost=0.00 size=1 width=16)
              ->  Nested Loop  (cost=0.00 size=1 width=12)
                    ->  Merge Join  (cost=0.00 size=1 width=8)
                          ->  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=2)
                          ->  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

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


Wed, 24 Feb 1999 18:36:05 +0300 (MSK)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Problem with complex query 
> Hello!
> 
> Query:
> 
> SELECT DISTINCT p.subsec_id
>    FROM central cn, shops sh, districts d, positions p
>       WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
>       AND   d.city_id = %d AND cn.pos_id = p.pos_id
>       AND   cn.date_i >= current_date - '7 days'::timespan
> 
> While running postgres slowly eats all swap space (30 Meg) and aborts:
> 
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally before or
> while processing the request.
> 
>    Is it I just have not enough memory or bug?

Not sure how to comment on this.  Is 6.5beta any better?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 9 May 1999 10:57:16 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Problem with complex query
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> SELECT DISTINCT p.subsec_id
>> FROM central cn, shops sh, districts d, positions p
>> WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
>> AND   d.city_id = %d AND cn.pos_id = p.pos_id
>> AND   cn.date_i >= current_date - '7 days'::timespan
>> 
>> While running postgres slowly eats all swap space (30 Meg) and aborts:

> Not sure how to comment on this.  Is 6.5beta any better?

Probably not :-(.  My guess is that the expression "current_date -
'7 days'::timespan" is being re-evaluated at each tuple, and since
we don't yet have intra-statement space recovery, the palloc'd space
just grows and grows.  Oleg, can you try evaluating that expression
on the application side and sending over a constant instead?

I think being able to recover palloc'd space after every few tuples
will have to be a top priority for 6.6; we've seen too many complaints
that trace back to this sort of thing.

			regards, tom lane

Sun, 09 May 1999 13:49:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Problem with complex query 
Hello!

   Tom, I want to remind you that you looked into my database and found the
problem was that central.shop_id was int4 but shops.shop_id int2. After
making all fields identical most of the problem was fixed.
   I just rerun the query now - and it worked!

On Sun, 9 May 1999, Tom Lane wrote:

> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> >> SELECT DISTINCT p.subsec_id
> >> FROM central cn, shops sh, districts d, positions p
> >> WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
> >> AND   d.city_id = %d AND cn.pos_id = p.pos_id
> >> AND   cn.date_i >= current_date - '7 days'::timespan
> >> 
> >> While running postgres slowly eats all swap space (30 Meg) and aborts:
> 
> > Not sure how to comment on this.  Is 6.5beta any better?
> 
> Probably not :-(.  My guess is that the expression "current_date -
> '7 days'::timespan" is being re-evaluated at each tuple, and since
> we don't yet have intra-statement space recovery, the palloc'd space
> just grows and grows.  Oleg, can you try evaluating that expression
> on the application side and sending over a constant instead?
> 
> I think being able to recover palloc'd space after every few tuples
> will have to be a top priority for 6.6; we've seen too many complaints
> that trace back to this sort of thing.
> 
> 			regards, tom lane
> 

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


Mon, 10 May 1999 14:34:27 +0400 (MSD)
Oleg Broytmann <phd@sun.med.ru>
Re: [HACKERS] Problem with complex query 