danicarrion/explain.md

## explain.md

      
        

                Raw

        
        
          
            
          
          
            
              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;