Re: Indices

On Sun, Aug 01, 2004 at 07:52:29AM +0100, Felipe Fernandez wrote:

> CREATE TABLE abonados (
> codigo integer NOT NULL,
> nombre text,
> nombrecomercial1 text,
> nombrecomercial2 text,
> apellido1 character varying(40),
> apellido2 character varying(40),
> direccion1 text,
> localidad integer,
> provincia integer,
> telefono1 character varying(9),
> telefono2 character varying(9),
> actividad integer,
> anulado boolean,
> tipo smallint,
> actividad2 integer,
> actividad3 integer,
> estado smallint,
> viapublica smallint,
> nro smallint
> );

No tienes llave primaria en la tabla. Mala politica.

> CREATE INDEX abonados_localidad ON abonados USING btree (localidad);
> CREATE INDEX abonados_apellido1 ON abonados USING btree
> (upper((apellido1)::text) varchar_pattern_ops);
> CREATE INDEX abonados_apellido2 ON abonados USING btree
> (upper((apellido2)::text) varchar_pattern_ops);
> CREATE INDEX abonados_nombre ON abonados USING btree (upper(nombre)
> varchar_pattern_ops);
> CREATE INDEX abonados_nombrec1 ON abonados USING btree
> (upper(nombrecomercial1) varchar_pattern_ops);
> CREATE INDEX abonados_nombrec2 ON abonados USING btree
> (upper(nombrecomercial2) varchar_pattern_ops);
> CREATE INDEX abonados_dir1 ON abonados USING btree (upper(direccion1)
> varchar_pattern_ops);
> CREATE UNIQUE INDEX abonados_codigo ON abonados USING btree (codigo);
> CREATE INDEX abonados_tfno1 ON abonados USING btree (telefono1);
> CREATE INDEX abonados_tfno2 ON abonados USING btree (telefono2);
> CREATE INDEX prov_apel1 ON abonados USING btree (provincia, apellido1);
> CREATE INDEX i_abonados1 ON abonados USING btree (localidad, nombre,
> apellido1);
> CREATE INDEX i_abonados2 ON abonados USING btree (provincia,
> upper(nombre), upper((apellido1)::text), upper((apellido2)::text));
> CREATE INDEX i_abonados3 ON abonados USING btree (provincia,
> upper(nombre), upper((apellido1)::text), upper((apellido2)::text),
> upper(direccion1));
> CREATE INDEX i_abonados4 ON abonados USING btree (localidad,
> upper((apellido1)::text));
> CREATE INDEX i_abonados5 ON abonados USING btree (localidad,
> upper(nombre), upper((apellido1)::text));
> CREATE INDEX i_abonados6 ON abonados USING btree (localidad,
> upper(nombre), upper((apellido1)::text), upper((apellido2)::text));
> CREATE INDEX i_abonados7 ON abonados USING btree (localidad,
> upper(nombre), upper((apellido1)::text), upper((apellido2)::text),
> upper(direccion1));
> CREATE INDEX i_abonados_order ON abonados USING btree (apellido1,
> apellido2, nombre);
> CREATE INDEX i_abonados0 ON abonados USING btree (provincia, nombre,
> apellido1);
> CREATE INDEX abonados_provincia ON abonados USING btree (provincia);

Esto de seguro esta mal. No necesitas tantos indices. Peor aun, cada
indice empeora significativamente el rendimiento de cualquier insert,
update y delete. Ademas, muchos de ellos no te dan ninguna ventaja en
select, por lo que para todos los propositos son inutiles.

Considera que si tienes una consulta como

select ... where a='...' and b='...' and c='...'

esta consulta puede responderse usando un indice en (a,b,c)
Ademas, una consulta
select ... where a='...' and b='...'
puede responderse usando _el mismo_ indice (a, b, c). Por lo tanto,
tener un indice en (a, b) es contraproducente.

Ahora bien, tener un indice en (a,b,c,d,e) no ayuda en nada para una
consulta en esos cinco campos. El indice en (a,b,c) sera mejor, porque
se hace un recorrido sobre los registros que cumplen esa condicion del
indice, a los cuales se les aplica un filtro para obtener los que
cumplen las otras dos condiciones.

Espero que se haya entendido.

> explain analyze select * from abonados where localidad = 38038;
>
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using abonados_localidad on abonados (cost=0.00..44506.19
> rows=13272 width=105) (actual time=439.790..170960.727 rows=14475 loops=1)
> Index Cond: (localidad = 38038)
> Total runtime: 171027.259 ms
> (3 rows)

Parece razonable, no?

> explain analyze select * from abonados where provincia = 38;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------
> Seq Scan on abonados (cost=0.00..236123.08 rows=96122 width=105)
> (actual time=0.033..421674.412 rows=30104 loops=1)
> Filter: (provincia = 38)
> Total runtime: 422476.781 ms
> (3 rows)

Ok, esto esta mal. La estimacion de registros esta algo alejada de la
realidad (pero un factor de 3 no es tanto). Prueba aumentando el
taman~o de la muestra de estadisticas para esta columna,

alter table abonados alter column provincia set statistics 30;

luego haz ANALYZE, y finalmente el EXPLAIN ANALYZE nuevamente. Luego, haz
SET enable_seqscan TO off;
y vuelves a sacar el EXPLAIN ANALYZE.

(el valor 30: prueba incrementando valores lentamente hasta que el
explain analyze te de valores razonablemente cercanos en el "rows". El
valor por defecto es 10. No subas mucho.).

Una cosa que me parece rara es que se demora 422 segs en hacer un
seqscan de la tabla completa (8 millones de registros) y 171 segs en
hacer un indexscan de 14000 registros. El indice puede estar muy gordo.
Prueba haciendo
REINDEX TABLE abonados;
Que version de Postgres estas usando?

> He creado el indice i_abonados_order para ver si ayudaba a mejorar las
> ordenaciones (ORDER BY) que la inmesa mayoria de las veces es por esos
> tres campos.

Una consulta usara solo un indice para una tabla. Si se usa un indice
para extraer los datos, no se usara otro para hacer el ordenamiento.
Lo mas probable es que ese indice no sirva para nada.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"¿Qué importan los años? Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda)