Consulta Indices en postgres

[paperina]

Hola, estoy testeando postgres para acabar de decidirme a trabajar con ella y quisera por favor si alguien me puede explicar detalladamente la utilización de los indices en postgres, mi ignorancia me hace ver que para consultas sirve igual definir una primary key que un indice (pero no se hacer consultas con ellos). Doy gracias de antemano y sobre todo si me ponen algún ejemplo detallado.

[seyko]

para consultas sirve igual definir una primary key que un indice (pero no se hacer consultas con ellos).

Al crear una PK postgresql automaticamente le crea un indice.
Cuando tu haces una consulta el planificador calcula si es necesario y mejor usar un indice o no, este trabajo suele dejarse al planificador, aunque puedes forzar el uso o no de un indice, pero no creo que lo necesites.
A parte de los indices que crea postgresql (al crear una PK, al definir un campo como UNIQUE), puedes crear indices en los campos que creas necesarios para mejorar el rendimiento de las consultas.

Salu2

[paperina]

Gracias por tu respuesta seiko. Me puedes poner algún ejemplo de índice donde se vea su aplicación más que nada por lo que me dices de que se puede poner un índice para el campo que quiera?

Saludos

[seyko]

por ejemplo, tienes una tabla personas:
id serial primary key, -- Aqui te crea un indice por ser PK
nombre varchar,
apellidos varchar,
dni varchar UNIQUE, -- Aqui te crea un indice por ser unique
f_nacimiento date

Ahora imagina que buscas sobre las personas preguntando por su fecha de nacimiento, como por ejemplo:
select id from personas where f_nacimiento < '01/01/1990'

Esta select hace un recorrido secuencial, es decir, si tienes 1 millon de personas, tiene que recorrer 1 millon de registros.

CREATE INDEX nombre_indice ON personas (f_nacimiento);
Ahora cuando utilizará el indice para la select anterior, la diferencia de rendimiento es muy notoria.

Salu2

[seyko]

una muestra practica:

Código:

pruebas=# create table personas(id serial primary key, nombre varchar, dni integer);
NOTICE:  CREATE TABLE creará una secuencia implícita «personas_id_seq» para la columna serial «personas.id»
NOTICE:  CREATE TABLE / PRIMARY KEY creará el índice implícito «personas_pkey» para la tabla «personas»
CREATE TABLE
pruebas=# insert into personas (nombre, dni) select 'nombre ' || foo, foo from generate_series(1,100000) foo;
INSERT 0 100000
pruebas=# EXPLAIN ANALYZE select * from personas where dni between 2500 AND 5000;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on personas  (cost=0.00..2133.00 rows=2497 width=20) (actual time=1.425..74.737 rows=2501 loops=1)
   Filter: ((dni >= 2500) AND (dni <= 5000))
 Total runtime: 79.222 ms
(3 rows)

pruebas=# create index idx_dni on personas(dni);
CREATE INDEX
pruebas=# EXPLAIN ANALYZE select * from personas where dni between 2500 AND 5000;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_dni on personas  (cost=0.00..93.20 rows=2497 width=20) (actual time=0.131..6.596 rows=2501 loops=1)
   Index Cond: ((dni >= 2500) AND (dni <= 5000))
 Total runtime: 10.985 ms
(3 rows)

Salu2

[X_fran]

mejor que un ejemplo practico no hay nada,
quisiera agregar que solo deberia(no quiero decir que sea obligatorio) crear indices sobre columnas que seran usadas como criterios de comparacion, tengo entendido que crear un indice sobre una columna es costoso, pues a la hora de insertar es como si se estubiera insertando doble dicho valor, mirando una tabla como un libro de muchas paginas, pues el indice buscara en la pagina donde se guardo el valor y no recorre el libro(tabla) completo.
eso fue lo que lei hac un tiempo, no soy experto en postgres, espero sirva de ayuda a los principiantes como yop; si me equivoque pues favor de corregir mi error.

[kolivera]

hola....tengo una duda....quisiera saber que es mas eficiente....si tengo una tabla que sera actualizada por años con 240 tuplas....si es recomendable implementar un indexado cuando solo este en el primer año...o si sera mejor a los 3 años indexar ....por favor....necesito ayuda....saludos