Query hangs when getting too complex...

From: Paulo Jan <admin(at)digital(dot)ddnet(dot)es>
To: pgsql-general(at)postgresql(dot)org
Subject: Query hangs when getting too complex...
Date: 2001-12-19 13:20:26
Message-ID: 3C20941A.44021ECF@digital.ddnet.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Koenig 2001-12-19 14:13:50 Re: Way to use count() and LIMIT?
Previous Message Giorgio Ponza 2001-12-19 13:03:50 Stupid question on INDEXES