Re: [pgsql-es-ayuda] Índices sobre constraints foreign key

Hola Iván:

Lo cierto es que el explain que pones es un poco engañoso. Hay varias cosas
que señalar.

1. La búsqueda secuencial en la tabla producto es porque tienes muy

El 9 de junio de 2017, 20:33, Ivan Perales M.<ivan(dot)perales(at)gmail(dot)com>
escribió:

> Hola buenas tardes.
>
> En algun momento del pasado, honestamente no recuerdo si leí o escuché que
> postgres por default creaba indices sobre las columnas que tienen un
> constraint foreign key. Ya que el rendimiento siempre ha sido óptimo y no
> he tenido problemas, realmente no me habia dado a la tarea de investigar al
> respecto.
>

No crea índice sobre la columna.

>
> Sin embargo acabo de leer un comentario que dice que ningun rdbms crea
> indices sobre éstas columnas por que lo que uno debe crearlos si es
> necesario.
>
> Ejecute un explain sobre una tabla que hace referencia a otra y ésto me
> arrojo en la salida:
>
> explain select e.id, p.id from productomovimiento as e left join producto
> p on p.id = e.producto_id where e.producto_id = 10;
>
> Nested Loop Left Join (cost=0.00..3.28 rows=1 width=8)
> Join Filter: (p.id = e.producto_id)
> -> Seq Scan on productomovimiento e (cost=0.00..1.00 rows=1 width=8)
> Filter: (producto_id = 10)
> -> Seq Scan on producto p (cost=0.00..2.26 rows=1 width=4)
> Filter: (id = 10)
>
>
> Estoy viendo que realiza un escaneo secuencial para filtrar los
> movimientos de cierto producto, yo esperaria que utilizara un indice. Esto
> significa que efectivamente debo crear un indice manualmente en cada
> columna con el constraint foreign key? si es así, por que el left join
> funciona muy rápido aun cuando se tengan algunos cientos de miles de filas?
>
>
Debes crear un índice si vas a filtrar por esa columna de la tabla. Lo del
tiempo de ejecución de la consulta es relativo. El tiempo de ejecución de
la consulta baja significativamente si tienes un índice sobre la columna
producto_id de la tabla productomovimiento. Te dejo bajo un ejemplo de los
dos casos.

dbonne=# create table producto(id serial primary key);
CREATE TABLE
dbonne=# create table productomovimiento(id serial, producto_id integer
references producto (id)); CREATE TABLE

dbonne=# insert into producto select generate_series(1, 1000);
INSERT 0 1000
dbonne=# insert into productomovimiento (producto_id) select trunc(random()
* 1000 + 1) as producto_id from generate_series(1, 1000000);
INSERT 0 1000000

dbonne=# analyze producto;
ANALYZE
dbonne=# analyze productomovimiento;
ANALYZE

1. Ejecución de la consulta sin un índice en la columna producto_id, con un
un millón de movimientos en la tabla productomovimiento:

dbonne=# explain analyze select e.id, p.id from productomovimiento as e
left join producto p on p.id = e.producto_id where e.producto_id = 10;

---------------------------------------------------------------------------------------------------------------Nested
Loop Left Join (cost=0.28..16943.85 rows=970 width=8) (actual
time=0.096..141.575 rows=978 loops=1)
Join Filter: (p.id = e.producto_id)
-> Seq Scan on productomovimiento e (cost=0.00..16925.00 rows=970
width=8) (actual time=0.059..140.832 rows=978 loops=1)
Filter: (producto_id = 10)
Rows Removed by Filter: 999022
-> Materialize (cost=0.28..4.30 rows=1 width=4) (actual
time=0.000..0.000 rows=1 loops=978)
-> Index Only Scan using producto_pkey on producto p
(cost=0.28..4.29 rows=1 width=4) (actual time=0.024..0.027 rows=1 loops=1)
Index Cond: (id = 10)
Heap Fetches: 0
Planning time: 0.333 ms
Execution time: 141.710 ms
(11 filas)

2. La misma consulta luego de creado el índice.

dbonne=# create index idx_producto_id ON productomovimiento (producto_id );
CREATE INDEX
dbonne=# explain analyze select e.id, p.id from productomovimiento as e
left join producto p on p.id = e.producto_id where e.producto_id = 10;

---------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=20.22..2383.63 rows=970 width=8) (actual
time=0.854..5.179 rows=978 loops=1)
Join Filter: (p.id = e.producto_id)
-> Bitmap Heap Scan on productomovimiento e (cost=19.94..2364.78
rows=970 width=8) (actual time=0.822..3.702 rows=978 loops=1)
Recheck Cond: (producto_id = 10)
Heap Blocks: exact=880
-> Bitmap Index Scan on idx_producto_id (cost=0.00..19.70
rows=970 width=0) (actual time=0.459..0.459 rows=978 loops=1)
Index Cond: (producto_id = 10)
-> Materialize (cost=0.28..4.30 rows=1 width=4) (actual
time=0.000..0.000 rows=1 loops=978)
-> Index Only Scan using producto_pkey on producto p
(cost=0.28..4.29 rows=1 width=4) (actual time=0.015..0.017 rows=1 loops=1)
Index Cond: (id = 10)
Heap Fetches: 0
Planning time: 0.691 ms
Execution time: 5.415 ms
(13 filas)

El tiempo de ejecución bajó de 141.710 ms a sólo 5.415 ms. Teniendo en
cuenta que hay 1 millón de movimientos en la tabla productomovimiento la
cunsulta se ejecuta rápido aún si la tabla no contiene índice. Pero,
definitivamente un índice ayudaría aún más.

Saludos

--
Daymel Bonne https://www.2ndQuadrant.com/
<https://www.2ndquadrant.com/>
Database Consultant, Training & Services