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.