Subselects running out of memory - Mailing list pgsql-general

Hi all:

    I'm working on a database of photographs, searchable by several
criteria. The tables are roughly something like this:


    create table fotos (id serial, foto varchar(32), es_titulo
varchar(255), en_titulo varchar(255), es_observaciones varchar(2560),
en_observaciones varchar(2560), es_textoref varchar(32), en_textoref
varchar(32), redactor varchar(44), fotografo1 int4 references
fotografos, fotografo2 int4, fotografo3 int4, fecha date, color bool,
disposicion varchar(10), precio_especial bool, es_restricciones
varchar(3072), en_restricciones varchar(3072), exclusivo bool clasif
char(6));

    create table lugar_foto (foto_id int4 references fotos (id), pais int4
references paises (id), comunidad varchar(30), provincia varchar(24),
ciudad varchar(30), es_lugar varchar(384), en_lugar varchar(384));


    (Actually there are more tables, but for the example this will do.
"fotos" keeps the photographs, while "lugar_foto" keeps grographical
information associated with each photo (where it was taken, etc.)).
    As I said, the users must be able to search for photos using a
Web-based form: they type a keyword, and all the photos associated with
it have to show up. The problem I'm having is that, if I write the query
in a certain way, it works, while if I phrase it with some of the
conditions in a different order, it runs out of memory. For example (not
the real query used in the app, just an example):

    SELECT id, es_titulo FROM fotos WHERE (es_titulo ~ '[SEARCH TEXT
HERE]') OR EXISTS (SELECT foto_id FROM lugar_foto WHERE
(lugar_foto.comunidad || lugar_foto.provincia || lugar_foto.ciudad ||
lugar_foto.es_lugar) ~ '[SEARCH TEXT HERE]' AND foto_id=id);

    Runs out of memory, but if I put the "foto_id=id2" before, it works,
like in:


    SELECT id, es_titulo FROM fotos WHERE (es_titulo ~ '[SEARCH TEXT
HERE]') OR EXISTS (SELECT foto_id FROM lugar_foto WHERE foto_id=id AND
(lugar_foto.comunidad || lugar_foto.provincia || lugar_foto.ciudad ||
lugar_foto.es_lugar) ~ '[SEARCH TEXT HERE]');

    Is there any reason for this? EXPLAIN shows the same cost for both
queries.
    Also, I was thinking of using a join instead of subselects to do this.
I tried using:

    SELECT fotos.id, fotos.es_titulo FROM fotos, lugar_foto WHERE
(fotos.es_titulo ~ '[SEARCH TEXT HERE]') OR ((lugar_foto.comunidad ||
lugar_foto.provincia) ~ '[SEARCH TEXT HERE]' AND
lugar_foto.foto_id=fotos.id);

    But it also ran out of memory. Am I missing something? Is the above
join correctly written? What would be the best way to do a query of this
kind?
    BTW, I am using Postgres 7.0.2, and the database has around 2400
entries.


                        Paulo Jan.
                        DDnet.