SQL Query never ending... - Mailing list pgsql-general

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.