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;

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';

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;