From: | "Jose Antonio Zacarias Rios" <jzacariasr(at)grupointerclan(dot)com> |
---|---|
To: | <pgsql-es-ayuda(at)postgresql(dot)org> |
Subject: | ayuda con consulta |
Date: | 2006-02-28 22:41:52 |
Message-ID: | B70EE6600A926B47B4EB279DA404765D3452E1@interexch01.grupointerclan.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-es-ayuda |
buen dia a todos.
estoy ejecutando una consulta (la adjunto)
SELECT "APU"."PR_GET_USERS_PER_DAY2"('22/02/2006');
el problema es que me tarda muchisimo en devolverme lo que solicito, la cantidad de registros es de aprox. 10 millones, esta corriendo sobre un amd64 3000, 1 Gb RAM, y tengo "shared_buffers = 16384"
que es lo que puede estar mal..?? el procesador me esta trabajando solamente al 10%, y el disco duro no se ve que este trabajando muy estresado..
podrian apoyarme.?
(Me estoy iniciando en esto y la consulta me la propocionaron)
agradeciendo de antemano su apoyo.
Saludos. Antonio Zacarías
--.....(la consulta es la siguiente)
-- Function: "APU"."PR_GET_USERS_PER_DAY2"("varchar")
-- DROP FUNCTION "APU"."PR_GET_USERS_PER_DAY2"("varchar");
CREATE OR REPLACE FUNCTION "APU"."PR_GET_USERS_PER_DAY2"("varchar")
RETURNS int4 AS
$BODY$
declare
s_report_date alias for $1;
d_report_date date;
result integer;
limit1 numeric;
limit2 numeric;
begin
d_report_date:= to_date ( s_report_date , 'dd/mm/yyyy');
limit1 := to_number
(trim(to_char (d_report_date,'yyyymmdd'))||
trim(to_char (date_part ('hour',d_report_date),'00'))||
trim(to_char (date_part ('minute',d_report_date),'00'))||
trim(to_char (date_part ('second',d_report_date),'00'))
,'99999999999999');
limit2 := to_number
(trim(to_char (d_report_date,'yyyymmdd'))||
trim(to_char (date_part ('hour',d_report_date),'23'))||
trim(to_char (date_part ('minute',d_report_date),'59'))||
trim(to_char (date_part ('second',d_report_date),'59'))
,'99999999999999');
insert into "APU"."TBL_REPORT_USERS" (report_date, id_username, id_server, id_feature, id_host, count_handles, min_elapsed_time,
avg_elapsed_time, max_elapsed_time, elapsed_time )
(
select
s_report_date as report_date,
id_username,
id_server,
id_feature,
id_host,
count_handles,
min_elapsed_time,
avg_elapsed_time,
max_elapsed_time,
elapsed_time
from
(
select
id_username,
id_server,
id_feature,
id_host,
count(*) as count_handles,
min (ts_interval) as min_elapsed_time,
avg (ts_interval) as avg_elapsed_time,
max (ts_interval) as max_elapsed_time,
sum (ts_interval) as elapsed_time
from
(
select
id_username,
id_server,
id_feature,
id_host,
handle,
n_date_in,
ts_in,
n_date_out,
ts_out,
age ( ts_out, ts_in ) as ts_interval
from
(
select
id_username,
id_server,
id_module as id_feature,
id_host,
handle,
n_date_in,
"APU"."fn_limit_inf"
(
min (
to_timestamp
(
trim(to_char (access_out_report,'yyyy-mm-dd'))||' '||
trim(to_char (date_part ('hour',hour_out_report),'09'))||':'||
trim(to_char (date_part ('minute',hour_out_report),'09'))||':'||
trim(to_char (date_part ('second',hour_out_report),'09')),
'yyyy-mm-dd HH:mi:ss'
)
) ,
CAST (s_report_date AS varchar) ,
id_username ,
id_server ,
id_module ,
id_host ,
handle) as ts_in,
max (n_date_out) as n_date_out,
"APU"."fn_limit_sup"
(
max
(
to_timestamp
(
trim(to_char (access_out_report,'yyyy-mm-dd'))||' '||
trim(to_char (date_part ('hour',hour_out_report),'09'))||':'||
trim(to_char (date_part ('minute',hour_out_report),'09'))||':'||
trim(to_char (date_part ('second',hour_out_report),'09')),
'yyyy-mm-dd HH:mi:ss'
)
) ,
CAST (s_report_date AS varchar) ,
id_username ,
id_server ,
id_module ,
id_host ,
handle) as ts_out
from "APU"."LMOUT"
where n_date_out between limit1 and limit2
group by
id_username,
id_server,
id_module,
id_host,
handle,
n_date_in
) LMOUT_GROUP
) LMOUT_G
group by
id_username,
id_server,
id_feature,
id_host
) SQ_LMOUT
);
return 0;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "APU"."PR_GET_USERS_PER_DAY2"("varchar") OWNER TO postgres;
From | Date | Subject | |
---|---|---|---|
Next Message | Brunil Dalila Romero M. | 2006-02-28 22:48:26 | generar gráficas |
Previous Message | Alvaro Herrera | 2006-02-28 22:37:29 | Re: pasar de segundos a hh:mm:ss |