----- Mensaje original ----- > De: "Edwin De La Cruz" <edwinsp...@gmail.com> > Para: "Jaime Casanova" <jaime.casan...@2ndquadrant.com>, > pgsql-es-ayuda@postgresql.org > Enviados: Jueves, 17 de Septiembre 2015 12:44:06 > Asunto: Re: [pgsql-es-ayuda] Criterio para crear indices > > El día 17 de septiembre de 2015, 10:39, Edwin De La Cruz > <edwinsp...@gmail.com> escribió: > > Mis proyectos de software libre en: > > Github - edwinspire > > > > > > El día 17 de septiembre de 2015, 10:23, Jaime Casanova > > <jaime.casan...@2ndquadrant.com> escribió: > >> 2015-09-17 9:57 GMT-05:00 Edwin De La Cruz <edwinsp...@gmail.com>: > >>> Saludos. > >>> Buenos tengo dos criterios para las particiones. > >>> la tabla events es la tabla "general" que tiene campos comunes > >>> para > >>> todas las tablas hijas. > >>> > >>> El resto de tablas hijas tienen campos adicionales que en la > >>> tabla no > >>> existen, esa fue la primera razon por la que particioné. > >>> > >>> Luego al ver que el desempeño iba cayendo fue que decido > >>> particionar > >>> las tablas hijas y dividirlas por meses. > >>> > >>> Ahora bien, el cuello de botella esta en la consulta: > >>> > >>> SELECT idevent FROM events WHERE ideventtype = > >>> ANY(et.auto_close_on_event_defined) > >>> AND status = 0 AND idaccount = NEW.idaccount AND zu = NEW.zu AND > >>> idevent != NEW.idevent > >>> > >>> Si comento esta linea de código todo va bien, pero cuando la > >>> activo se > >>> presenta el problema, es ahi donde debo atachar y resilver pero > >>> no se > >>> como. > >>> > >> > >> la razón por la que el desempeño fue cayendo es que como mencione > >> la > >> consulta no tiene una manera de excluir particiones. > >> para eso debes agregar a la consulta una clausula en el where que > >> haga > >> referencia a como esta particionada la tabla Y agregar los > >> constraints > >> check que mencione a las tablas hijas (ambas cosas deben hacerse, > >> no > >> solo una) > >> > >> al crear mas particiones por debajo solo hiciste las cosas peor de > >> lo > >> que estaban. > >> la razón por la que te baja el rendimiento es que al tener varias > >> particiones y no excluir ninguna lees la misma cantidad de > >> registros > >> que antes solo que ahora las lees de distintas tablas y > >> adicionalmente > >> ahora agregas pasos adicionales a tu consulta pues debes mezclar > >> los > >> resultados de las distintas tablas. > >> > >> finalmente, estas consciente de que los campos adicionales en las > >> tablas hijas no serán visibles si consultas desde la tabla padre, > >> verdad? > >> > > > > Si lo se, precisamente asi necesito que funcione, lo normal es que > > haga la consulta a la tabla padre, si necesito mayor informacion > > (los > > campos de la tabla hija), solo entonces consulto a la hija. > > > >> nuevamente, podrías mostrar el plan de ejecución? es decir, la > >> salida > >> del explain analyze (por favor en un archivo de texto adunto) > >> > > > > Aqui va: > > > > EXPLAIN ANALYZE SELECT idevent, ts, loaded, dateevent, status, > > idaccount, code, priority, > > ideventtype, description, idadmin, last_comment, account, > > zu, > > note > > FROM events; > > > > > > > > > > "Append (cost=0.00..334407.05 rows=7227407 width=189) (actual > > time=0.015..13199.182 rows=6727718 loops=1)" > > " -> Seq Scan on events (cost=0.00..0.00 rows=1 width=260) > > (actual > > time=0.000..0.000 rows=0 loops=1)" > > " -> Seq Scan on events_201509 (cost=0.00..181671.35 > > rows=3722635 > > width=220) (actual time=0.013..1682.899 rows=3389222 loops=1)" > > " -> Seq Scan on events_jobs (cost=0.00..276.40 rows=2840 > > width=316) (actual time=0.026..2.045 rows=2840 loops=1)" > > " -> Seq Scan on events_dbsizes (cost=0.00..2105.30 rows=53930 > > width=225) (actual time=0.003..74.458 rows=53930 loops=1)" > > " -> Seq Scan on events_diskspace (cost=0.00..0.00 rows=1 > > width=196) (actual time=0.000..0.000 rows=0 loops=1)" > > " -> Seq Scan on events_sqlserver_uptime (cost=0.00..3.94 > > rows=94 > > width=170) (actual time=0.014..0.053 rows=94 loops=1)" > > " -> Seq Scan on events_jobs_201509 (cost=0.00..21829.20 > > rows=224220 width=275) (actual time=0.003..727.342 rows=232047 > > loops=1)" > > " -> Seq Scan on events_jobs_201508 (cost=0.00..6.80 rows=80 > > width=291) (actual time=3.902..3.957 rows=67 loops=1)" > > " -> Seq Scan on events_jobs_201507 (cost=0.00..1.01 rows=1 > > width=281) (actual time=0.003..0.003 rows=1 loops=1)" > > " -> Seq Scan on events_jobs_201411 (cost=0.00..1.01 rows=1 > > width=285) (actual time=0.004..0.005 rows=1 loops=1)" > > " -> Seq Scan on events_jobs_201506 (cost=0.00..8.39 rows=39 > > width=295) (actual time=0.003..0.040 rows=39 loops=1)" > > " -> Seq Scan on events_dbsizes_201509 (cost=0.00..120743.85 > > rows=2990385 width=147) (actual time=0.015..8242.433 rows=2819164 > > loops=1)" > > " -> Seq Scan on events_diskspace_201509 (cost=0.00..5452.39 > > rows=161639 width=131) (actual time=0.005..178.467 rows=158971 > > loops=1)" > > " -> Seq Scan on events_sqlserver_uptime_201509 > > (cost=0.00..2307.41 > > rows=71541 width=127) (actual time=0.005..70.946 rows=71342 > > loops=1)" > > "Planning time: 11.166 ms" > > "Execution time: 14367.252 ms" > > > Aqui el explain de la consulta que me da problemas: > > EXPLAIN ANALYZE SELECT idevent FROM events WHERE ideventtype = > ANY('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}') > AND status = 0 AND idaccount = 2089 AND zu = 3997923978 > > > "Append (cost=0.00..208983.03 rows=14 width=8) (actual > time=7829.613..7829.613 rows=0 loops=1)" > " -> Seq Scan on events (cost=0.00..0.00 rows=1 width=8) (actual > time=0.001..0.001 rows=0 loops=1)" > " Filter: ((status = 0) AND (idaccount = 2089) AND (zu = > 3997923978::bigint) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " -> Index Scan using > events_201509_idevent_idaccount_ideventtype_zu_idx on events_201509 > (cost=0.43..105618.81 rows=1 width=8) (actual time=2691.187..2691.187 > rows=0 loops=1)" > " Index Cond: ((idaccount = 2089) AND (zu = > 3997923978::bigint))" > " Filter: ((status = 0) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " -> Index Scan using > events_jobs_dateevent_idaccount_ideventtype_zu_key on events_jobs > (cost=0.28..144.37 rows=1 width=8) (actual time=131.836..131.836 > rows=0 loops=1)" > " Index Cond: ((idaccount = 2089) AND (zu = > 3997923978::bigint))" > " Filter: ((status = 0) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " -> Index Scan using > events_dbsizes_idevent_idaccount_ideventtype_zu_idx on events_dbsizes > (cost=0.41..2615.75 rows=1 width=8) (actual time=11.693..11.693 > rows=0 > loops=1)" > " Index Cond: ((idaccount = 2089) AND (zu = > 3997923978::bigint))" > " Filter: ((status = 0) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " -> Seq Scan on events_diskspace (cost=0.00..0.00 rows=1 width=8) > (actual time=0.001..0.001 rows=0 loops=1)" > " Filter: ((status = 0) AND (idaccount = 2089) AND (zu = > 3997923978::bigint) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " -> Seq Scan on events_sqlserver_uptime (cost=0.00..6.29 rows=1 > width=8) (actual time=0.253..0.253 rows=0 loops=1)" > " Filter: ((status = 0) AND (idaccount = 2089) AND (zu = > 3997923978::bigint) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " Rows Removed by Filter: 94" > " -> Index Scan using > events_jobs_201509_dateevent_idaccount_ideventtype_zu_key on > events_jobs_201509 (cost=0.42..7402.65 rows=1 width=8) (actual > time=1014.198..1014.198 rows=0 loops=1)" > " Index Cond: ((idaccount = 2089) AND (zu = > 3997923978::bigint))" > " Filter: ((status = 0) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " -> Seq Scan on events_jobs_201508 (cost=0.00..8.80 rows=1 > width=8) (actual time=0.056..0.056 rows=0 loops=1)" > " Filter: ((status = 0) AND (idaccount = 2089) AND (zu = > 3997923978::bigint) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " Rows Removed by Filter: 67" > " -> Seq Scan on events_jobs_201507 (cost=0.00..1.03 rows=1 > width=8) (actual time=0.004..0.004 rows=0 loops=1)" > " Filter: ((status = 0) AND (idaccount = 2089) AND (zu = > 3997923978::bigint) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " Rows Removed by Filter: 1" > " -> Seq Scan on events_jobs_201411 (cost=0.00..1.03 rows=1 > width=8) (actual time=0.004..0.004 rows=0 loops=1)" > " Filter: ((status = 0) AND (idaccount = 2089) AND (zu = > 3997923978::bigint) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " Rows Removed by Filter: 1" > " -> Seq Scan on events_jobs_201506 (cost=0.00..9.37 rows=1 > width=8) (actual time=0.030..0.030 rows=0 loops=1)" > " Filter: ((status = 0) AND (idaccount = 2089) AND (zu = > 3997923978::bigint) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " Rows Removed by Filter: 39" > " -> Index Scan using > events_dbsizes_201509_dateevent_idaccount_ideventtype_zu_key on > events_dbsizes_201509 (cost=0.43..86212.48 rows=1 width=8) (actual > time=3369.240..3369.240 rows=0 loops=1)" > " Index Cond: ((idaccount = 2089) AND (zu = > 3997923978::bigint))" > " Filter: ((status = 0) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " -> Index Scan using > events_diskspace_201509_dateevent_idaccount_ideventtype_zu_key on > events_diskspace_201509 (cost=0.42..4808.84 rows=1 width=8) (actual > time=396.091..396.091 rows=0 loops=1)" > " Index Cond: ((idaccount = 2089) AND (zu = > 3997923978::bigint))" > " Filter: ((status = 0) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > " -> Index Scan using > events_sqlserver_uptime_20150_dateevent_idaccount_ideventty_key on > events_sqlserver_uptime_201509 (cost=0.42..2153.61 rows=1 width=8) > (actual time=215.009..215.009 rows=0 loops=1)" > " Index Cond: ((idaccount = 2089) AND (zu = > 3997923978::bigint))" > " Filter: ((status = 0) AND (ideventtype = ANY > ('{22,29,30,31,32,33,34,35,36,38,44,45,46,63}'::integer[])))" > "Planning time: 122.060 ms" > "Execution time: 7829.934 ms" >
Si entendi correctamente, particionaste por meses, pero la consulta ultima no filtra por fechas de ningun modo. El planificador no tiene modo de saber que particion revisar, y va a revisar todas. Si podes ajustar el select a la condicion sobre la cual particionaste, es mas probable que use las particiones "necesarias" unicamente. HTH Gerardo - Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda@postgresql.org) Para cambiar tu suscripci�n: http://www.postgresql.org/mailpref/pgsql-es-ayuda