Taller para el Open Space de WeCodeFest 2020

explain.md

PostgreSQL EXPLAIN

Taller para el Open Space de WeCodeFest 2020.

Ejemplos tomados de: https://public.dalibo.com/exports/conferences/_archives/_2012/201211_explain/understanding_explain.pdf

Preparación del taller

Si queréis seguir el taller con vuestro ordenador, lo primero es instalar PostgreSQL si no lo tenéis. Lo más fácil y limpio:

docker run --name explain -e POSTGRES_PASSWORD=mysecretpassword -d postgres

Tenemos accesible el puerto 5432 para conectar desde nuestro pgAdmin o psql, pero por si acaso vamos a usar directamente el psql que hay dentro de la imagen:

docker exec -it explain psql -U postgres

Dentro de psql creamos una base de datos y nos conectamos:

create database wecode;
\c wecode

Operaciones

A lo largo del taller igual tendremos que:

• Reiniciar el servidor: docker restart explain
• Lanzar psql: docker exec -it explain psql -U postgres
• Cambiar paralelización:

set max_parallel_workers_per_gather to 0;

reset max_parallel_workers_per_gather;

Consultas SQL que utilizaremos

Simplemente para que las podáis copiar y pegar durante el taller.

Carga inicial de datos

create table foo (c1 integer, c2 text);
insert into foo select i, md5(random()::text) from generate_series(1, 1000000) as i;

Primeras pruebas

explain select * from foo;

select relpages*current_setting('seq_page_cost')::float4 + reltuples*current_setting('cpu_tuple_cost')::float4 as total_cost from pg_class where relname='foo';

explain (analyze) select * from foo;

el coste suelen ser estable. los tiempos pueden fluctuar y dependen de buffers, etc. el coste y el tiempo tiene formato a..b

• a: coste/tiempo de devolver la primera fila
• b: coste/tiempo de devolver la última fila en queries con orden, el coste de devolver la primera fila es MUY alto (tiene que ordenar la tabla completa) en queries que puede hacer streaming (leer y servir) el coste inicial puede ser muy bajo (devolverán muy rápido la primera fila)

Shared buffers

explain (analyze,buffers) select * from foo;

show shared_buffers;

alter system set shared_buffers to '1gb';

Filtrado

explain select * from foo where c1 > 500;

create index on foo(c1);

explain (analyze) select * from foo where c1 > 500;

explain select * from foo where c1 < 500;

set enable_seqscan to off;
explain (analyze) select * from foo where c1 > 500;
set enable_seqscan to on;

explain select * from foo where c1 < 500 and c2 like 'abcd%';

explain (analyze) select * from foo where c2 like 'abcd%';

create index on foo(c2);

create index on foo(c2 text_pattern_ops);

explain select c1 from foo where c1 < 500;

Ordenación

drop index foo_c1_idx;

explain (analyze) select * from foo order by c1;

explain (analyze,buffers) select * from foo order by c1;

set work_mem to '200mb';

work_mem es el espacio donde hacer las ordenaciones. Si es capaz de meter toda la tabla en el work_mem, ordenará mucho más rápido. Si no, ordena escribiendo en disco.

reset work_mem;

Agregación

explain select count(*) from foo;

explain (analyze) select max(c2) from foo;

Agrupar

explain (analyze) select c2, count(*) from foo group by c2;

Join

create table bar (c1 integer, c2 boolean);
insert into bar select i, i%2=1 from generate_series(1, 500000) as i;

explain (analyze) select * from foo join bar on foo.c1=bar.c1;

hash join es el join más rápido, pero sólo sirve para join por igualdad.

si las dos tablas tienen índice por las columnas de join, utiliza merge join