Oleg Broytmann <phd@sun.med.ru> wrote:
> I rewrote my 4-tables join to use subselects:
>
> SELECT DISTINCT subsec_id FROM positions
> WHERE pos_id IN
> (SELECT DISTINCT pos_id
> FROM central
> WHERE shop_id IN
> (SELECT shop_id FROM shops
> WHERE distr_id IN
> (SELECT distr_id FROM districts
> WHERE city_id = 2)
> )
> )
> ;
>
> This does not work, either - postgres loops forever, until I cancel
> psql.
Yes, it's very ancient bug I knew it from time when subselects fisrt
appeared.
> This finally solves my problem, but I need to pass a long way to find
> that postgres cannot handle such not too complex joins and subselects.
Postgres cannot quick handle even simpler subselect on small enough
base (~ 1000 records) and when subselect return only one value.
Executing query like "SELECT ... WHERE ... IN ( SELECT ..."
Postgres eats memory and takes too long time too complete.
When it eats to many memory FreeBSD killed it.
The single way to resolve it is to rewrite subselect using EXISTS.
With best regards,
Igor Sysoev
http://www.nitek.ru/~igor/