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