SELECT with MANY tables

On Mon, 24 Nov 2003, Javier Carlos wrote:

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.

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

Quoting Stephan Szabo <sszabo@megazone.bigpanda.com>:

On Mon, 24 Nov 2003, Javier Carlos wrote:

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.

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

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

On Mon, 24 Nov 2003, Javier Carlos wrote:

Quoting Stephan Szabo <sszabo@megazone.bigpanda.com>:

On Mon, 24 Nov 2003, Javier Carlos wrote:

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.

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

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.

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)

Quoting Stephan Szabo <sszabo@megazone.bigpanda.com>:

On Mon, 24 Nov 2003, Javier Carlos wrote:

Quoting Stephan Szabo <sszabo@megazone.bigpanda.com>:

On Mon, 24 Nov 2003, Javier Carlos wrote:

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.

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

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.

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)

Quoting Stephan Szabo <sszabo@megazone.bigpanda.com>:

On Mon, 24 Nov 2003, Javier Carlos wrote:

Quoting Stephan Szabo <sszabo@megazone.bigpanda.com>:

On Mon, 24 Nov 2003, Javier Carlos wrote:

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.

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

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.

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)

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,s01p06,s01p07,s01p08,s01p09,s01p10,s01p1101,s01p1102,s01p12,s01p13,s01p14,s01p15,s01p16,s01p17,s01p18,s01p19,s01p20,s01p21,s01p23,s01p24,s01p25,s01p26,s02p01,s02p02,s02p03,s03p02,s04p01,s04p02,s04p03,s04p04,s04p05,s04p07,s04p08,s04p09,s04p1001,s04p1002,s04p1003,s04p11,s04p12,s04p13,s09p01,s10p01,s11p01,s12p01,s13p01,s14p01,s15p01,s16p01,s17p01,s18p0201,s18p0202,s18p0203,s18p0204,s18p0205,s18p0206,s18p0207,s18p0208,s18p0209,s18p0210,s18p0211,s18p0212,s18p0213,s18p0214,s18p0215,s18p0216,s18p0217,s18p0218,s18p0219,s18p0220,s18p0221,s18p0301,s18p0302,s18p0303,s18p0304,s18p0305,s18p0306,s18p0307,s18p0308,s18p0309,s18p0310,s18p0311,s18p0312,s18p0313,s18p0314,s18p0315,s18p0316,s18p0317,s18p0318,s18p0319,s18p0320,s18p0321,s19p0201,s19p0202,s19p0203,s19p0204,s19p0205,s19p0206,s19p0207,s19p0208,s19p0209,s19p0210,s19p0211,s19p0212,s19p0213,s19p0214,s19p0215,s19p0216,s19p0217,s19p0218,s19p0219,s19p0301,s19p0302,s19p0303,s19p0304,s19p0305,s19!
p0306,s19p0307,s19p0308,s19p0309,s19p0310,s19p0311,s19p0312,s19p0313,s19p0314,s19p0315,s19p0316,s19p0317,s19p0318,s19p0319,s19p04,s19p0501,s19p0502,s19p0503,s19p0504,s19p0505,s19p0506,s19p0507,s19p0508,s19p0509,s19p0510,s19p0511,s19p0512,s19p0513,s19p0514,s19p0515,s19p0516,s19p0517,s19p0518,s19p0519,s19p0601,s19p0602,s19p0603,s19p0604,s19p0605,s19p0606,s19p0607,s19p0608,s19p0609,s19p0610,s19p0611,s19p0612,s19p0613,s19p0614,s19p0615,s19p0616,s19p0617,s19p0618,s19p0619,s19p0801,s19p0802,s19p0803,s19p0804,s19p0805,s19p0806,s19p0807,s19p0808,s19p0809,s19p0810,s19p0811,s19p0812,s19p0813,s19p0814,s19p0815,s19p0816,s19p0817,s19p0818,s19p0901,s19p0902,s19p0903,s19p0904,s19p0905,s19p0906,s19p0907,s19p0908,s19p0909,s19p0910,s19p0911,s19p0912,s19p0913,s19p0914,s19p0915,s19p0916,s19p0917,s19p0918,s19p10,s19p1101,s19p1102,s19p1103,s19p1104,s19p1105,s19p1106,s19p1107,s19p1108,s19p1109,s19p1110,s19p1111,s19p1112,s19p1113,s19p1114,s19p1115,s19p1116,s19p1117,s19p1118,s19p1201,s19p1202,s19p1!
203,s19p1204,s19p1205,s19p1206,s19p1207,s19p1208,s19p1209,s19p!
1210,s19
p1211,s19p1212,s19p1213,s19p1214,s19p1215,s19p1216,s19p1217,s19p1218,s19p13,s19p14,s19p1601,s19p1602,s19p1603,s19p1604,s19p1605,s19p1606,s19p1701,s19p1702,s19p1703,s19p1704,s19p1705,s19p1706,s19p18,s19p1901,s19p1902,s19p1903,s19p1904,s19p1905,s19p1906,s19p2001,s19p2002,s19p2003,s19p2004,s19p2005,s19p2006,s20p0201,s20p0202,s20p0203,s20p0204,s20p0205,s20p0206,s20p0207,s20p0301,s20p0302,s20p0303,s20p0304,s20p0305,s20p0306,s20p0307,s20p04,s20p0501,s20p0502,s20p0503,s20p0504,s20p0505,s20p0506,s20p0507,s20p0601,s20p0602,s20p0603,s20p0604,s20p0605,s20p0606,s20p0607,s21p0201,s21p0202,s21p0203,s21p0204,s21p0205,s21p0206,s21p0207,s21p0301,s21p0302,s21p0303,s21p0304,s21p0305,s21p0306,s21p0307,s21p04,s21p0501,s21p0502,s21p0503,s21p0504,s21p0505,s21p0506,s21p0507,s21p0601,s21p0602,s21p0603,s21p0604,s21p0605,s21p0606,s21p0607,s22p0201,s22p0202,s22p0203,s22p0204,s22p0205,s22p0206,s22p0207,s22p0301,s22p0302,s22p0303,s22p0304,s22p0305,s22p0306,s22p0307,s22p04,s22p0501,s22p0502,s22p0503,s22p0!
504,s22p0505,s22p0506,s22p0507,s22p0601,s22p0602,s22p0603,s22p0604,s22p0605,s22p0606,s22p0607,s22p07,s23p01,s23p02,s23p03,s23p04,s23p05,s23p06,s23p07,s23p08,s23p09,s23p10,s23p11,s23p12,s23p13,s24p01,s24p02,s24p03,s24p04,s24p05,s24p07,s25p0101,s25p0102,s25p0103,s25p0104,s25p0105,s25p0106,s25p0107,s25p0108,s25p0109,s25p0110,s25p0111,s25p0112,s25p0113,s25p0114,s25p0115,s25p0116,s25p0117,s25p0201,s25p0202,s25p0203,s25p0204,s25p0205,s25p0206,s25p0207,s25p0208,s25p0209,s25p0210,s25p0211,s25p0212,s25p0213,s25p0214,s25p0215,s25p0216,s25p0217,s25p0301,s25p0302,s25p0303,s25p0304,s25p0305,s25p0306,s25p0307,s25p0308,s25p0309,s25p0310,s25p0311,s25p0312,s25p0313,s25p0314,s25p0315,s25p0316,s25p0317,s25p04,s25p05,s26p01,s26p02,s26p03,s26p04,s26p05,s26p06,s26p07,s26p08,s27p01,s27p02,s28p0101,s28p0102,s29p0101,s29p0102,s29p0103,s29p0104,s29p0105,s29p0201,s29p0202,s29p0203,s29p0204,s29p0205,s29p0206,s29p0207,s29p0208,s30p0301,s30p0302,s30p0303,s30p0401,s30p0402,s30p0403,s30p05,s30p06,s31p01,s!
31p02,s31p03,s32p0104,s32p0204,s32p0304,s32p0404,s32p0504,s32p!
0604,s32
p0704,s32p0804,s32p0904,s33p01,s33p0201,s33p03,s33p0501,s33p0502,s33p06,s33p0701,s33p0702,s33p08,s33p0901,s33p10,s33p1201,s33p1202,s33p13,s33p1401,s33p1402,s33p15,s33p1601,s33p17,s33p1901,s33p1902,s33p20,s33p2101,s33p2102,s34p01,s34p0201,s34p03,s34p0501,s34p0502,s34p06,s34p0701,s34p0702,s34p08,s34p0901,s34p10,s34p1201,s34p1202,s34p13,s34p1401,s34p1402,s34p15,s34p1601,s34p17,s34p1901,s34p1902,s34p20,s34p2101,s34p2102
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,tbl_siniestros,tbl_percepciones,tbl_violencia,tbl_participacion_comunitaria,tbl_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

Javier Carlos <fjcarlos@correo.insp.mx> writes:

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.

Hm. Are you using the default GEQO settings, or something custom?

regards, tom lane

Quoting Tom Lane <tgl@sss.pgh.pa.us>:

Javier Carlos <fjcarlos@correo.insp.mx> writes:

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.

Hm. Are you using the default GEQO settings, or something custom?

regards, tom lane

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