RE: [pgsql-es-ayuda] Duda declaración de indice.

Muchas gracias por tu explicación. Saludos.

De: Anthony Sotolongo [mailto:asotolongo(at)gmail(dot)com]
Enviado el: lunes, 24 de octubre de 2016 1:10
Para: Lazaro Garcia; 'Lista Postgres ES'
Asunto: Re: [pgsql-es-ayuda] Duda declaración de indice.

Hola Lazaro

On 24/10/16 12:07, Lazaro Garcia wrote:

Hola a todos en la lista, les escribo porque tengo la siguiente duda en
cuanto a la creación de índices. Cuando una columna es indexada y admite
campos nulos, el índice almacena alguna referencia a este null o no lo tiene
en cuenta.

Ambas consultas hacen lo mismo??

CREATE INDEX idx_password_change_key ON public.tbl_user USING btree
(password_change_key) WHERE (password_change_key IS NOT NULL);

CREATE INDEX idx_password_change_key ON public.tbl_user USING btree
(password_change_key);

no creo que las consultas hagan lo mismo, pues según tengo entendido la
primera te crea un indice parcial donde se solamente indexa los valores que
cumplen la condición que colocas y la segunda indexa todos los valores de
la tabla (incluido los null). Por lo que tengo entendido los null también
son indexados, según la
doc(https://www.postgresql.org/docs/9.4/static/sql-createindex.html)
Lo siguiente puede formar parte de la clausula de creación de indices, para
colocar los NULL:
NULLS FIRST
Specifies that nulls sort before non-nulls. This is the default when DESC is
specified.

NULLS LAST
Specifies that nulls sort after non-nulls. This is the default when DESC is
not specified.

te pongo un ejemplo:
tengo la tabla customers y le creo dos indices:

CREATE INDEX idx_gender1 ON customers USING btree (gender) WHERE (gender IS
NOT NULL);
CREATE INDEX idx_gender2 ON customers USING btree (gender) ;

corroborando lo que te comentaba arriba el indice donde se filtra por un
conjunto es más pequeño los tamaños son los siguientes
table;index, size
"customers";"idx_gender2";"0.48 MB"
"customers";"idx_gender1";"0.45 MB"

Si pregunto por los null en una consulta el optimizador va a buscar en el
indice(idx_gender2);
explain ANALYZE
select count(*) from customers where gender is null

"Aggregate (cost=7.32..7.33 rows=1 width=0) (actual time=0.317..0.317
rows=1 loops=1)"
" -> Index Only Scan using idx_gender2 on customers (cost=0.29..7.32
rows=1 width=0) (actual time=0.017..0.248 rows=1001 loops=1)"
" Index Cond: (gender IS NULL)"
" Heap Fetches: 1001"
"Planning time: 0.074 ms"
"Execution time: 0.343 ms"

Si pregunto por los not null en una consulta el optimizador va a buscar en
el indice(idx_gender1);
explain ANALYZE
select count(*) from customers where gender is not null

"Aggregate (cost=761.01..761.02 rows=1 width=0) (actual time=5.837..5.838
rows=1 loops=1)"
" -> Index Only Scan using idx_gender1 on customers (cost=0.29..707.42
rows=21434 width=0) (actual time=0.022..3.836 rows=18999 loops=1)"
" Index Cond: (gender IS NOT NULL)"
" Heap Fetches: 24"
"Planning time: 0.088 ms"
"Execution time: 5.873 ms"

Saludos a todos.

saludos