Re: Ayuda con explain

Buenas a todos,
les pediría un poco de luz acerca de cómo interpretar el siguiente plan
de ejecución y si existe posibilidad de mejora.

QUERY:
explain
select emp.id_empresa, fil.id_filiacion, emp.id_estado,
emp.id_tipo_balance, emp.id_pais, emp.id_provincia,
emp.id_municipio, emp.domicilio, emp.id_tipo_via,
emp.poblacion, emp.distrito, emp.email, emp.no_lssi, emp.web,
emp.telefono, emp.fax, emp.usuario, emp.consolidado, emp.id_cnae,
emp.id_grupo_empresa, fil.nombre,fil.cif_nif, emp.denominacion
from red_empresas emp, com_filiaciones fil
where emp.id_filiacion = fil.id_filiacion
and fil.es_empresa='TRUE'
and emp.id_pais='ESP';

PLAN:
Hash Join (cost=85.60..120.13 rows=125 width=246)
Hash Cond: ("outer".id_filiacion = "inner".id_filiacion)
-> Seq Scan on red_empresas emp (cost=0.00..30.07 rows=642
width=220)
Filter: ((id_pais)::text = 'ESP'::text)
-> Hash (cost=83.95..83.95 rows=659 width=30)
-> Seq Scan on com_filiaciones fil (cost=0.00..83.95 rows=659
width=30)
Filter: es_empresa
(7 filas)

TABLAS:
CREATE TABLE red_empresas (
id_empresa INTEGER NOT NULL DEFAULT
nextval('red_empresas_id_empresa_seq'),
denominacion VARCHAR(255)NOT NULL,
id_filiacion INTEGER NOT NULL,
id_estado INTEGER NOT NULL,
id_tipo_balance INTEGER NOT NULL,
id_pais VARCHAR(3) NOT NULL,
id_provincia VARCHAR(2) ,
id_municipio VARCHAR(3) ,
id_tipo_via INTEGER NOT NULL ,
domicilio VARCHAR(255),
poblacion VARCHAR(255),
distrito VARCHAR(3) ,
email VARCHAR(255),
no_lssi BOOL ,
web VARCHAR(255),
telefono VARCHAR(20) ,
fax VARCHAR(20) ,
usuario VARCHAR(20) ,
consolidado BOOL NOT NULL DEFAULT false,
id_cnae VARCHAR(5) ,
id_grupo_empresa INTEGER ,
aud_fcr TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
CURRENT_TIMESTAMP,
aud_ucr VARCHAR(20) NOT NULL DEFAULT CURRENT_USER,
aud_far TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
CURRENT_TIMESTAMP,
aud_uar VARCHAR(20) NOT NULL DEFAULT CURRENT_USER,
CONSTRAINT red_empresas_pk
PRIMARY KEY (id_empresa),
CONSTRAINT red_empresas_com_filiaciones_fk
FOREIGN KEY (id_filiacion) REFERENCES com_filiaciones
(id_filiacion)
ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT red_empresas_com_paises_fk
FOREIGN KEY (id_pais) REFERENCES com_paises (id_pais)
ON DELETE RESTRICT ON UPDATE RESTRICT
....
);
CREATE INDEX red_empresas_ind1 ON red_empresas (id_filiacion);
CREATE INDEX red_empresas_ind2 ON red_empresas (id_pais);

--------

CREATE TABLE com_filiaciones (
id_filiacion INTEGER NOT NULL DEFAULT
nextval('com_filiaciones_id_filiacion_seq'),
cif_nif VARCHAR(15),
es_empresa BOOL NOT NULL DEFAULT false,
nombre VARCHAR(255) NOT NULL,
apellido1 VARCHAR(60),
apellido2 VARCHAR(60),
no_lopd BOOL,
observaciones VARCHAR(255),
aud_fcr TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
CURRENT_TIMESTAMP,
aud_ucr VARCHAR(20) NOT NULL DEFAULT CURRENT_USER,
aud_far TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
CURRENT_TIMESTAMP,
aud_uar VARCHAR(20) NOT NULL DEFAULT CURRENT_USER,
CONSTRAINT com_filiaciones_pk PRIMARY KEY (id_filiacion)
);

Muchas gracias de antemano.

Jose Maria Mencia Fernandez wrote:
> Buenas a todos,
> les pediría un poco de luz acerca de cómo interpretar el siguiente plan
> de ejecución y si existe posibilidad de mejora.
>
> QUERY:
> explain
> select emp.id_empresa, fil.id_filiacion, emp.id_estado,
> emp.id_tipo_balance, emp.id_pais, emp.id_provincia,
> emp.id_municipio, emp.domicilio, emp.id_tipo_via,
> emp.poblacion, emp.distrito, emp.email, emp.no_lssi, emp.web,
> emp.telefono, emp.fax, emp.usuario, emp.consolidado, emp.id_cnae,
> emp.id_grupo_empresa, fil.nombre,fil.cif_nif, emp.denominacion
> from red_empresas emp, com_filiaciones fil
> where emp.id_filiacion = fil.id_filiacion
> and fil.es_empresa='TRUE'
> and emp.id_pais='ESP';
>
> PLAN:
> Hash Join (cost=85.60..120.13 rows=125 width=246)
> Hash Cond: ("outer".id_filiacion = "inner".id_filiacion)
> -> Seq Scan on red_empresas emp (cost=0.00..30.07 rows=642
> width=220)
> Filter: ((id_pais)::text = 'ESP'::text)
> -> Hash (cost=83.95..83.95 rows=659 width=30)
> -> Seq Scan on com_filiaciones fil (cost=0.00..83.95 rows=659
> width=30)
> Filter: es_empresa
> (7 filas)

Debes mandar el EXPLAIN ANALYZE.

--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------

Ok, ejecuto con explain analyze ...

y el resultado es:

Hash Join (cost=85.60..120.13 rows=125 width=246) (actual
time=9.298..19.990 rows=642 loops=1)
Hash Cond: ("outer".id_filiacion = "inner".id_filiacion)
-> Seq Scan on red_empresas emp (cost=0.00..30.07 rows=642
width=220) (actual time=0.042..4.460 rows=642 loops=1)
Filter: ((id_pais)::text = 'ESP'::text)
-> Hash (cost=83.95..83.95 rows=659 width=30) (actual
time=9.085..9.085 rows=662 loops=1)
-> Seq Scan on com_filiaciones fil (cost=0.00..83.95 rows=659
width=30) (actual time=0.029..5.586 rows=662 loops=1)
Filter: es_empresa
Total runtime: 22.831 ms
(8 filas)

Casualmente la mayoría de los registros poseen id_pais = 'ESP', ¿ es por
ello que el servidor decide aplicar Seq Scan en vez de usar índice?

Jose Maria Mencia Fernandez escribió:

> Casualmente la mayoría de los registros poseen id_pais = 'ESP', ¿ es por
> ello que el servidor decide aplicar Seq Scan en vez de usar índice?

Esa es una posibilidad, sí.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 5/9/07, Jose Maria Mencia Fernandez <jmencia(at)alimarket(dot)es> wrote:
> Ok, ejecuto con explain analyze ...
>
> y el resultado es:
>
> Hash Join (cost=85.60..120.13 rows=125 width=246) (actual
> time=9.298..19.990 rows=642 loops=1)

Por que espera encontrar solo 125 filas mientras en realidad recupera
642? es este un problema de correlacion?

--
Atentamente,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

El jue, 10-05-2007 a las 00:28 -0500, Jaime Casanova escribió:
> On 5/9/07, Jose Maria Mencia Fernandez <jmencia(at)alimarket(dot)es> wrote:
> > Ok, ejecuto con explain analyze ...
> >
> > y el resultado es:
> >
> > Hash Join (cost=85.60..120.13 rows=125 width=246) (actual
> > time=9.298..19.990 rows=642 loops=1)
>
> Por que espera encontrar solo 125 filas mientras en realidad recupera
> 642? es este un problema de correlacion?
>

Perdona, pero no entiendo la pregunta ni el por qué de esa diferencia.
Pensé que las dudas con el explain las tenía yo ;-)

Jaime Casanova wrote:
> On 5/9/07, Jose Maria Mencia Fernandez <jmencia(at)alimarket(dot)es> wrote:
>> Ok, ejecuto con explain analyze ...
>>
>> y el resultado es:
>>
>> Hash Join (cost=85.60..120.13 rows=125 width=246) (actual
>> time=9.298..19.990 rows=642 loops=1)
>
> Por que espera encontrar solo 125 filas mientras en realidad recupera
> 642? es este un problema de correlacion?
>

Un VACUUM ANALYZE no viene mal nunca. :-)

Pero haría eso cambiar el metodo de JOIN que usa? Digo, incluso 642 son
tan pocas filas.

--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------

Martin Marques escribió:
> Jaime Casanova wrote:
> >On 5/9/07, Jose Maria Mencia Fernandez <jmencia(at)alimarket(dot)es> wrote:
> >>Ok, ejecuto con explain analyze ...
> >>
> >>y el resultado es:
> >>
> >> Hash Join (cost=85.60..120.13 rows=125 width=246) (actual
> >>time=9.298..19.990 rows=642 loops=1)
> >
> >Por que espera encontrar solo 125 filas mientras en realidad recupera
> >642? es este un problema de correlacion?
> >
>
> Un VACUUM ANALYZE no viene mal nunca. :-)
>
> Pero haría eso cambiar el metodo de JOIN que usa? Digo, incluso 642 son
> tan pocas filas.

Podría ser ... cuál era el resto del explain?

Pensé que este problema ya había sido solucionado.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 5/9/07, Jose Maria Mencia Fernandez <jmencia(at)alimarket(dot)es> wrote:
> Ok, ejecuto con explain analyze ...
>
> y el resultado es:
>
> Hash Join (cost=85.60..120.13 rows=125 width=246) (actual
> time=9.298..19.990 rows=642 loops=1)
> Hash Cond: ("outer".id_filiacion = "inner".id_filiacion)
> -> Seq Scan on red_empresas emp (cost=0.00..30.07 rows=642
> width=220) (actual time=0.042..4.460 rows=642 loops=1)
> Filter: ((id_pais)::text = 'ESP'::text)
> -> Hash (cost=83.95..83.95 rows=659 width=30) (actual
> time=9.085..9.085 rows=662 loops=1)
> -> Seq Scan on com_filiaciones fil (cost=0.00..83.95 rows=659
> width=30) (actual time=0.029..5.586 rows=662 loops=1)
> Filter: es_empresa
> Total runtime: 22.831 ms
> (8 filas)
>
>

cuantos registros hay en cada una de las tablas?

> Casualmente la mayoría de los registros poseen id_pais = 'ESP', ¿ es por
> ello que el servidor decide aplicar Seq Scan en vez de usar índice?
>

efectivamente, un campo con muchos valores identicos y solo unos
cuantos distintos no es buen candidato para un indice (a menos que el
indice sea parcial where id_pais not in ('ESP'))

En cuanto al asunto de la correlacion... postgres guarda estadisticas
que le permiten estimar que cantidad de registros regresara para
condiciones como:
fil.es_empresa='TRUE' y emp.id_pais='ESP', pero en cambio le resulta
mucho mas dificil estimar que cantidad de los registros que regresa de
una tabla coinciden con los de la otra (emp.id_filiacion =
fil.id_filiacion).
...

Otro motivo por el que tu consulta se hace lenta, no se cuanto afecte,
es el hecho de que cada fila de la tabla red_empresas tiene 220 bytes
(efecto de la gran cantidad de columnas varchar(255) que quiza podrian
estar en otra tabla, por cierto porque varchar(tamaño) en vez de
text?). Eso limita la cantidad de registros que se leen en una sola
operacion de e/s. asi en vez de leer las 642 filas de golpe lo hara en
varias operaciones e/s y si ademas tienes tuplas muertas (falta de
vacuum suficientemente periodico) leerias muchas mas paginas de las
realmente necesarias. Claro solo estoy especulando aqui.

--
Atentamente,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

Jaime Casanova escribió:
> On 5/9/07, Jose Maria Mencia Fernandez <jmencia(at)alimarket(dot)es> wrote:
> >Ok, ejecuto con explain analyze ...
> >
> >y el resultado es:
> >
> > Hash Join (cost=85.60..120.13 rows=125 width=246) (actual
> >time=9.298..19.990 rows=642 loops=1)
>
> Por que espera encontrar solo 125 filas mientras en realidad recupera
> 642? es este un problema de correlacion?

Ahora que vi el EXPLAIN en tu otro mail, te puedo decir que si, es un
problema de correlacion, porque los dos nodos hijos de este están
bastante bien estimados.

No estoy seguro si funcionará hacer tomar más estadísticas para las dos
columnas que forman el join. Supongo que vale la pena hacer la prueba
(ALTER TABLE ... SET STATISTICS)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.