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

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.

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

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

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.