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

Si tienes razón, me falto clarificar que era un caso nuevo donde ya no
requiero los movimientos de un producto, si no requiero los movimientos
realizados en cierta sección (departamento).

Pero muchas gracias por tu ayuda, me diste el camino para seguir haciendo
pruebas.

Saludos

2017-06-10 0:10 GMT-05:00 Daymel Bonne <dbonne(at)2ndquadrant(dot)ec>:

> Hola Iván:
>
> La consulta que envías es completamente diferente a la que pusiste en el
> primer correo:
>
> El 9 de junio de 2017, 23:45, Ivan Perales M.<ivan(dot)perales(at)gmail(dot)com>
> escribió:
>
>> Muchas gracias por tu respuesta.
>>
>> Entonces solo para clarificar, ya que la columna objetivo de la tabla
>> referenciada es un primary key y éstas si llevan un índice implícito, una
>> vez que postgres filtro las filas de la primer tabla, para buscar la
>> información de la segunda tabla ya utiliza el indice de esa segunda tabla.
>> Indexar cualquier columna con un contraint foreign key solo es útil si se
>> va a filtrar por esa columna, verdad?
>>
>> Entonces por ejemplo, cual de las siguientes queries seria recomendable
>> hacer:
>>
>> select e.*, p.* from productomovimiento as e left join producto p on p.id
>> = e.producto_id where p.seccion = 'Electrónicos';
>>
>
> Esta consulta no es igual que la que pones en el correo anterior. En el
> anterior correo filtras por un ID específico, aqui no. Puede que muchos
> productos cumplan que, p.seccion = 'Electrónicos' y por esta razón puede
> que postgres no use el índice en la tabla producto en absoluto. Tal vez un
> explain de esta consulta???
>
>
>> select e.*, p.* from productomovimiento as e left join producto p on p.id
>> = e.producto_id where e.producto_id in (select f.id from producto f
>> where f.seccion = 'Electrónicos');
>>
>> Creo que a simple vista sería mas optima la segunda, pero voy a realizar
>> algunos ejercicios para determinarlo.
>>
>
> Lo contrario. No confíes en tu vista, confia en postgres. Probablemente
> hará las cosas mejor de lo que imaginas. ;)
>
> Saludos
>
>
>> Saludos
>>
>>
>> 2017-06-09 23:26 GMT-05:00 Daymel Bonne <dbonne(at)2ndquadrant(dot)ec>:
>>
>>> 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
>>>
>>
>>
>>
>> --
>> Lindolfo Iván Perales Mancinas
>> Solo existen 10 tipos de personas en el mundo, las que saben binario y
>> las que no.
>>
>
>
>
> --
> Daymel Bonne https://www.2ndQuadrant.com/
> <https://www.2ndquadrant.com/>
> Database Consultant, Training & Services
>

--
Lindolfo Iván Perales Mancinas
Solo existen 10 tipos de personas en el mundo, las que saben binario y las
que no.