Tablas y Secuencias Temporales

Hola a todos, hoy se me ocurre escribir sobre tablas y secuencias temporales en postgresql. Las tablas temporales y secuencias temporales son aquellas que solo viven mientras la conexion esta activa y despues son destruidas por el backend de postgresql cuando nos desconectamos. Son similares a las tablas y secuencias normales salvo que son de corta duracion y para crearlas solo basta agregar la palabra temporary a la sintaxis ya conocida, por ejemplo:

CREATE TEMPORARY TABLE clientes AS select * from clientes;

CREATE TEMPORARY SEQUENCE alguna_secuencia START 1;

Saludos.

Obtimizando Select count(*) para tablas con mas de 10 millones de registros en Postgresql

Hola a todos, el dia de hoy me complace escribir sobre la optimización de la consulta select count(*) en postgresql, cuando tenemos tablas muy grandes a veces el problema es el tiempo que se lleva al procesar una consulta lineal del tipo select count(*), obviamente el primer mecanismo seria crear un indice sobre dicha tabla empleando CREATE INDEX. Por ejemplo en una tabla que tengo de nombre produccion tengo al rededor de 11916936 registros, ya son muchos registros :).

A continuación muestro la estructura interna de la tabla produccion:

sicodelico=> \d produccion;
                              Tabla «public.produccion»
      Columna      |  Tipo   |                      Modificadores                      
-------------------+---------+---------------------------------------------------------
 id                | integer | not null default nextval('produccion_id_seq'::regclass)
 no_serie          | integer | not null default 0
 id_modelo         | integer | not null
 id_personal       | integer | not null
 fecha_fabricacion | date    | not null default '2005-01-01'::date
 fecha_salida      | date    | 
Índices:
    «produccion_pkey» PRIMARY KEY, btree (id)
Restricciones de llave foránea:
    «produccion_id_modelo_fkey» FOREIGN KEY (id_modelo) REFERENCES modelos(id)
    «produccion_id_personal_fkey» FOREIGN KEY (id_personal) REFERENCES personal(id)

Pues bien, empezemos creando el indice de la tabla produccion para busquedas mediante numero_serie de la siguiente manera:


CREATE INDEX no_serie_idx ON produccion(no_serie);


Aqui es necesario hacer notar que el crear muchos indices puede traer consecuencias nefastas, ya que cada indice ocupa espacio en disco, y muchos indices pueden ralentizar nuestro sistema por el I/O excedente.

Bueno, bueno, ahora lo que necesitamos hacer es ordenar fisicamente (esto es a nivel de cluster) la información, basandonos en el indice ya creado.

Para hacer esta ordenación nos valemos del comando CLUSTER de postgresql.


CLUSTER no_serie_idx on produccion;


Ya con esto tenemos ordenados nuestros datos con forme a los clusters contiguos del disco duro y con esto evitamos desplazamientos inecesarios de la cabeza lectora del disco duro.

En mi ejemplo tarde 34 segundos para completar el select count(*).

Aqui muestro la salida del Explain Analyze:

sicodelico=> EXPLAIN ANALYZE SELECT count(*) from produccion;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=231018.70..231018.71 rows=1 width=0) (actual time=33775.520..33775.522 rows=1 loops=1)
   ->  Seq Scan on produccion_large  (cost=0.00..201226.36 rows=11916936 width=0) (actual time=0.068..17800.818 rows=11916936 loops=1)
 Total runtime: 33775.597 ms

Cuando hice la prueba sin optimizar el disco tardo poco mas de 1 minuto, y sin indice mejor ya ni lo menciono.


Saludos.