Re: Ayuda para optimizar consulta

*La consulta un poco modificada:*
SELECT
uploaddet_importcomp.indice,
case when historicotemp.activo ='N' then 'Beneficiario inactivo.' else null
end as motivo_res,
case when historicotemp.activo ='N' then 'I' else 'S' end as estado_res,
historicotemp.afitipocategoria categ,
historicotemp.clavebeneficiario
FROM
uploaddet_importcomp inner join nacer.historicotemp on
uploaddet_importcomp.fil_clasedoc||uploaddet_importcomp.fil_tipodoc||uploaddet_importcomp.fil_nrodoc
|| uploaddet_importcomp.fil_nacim::date=
historicotemp.aficlasedoc || historicotemp.afitipodoc ||
historicotemp.afidni || historicotemp.afifechanac::date
WHERE
date_trunc('month', uploaddet_importcomp.pres_fecha::date) =
historicotemp.periodo

*El explain analyze:*
"Hash Join (cost=11.57..911637.34 rows=48162 width=24) (actual
time=112204.576..112204.576 rows=0 loops=1)"
" Hash Cond: (((((historicotemp.aficlasedoc)::text ||
(historicotemp.afitipodoc)::text) || (historicotemp.afidni)::text) ||
((historicotemp.afifechanac)::date)::text) =
(((uploaddet_importcomp.fil_clasedoc || uploaddet_importcomp.fil_tipodoc)
|| uploaddet_ (...)"
" Join Filter: (date_trunc('month'::text,
((uploaddet_importcomp.pres_fecha)::date)::timestamp with time zone) =
historicotemp.periodo)"
" -> Seq Scan on historicotemp (cost=0.00..630843.31 rows=9632331
width=49) (actual time=15.166..95689.663 rows=9632331 loops=1)"
" -> Hash (cost=10.70..10.70 rows=70 width=164) (actual
time=0.029..0.029 rows=1 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on uploaddet_importcomp (cost=0.00..10.70 rows=70
width=164) (actual time=0.007..0.007 rows=1 loops=1)"
"Total runtime: 112204.718 ms"

*Las tablas:*
CREATE TABLE uploaddet_importcomp (
id_upload integer,
indice serial primary key,
fil_clasedoc text,fil_tipodoc text,fil_nrodoc text,fil_nacim text,fil_sexo
text,
fil_apellido text,fil_nombre text,pres_codigo text,pres_fecha text,
pres_cant text,ctrl_peso text,ctrl_talla text,ctrl_perim text,ctrl_ta text,
emb_semanas text,emb_peso text,emb_ta text,emb_fum text,emb_fpp text,
cc_fechadiag text,cc_diag text,cc_fechainicio text,cm_fechadiag text,
cm_carcinoma text,cm_size text,cm_ganglios text,cm_metastasis text,
cm_estadio text,cm_fechainicio text,estado text,motivo text,tipo_presta
text,
cuie text, afitipocategoria character varying(1),clavebeneficiario
character varying
);
CREATE INDEX ON uploaddet_importcomp (estado);
CREATE INDEX ON uploaddet_importcomp (fil_clasedoc,fil_tipodoc,fil_nrodoc);

CREATE TABLE nacer.historicotemp
(
id_smiafiliados character varying(20) NOT NULL,
clavebeneficiario character varying(16),
afiapellido character varying(40),
afinombre character varying(40),
afitipodoc character varying(5),
aficlasedoc character(1),
afidni character varying(12),
afisexo character(1),
afiprovincia character varying(20),
afilocalidad character varying(40),
afitipocategoria smallint,
afifechanac timestamp without time zone,
...
...
fechainscripcion timestamp without time zone,
fechadiagnosticoembarazo timestamp without time zone,
semanasembarazo integer,
fechaprobableparto timestamp without time zone,
fechaefectivaparto timestamp without time zone,
activo character(1),
...
motivobaja smallint,
cuieefectorasignado character varying(6),
cuielugaratencionhabitual character varying(6),
fum timestamp without time zone,
CONSTRAINT historicotemp_new_pkey PRIMARY KEY (id_smiafiliados, periodo)
)
;
ALTER TABLE nacer.historicotemp

CREATE INDEX historicotemp_afitipodoc_aficlasedoc_afidni_idx
ON nacer.historicotemp USING btree (afitipodoc, aficlasedoc, afidni);

CREATE INDEX historicotemp_idx1
ON nacer.historicotemp USING btree (clavebeneficiario);

CREATE INDEX historicotemp_idx_cuieefectorasignado
ON nacer.historicotemp USING btree (cuieefectorasignado);

CREATE INDEX historicotemp_idx_dni
ON nacer.historicotemp USING btree (afidni);

CREATE INDEX historicotemp_idx_periodo
ON nacer.historicotemp USING btree (periodo);

Guillermo Villanueva

El 11 de mayo de 2015, 11:27, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
escribió:

> Guillermo E. Villanueva escribió:
> > Amigos los molesto para pedirles si me pueden ayudar a optimizar la
> > siguiente consulta:
>
> Puedes mostrar la definicion de las tablas (idealmente un pg_dump -s) y
> un EXPLAIN ANALYZE de la consulta?
>
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>