Re: SELECT with MANY tables

Quoting Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>:

>
> On Mon, 24 Nov 2003, Javier Carlos wrote:
>
> > Quoting Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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(at)megazone(dot)bigpanda(dot)com>:

>
> On Mon, 24 Nov 2003, Javier Carlos wrote:
>
> > Quoting Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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