Re: Indices

Alvaro Herrera Munoz wrote:

>On Sun, Aug 01, 2004 at 12:52:15AM +0100, Felipe Fernandez wrote:
>
>
>>Tengo definidos estos dos indices exactamente igual en una query los
>>utiliza y en otra no ¿Alguien me puede indicar porque?
>>
>>
>
>Definiciones de las tablas, por favor?
>
>Ademas, por favor publica los explain analyze, no solo los explain.
>
>
>

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
);

REVOKE ALL ON TABLE abonados FROM PUBLIC;
GRANT ALL ON TABLE abonados TO nobody;

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);

Ahora los analisis (para 8 millones de registros)

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)

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)

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.
Gracias