Query hangs when getting too complex... - Mailing list pgsql-general

Hi all:

    I have here a query that hangs everytime I try to execute it, even
though IMO it isn't *that* complicated (nor is the database *that* big).
The relevant tables are:


                   Table "archivos"
     Attribute     |           Type           | Modifier
-------------------+--------------------------+----------
 idarchivo         | integer                  | not null
 codigofoto        | numeric(18,0)            |
 idsexo            | integer                  |
 idprovincia       | integer                  |
 idccaa            | integer                  |
 idpais            | integer                  |
 place             | character varying(255)   |
 fechafoto         | timestamp with time zone |
 nino              | numeric(1,0)             | not null
 joven             | numeric(1,0)             | not null
 adulto            | numeric(1,0)             | not null
 anciano           | numeric(1,0)             | not null
 posado            | numeric(1,0)             | not null
 title             | character varying(255)   |
 description       | character varying(1000)  |
 limitations       | character varying(500)   |



           Table "archivos_fulltext_en"
  Attribute  |           Type           | Modifier
-------------+--------------------------+----------
 idarchivo   | integer                  |
 title       | character varying(255)   |
 description | character varying(1000)  |
 place       | character varying(255)   |
 country     | character varying(255)   |
 state       | character varying(255)   |
 province    | character varying(255)   |
 sex         | character varying(50)    |
 codigofoto  | numeric(18,0)            |
 fechafoto   | timestamp with time zone |
 revision    | integer                  |
 race        | character varying(4096)  |
 tesauro_en  | text                     |
 class       | character varying(4096)  |
 personaje   | character varying(4096)  |


    Where "archivos" is, let's say, the "real" table, and it contains
information about photographs. "archivos_fulltext_en" is a denormalized
version that I created to do full text searches, with all the text
fields in "archivos", plus some other fields ("personaje", "race", etc.)
that were originally stored in other tables (because they required
many-to-one relationships).
    And the query that is giving me problems is:


    SELECT count(idarchivo)  FROM archivos  WHERE
    revision <= 3 AND (EXISTS
    (SELECT idarchivo FROM archivos_fulltext_en WHERE revision <= 3
    AND archivos_fulltext_en.idarchivo=archivos.idarchivo
    AND LOWER(TRANSLATE(archivos_fulltext_en.title ||
archivos_fulltext_en.description || archivos_fulltext_en.place ||
archivos_fulltext_en.province || archivos_fulltext_en.state ||
archivos_fulltext_en.country || archivos_fulltext_en.race ||
archivos_fulltext_en.sex || archivos_fulltext_en.class ||
archivos_fulltext_en.tesauro_en, '[áéíóúÁÉÍÓÚ]', '[aeiouAEIOU]')) LIKE
'%actress%'))
    AND idsexo=2 AND archivos.joven = 1 AND posado=1
    AND fechafoto BETWEEN '01/1/1976' AND '19/12/2001'

    When performing this query, Postgres just hangs there, and leaves the
following in the log:

Dec 19 13:57:18 master postgres[29995]: [381] DEBUG:
StartTransactionCommand
Dec 19 13:57:18 master postgres[29995]: [382-1] DEBUG:  query:  SELECT
count(idarchivo)  FROM archivos  WHERE
Dec 19 13:57:18 master postgres[29995]: [382-2]  revision <= 3 AND
Dec 19 13:57:18 master postgres[29995]: [382-3]  (EXISTS (SELECT
idarchivo FROM archivos_fulltext_en WHERE revision
Dec 19 13:57:18 master postgres[29995]: [382-4] <= 3 AND
archivos_fulltext_en.idarchivo=archivos.idarchivo AND
LOWER(TRANSLATE(archivos_fulltext_en.title ||
Dec 19 13:57:18 master postgres[29995]: [382-5]
archivos_fulltext_en.description || archivos_fulltext_en.place ||
archivos_fulltext_en.province || archivos_fulltext_en.state
Dec 19 13:57:18 master postgres[29995]: [382-6]  ||
archivos_fulltext_en.country || archivos_fulltext_en.race ||
archivos_fulltext_en.sex || archivos_fulltext_en.class ||
Dec 19 13:57:18 master postgres[29995]: [382-7]
archivos_fulltext_en.tesauro_en, '[áéíóúÁÉÍÓÚ]', '[aeiouAEIOU]')) LIKE
'%actress%'))
Dec 19 13:57:18 master postgres[29995]: [382-8] AND  idsexo=2 AND
archivos.joven = 1 AND
Dec 19 13:57:18 master postgres[29995]: [382-9] posado=1 AND fechafoto
BETWEEN '01/1/1976' AND '19/12/2001';
Dec 19 13:57:18 master postgres[29995]: [383] DEBUG:  parse tree:


    And it just stays there forever. "ps -auxwww" shows the backend as
"idle":

    postgres 29995  6.0  5.8 57108 53808 pts/1   R    13:31   1:37
postgres: postgres covermaster [local] idle

    And eventually I have to abort it and even kill the process by hand
(kill -9)

    Now: the interesting thing is that if I remove just one of the
conditions in the above query, it works. No matter which one:
"idsexo=2", "posado=1", "fechafoto BETWEEN '01/1/1976' AND
'19/12/2001'"... it just works.
    As for the number of records in each table:


covermaster=# select count(idarchivo) from archivos;
 count
-------
 27340
(1 row)

covermaster=# select count(idarchivo) from archivos_fulltext_en;
 count
-------
  4249

    The difference is due to the fact that, so far, the only photos that
are online are the ones where revision <= 3, so I just denormalized the
records that fulfilled that condition:

covermaster=# select count(idarchivo) from archivos where revision <= 3;
 count
-------
  4249
(1 row)


    All this is taking place in a Dual Athlon server with 1Gb. RAM, running
Red Hat 6.2 and Postgres 7.1.2 (installed from RPMS). I have set the
postgres.conf file to:

    sort_mem = 8192
    shared_buffers = 6144

    (And have increased the kernel shared memory to:

    kernel.shmall=67108864
    kernel.shmmax=67108864).

    All the relevant fields in the above tables (revision, idsexo, joven,
etc.) are indexed, and I ran VACUUM ANALYZE right before the query
without it giving any errors.
    Any ideas? The above query isn't even the most complex one that I'll be
performing; eventually there might be 5 or 6 more conditions to be
added... but of course, that can't happen if I don't solve this first.


                        Paulo Jan.
                        DDnet.