Re: Problems with unconstrained join

Not joining two tables in a where clause can take down a database if the
tables have enough rows or the database server is not overly powerful.

Perhaps if you limit the # of rows back (I think the Postgres command is
LIMIT ## where ## is the number of rows - Oracle uses RowNum).

David.
----- Original Message -----
From: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, March 06, 2002 6:21 AM
Subject: [GENERAL] Problems with unconstrained join

> Something rather weird happens if you ask for an unlimited join that
> gives too many rows:
>
> bray=# select count(*) from product;
> count
> -------
> 5482
> (1 row)
>
> bray=# select count(*) from stock;
> count
> -------
> 5482
> (1 row)
>
> [ Cartesian product = 30,052,324 rows ]
>
> bray=# select p.id from product as p, stock;
> server sent data ("D" message) without prior row description ("T" message)
> server sent data ("D" message) without prior row description ("T" message)
> server sent data ("D" message) without prior row description ("T" message)
> ---[lots of those]...
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent data ("D" message) without prior row description ("T" message)
> 5002D2DH5002D03D H5003D003DD5003D4DH5004D04D H5004D005DD5005D5DH5005D06D
H5006D006DD5006D7DH5007D07D H5007D008DD5008D8DH5008D09D
H5009D009DD5009D0DH5010D10D H5010D010DD5011D1DH5011D12D
H5012D012DD5012D3DH5013D13D H5013D013DD5014D4DH5014D15D
H5015D015DD5015D6DH5016D16D H5016D01
> ...[quite a lot more of that]...
> Cancel request sent
> Cancel request sent
>
> I used ctrl-C to cancel, but the query did not terminate. The backend
> was listed as idle, so I guess that psql had choked. I had to kill it
> off.
>
> The PostgreSQL version is 7.2.
>
> Obviously psql or the backend was being asked to cope with too much
> data, but is there any way to handle the situation more cleanly?
>
> --
> Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
> Isle of Wight http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
>
> "The LORD is my light and my salvation; whom shall I
> fear? the LORD is the strength of my life; of whom
> shall I be afraid?" Psalms 27:1
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster