Hi all,
can someone help me?
I don't know if this is the correct
list for this matter. If I'm wrong, please bear with me and
point me in right direction.
I have a large query which, largely
after more than 24 hours running, doesn't come to an end;
However I can see, using system tools, that the postgres process
keeps, although slowly, reading and writing bytes and the
"afinity" dedicated cores are at 6.25% .
I tried https://www.depesz.com/ but
the query was rejected.
-- Scenario:
-- OS: Windows 12 R2 Standard
-- RAM: 128GB
-- CPU: Intel Xeon E5-2640 v4 @2.40GH (2 processors) (16 cores)
-- PostgreSQL 9.6
-- Database category: OLAP (Tables used in the query are not
dynamic and the statistics for all of them are up to date)
--
*******************************************************************************************************
-- The query you'll see later bellow uses the following tables:
--
-- This facts table has around 1500000 rows.
CREATE TEMPORARY TABLE analise_transac_mes
( ctrl_cod_valida integer NOT NULL,
cod_controlo integer NOT NULL,
causa character varying(300),
Fornecedor text,
ordem integer,
num_serie text,
titulo text,
tipo_produto text,
data_dia_hora text,
cod_viatura text,
cod_licenca text,
val_inval_excl character varying(12),
mes character varying(25),
tipo_licenca character varying(25),
data_controlo timestamp without time zone NOT NULL DEFAULT
now(),
utilizador character varying(30) DEFAULT "current_user"(),
senha_entrega character varying(12),
senha_fornecedor_entrega character varying(12),
proc_extra character(3),
quando date DEFAULT now(),
cod_cliente character varying(15),
tem_venda character varying(6),
CONSTRAINT pk_analise_transac_mes PRIMARY KEY
(ctrl_cod_valida);
--
SELECT pg_prewarm('TT_Analise_Transac_Oper_Mes');
--
-- With following indexes:
CREATE INDEX IF NOT EXISTS
TT_Analise_Transac_Oper_Mes_Fornecedor ON
TT_Analise_Transac_Oper_Mes(Fornecedor);
CREATE INDEX IF NOT EXISTS TT_Analise_Transac_Oper_Mes_encomenda
ON TT_Analise_Transac_Oper_Mes(encomenda);
CREATE INDEX IF NOT EXISTS
TT_Analise_Transac_Oper_Mes_val_inval_excl ON
TT_Analise_Transac_Oper_Mes(val_inval_excl);
CREATE INDEX IF NOT EXISTS
TT_Analise_Transac_Oper_Mes_proc_extra ON
TT_Analise_Transac_Oper_Mes(proc_extra);
CREATE INDEX IF NOT EXISTS
TT_Analise_Transac_Oper_Mes_senha_entrega ON
TT_Analise_Transac_Oper_Mes(senha_entrega);
CREATE INDEX IF NOT EXISTS
TT_Analise_Transac_Oper_Mes_senha_fornecedor_entrega ON
TT_Analise_Transac_Oper_Mes(senha_fornecedor_entrega);
--
--
********************************************************************************
-- Following table has 1800 rows
--
CREATE TABLE bilhetica_base_2017_01.lst_km_por_etapa_2017
(
cod_encomenda text NOT NULL,
encomenda character varying(150),
encomenda_amtl character varying(150),
Fornecedor character varying(60) NOT NULL,
etapa_km numeric(13,9),
mes_ref character varying(15) NOT NULL,
utilizador character varying(30) DEFAULT "current_user"(),
data timestamp without time zone DEFAULT now(),
notas character varying,
caracter character(1),
senha_entrega character varying(12),
senha_fornecedor_entrega character varying(12),
CONSTRAINT pk_lst_km_por_etapa_2017 PRIMARY KEY
(cod_encomenda, Fornecedor, mes_ref),
CONSTRAINT dv_lst_km_por_etapa_caracter CHECK (caracter = ANY
(ARRAY[NULL::bpchar, 'P'::bpchar, 'D'::bpchar]))
);
SELECT pg_prewarm('TT_Km_por_Etapa_2017');
--
--With following indexes:
CREATE INDEX i_Km_por_Etapa_Fornecedor ON TT_Km_por_Etapa_2017
(Fornecedor);
CREATE INDEX i_Km_por_Etapa_Mes_Ref ON TT_Km_por_Etapa_2017
(Mes_Ref);
CREATE INDEX i_Km_por_Etapa_Cod_encomenda ON
TT_Km_por_Etapa_2017 (Cod_encomenda);
CREATE INDEX i_Km_por_Etapa_encomenda ON TT_Km_por_Etapa_2017
(encomenda);
--
--
********************************************************************************
-- This table has 90 rows
CREATE TABLE bilhetica_base_2017_01.encomendas_n2v_2017
(
senha_fornecedor_entrega text,
senha_entrega text,
cod_encomenda character varying(12) NOT NULL,
desig_encomenda_aml text,
desig_encomenda_polis_antigo text,
desig_encomenda_polis_novo text,
encomenda_base text,
modalidade text,
tipo_aml text,
tipo_polis text,
tarifa text,
ultima_actualizacao text,
ano_corrente text,
pvp_ano_corrente numeric(7,3),
desconto_ano_corrente numeric(6,2),
mes_pvp_ano_corrente character varying(13),
pvp_ref_ano_corrente numeric(7,3),
mes_pvp_ref_ano_corrente character varying(13),
siit_4_18_e_sub23 character varying(25),
entra_nas_contas character(1),
etapa_km_julho numeric(6,3),
mes_ref character varying(13) NOT NULL,
versao text,
notas_aml text,
notas_polis text,
notas text,
CONSTRAINT pk_encomendas_n2v_2017 PRIMARY KEY (cod_encomenda,
mes_ref));
--
SELECT pg_prewarm('TT_encomendas_N2V_2017');
--
-- With following indexes;
CREATE INDEX i_encomendas_n2v_2017_senha_entrega ON
TT_encomendas_N2v_2017 (senha_entrega);
CREATE INDEX i_encomendas_n2v_2017_senha_fornecedor_entrega ON
TT_encomendas_N2v_2017 (senha_fornecedor_entrega);
CREATE INDEX i_encomendas_n2v_2017_encomenda_base ON
TT_encomendas_N2v_2017 (encomenda_base);
--
********************************************************************************
-- This table has 7 rows
CREATE TEMPORARY TABLE TT_EOTB1 AS
SELECT Fornecedor, encomenda_Base, COUNT(*) as EOTB_Etapas
FROM TT_Analise_Transac_Oper_Mes AS AT
JOIN TT_encomendas_N2V_2017 AS N2V
ON N2V.senha_entrega = AT.senha_entrega AND
N2V.senha_fornecedor_entrega = AT.senha_fornecedor_entrega
GROUP BY Fornecedor, encomenda_Base;
--
-- With following indexes;
CREATE INDEX I_Fornecedor ON TT_EOTB1 (Fornecedor);
CREATE INDEX I_encomenda_Base ON TT_EOTB1 (encomenda_Base);
--
SELECT pg_prewarm('TT_Analise_Transac_Oper_Mes');
--
--
--
********************************************************************************
--
-- And then I have this query I've been fighting with for
months:
-- With a shorter number of rows in facts table
analise_transac_mes it does what is expected quickly but, with
1500000 rows I never saw it finishing.
-- The insert part may be ignored for now.
-- INSERT INTO
blt_comp_e_rep_2017_03.Calc_Rec_Com_h_Ago_2017(
-- Fornecedor, num_serie, encomenda,
encomenda_base, etapas, etapa_km, distancia_percorrida,
-- distancia_percorrida_otb, xpto,
variancia, desvio_padrao, escalao_quilometrico,
-- tarifa_ocasional_ref,
tarifa_passe_ref, registos, receita_comercial, senha_entrega,
senha_fornecedor_entrega)
--
select Final.Fornecedor,
Final.Num_Serie, Final.encomenda, Final.encomenda_Base,
Final.Etapas, Final.Etapa_Km, Final.Distancia_Percorrida,
Final.Distancia_Percorrida_OTB,
Final.XPTO,
Final.Variancia,
Final.Desvio_Padrao, Final.Escalao_Quilometrico,
Tarifa_Ocasional_Ref, Tarifa_Passe_Ref, NULL::INTEGER as
Registos,
CASE
WHEN
Final.Tarifa_Ocasional_Ref * Final.Etapas <= Tarifa_Passe_Ref
THEN Final.Tarifa_Ocasional_Ref * Final.Etapas
ELSE Final.Tarifa_Passe_Ref
END AS Receita_Comercial,
Final.senha_entrega,
Final.senha_fornecedor_entrega
from
(Select NS.Fornecedor, NS.Num_Serie,
NS.encomenda, NS.encomenda_Base, NS.Etapas, NS.Etapa_Km,
NS.Distancia_Percorrida,
Distancia_Percorrida_OTB,
TB.XPTO, TB.Variancia, TB.Desvio_Padrao,
TB.Escalao_Quilometrico,
CASE
WHEN
(TB.Escalao_Quilometrico > 0 AND TB.EScalao_Quilometrico
<= 5) THEN 1.110
WHEN
(TB.Escalao_Quilometrico > 5 AND TB.EScalao_Quilometrico
<= 9) THEN 1.425
WHEN
(TB.Escalao_Quilometrico > 9 AND TB.EScalao_Quilometrico
<= 17) THEN 1.730
WHEN
(TB.Escalao_Quilometrico > 17 AND TB.EScalao_Quilometrico
<= 24) THEN 2.225
END AS Tarifa_Ocasional_Ref,
--
CASE
WHEN
(TB.EScalao_Quilometrico > 0 AND TB.EScalao_Quilometrico
<= 5) THEN 27.10
WHEN
(TB.EScalao_Quilometrico > 5 AND TB.EScalao_Quilometrico
<= 9) THEN 38.65
WHEN
(TB.EScalao_Quilometrico > 9 AND TB.EScalao_Quilometrico
<= 13) THEN 48.80
WHEN
(TB.EScalao_Quilometrico > 13 AND TB.EScalao_Quilometrico
<= 17) THEN 60.30
WHEN
(TB.EScalao_Quilometrico > 17 AND TB.EScalao_Quilometrico
<= 21) THEN 70.20
END AS Tarifa_Passe_Ref,
senha_entrega,
senha_fornecedor_entrega
--
FROM
(SELECT AT.Fornecedor, AT.Num_Serie,
AT.encomenda, AT.senha_entrega, AT.senha_fornecedor_entrega,
AT.encomenda_Base, AT.Etapas, E.Etapa_Km, AT.Etapas*E.Etapa_Km
as Distancia_Percorrida,
DOTB.DOTB_Distancias /
EOTB.EOTB_Etapas as Distancia_Percorrida_OTB
FROM (SELECT Fornecedor,
AT.num_serie, encomenda, Cod_encomenda, AT.senha_entrega,
AT.senha_fornecedor_entrega, encomenda_Base, COUNT(*) as
Etapas -- Etapas por Num_Serie, por Título e por Fornecedor
FROM
TT_Analise_Transac_Oper_Mes AS AT
JOIN TT_encomendas_N2V_2017
AS N2V
ON N2V.senha_entrega =
AT.senha_entrega AND N2V.senha_fornecedor_entrega =
AT.senha_fornecedor_entrega
GROUP BY Fornecedor,
Num_Serie, encomenda_Base, encomenda, Cod_encomenda,
AT.senha_entrega, AT.senha_fornecedor_entrega) as AT
--
INNER JOIN (SELECT
Fornecedor, encomenda, Cod_encomenda, Etapa_Km
FROM
TT_Km_por_Etapa_2017
WHERE Mes_Ref
= 'maio') AS E
--
ON AT.Fornecedor =
E.Fornecedor AND AT.Cod_encomenda = E.Cod_encomenda
--
-- aqui já
INNER JOIN (SELECT Fornecedor, encomenda_Base,
EOTB_Etapas
FROM TT_EOTB1) AS EOTB
--
ON EOTB.Fornecedor = AT.Fornecedor AND
EOTB.encomenda_Base = AT.encomenda_Base
--
INNER JOIN
--
(SELECT Fornecedor, encomenda_Base, sum(Etapas*Etapa_Km)
as DOTB_Distancias
FROM (SELECT Fornecedor, encomenda_Base, Etapas,
Etapa_KM, Etapas*Etapa_Km as DOTB_Distancias
FROM (SELECT EOTTB.Fornecedor, encomenda_Base,
EOTTB.encomenda, EOTTB.Cod_encomenda, Etapas, Etapa_Km
FROM (SELECT Fornecedor, encomenda,
N2V.Cod_encomenda, encomenda_Base, COUNT(*) as Etapas
FROM TT_Analise_Transac_Oper_Mes
AS AT
JOIN
TT_encomendas_N2V_2017 AS N2V
ON N2V.senha_entrega
= AT.senha_entrega AND N2V.senha_fornecedor_entrega =
AT.senha_fornecedor_entrega
GROUP BY Fornecedor,
encomenda_Base, encomenda, Cod_encomenda) as EOTTB
JOIN (SELECT Fornecedor,
encomenda, Cod_encomenda, Etapa_Km
FROM
TT_Km_por_Etapa_2017 AS K
WHERE K.Mes_Ref = 'maio'
) AS OTEK
ON EOTTB.Fornecedor =
OTEK.Fornecedor AND EOTTB.Cod_encomenda = OTEK.Cod_encomenda) as
DOT) as DOTB
--
GROUP BY DOTB.Fornecedor, DOTB.encomenda_Base) AS DOTB
--
ON DOTB.Fornecedor = EOTB.Fornecedor AND DOTB.encomenda_Base
= EOTB.encomenda_Base) AS NS
--
INNER JOIN
--
(SELECT Base.Fornecedor, Base.encomenda_Base,
trunc(Base.XPTO,3) AS XPTO, EOTB.Etapas,
TRUNC(Base.XPTO / EOTB.Etapas,3) AS
Variancia, TRUNC(SQRT(Base.XPTO / EOTB.Etapas),3) AS
Desvio_Padrao,
DOTB1.DOTB_Distancias/EOTB1.EOTB_Etapas +
SQRT(Base.XPTO / EOTB.Etapas) AS Escalao_Quilometrico
FROM
(SELECT AT.Fornecedor, AT.encomenda_Base, SUM(AT.Etapas *
(E.Etapa_Km - (DOTB_Distancias/EOTB.EOTB_Etapas))^2) AS XPTO
--
FROM (SELECT Fornecedor, AT.num_serie, encomenda,
Cod_encomenda, encomenda_Base, COUNT(*) as Etapas
FROM TT_Analise_Transac_Oper_Mes AS AT
JOIN TT_encomendas_N2V_2017 AS N2V
ON N2V.senha_entrega =
AT.senha_entrega AND N2V.senha_fornecedor_entrega =
AT.senha_fornecedor_entrega
GROUP BY Fornecedor, Num_Serie, encomenda_Base,
encomenda, Cod_encomenda) as AT
--
INNER JOIN (SELECT Fornecedor, encomenda,
Cod_encomenda, Etapa_Km
FROM TT_Km_por_Etapa_2017 AS K
WHERE K.Mes_Ref = 'maio' ) AS E
--
ON AT.Fornecedor = E.Fornecedor AND
AT.Cod_encomenda = E.Cod_encomenda
--
-- aqui já
INNER JOIN (SELECT Fornecedor, encomenda_Base,
EOTB_Etapas
FROM TT_EOTB1) AS EOTB
--
ON EOTB.Fornecedor = AT.Fornecedor AND
EOTB.encomenda_Base = AT.encomenda_Base
--
INNER JOIN
--
(SELECT Fornecedor, encomenda_Base, sum(Etapas*Etapa_Km)
as DOTB_Distancias
FROM (SELECT Fornecedor, encomenda_Base, Etapas,
Etapa_KM, Etapas*Etapa_Km as DOTB_Distancias
FROM (SELECT EOTTB.Fornecedor, encomenda_Base,
EOTTB.encomenda, Etapas, Etapa_Km
FROM (SELECT Fornecedor, encomenda,
Cod_encomenda, encomenda_Base, COUNT(*) as Etapas
FROM TT_Analise_Transac_Oper_Mes
AS AT
JOIN
TT_encomendas_N2V_2017 AS N2V
ON N2V.senha_entrega
= AT.senha_entrega AND N2V.senha_fornecedor_entrega =
AT.senha_fornecedor_entrega
GROUP BY Fornecedor,
encomenda_Base, encomenda, Cod_encomenda) as EOTTB
JOIN (SELECT Fornecedor,
encomenda, Cod_encomenda, Etapa_Km
FROM
TT_Km_por_Etapa_2017 AS K
WHERE K.Mes_Ref = 'maio'
) AS OTEK
ON EOTTB.Fornecedor =
OTEK.Fornecedor AND EOTTB.Cod_encomenda = OTEK.Cod_encomenda) as
DOT) as DOTB
--
GROUP BY DOTB.Fornecedor, DOTB.encomenda_Base) AS DOTB
--
ON DOTB.Fornecedor = EOTB.Fornecedor AND DOTB.encomenda_Base
= EOTB.encomenda_Base
GROUP BY AT.Fornecedor, AT.encomenda_Base) AS Base
--
INNER JOIN
--
(SELECT Fornecedor, encomenda_Base, EOTB_Etapas AS Etapas
FROM TT_EOTB1) AS EOTB
--
ON EOTB.Fornecedor = Base.Fornecedor AND
EOTB.encomenda_Base = Base.encomenda_Base
--
INNER JOIN
--
(SELECT Fornecedor, encomenda_Base, sum(Etapas*Etapa_Km)
as DOTB_Distancias
FROM (SELECT Fornecedor, encomenda_Base, Etapas,
Etapa_KM, Etapas*Etapa_Km as DOTB_Distancias
FROM (SELECT EOTTB.Fornecedor, encomenda_Base,
EOTTB.encomenda, EOTTB.Cod_encomenda, Etapas, Etapa_Km
FROM (SELECT Fornecedor, encomenda,
Cod_encomenda, encomenda_Base, COUNT(*) as Etapas
FROM TT_Analise_Transac_Oper_Mes
AS AT
JOIN
TT_encomendas_N2V_2017 AS N2V
ON N2V.senha_entrega
= AT.senha_entrega AND N2V.senha_fornecedor_entrega =
AT.senha_fornecedor_entrega
GROUP BY Fornecedor,
encomenda_Base, encomenda, Cod_encomenda) as EOTTB
JOIN (SELECT Fornecedor,
encomenda, T.Cod_encomenda, Etapa_Km
FROM
TT_Km_por_Etapa_2017 AS K
JOIN
TT_encomendas_N2V_2017 AS T
ON K.Cod_encomenda =
T.Cod_encomenda
WHERE K.Mes_Ref = 'maio'
) AS OTEK
ON EOTTB.Fornecedor =
OTEK.Fornecedor AND EOTTB.Cod_encomenda = OTEK.Cod_encomenda) as
DOT) as DOTB
--
GROUP BY DOTB.Fornecedor, DOTB.encomenda_Base) AS DOTB1
--
ON DOTB1.Fornecedor = Base.Fornecedor AND
DOTB1.encomenda_Base = Base.encomenda_Base
--
INNER JOIN
--
(SELECT Fornecedor, encomenda_Base, EOTB_Etapas
FROM TT_EOTB1 AS AT) AS EOTB1
--
ON EOTB1.Fornecedor = Base.Fornecedor AND
EOTB1.encomenda_Base = Base.encomenda_Base) AS TB
--
ON NS.Fornecedor = TB.Fornecedor AND NS.encomenda_Base =
TB.encomenda_Base ) AS Final
--
--
***********************************************************************************************
-- This set of SQL instructions is the main part of a function.
Thanks in advance
Dias Costa
--
J. M. Dias Costa
Telef. 214026948 Telem. 939307421
Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.
Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não respeitem o
malfadado acordo ortográfico.
