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

-
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