Dudas optimización consultas y funciones

Hola lista,

Después de mirar mucha documentación, y de hacer mil pruebas aun no he
llegado a la conclusión de cómo optimizar algunas consultas.

Para entrar en materia os explico en qué situación me encuentro:

- Postgres 9.1 con replica en otra maquina en local.
- Tengo una Tabla con aproximadamente 12 millones de TX y creciendo.
- Esta tabla está compuesta por 32 campos.
- Esta tabla almacena las operaciones/ventas realizadas des de
diferentes tiendas.

Por un lado, tenemos una función en la BBDD que calcula la suma de importes
de las ventas realizadas a partir des de el último cierre de "tienda". Los
cierres de tienda se almacenan en otra tabla, y la búsqueda de la operación
respecto al cierre es relativamente rápida. El problema se encuentra en
realizar la suma de importes, ya que segun la tienda / terminal tienen
costes temporales muy altos.

Sin entrar en detalle de las query, hemos echo un análisi de las posibles
soluciones a aplicar:

- Como comentan en varios foros hemos hecho un PREPARE de cada una de
las consultas que contiene la función y seguidamente un explain para
comprovar qué plan utiliza en cada caso.
- A partir del resultado obtenido, hemos optador por:
- Crear nuevos índices según campos de la query.
- Modificar ciertas consultas del tipo SELECT max(documento) ...,
por SELECT .... ORDER BY documento DESC LIMIT ya que hemos
comprobado que
es mucho mas rápido.
- Regularizar los tipos de datos que pasamos por paràmetro en la
función para que siempre coincidan con los tipos de datos con la BBDD.
- Finalmente, hemos conseguido que el analize de los PREPARE nos
compute un coste aproximado a cero. En teoría, en este punto hemos
conseguido optimizar al máximo las consultas de la función, pero
realmente
no és asi..... Más adelante os explico
- Por otro lado, una vez hemos podido optimizar las consultas, hemos
hecho pruebas con datos concretos y el resultado ha sido muy variado, des
de consultas que duran milisegundos, hasta otras que duran más de un
minuto(Algo intorelable en nuestra aplicación ya que estamos hablando de un
sistema transaccional con "TIMEOUT" de 10 segundo de respuesta.)
- Al ver tal desbarajuste, hemos echo un ANALIZE directamente de cada
una de las QUERY de la función, dándole datos concretos, con la sorpresa
que el plan a utilizar es diferente segun el valor aportado.
- Finalmente, hemos hecho la suposición de que la tabla es demasiado
grande, y como POSTGRES nunca se equivoca, hemos optado por acotar ciertas
consultas respecto un rango de fechas, con lo que hemos mejorado algunos
tiempos de respuesta para los valores más problemáticos, pero para los que
el coste era mínimo, ahora el tiempo de respuesta ha aumentado.

Está claro que algo estamos haciendo mal, por que nos sucede en más de una
ocasión, con lo que tengo varias dudas de las cosas que hay que tener en
cuenta en el momento de optimizar consultas:

- Después de pasarnos horas mirando el funcionamiento de POSTGRES en el
momento de hacer un query plan, hemos llegado a la conslusión que no
tenemos ni idea en qué nos tenemos basar para optimizar correctamente una
query/funcion.
- Cuál es la mejor herramienta para ver realmente que plan se usa para
una query???
- Cual es la mejor forma de analizar las funciones de BBDD para saber
qué sucede??
- Cuáles són las mejores cosas a tener en cuenta para optimizar
querys/funciones.
- Qué tipo de consultas són más lentas que otras??
- Qué tipo de datos pueden dar problemas???
- Es más óptimo hacer querys des de SRC en vez de encapsular-las en
funciones de BBDD'??.
- La funciones tienen un plan predefinido en el momento de crear-las, o
segun se llaman va cambiando?? Y Si está predefinido por defecto, existe la
manera para que recalcule el plan cada vez que se ejecute??
- etc...

Muy probablemente esté diciendo alguna estupidez, pero a estas alturas mi
mente ya no da mas de si, jejejej.

Nota: Creo que lo que pido va más allá de la documentación, me refiero a la
experiencia de la gente que lleva muchoas años con Postgres y saben cómo
dominarlo, y el porqué de las cosas sin llegar al ddetalle al nivel de
bit(jjj). Vaya, para gente simple como yo. jjjj

Gracias de antemano,

Un saludo

--
*Ruben Fitó *
Software Engineer
[image: Ubiquat Technologies, SL] r(dot)fito(at)ubiquat(dot)com<j(dot)catarineu(at)ubiquat(dot)com>

www.ubiquat.com
Tota la informació continguda en aquest document i arxius adjunts és
CONFIDENCIAL protegida per llei de secret comercial. Si l'ha rebut per
error, si us plau elimini'l i posi's en contacte amb l'emissor.

All information contained in this document and any attachments are
CONFIDENTIAL and protected under trade secret laws. If you receive this
message by mistake, please delete it and notify it immediately to the
sender.