Обсуждение: Big join breaks psql

The following (rather long) query run fine for some time when executed
through Micro$oft Query via Psqlodbc. We never tried it under psql.
Today it appeared broken (some trickery on the Excel worksheet seems to
have fix that) and for the first time I tried to run it from the command
line. I never managed, psql always breaks in the same way:

cb=> \i /home/alessio/check.sql
SELECT peergroup.id, convid.name, convid.bb_cb, convid.bb_eq,
convdaily.date, convdaily.paritygross, convdaily.bidprice,
convdaily.askprice, convdaily.stockprice, tradingsignal.job,
tradingsignal.buysell, tradingsignal.delta, tradingsignal.premium
FROM convdaily convdaily, convid convid, peergroup peergroup,
tradingsignal tradingsignal
WHERE convid.id = peergroup.id AND peergroup.id = tradingsignal.id AND
convdaily.id = peergroup.id AND convdaily.date = tradingsignal.date AND
((tradingsignal.job=6) AND (tradingsignal.buysell='B') AND
(peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR
(tradingsignal.job=7) AND
(tradingsignal.buysell='B') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000') OR (tradingsignal.job=8) AND
(tradingsignal.buysell='B') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000') OR (tradingsignal.job=210) AND
(tradingsignal.buysell='B') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000') OR (tradingsignal.job=211) AND
(tradingsignal.buysell='B') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000') OR (tradingsignal.job=6) AND
(tradingsignal.buysell='U') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000') OR (tradingsignal.job=7) AND
(tradingsignal.buysell='U') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000') OR (tradingsignal.job=8) AND
(tradingsignal.buysell='U') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000') OR (tradingsignal.job=210) AND
(tradingsignal.buysell='U') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000') OR (tradingsignal.job=211) AND
(tradingsignal.buysell='U') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000') OR (tradingsignal.job=212) AND
(tradingsignal.buysell='B') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000') OR (tradingsignal.job=212) AND
(tradingsignal.buysell='U') AND (peergroup.pgid=45) AND
(convdaily.date='21-02-2000'))
ORDER BY peergroup.id, tradingsignal.job;
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is
impossible.  Terminating.

Some info on the tables:

cb=> select count(*) from convdaily; count
-------
2260691
(1 row)

cb=> select count(*) from convid;
count
-----3666
(1 row)

cb=> select count(*) from peergroup;
count
----- 730
(1 row)

cb=> select count(*) from tradingsignal;count
------
221374
(1 row)

Is it a problem on the backend or on psql? Is simply the query using a
too big 4-table join? I am wondering, since postmaster.log states 

FATAL 1:  Memory exhausted in AllocSetAlloc()

We are running PostgreSQL 6.5.2 on alphaev6-dec-osf4.0f, compiled by cc.

Any idea would be greatly appreciated.
Thanks in advance.

-- 
Alessio F. Bragadini        alessio@albourne.com
APL Financial Services        http://www.sevenseas.org/~alessio
Nicosia, Cyprus             phone: +357-2-750652

"It is more complicated than you think"    -- The Eighth Networking Truth from RFC 1925

Alessio Bragadini <alessio@albourne.com> writes:
> The following (rather long) query run fine for some time when executed
> through Micro$oft Query via Psqlodbc. We never tried it under psql.
> Today it appeared broken (some trickery on the Excel worksheet seems to
> have fix that) and for the first time I tried to run it from the command
> line. I never managed, psql always breaks in the same way:

Try doingset ksqo = 'on';
in psql before you run the query.  I think the ODBC driver does that for
you automatically.

The regular 6.5 optimizer tends to blow up when faced with large
OR-of-ANDs WHERE clauses.  7.0 will be a lot better about it...
but in the meantime you need the KSQO hack.
        regards, tom lane

Tom Lane wrote:

> Try doing
>         set ksqo = 'on';
> in psql before you run the query.  I think the ODBC driver does that for
> you automatically.

Thanks for the suggestion, unfortunately I get the same behaviour.

-- 
Alessio F. Bragadini        alessio@albourne.com
APL Financial Services        http://www.sevenseas.org/~alessio
Nicosia, Cyprus             phone: +357-2-750652

"It is more complicated than you think"    -- The Eighth Networking Truth from RFC 1925