Skip site navigation (1) Skip section navigation (2)

Ayuda con una consulta...

From: Nicolás Domínguez Florit <ndomin(at)rec(dot)unicen(dot)edu(dot)ar>
To: pssql-es-ayuda <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: Ayuda con una consulta...
Date: 2007-11-29 13:58:47
Message-ID: 474EC597.1070007@rec.unicen.edu.ar (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#006600">
Tengo una consulta que me esta tardando mucho y me gustar&iacute;a que cuando
tengan unos minutos le echen un vistazo para ver si se les ocurre algo.
Les paso las tabas, los registros de cada tabla, la consulta y el
explain analyze.<br>
<br>
Uso un motor 8.2.4, <br>
<br>
Tablas:<br>
-------<br>
<br>
CREATE TABLE rrhh_cargos<br>
(<br>
&nbsp; numero_cargo numeric(9) NOT NULL,<br>
&nbsp; cod_ona_unidad_academica integer NOT NULL,<br>
&nbsp; cod_ona_rama character(4) NOT NULL,<br>
&nbsp; cod_ona_disciplina character(4) NOT NULL,<br>
&nbsp; cod_ona_area character(4) NOT NULL,<br>
&nbsp; cod_fuente_financiamiento numeric(2) NOT NULL,<br>
&nbsp; cod_rrhh_importacion integer NOT NULL,<br>
&nbsp; cuil_cuit numeric(11) NOT NULL,<br>
&nbsp; cod_cargo_universidad character(4) NOT NULL,<br>
&nbsp; cod_rrhh_estado_laboral integer NOT NULL,<br>
&nbsp; cod_rrhh_estado_cargo character(1),<br>
&nbsp; cod_rrhh_tipo_planta character(1) NOT NULL,<br>
&nbsp; cod_rrhh_situacion_laboral integer NOT NULL,<br>
&nbsp; cod_rrhh_agrupamiento_no_docente character(1),<br>
&nbsp; cod_rrhh_nivel_ensenianza integer NOT NULL,<br>
&nbsp; grado numeric(1) NOT NULL,<br>
&nbsp; horas_dedicacion numeric(2) NOT NULL,<br>
&nbsp; meses_antiguedad numeric(3) NOT NULL,<br>
&nbsp; anios_antiguedad numeric(2) NOT NULL,<br>
&nbsp; fecha_alta date NOT NULL,<br>
&nbsp; fecha_baja date,<br>
&nbsp; cod_dependencia numeric(2) NOT NULL,<br>
&nbsp; cod_sub_dependencia numeric(2) NOT NULL,<br>
&nbsp; programa numeric(2) NOT NULL,<br>
&nbsp; subprograma numeric(2) NOT NULL,<br>
&nbsp; proyecto numeric(2) NOT NULL,<br>
&nbsp; actividad numeric(2) NOT NULL,<br>
&nbsp; jefatura character(1) NOT NULL,<br>
&nbsp; cod_rrhh_equivalencia_cargo integer NOT NULL,<br>
&nbsp; cod_rrhh_escalafon character(1) NOT NULL,<br>
&nbsp; numero_cargo_persona integer,<br>
&nbsp; dedicacion_docente numeric(5,2),<br>
&nbsp; dedicacion_investigacion numeric(5,2),<br>
&nbsp; dedicacion_extension numeric(5,2),<br>
&nbsp; dedicacion_gestion numeric(5,2),<br>
&nbsp; CONSTRAINT rrhh_cargos_pkey PRIMARY KEY (numero_cargo,
cod_rrhh_importacion),<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_cargo_universidad_fkey FOREIGN KEY
(cod_cargo_universidad, cod_rrhh_escalafon, cod_rrhh_importacion)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_escalafones_universidades (cod_cargo_universidad,
cod_rrhh_escalafon, cod_rrhh_importacion) MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_fuente_financiamiento_fkey FOREIGN KEY
(cod_fuente_financiamiento)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES fuentes_financiamiento (cod_fuente_financiamiento)
MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_ona_rama_fkey FOREIGN KEY (cod_ona_rama,
cod_ona_disciplina, cod_ona_area)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES ona_jerarquias (cod_ona_rama, cod_ona_disciplina,
cod_ona_area) MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_ona_unidad_academica_fkey FOREIGN KEY
(cod_ona_unidad_academica)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES ona_unidades_academicas (cod_ona_unidad_academica)
MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_rrhh_agrupamiento_no_docente_fkey FOREIGN
KEY (cod_rrhh_agrupamiento_no_docente)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_agrupamientos_no_docentes
(cod_rrhh_agrupamiento_no_docente) MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_rrhh_equivalencia_cargo_fkey FOREIGN KEY
(cod_rrhh_equivalencia_cargo, cod_rrhh_escalafon)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_equivalencias_cargos
(cod_rrhh_equivalencia_cargo, cod_rrhh_escalafon) MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_rrhh_estado_cargo_fkey FOREIGN KEY
(cod_rrhh_estado_cargo)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_estados_cargos (cod_rrhh_estado_cargo) MATCH
SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_rrhh_estado_laboral_fkey FOREIGN KEY
(cod_rrhh_estado_laboral)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_estados_laborales (cod_rrhh_estado_laboral) MATCH
SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_rrhh_nivel_ensenianza_fkey FOREIGN KEY
(cod_rrhh_nivel_ensenianza)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_niveles_ensenianza (cod_rrhh_nivel_ensenianza)
MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_rrhh_situacion_laboral_fkey FOREIGN KEY
(cod_rrhh_situacion_laboral)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_situaciones_laborales
(cod_rrhh_situacion_laboral) MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cod_rrhh_tipo_planta_fkey FOREIGN KEY
(cod_rrhh_tipo_planta)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_tipos_planta (cod_rrhh_tipo_planta) MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_cargos_cuil_cuit_fkey FOREIGN KEY (cuil_cuit,
cod_rrhh_importacion)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_datos_personales (cuil_cuit,
cod_rrhh_importacion) MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT<br>
) ;<br>
-- cantidad de registros: 1.749.254<br>
<br>
<br>
CREATE TABLE rrhh_escalafones_universidades<br>
(<br>
&nbsp; cod_cargo_universidad character(4) NOT NULL,<br>
&nbsp; cod_rrhh_equivalencia_cargo integer NOT NULL,<br>
&nbsp; cod_rrhh_escalafon character(1) NOT NULL,<br>
&nbsp; cod_rrhh_importacion integer NOT NULL,<br>
&nbsp; cod_rrhh_nivel_ensenianza integer NOT NULL,<br>
&nbsp; descripcion character varying(30) NOT NULL,<br>
&nbsp; dedicacion character varying(30) NOT NULL,<br>
&nbsp; horas_dedicacion numeric(2) NOT NULL,<br>
&nbsp; sueldo_basico numeric(10,2) NOT NULL,<br>
&nbsp; remunerativo_bonificable numeric(10,2) NOT NULL,<br>
&nbsp; remunerativo_no_bonificable numeric(10,2) NOT NULL,<br>
&nbsp; no_remunerativo_bonificable numeric(10,2) NOT NULL,<br>
&nbsp; no_remunerativo_no_bonificable numeric(10,2) NOT NULL,<br>
&nbsp; otra_remuneraciones numeric(10,2) NOT NULL,<br>
&nbsp; decreto_1610 numeric(10,2) NOT NULL,<br>
&nbsp; gastos_representacion numeric(10,2) NOT NULL,<br>
&nbsp; CONSTRAINT rrhh_escalafones_universidades_pkey PRIMARY KEY
(cod_cargo_universidad, cod_rrhh_escalafon, cod_rrhh_importacion),<br>
&nbsp; CONSTRAINT
rrhh_escalafones_universidades_cod_rrhh_equivalencia_cargo_fkey FOREIGN
KEY (cod_rrhh_equivalencia_cargo, cod_rrhh_escalafon)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_equivalencias_cargos
(cod_rrhh_equivalencia_cargo, cod_rrhh_escalafon) MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_escalafones_universidades_cod_rrhh_importacion_fkey
FOREIGN KEY (cod_rrhh_importacion)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_importaciones (cod_rrhh_importacion) MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT
rrhh_escalafones_universidades_cod_rrhh_nivel_ensenianza_fkey FOREIGN
KEY (cod_rrhh_nivel_ensenianza)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_niveles_ensenianza (cod_rrhh_nivel_ensenianza)
MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT<br>
) ;<br>
-- cantidad de registros: 24.063<br>
<br>
<br>
CREATE TABLE rrhh_cubo_publicado_810<br>
(<br>
&nbsp; cod_rrhh_importacion integer,<br>
&nbsp; cod_pedido integer,<br>
&nbsp; cod_institucion integer,<br>
&nbsp; periodo date,<br>
&nbsp; fecha_desde timestamp without time zone,<br>
&nbsp; fecha_publicacion timestamp with time zone<br>
) ;<br>
-- cantidad de registros: 283<br>
<br>
<br>
CREATE TABLE rrhh_detalles_liquidaciones_haberes<br>
(<br>
&nbsp; cod_rrhh_concepto_liquidacion_haberes numeric(6) NOT NULL,<br>
&nbsp; cod_rrhh_importacion integer NOT NULL,<br>
&nbsp; numero_cargo numeric(9) NOT NULL,<br>
&nbsp; importe numeric(12,2) NOT NULL,<br>
&nbsp; anio_retroactivo numeric(4) NOT NULL,<br>
&nbsp; mes_retroactivo numeric(2) NOT NULL,<br>
&nbsp; CONSTRAINT
rrhh_detalles_liquidaciones_h_cod_rrhh_concepto_liquidacio_fkey FOREIGN
KEY (cod_rrhh_concepto_liquidacion_haberes, cod_rrhh_importacion)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_conceptos_liquidaciones_haberes
(cod_rrhh_concepto_liquidacion_haberes, cod_rrhh_importacion) MATCH
SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT
rrhh_detalles_liquidaciones_haberes_cod_rrhh_importacion_fkey FOREIGN
KEY (cod_rrhh_importacion)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_importaciones (cod_rrhh_importacion) MATCH SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT,<br>
&nbsp; CONSTRAINT rrhh_detalles_liquidaciones_haberes_numero_cargo_fkey
FOREIGN KEY (numero_cargo, cod_rrhh_importacion)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES rrhh_cargos (numero_cargo, cod_rrhh_importacion) MATCH
SIMPLE<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE RESTRICT ON DELETE RESTRICT<br>
) ;<br>
-- cantidad de registros: 29.566.415<br>
<br>
<br>
Consulta:<br>
---------<br>
<br>
explain analyze<br>
select t.periodo,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.cod_institucion,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.numero_cargo,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cuil_cuit,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cod_rrhh_escalafon,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cod_cargo_universidad,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; eu.descripcion as categ_univ_descripcion,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; eu.dedicacion as categ_univ_dedicacion,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cod_rrhh_equivalencia_cargo,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cod_ona_unidad_academica,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cod_fuente_financiamiento,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cod_rrhh_estado_laboral,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cod_rrhh_estado_cargo,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cod_rrhh_tipo_planta,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cod_rrhh_situacion_laboral,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.cod_rrhh_nivel_ensenianza,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.meses_antiguedad,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ca.horas_dedicacion,<br>
&nbsp;&nbsp;&nbsp; ca.numero_cargo_persona as nro_cargo_pers,<br>
&nbsp;&nbsp;&nbsp; ca.cod_rrhh_agrupamiento_no_docente,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; d.cod_rrhh_concepto_liquidacion_haberes,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; d.anio_retroactivo,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; d.mes_retroactivo,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; d.importe,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 as&nbsp; Cant_ConceptosXCargos&nbsp;&nbsp;&nbsp; <br>
from rrhh_cargos ca,<br>
&nbsp;&nbsp;&nbsp;&nbsp; rrhh_escalafones_universidades eu,<br>
&nbsp;&nbsp;&nbsp;&nbsp; rrhh_cubo_publicado_810 t,<br>
&nbsp;&nbsp;&nbsp;&nbsp; rrhh_detalles_liquidaciones_haberes d<br>
where t.cod_institucion&lt;&gt;7 and<br>
ca.cod_rrhh_importacion=t.cod_rrhh_importacion<br>
and eu.cod_cargo_universidad = ca.cod_cargo_universidad<br>
and eu.cod_rrhh_importacion = ca.cod_rrhh_importacion<br>
and eu.cod_rrhh_escalafon = ca.cod_rrhh_escalafon<br>
and (ca.cod_rrhh_escalafon = 'D') and (ca.cod_rrhh_nivel_ensenianza = 1
)<br>
and d.cod_rrhh_importacion=t.cod_rrhh_importacion<br>
and d.numero_cargo = ca.numero_cargo<br>
<br>
<br>
Explain Analyze:<br>
-----------------<br>
<br>
<a class="moz-txt-link-freetext" href="http://explain-analyze.info/query_plans/1499-nicolasdom-siu">http://explain-analyze.info/query_plans/1499-nicolasdom-siu</a><br>
<br>
<br>
Antes de correr la consulta se habia realizado:<br>
&nbsp; analyze rrhh_cargos;<br>
&nbsp; analyze rrhh_escalafones_universidades;<br>
&nbsp; analyze rrhh_cubo_publicado_810;<br>
&nbsp; analyze rrhh_detalles_liquidaciones_haberes ;<br>
<br>
<br>
Muchas Gracias!<br>
<br>
Nicolas.<br>
<br>
</body>
</html>


Attachment: unknown_filename
Description: text/html (12.3 KB)

Responses

pgsql-es-ayuda by date

Next:From: Jessie CordobaDate: 2007-11-29 14:05:26
Subject: Trasladar base de datos
Previous:From: Alvaro HerreraDate: 2007-11-29 13:37:04
Subject: Re: Como hago para retirarme de la lista

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group