Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Optimización de consultas en PostgreSql

No description
by

Juan Castillo

on 27 October 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Optimización de consultas en PostgreSql

Optimización de consultas en PostgreSql
Ciclo de vida de una consulta en postgreSQL
El primer paso para entender como funciona una base de datos PostgreSQL es entender el ciclo de vida de una consulta.

Transmisión de la cadena de consulta al backend de la base de datos.
Análisis(Parsing) de la cadena de consulta.
Planificación de la consulta para optimizar la recuperación de datos.
Recuperación de datos desde el hardware.
Transmisión de los resultados del cliente.

Partición de tablas
Es una forma de organizar los datos clasificándolas según criterios de agrupación, de manera que cada transacción  realizada en una tabla padre se redirija automáticamente a un menor grupo de datos que están agrupados en las tablas hijas, el interés radica a la hora de realizar las consultas con un ahorro significativo en el tiempo de respuesta. Consiste en Segmentación de la información mediante criterios. Debe aplicarse cuando existen tablas con gran volumen de datos y para asignar permisos a un grupo de datos específico de una tabla.
Uso del comando EXPLAIN
Este comando muestra el plan de ejecución que el planificador Postgres genera para la consulta dada. Este muestra la manera en que serán escaneadas las tablas referenciadas; ya sea escaneo secuencial plano, escaneo por índice, etc. En el caso que se referencian varias tablas, los algoritmos de unión que serán utilizados para agrupar las tuplas requeridas de cada tabla de entrada.

Optimización a través de índices
Indice:

Los índices son una forma común para mejorar el rendimiento de base de datos. Un índice permite al servidor de base de datos para encontrar y recuperar filas específicas mucho más rápido de lo que podía hacer sin un índice. Sin embargo, los índices también se suman a la sobrecarga del sistema de base de datos como un todo, por lo que deben utilizarse con cuidado.

Optimización a través de índices
Al crear una PK automáticamente se crea un índice. Cuando se crea una consulta el planificador calcula si es necesario y mejor usar un índice o no, este trabajo suele dejarse al planificador, aunque se puede forzar el uso o no de un índice. A parte de los índices que crea postgresql (al crear una PK, al definir un campo como UNIQUE), se crean índices en los campos que sean necesarios para mejorar el rendimiento de las consultas.
¿Para que sirve?
Reduce la cantidad de datos a recorrer en cada consulta SQL

Aumenta el rendimiento (Menos datos que recorrer => execución más rápida).

Ventajas
Trabajamos con segmentos de datos más pequeños.

Obtenemos índices más pequeños.

Realizamos los backups más rápidos.

En conclusión,  la partición de

Uso del comando EXPLAIN
Costo inicio estimado (tiempo inicial que toma devolverse la primer tupla)
Costo total estimado (tiempo total para devolver todas las tuplas.
Número estimado de filas escaneadas (Se cumple solamente si la ejecución de la consulta es completa.).
Cantidad estimado de filas de salida.

Ejemplo de particiones de tablas
Este comando muestra el plan de ejecución que el PostgreSQL planificador genera para la declaración suministrada. El plan de ejecución muestra cómo se escaneará la tabla(s) de referencia en la declaración bien sea por recorrido secuencial normal, el índice de exploración, etc.
Se puede visualizar el costo estimado de ejecución de la sentencia, que es la suposición del planificador en el tiempo que se necesita para ejecutar la sentencia (medido en unidades de página de disco obtiene)

Uso del comando ANALIZE
Recoge información sobre el contenido de las tablas de la base de datos y almacena los resultados en la tabla del sistema pg_statistic. El optimizador de consultas utiliza estas estadísticas para determinar los planes de ejecución más eficientes para consultas.

Sin ningún parámetro, ANALIZE examina todas las tablas de la base de datos actual. Con un parametro, ANALIZE examina sólo esa tabla. Además, es posible dar una lista de nombres de columna, en cuyo caso sólo se recogen las estadísticas de las columnas.

Ejemplo con índices
Por ejemplo, una tabla personas:

id serial primary key,

-- Aqui crea un indice por ser PK
nombre varchar,
apellidos varchar,
dni varchar UNIQUE,
-- Aqui se puede crear un indice por ser unique
f_nacimiento date
Ejemplo de índices
Ahora suponga que se busca sobre las personas preguntando por su fecha de nacimiento, como por ejemplo:

select id from personas where f_nacimiento < 01/01/1990'

Este select hace un recorrido secuencial, es decir, si tienes 1 millón de personas, tiene que recorrer 1 millón de registros.

CREATE INDEX nombre_indice ON personas(f_nacimiento);

Ahora cuando utilizará el indice para la select anterior, la diferencia de rendimiento es muy notoria.
Métodos de búsqueda del planificador de consulta
Rastreo secuencial (SEQ SCAN):
Revisa la tabla de principio a fin, Evalúa la condición de la consulta para decidir si incorporar la fila al resultado. Se pasa por encima de las filas muertas y el costo inicial es siempre O (recuperación inmediata de la primera fila).

Rastreo indexado (INDEX SCAN):
Utiliza un índice de la tabla implicada, no tiene que revisar todas las filas de la tabla, Pero algunos bloques (páginas) pueden ir y venir varias veces, si la tabla está muy desordenada según el índice (no se ha hecho un CLUSTER recientemente), el resultado es ordenado según el orden del índice. Sólo se utiliza en índices árbol-B, árbol-R y GiST.
El planificador/optimizador utiliza este rastreo cuando puede reducir el número de filas a revisar o la necesidad de ordenar el resultado.


EXPLAIN ANALIZE
EXPLAIN ANALIZE
Permite visualizar el costo estimado de ejecución de la sentencia, que es la suposición del planificador en el tiempo que se necesita para ejecutar la sentencia (medido en unidades de página de disco obtiene). Nos muestra:
 
Tiempo de puesta en marcha antes de la primera fila se puede devolver
Tiempo total para devolver todas las filas.


Métodos de búsqueda del planificador de consulta
Bucle anidado (NESTED LOOP):
Para la realización de la concatenación entre tablas Para cada fila de la tabla "externa" busca las filas de la tabla interna que cumplan la condición de concatenación. Sólo se usa para los joins internos.
Concatenación por fusión (MERGE JOIN):
Para la realización de la concatenación entre tablas. Los dos conjuntos deben estar ordenados por la columna de la concatenación. Utiliza el hecho de estar ordenados los conjuntos para realizar la concatenación. Se usa para todo tipo de joins y para la unión.
Hash y concatenación hash (HASH y HASH JOIN):
Se usan combinadamente parada realización de la concatenación entre tablas. La concatenación hash empieza utilizando el operador hash sobre la tabla interna que crear un índice hash temporal sobre esta tabla. Luego la concatenación comienza tomando una fila de la tabla externa y buscando, haciendo uso del índice hash, alguna fila de la tabla interna que combine Se usa para joins internos, joins por la izquierda y para la unión.

Rápidos tips de optimización
Deshabilitar autocommit (enviar un BEGIN al comienzo y un COMMIT al final).
Usar COPY, que es un comando optimizado para cargar gran cantidad de filas.
Crear los indices luego de cargar la tabla.
Crear las restricciones de clave foránea luego de cargar la tabla.
Incrementar la memoria de trabajo (maintance_work_mem), ayudando al CREATE INDEX y ALTER TABLE ADD FOREIGN KEY.

Rápidos tips de optimización
Incrementar los segmentos de control (checkpoint_segments), lo que reducirá su ocurrencia minimizando las escrituras al disco.
Ejecutar ANALYZE siempre que haya alterado significativamente la distribución de los datos en una tabla, para mantener actualizadas las estadísticas.
autovacuum: El proceso realiza una limpieza de tuplas muertas que han sido marcadas como borradas o modificadas, ya que el motor de base de datos no las borra inmediatamente de la parte física para no sobrecargar las operaciones normales.
Full transcript