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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Mon, 24 Nov 2003, Javier Carlos wrote:

As a question, does the query work if you SET geqo=off; or SET
geqo_threshold=<some higher number, say 20>?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Mon, 24 Nov 2003, Javier Carlos wrote:

What does your postmaster log say? There should be something in there.
I also forgot to ask for EXPLAIN output under normal conditions and with
geqo off (although it's possible that it will fail as well for the latter)


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Quoting Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com>:

I tried with setting geqo=off and It didn't work. Then I set
geqo_threshold=20, and neither. In both the result was:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>


Javier

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Javier Carlos <fjcarlos (AT) correo (DOT) insp.mx> writes:
Hm. Are you using the default GEQO settings, or something custom?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Quoting Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com>:


Quoting Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com>:

I did the following and it solved the problem:

* The database with problems was one that I had migrated from PostgreSQL 7.3.4
using pg_dumpall.

* I recreate the database and made all the normalization process again; in this
process I used other databases migrated from PostgreSQL 7.3.4 using pg_dump.

* Then I did a vaccumm -f -a and ran the large query again, and it works!!

* This was the query (it took aprox. 1.5 minutes to print the results):

SELECT
A.id_hogar,s01p01,s01p02,s01p03,s01p04,s01p05,s01p 06,s01p07,s01p08,s01p09,s01p10,s01p1101,s01p1102,s 01p12,s01p13,s01p14,s01p15,s01p16,s01p17,s01p18,s0 1p19,s01p20,s01p21,s01p23,s01p24,s01p25,s01p26,s02 p01,s02p02,s02p03,s03p02,s04p01,s04p02,s04p03,s04p 04,s04p05,s04p07,s04p08,s04p09,s04p1001,s04p1002,s 04p1003,s04p11,s04p12,s04p13,s09p01,s10p01,s11p01, s12p01,s13p01,s14p01,s15p01,s16p01,s17p01,s18p0201 ,s18p0202,s18p0203,s18p0204,s18p0205,s18p0206,s18p 0207,s18p0208,s18p0209,s18p0210,s18p0211,s18p0212, s18p0213,s18p0214,s18p0215,s18p0216,s18p0217,s18p0 218,s18p0219,s18p0220,s18p0221,s18p0301,s18p0302,s 18p0303,s18p0304,s18p0305,s18p0306,s18p0307,s18p03 08,s18p0309,s18p0310,s18p0311,s18p0312,s18p0313,s1 8p0314,s18p0315,s18p0316,s18p0317,s18p0318,s18p031 9,s18p0320,s18p0321,s19p0201,s19p0202,s19p0203,s19 p0204,s19p0205,s19p0206,s19p0207,s19p0208,s19p0209 ,s19p0210,s19p0211,s19p0212,s19p0213,s19p0214,s19p 0215,s19p0216,s19p0217,s19p0218,s19p0219,s19p0301, s19p0302,s19p0303,s19p0304,s19p0305,s19!
p0306,s19p0307,s19p0308,s19p0309,s19p0310,s19p0311 ,s19p0312,s19p0313,s19p0314,s19p0315,s19p0316,s19p 0317,s19p0318,s19p0319,s19p04,s19p0501,s19p0502,s1 9p0503,s19p0504,s19p0505,s19p0506,s19p0507,s19p050 8,s19p0509,s19p0510,s19p0511,s19p0512,s19p0513,s19 p0514,s19p0515,s19p0516,s19p0517,s19p0518,s19p0519 ,s19p0601,s19p0602,s19p0603,s19p0604,s19p0605,s19p 0606,s19p0607,s19p0608,s19p0609,s19p0610,s19p0611, s19p0612,s19p0613,s19p0614,s19p0615,s19p0616,s19p0 617,s19p0618,s19p0619,s19p0801,s19p0802,s19p0803,s 19p0804,s19p0805,s19p0806,s19p0807,s19p0808,s19p08 09,s19p0810,s19p0811,s19p0812,s19p0813,s19p0814,s1 9p0815,s19p0816,s19p0817,s19p0818,s19p0901,s19p090 2,s19p0903,s19p0904,s19p0905,s19p0906,s19p0907,s19 p0908,s19p0909,s19p0910,s19p0911,s19p0912,s19p0913 ,s19p0914,s19p0915,s19p0916,s19p0917,s19p0918,s19p 10,s19p1101,s19p1102,s19p1103,s19p1104,s19p1105,s1 9p1106,s19p1107,s19p1108,s19p1109,s19p1110,s19p111 1,s19p1112,s19p1113,s19p1114,s19p1115,s19p1116,s19 p1117,s19p1118,s19p1201,s19p1202,s19p1!
203,s19p1204,s19p1205,s19p1206,s19p1207,s19p1208,s 19p1209,s19p!
1210,s19
p1211,s19p1212,s19p1213,s19p1214,s19p1215,s19p1216 ,s19p1217,s19p1218,s19p13,s19p14,s19p1601,s19p1602 ,s19p1603,s19p1604,s19p1605,s19p1606,s19p1701,s19p 1702,s19p1703,s19p1704,s19p1705,s19p1706,s19p18,s1 9p1901,s19p1902,s19p1903,s19p1904,s19p1905,s19p190 6,s19p2001,s19p2002,s19p2003,s19p2004,s19p2005,s19 p2006,s20p0201,s20p0202,s20p0203,s20p0204,s20p0205 ,s20p0206,s20p0207,s20p0301,s20p0302,s20p0303,s20p 0304,s20p0305,s20p0306,s20p0307,s20p04,s20p0501,s2 0p0502,s20p0503,s20p0504,s20p0505,s20p0506,s20p050 7,s20p0601,s20p0602,s20p0603,s20p0604,s20p0605,s20 p0606,s20p0607,s21p0201,s21p0202,s21p0203,s21p0204 ,s21p0205,s21p0206,s21p0207,s21p0301,s21p0302,s21p 0303,s21p0304,s21p0305,s21p0306,s21p0307,s21p04,s2 1p0501,s21p0502,s21p0503,s21p0504,s21p0505,s21p050 6,s21p0507,s21p0601,s21p0602,s21p0603,s21p0604,s21 p0605,s21p0606,s21p0607,s22p0201,s22p0202,s22p0203 ,s22p0204,s22p0205,s22p0206,s22p0207,s22p0301,s22p 0302,s22p0303,s22p0304,s22p0305,s22p0306,s22p0307, s22p04,s22p0501,s22p0502,s22p0503,s22p0!
504,s22p0505,s22p0506,s22p0507,s22p0601,s22p0602,s 22p0603,s22p0604,s22p0605,s22p0606,s22p0607,s22p07 ,s23p01,s23p02,s23p03,s23p04,s23p05,s23p06,s23p07, s23p08,s23p09,s23p10,s23p11,s23p12,s23p13,s24p01,s 24p02,s24p03,s24p04,s24p05,s24p07,s25p0101,s25p010 2,s25p0103,s25p0104,s25p0105,s25p0106,s25p0107,s25 p0108,s25p0109,s25p0110,s25p0111,s25p0112,s25p0113 ,s25p0114,s25p0115,s25p0116,s25p0117,s25p0201,s25p 0202,s25p0203,s25p0204,s25p0205,s25p0206,s25p0207, s25p0208,s25p0209,s25p0210,s25p0211,s25p0212,s25p0 213,s25p0214,s25p0215,s25p0216,s25p0217,s25p0301,s 25p0302,s25p0303,s25p0304,s25p0305,s25p0306,s25p03 07,s25p0308,s25p0309,s25p0310,s25p0311,s25p0312,s2 5p0313,s25p0314,s25p0315,s25p0316,s25p0317,s25p04, s25p05,s26p01,s26p02,s26p03,s26p04,s26p05,s26p06,s 26p07,s26p08,s27p01,s27p02,s28p0101,s28p0102,s29p0 101,s29p0102,s29p0103,s29p0104,s29p0105,s29p0201,s 29p0202,s29p0203,s29p0204,s29p0205,s29p0206,s29p02 07,s29p0208,s30p0301,s30p0302,s30p0303,s30p0401,s3 0p0402,s30p0403,s30p05,s30p06,s31p01,s!
31p02,s31p03,s32p0104,s32p0204,s32p0304,s32p0404,s 32p0504,s32p!
0604,s32
p0704,s32p0804,s32p0904,s33p01,s33p0201,s33p03,s33 p0501,s33p0502,s33p06,s33p0701,s33p0702,s33p08,s33 p0901,s33p10,s33p1201,s33p1202,s33p13,s33p1401,s33 p1402,s33p15,s33p1601,s33p17,s33p1901,s33p1902,s33 p20,s33p2101,s33p2102,s34p01,s34p0201,s34p03,s34p0 501,s34p0502,s34p06,s34p0701,s34p0702,s34p08,s34p0 901,s34p10,s34p1201,s34p1202,s34p13,s34p1401,s34p1 402,s34p15,s34p1601,s34p17,s34p1901,s34p1902,s34p2 0,s34p2101,s34p2102
FROM tbl_caracteristicas_viv
A,tbl_residencia_viv,tbl_solicitud_inc,tbl_filtros ,tbl_instit_hacia_hogar,tbl_gasto_semanal,tbl_gast o_mensual,tbl_gasto_trimestral,tbl_gasto_anual,tbl _gasto_servicios,tbl_negocios_hogar,tbl_bienes_hog ar,tbl_toma_decisiones,tbl_siniestros,tbl_percepci ones,tbl_violencia,tbl_participacion_comunitaria,t bl_jefe_hogar,tbl_pareja_jefe_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 AND A.id_hogar=tbl_toma_decisiones.id_hogar
AND A.id_hogar=tbl_siniestros.id_hogar AND A.id_hogar=tbl_percepciones.id_hogar
AND A.id_hogar=tbl_violencia.id_hogar AND
A.id_hogar=tbl_participacion_comunitaria.id_hogar AND
A.id_hogar=tbl_jefe_hogar.id_hogar AND A.id_hogar=tbl_pareja_jefe_hogar.id_hogar
ORDER BY A.id_hogar


So I think that the problem was the pg_dumpall of PostgreSQL 7.3.4. I think
that corrupted some indexes.


Thanks to all of you.

Javier


---------------------------------------
Instituto Nacional de Salud Pública
Evaluación Oportunidades - DataWeb
http://evaloportunidades.insp.mx

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Quoting Tom Lane <tgl (AT) sss (DOT) pgh.pa.us>:

Hi,

I'm using the default GEQO settings:

#geqo = true
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0
#geqo_selection_bias = 2.0

Yesterday I solved the problem recreating the database and remigrating all
the information. I think that the problem was the pg_dumpall of PostgreSQL
7.3.4, maybe corrupted some indexes.

Regards,

Javier



---------------------------------------
Instituto Nacional de Salud Pública
Evaluación Oportunidades - DataWeb
http://evaloportunidades.insp.mx

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend