SELECT with MANY tables - Mailing list pgsql-bugs

============================================================================
                        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