Thread: 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 beforeor 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 justGOSUB without RETURN.
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_idAND 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.00size=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 positionsp (cost=0.00 size=0 width=4) EXPLAIN Oleg. ---- Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net Programmers don't die, they justGOSUB 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, Pennsylvania19026
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 justGOSUB without RETURN.