SQL Query never ending...

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.