SELECT with MANY tables

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : F. Javier Carlos Rivera
Your email address : fjcarlos ( at ) correo ( dot ) insp ( dot ) mx

System Configuration
----------------------
Architecture (example: Intel Pentium) : Intel Pentium 4

Operating System (example: Linux 2.0.26 ELF) : Debian GNU/Linux 3.0 2.4.21

RAM : 256 MB

PostgreSQL version (example: PostgreSQL-6.3.2) : PostgreSQL-7.4

Compiler used (example: gcc 2.7.2) : 2.95.4

Please enter a FULL description of your problem:
-------------------------------------------------
When I make a SELECT with many tables (more than 12), postgresql eats all my
%CPU and I've waited more than 1 hour and stays the same. The weird thing is
that with 10 tables the same select with the same joins only takes about 5
seconds. First I thought that It was a problem related with one specific table,
but I've changed in the SELECT the tables and while the number of tables remains
less than 12 all is ok.

With postgresql 7.3.4 I didn't have this problem, although the performance of
the queries was slower than with 7.4.

Thanks,

Javier

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
-----------------------------------------------------------------------
*** This query works great (less than 5 seconds):
SELECT A.id_hogar
FROM tbl_caracteristicas_viv A,
tbl_residencia_viv,
tbl_solicitud_inc,
tbl_filtros,
tbl_instit_hacia_hogar,
tbl_gasto_semanal,
tbl_gasto_mensual,
tbl_gasto_trimestral,
tbl_gasto_anual,
tbl_gasto_servicios,
tbl_negocios_hogar,
tbl_bienes_hogar
WHERE
A.id_hogar=tbl_residencia_viv.id_hogar AND
A.id_hogar=tbl_solicitud_inc.id_hogar AND
A.id_hogar=tbl_filtros.id_hogar AND
A.id_hogar=tbl_instit_hacia_hogar.id_hogar AND
A.id_hogar=tbl_gasto_semanal.id_hogar AND
A.id_hogar=tbl_gasto_mensual.id_hogar AND
A.id_hogar=tbl_gasto_trimestral.id_hogar AND
A.id_hogar=tbl_gasto_anual.id_hogar AND
A.id_hogar=tbl_gasto_servicios.id_hogar AND
A.id_hogar=tbl_negocios_hogar.id_hogar AND
A.id_hogar=tbl_bienes_hogar.id_hogar

*** Then I add one more table ("tbl_toma_decisiones") and query didn't work
(more than 30 minutes and nothing :( ):
SELECT A.id_hogar
FROM tbl_caracteristicas_viv A,
tbl_residencia_viv,
tbl_solicitud_inc,
tbl_filtros,
tbl_instit_hacia_hogar,
tbl_gasto_semanal,
tbl_gasto_mensual,
tbl_gasto_trimestral,
tbl_gasto_anual,
tbl_gasto_servicios,
tbl_negocios_hogar,
tbl_bienes_hogar,
tbl_toma_decisiones
WHERE
A.id_hogar=tbl_residencia_viv.id_hogar AND
A.id_hogar=tbl_solicitud_inc.id_hogar AND
A.id_hogar=tbl_filtros.id_hogar AND
A.id_hogar=tbl_instit_hacia_hogar.id_hogar AND
A.id_hogar=tbl_gasto_semanal.id_hogar AND
A.id_hogar=tbl_gasto_mensual.id_hogar AND
A.id_hogar=tbl_gasto_trimestral.id_hogar AND
A.id_hogar=tbl_gasto_anual.id_hogar AND
A.id_hogar=tbl_gasto_servicios.id_hogar AND
A.id_hogar=tbl_negocios_hogar.id_hogar AND
A.id_hogar=tbl_bienes_hogar.id_hogar AND
A.id_hogar=tbl_toma_decisiones.id_hogar

I thought that the problem was the table "tbl_toma_decisiones", but then I
omitted a table in the select and did this query and it worked (less than 5
seconds):

SELECT A.id_hogar
FROM tbl_caracteristicas_viv A,
tbl_residencia_viv,
tbl_solicitud_inc,
tbl_filtros,
tbl_instit_hacia_hogar,
tbl_gasto_semanal,
tbl_gasto_mensual,
tbl_gasto_trimestral,
tbl_gasto_anual,
tbl_gasto_servicios,
tbl_negocios_hogar,
tbl_toma_decisiones
WHERE
A.id_hogar=tbl_residencia_viv.id_hogar AND
A.id_hogar=tbl_solicitud_inc.id_hogar AND
A.id_hogar=tbl_filtros.id_hogar AND
A.id_hogar=tbl_instit_hacia_hogar.id_hogar AND
A.id_hogar=tbl_gasto_semanal.id_hogar AND
A.id_hogar=tbl_gasto_mensual.id_hogar AND
A.id_hogar=tbl_gasto_trimestral.id_hogar AND
A.id_hogar=tbl_gasto_anual.id_hogar AND
A.id_hogar=tbl_gasto_servicios.id_hogar AND
A.id_hogar=tbl_negocios_hogar.id_hogar AND
A.id_hogar=tbl_toma_decisiones.id_hogar

* Note: In the above queries I omitted all the COLUMNS in the select for
readability.

-------------------------------------------------
http://www.insp.mx