Re: [pgsql-es-ayuda] Aumentar el desempeño del servidor

Bueno, te mando todo lo que tengo, espero que tengas paciencia para
analizar.:

En la realidad es apenas una tabla que utilizo en un listado, pero como es
un listado con informaciones para la producción, entonces tenemos que
agrupar los datos para que sean mostrados de acuerdo con lo que ellos
quieren, por eso tuve que crear una function para atender las necesidades.
No te asustes con los nombres, este servidor está en Brasil.

==============================================================

La tabla:

CREATE TABLE "RPCPROC3"
(
"CDINT" numeric(14) NOT NULL DEFAULT
nextval(('public."RPCPROC3_DFRECNUM_seq"'::text)::regclass),
"NRROL" integer,
"NRITE" smallint,
"NRLAN" smallint,
"CDMAQ" character(15),
"DTLAV" date,
"HRINI" character(8),
"HRFIN" character(8),
"QTPRO" numeric(12,4),
"KGPRO" numeric(12,4),
"NOMEU" character(50),
"OBSER" character(250),
"PROGR" character(50),
"REPRO" smallint,
"CDEMP" smallint,
"CDCLI" integer,
"NOMEC" character(80),
"CDPRO" character(15),
"CDEMB" character(6),
"NOME1" character(65),
"QTCON" numeric(12,4),
"PESAG" numeric(12,4),
"DESCR_CDMAQ" character(50),
"SETOR_CDMAQ" smallint,
"CDEMP_CDMAQ" smallint,
"TPPRO_CDMAQ" smallint,
CONSTRAINT "PK_RPCPROC3" PRIMARY KEY ("CDINT")
)
WITH (OIDS=FALSE);
ALTER TABLE "RPCPROC3" OWNER TO postgres;
GRANT ALL ON TABLE "RPCPROC3" TO postgres;
GRANT ALL ON TABLE "RPCPROC3" TO "SISBAR";

-- Index: rpcproc3001

-- DROP INDEX rpcproc3001;

CREATE UNIQUE INDEX rpcproc3001
ON "RPCPROC3"
USING btree
("CDEMP", "NRROL", "NRITE", "NRLAN");

-- Index: rpcproc3002

-- DROP INDEX rpcproc3002;

CREATE INDEX rpcproc3002
ON "RPCPROC3"
USING btree
("DTLAV", "CDEMP_CDMAQ");

-- Index: rpcproc3003

-- DROP INDEX rpcproc3003;

CREATE INDEX rpcproc3003
ON "RPCPROC3"
USING btree
("CDEMP_CDMAQ", "DTLAV");

-- Index: rpcproc3004

-- DROP INDEX rpcproc3004;

CREATE INDEX rpcproc3004
ON "RPCPROC3"
USING btree
("CDEMP_CDMAQ", "CDEMP", "CDMAQ", "DTLAV", "HRINI", "HRFIN");

==============================================================

la función que uso en una View:

CREATE OR REPLACE FUNCTION "fOSsEmDtLav"(smallint, smallint, bpchar, date,
bpchar, bpchar)
RETURNS character varying AS
$BODY$Declare

-- Argumentos
pCDEMP_CDMAQ ALIAS FOR $1;
pCDEMP ALIAS FOR $2;
pCDMAQ ALIAS FOR $3;
pDTLAV ALIAS FOR $4;
pHRINI ALIAS FOR $5;
pHRFIN ALIAS FOR $6;

--Variaveis

sRetorno varchar(4000) := '' ;

tupla_RPCPROC3 record;

BEGIN

FOR tupla_RPCPROC3 IN

select distinct "NRROL" from "RPCPROC3"
WHERE
"CDEMP_CDMAQ" = pCDEMP_CDMAQ AND
"CDEMP" = pCDEMP AND
"CDMAQ" = pCDMAQ AND
"DTLAV" = pDTLAV AND
"HRINI" = pHRINI AND
"HRFIN" = pHRFIN

--order by "NRITE"

LOOP

If sRetorno != '' Then sRetorno := sRetorno || '-' ; end if;
sRetorno := sRetorno || '[' || tupla_RPCPROC3."NRROL" || ']' ;

END LOOP ;

return sRetorno;

END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;

=============================================================

La view:

CREATE OR REPLACE VIEW "vTEMPOMAQUINAS" AS
SELECT DISTINCT rpc1."CDEMP_CDMAQ", rpc1."CDEMP", rpc1."CDMAQ",
rpc1."DESCR_CDMAQ"::character varying AS "DESCR_CDMAQ", rpc1."DTLAV",
rpc1."HRINI", rpc1."HRFIN", round((substr(rpc1."HRFIN"::text, 1,
2)::integer::double precision + substr(rpc1."HRFIN"::text, 4, 2)::real /
60::double precision - (substr(rpc1."HRINI"::text, 1, 2)::integer::double
precision + substr(rpc1."HRINI"::text, 4, 2)::real / 60::double
precision))::numeric, 2) AS tempo, rpc1."PROGR", ( SELECT sum(rpc2."KGPRO")
AS sum
FROM "RPCPROC3" rpc2
WHERE rpc1."CDEMP_CDMAQ" = rpc2."CDEMP_CDMAQ" AND rpc1."CDEMP" =
rpc2."CDEMP" AND rpc1."CDMAQ" = rpc2."CDMAQ" AND rpc1."DTLAV" = rpc2."DTLAV"
AND rpc1."HRINI" = rpc2."HRINI" AND rpc1."HRFIN" = rpc2."HRFIN") AS ttpro,
"fOSsEmDtLav"(rpc1."CDEMP_CDMAQ", rpc1."CDEMP", rpc1."CDMAQ", rpc1."DTLAV",
rpc1."HRINI", rpc1."HRFIN") AS oss
FROM "RPCPROC3" rpc1
WHERE rpc1."HRINI" <> ''::bpchar AND rpc1."HRFIN" <> ''::bpchar
ORDER BY rpc1."CDEMP_CDMAQ", rpc1."CDEMP", rpc1."CDMAQ",
rpc1."DESCR_CDMAQ"::character varying, rpc1."DTLAV", rpc1."HRINI",
rpc1."HRFIN", round((substr(rpc1."HRFIN"::text, 1, 2)::integer::double
precision + substr(rpc1."HRFIN"::text, 4, 2)::real / 60::double precision -
(substr(rpc1."HRINI"::text, 1, 2)::integer::double precision +
substr(rpc1."HRINI"::text, 4, 2)::real / 60::double precision))::numeric,
2), rpc1."PROGR", ( SELECT sum(rpc2."KGPRO") AS sum
FROM "RPCPROC3" rpc2
WHERE rpc1."CDEMP_CDMAQ" = rpc2."CDEMP_CDMAQ" AND rpc1."CDEMP" =
rpc2."CDEMP" AND rpc1."CDMAQ" = rpc2."CDMAQ" AND rpc1."DTLAV" = rpc2."DTLAV"
AND rpc1."HRINI" = rpc2."HRINI" AND rpc1."HRFIN" = rpc2."HRFIN"),
"fOSsEmDtLav"(rpc1."CDEMP_CDMAQ", rpc1."CDEMP", rpc1."CDMAQ", rpc1."DTLAV",
rpc1."HRINI", rpc1."HRFIN");

==============================================================

y finalmente el Select:

SELECT

vTEMPOMAQUINAS."CDEMP_CDMAQ", vTEMPOMAQUINAS."CDMAQ",
vTEMPOMAQUINAS."DESCR_CDMAQ", vTEMPOMAQUINAS."DTLAV",
vTEMPOMAQUINAS."HRINI", vTEMPOMAQUINAS."HRFIN", vTEMPOMAQUINAS."tempo",
vTEMPOMAQUINAS."PROGR", vTEMPOMAQUINAS."ttpro", vTEMPOMAQUINAS."oss"

FROM

"public"."vTEMPOMAQUINAS" vTEMPOMAQUINAS

WHERE

vTEMPOMAQUINAS."DTLAV" >= '2008-04-01' AND

vTEMPOMAQUINAS."DTLAV" <= '2008-04-02'

=============================================================

Atentamente,

Antonio Salas Mena
t. 663 078 276

--------------------------------------------------
From: "Martin Marques" <martin(at)marquesminen(dot)com(dot)ar>
Sent: Monday, May 05, 2008 4:01 PM
To: "Antonio Salas Mena" <antoniosm1966(at)hotmail(dot)com>
Cc: <pgsql-es-ayuda(at)postgresql(dot)org>; <jonatha(at)bardusch(dot)com(dot)br>
Subject: Re: [pgsql-es-ayuda] Aumentar el desempeño del servidor

> Antonio Salas Mena escribió:
>> Martin,
>>
>> Perdón, pero ¿que quieres decir "con algo de estructura"?
>>
>> Algunos datos (registros)? select's?
>
> No, la estructura de tablas que intervienen en el SELECT, pero creo que
> con la consulta va a bastar.
>