Re: Criterio para crear indices

----- Mensaje original -----
> De: "Edwin De La Cruz" <edwinspire(at)gmail(dot)com>
> Para: "Jaime Casanova" <jaime(dot)casanova(at)2ndquadrant(dot)com>, pgsql-es-ayuda(at)postgresql(dot)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
> <edwinspire(at)gmail(dot)com> escribió:
> > Mis proyectos de software libre en:
> > Github - edwinspire
> >
> >
> > El día 17 de septiembre de 2015, 10:23, Jaime Casanova
> > <jaime(dot)casanova(at)2ndquadrant(dot)com> escribió:
> >> 2015-09-17 9:57 GMT-05:00 Edwin De La Cruz <edwinspire(at)gmail(dot)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(at)postgresql(dot)org)
Para cambiar tu suscripcin:
http://www.postgresql.org/mailpref/pgsql-es-ayuda