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.
2018-06-20 18:35 GMT-03:00 DiasCosta <diascosta@diascosta.org>:
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.
Here is a good start...
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.
Are you talking about http://explain.depesz.com ?? If yes you should copy
and paste the EXPLAIN output of your query... If you don't do it yet,
please do it and send to us the results .
And use some external service like pastebin.com to send long SQL statements.
Regards,
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
On Wed, Jun 20, 2018 at 3:34 PM, Fabrízio de Royes Mello <
fabrizio@timbira.com.br> wrote:
And use some external service like pastebin.com to send long SQL
statements.
Or just attach a text file - those are allowed on these lists.
David J.
Hello David and Fabrízio,
The names of the tables and indexes differ from the original script.
Only the names.
This is the query plan for only 19684 rows.
I have another query running for around 30000 rows, but it takes an
eternity to finish.
If it finishes in acceptable time I'll make it available to you.
As in previous times when trying to optimize, I submitted this execution
plan to https://explain.depesz.com but now, as it happened then, I am
not able to extract information to decide me on what to do or to decide
on a path leading to optimization.
The environment conditions are exactly the same as described in my
previous message.
Thank you in advance for your attention and help.
They will be greatly appreciated.
Dias Costa
--
*******************************************************************************
"QUERY PLAN"
"Nested Loop (cost=3336.02..3353.51 rows=1 width=528) (actual
time=867.213..6452673.494 rows=19684 loops=1)"
" Output: at_2.operador, at_2.num_serie, at_2.titulo,
n2v_1.titulo_base, (count(*)), tt_km_por_etapa_2017.etapa_km,
(((count(*)))::numeric * tt_km_por_etapa_2017.etapa_km),
((sum((((count(*)))::numeric * k.etapa_km))) /
(tt_eotb1.eotb_etapas)::numeric), tr (...)"
" Join Filter: ((at_2.operador = at_5.operador) AND
(tt_eotb1.titulo_base = n2v_4.titulo_base))"
" Rows Removed by Join Filter: 157472"
" Buffers: local hit=418076253"
" -> Nested Loop (cost=2658.99..2673.26 rows=1 width=782) (actual
time=744.047..6272023.716 rows=19684 loops=1)"
" Output: at_2.operador, at_2.num_serie, at_2.titulo,
n2v_1.titulo_base, (count(*)), at_2.ticket_code,
at_2.ticket_operator_code, tt_km_por_etapa_2017.etapa_km,
tt_km_por_etapa_2017.operador, tt_eotb1.eotb_etapas, tt_eotb1.operador,
tt_eotb1.titulo_b (...)"
" Join Filter: ((at_2.operador = at_4.operador) AND
(tt_eotb1.titulo_base = n2v_3.titulo_base))"
" Rows Removed by Join Filter: 157472"
" Buffers: local hit=418064955"
" -> Nested Loop (cost=1329.63..1337.01 rows=1 width=686)
(actual time=369.637..1236.464 rows=19684 loops=1)"
" Output: at_2.operador, at_2.num_serie, at_2.titulo,
n2v_1.titulo_base, (count(*)), at_2.ticket_code,
at_2.ticket_operator_code, tt_km_por_etapa_2017.etapa_km,
tt_km_por_etapa_2017.operador, tt_eotb1.eotb_etapas, tt_eotb1.operador,
tt_eotb1.ti (...)"
" Buffers: local hit=558900"
" -> Nested Loop (cost=1329.49..1336.74 rows=1 width=614)
(actual time=369.631..1126.109 rows=19684 loops=1)"
" Output: at_2.operador, at_2.num_serie, at_2.titulo,
n2v_1.titulo_base, (count(*)), at_2.ticket_code,
at_2.ticket_operator_code, tt_km_por_etapa_2017.etapa_km,
tt_km_por_etapa_2017.operador, (sum((((count(*)))::numeric *
k.etapa_km))), a (...)"
" Buffers: local hit=519532"
" -> Nested Loop (cost=1329.36..1336.47 rows=1
width=542) (actual time=369.625..1015.389 rows=19684 loops=1)"
" Output: at_2.operador, at_2.num_serie,
at_2.titulo, n2v_1.titulo_base, (count(*)), at_2.ticket_code,
at_2.ticket_operator_code, tt_km_por_etapa_2017.etapa_km,
tt_km_por_etapa_2017.operador, (sum((((count(*)))::numeric * k.etapa_km
(...)"
" Buffers: local hit=480164"
" -> Nested Loop (cost=1329.22..1336.20 rows=1
width=470) (actual time=369.614..895.215 rows=19684 loops=1)"
" Output: at_2.operador, at_2.num_serie,
at_2.titulo, n2v_1.titulo_base, (count(*)), at_2.ticket_code,
at_2.ticket_operator_code, tt_km_por_etapa_2017.etapa_km,
tt_km_por_etapa_2017.operador, (sum((((count(*)))::numeric * k.et (...)"
" Buffers: local hit=440796"
" -> Merge Join (cost=1328.95..1333.92
rows=1 width=358) (actual time=369.586..503.283 rows=19684 loops=1)"
" Output: at_2.operador,
at_2.num_serie, at_2.titulo, n2v_1.titulo_base, (count(*)),
at_2.ticket_code, at_2.ticket_operator_code, n2v_1.cod_titulo,
(sum((((count(*)))::numeric * k.etapa_km))), at_1.operador, n2v.titulo_b
(...)"
" Merge Cond: (at_1.operador =
at_2.operador)"
" Join Filter: (n2v_1.titulo_base =
n2v.titulo_base)"
" Rows Removed by Join Filter: 157472"
" Buffers: local hit=22563"
" -> GroupAggregate
(cost=672.74..674.98 rows=1 width=96) (actual time=119.552..128.686
rows=9 loops=1)"
" Output: at_1.operador,
n2v.titulo_base, sum((((count(*)))::numeric * k.etapa_km))"
" Group Key: at_1.operador,
n2v.titulo_base"
" Buffers: local hit=11295"
" -> Merge Join
(cost=672.74..674.96 rows=1 width=88) (actual time=119.517..128.610
rows=41 loops=1)"
" Output:
at_1.operador, n2v.titulo_base, (count(*)), k.etapa_km"
" Merge Cond:
(at_1.operador = (k.operador)::text)"
" Join Filter:
((n2v.cod_titulo)::text = k.cod_titulo)"
" Rows Removed by Join
Filter: 3649"
" Buffers: local hit=11295"
" -> GroupAggregate
(cost=656.21..657.63 rows=57 width=188) (actual time=118.675..126.741
rows=41 loops=1)"
" Output:
at_1.operador, at_1.titulo, n2v.cod_titulo, n2v.titulo_base, count(*),
n2v.cod_titulo"
" Group Key:
at_1.operador, n2v.titulo_base, at_1.titulo, n2v.cod_titulo"
" Buffers: local
hit=11268"
" -> Sort
(cost=656.21..656.35 rows=57 width=138) (actual time=118.668..120.741
rows=25270 loops=1)"
" Output: at_1.operador, at_1.titulo, n2v.titulo_base, n2v.cod_titulo"
" Sort Key: at_1.operador, n2v.titulo_base, at_1.titulo, n2v.cod_titulo"
" Sort Method: quicksort Memory: 3783kB"
" Buffers: local hit=11268"
" -> Nested Loop (cost=5.20..654.54 rows=57 width=138) (actual
time=0.918..81.299 rows=25270 loops=1)"
" Output: at_1.operador, at_1.titulo, n2v.titulo_base, n2v.cod_titulo"
" Buffers: local hit=11268"
" -> Seq Scan on pg_temp_6.tt_titulos_n2v_2017 n2v (cost=0.00..3.90
rows=90 width=138) (actual time=0.013..0.032 rows=90 loops=1)"
" Output: n2v.ticket_operator_code, n2v.ticket_code, n2v.cod_titulo,
n2v.desig_titulo_aml, n2v.desig_titulo_otlis_antigo,
n2v.desig_titulo_otlis_novo, n2v.titulo_base, n2v.modalidade, (...)"
" Buffers: local hit=3"
" -> Bitmap Heap Scan on pg_temp_6.tt_analise_transac_oper_mes at_1
(cost=5.20..7.22 rows=1 width=148) (actual time=0.756..0.840 rows=281
loops=90)"
" Output: at_1.ctrl_cod_valida, at_1.cod_controlo, at_1.causa,
at_1.operador, at_1.ordem, at_1.num_serie, at_1.titulo,
at_1.tipo_valida, at_1.data_dia_hora, at_1.cod_carreira, at_1.cod (...)"
" Recheck Cond: (((at_1.ticket_operator_code)::text =
n2v.ticket_operator_code) AND ((at_1.ticket_code)::text = n2v.ticket_code))"
" Heap Blocks: exact=8900"
" Buffers: local hit=11265"
" -> BitmapAnd (cost=5.20..5.20 rows=1 width=0) (actual
time=0.743..0.743 rows=0 loops=90)"
" Buffers: local hit=2365"
" -> Bitmap Index Scan on
tt_analise_transac_oper_mes_ticket_operator_code (cost=0.00..2.48
rows=126 width=0) (actual time=0.649..0.649 rows=8149 loops=90)"
" Index Cond: ((at_1.ticket_operator_code)::text =
n2v.ticket_operator_code)"
" Buffers: local hit=2156"
" -> Bitmap Index Scan on tt_analise_transac_oper_mes_ticket_code
(cost=0.00..2.48 rows=126 width=0) (actual time=0.041..0.041 rows=361
loops=70)"
" Index Cond: ((at_1.ticket_code)::text = n2v.ticket_code)"
" Buffers: local hit=209"
" -> Sort
(cost=16.53..16.55 rows=9 width=186) (actual time=0.832..1.143 rows=3691
loops=1)"
" Output:
k.etapa_km, k.operador, k.cod_titulo"
" Sort Key:
k.operador"
" Sort Method:
quicksort Memory: 93kB"
" Buffers: local
hit=27"
" -> Bitmap Heap
Scan on pg_temp_6.tt_km_por_etapa_2017 k (cost=2.35..16.39 rows=9
width=186) (actual time=0.084..0.258 rows=900 loops=1)"
" Output: k.etapa_km, k.operador, k.cod_titulo"
" Recheck Cond: ((k.mes_ref)::text = 'maio'::text)"
" Heap Blocks: exact=23"
" Buffers: local hit=27"
" -> Bitmap Index Scan on i_km_por_etapa_mes_ref (cost=0.00..2.34
rows=9 width=0) (actual time=0.076..0.076 rows=900 loops=1)"
" Index Cond: ((k.mes_ref)::text = 'maio'::text)"
" Buffers: local hit=4"
" -> Materialize
(cost=656.21..658.77 rows=57 width=262) (actual time=242.933..280.558
rows=177148 loops=1)"
" Output: at_2.operador,
at_2.num_serie, at_2.titulo, n2v_1.cod_titulo, at_2.ticket_code,
at_2.ticket_operator_code, n2v_1.titulo_base, (count(*)), at_2.num_serie"
" Buffers: local hit=11268"
" -> GroupAggregate
(cost=656.21..658.06 rows=57 width=294) (actual time=242.927..256.743
rows=19684 loops=1)"
" Output:
at_2.operador, at_2.num_serie, at_2.titulo, n2v_1.cod_titulo,
at_2.ticket_code, at_2.ticket_operator_code, n2v_1.titulo_base,
count(*), at_2.num_serie"
" Group Key:
at_2.operador, at_2.num_serie, n2v_1.titulo_base, at_2.titulo,
n2v_1.cod_titulo, at_2.ticket_code, at_2.ticket_operator_code"
" Buffers: local hit=11268"
" -> Sort
(cost=656.21..656.35 rows=57 width=254) (actual time=242.917..245.409
rows=25270 loops=1)"
" Output:
at_2.operador, at_2.titulo, n2v_1.cod_titulo, at_2.ticket_code,
at_2.ticket_operator_code, n2v_1.titulo_base, at_2.num_serie"
" Sort Key:
at_2.operador, at_2.num_serie, n2v_1.titulo_base, at_2.titulo,
n2v_1.cod_titulo, at_2.ticket_code, at_2.ticket_operator_code"
" Sort Method:
quicksort Memory: 4322kB"
" Buffers: local
hit=11268"
" -> Nested Loop
(cost=5.20..654.54 rows=57 width=254) (actual time=0.923..84.952
rows=25270 loops=1)"
" Output: at_2.operador, at_2.titulo, n2v_1.cod_titulo,
at_2.ticket_code, at_2.ticket_operator_code, n2v_1.titulo_base,
at_2.num_serie"
" Buffers: local hit=11268"
" -> Seq Scan on pg_temp_6.tt_titulos_n2v_2017 n2v_1 (cost=0.00..3.90
rows=90 width=138) (actual time=0.009..0.028 rows=90 loops=1)"
" Output: n2v_1.ticket_operator_code, n2v_1.ticket_code,
n2v_1.cod_titulo, n2v_1.desig_titulo_aml,
n2v_1.desig_titulo_otlis_antigo, n2v_1.desig_titulo_otlis_novo,
n2v_1.titulo_base, n2v_1.mo (...)"
" Buffers: local hit=3"
" -> Bitmap Heap Scan on pg_temp_6.tt_analise_transac_oper_mes at_2
(cost=5.20..7.22 rows=1 width=180) (actual time=0.756..0.844 rows=281
loops=90)"
" Output: at_2.ctrl_cod_valida, at_2.cod_controlo, at_2.causa,
at_2.operador, at_2.ordem, at_2.num_serie, at_2.titulo,
at_2.tipo_valida, at_2.data_dia_hora, at_2.cod_carreira, at_2.cod_parag
(...)"
" Recheck Cond: (((at_2.ticket_operator_code)::text =
n2v_1.ticket_operator_code) AND ((at_2.ticket_code)::text =
n2v_1.ticket_code))"
" Heap Blocks: exact=8900"
" Buffers: local hit=11265"
" -> BitmapAnd (cost=5.20..5.20 rows=1 width=0) (actual
time=0.742..0.742 rows=0 loops=90)"
" Buffers: local hit=2365"
" -> Bitmap Index Scan on
tt_analise_transac_oper_mes_ticket_operator_code (cost=0.00..2.48
rows=126 width=0) (actual time=0.649..0.649 rows=8149 loops=90)"
" Index Cond: ((at_2.ticket_operator_code)::text =
n2v_1.ticket_operator_code)"
" Buffers: local hit=2156"
" -> Bitmap Index Scan on tt_analise_transac_oper_mes_ticket_code
(cost=0.00..2.48 rows=126 width=0) (actual time=0.041..0.041 rows=361
loops=70)"
" Index Cond: ((at_2.ticket_code)::text = n2v_1.ticket_code)"
" Buffers: local hit=209"
" -> Index Scan using
i_km_por_etapa_cod_titulo on pg_temp_6.tt_km_por_etapa_2017
(cost=0.28..2.27 rows=1 width=186) (actual time=0.006..0.017 rows=1
loops=19684)"
" Output:
tt_km_por_etapa_2017.cod_titulo, tt_km_por_etapa_2017.titulo,
tt_km_por_etapa_2017.titulo_amtl, tt_km_por_etapa_2017.operador,
tt_km_por_etapa_2017.etapa_km, tt_km_por_etapa_2017.mes_ref,
tt_km_por_etapa_2017.u (...)"
" Index Cond:
(tt_km_por_etapa_2017.cod_titulo = (n2v_1.cod_titulo)::text)"
" Filter:
(((tt_km_por_etapa_2017.mes_ref)::text = 'maio'::text) AND
(at_2.operador = (tt_km_por_etapa_2017.operador)::text))"
" Rows Removed by Filter: 19"
" Buffers: local hit=418233"
" -> Index Scan using i_titulo_base on
pg_temp_6.tt_eotb1 tt_eotb1_1 (cost=0.14..0.26 rows=1 width=72) (actual
time=0.003..0.004 rows=1 loops=19684)"
" Output: tt_eotb1_1.operador,
tt_eotb1_1.titulo_base, tt_eotb1_1.eotb_etapas"
" Index Cond: (tt_eotb1_1.titulo_base =
n2v_1.titulo_base)"
" Filter: (at_2.operador =
tt_eotb1_1.operador)"
" Buffers: local hit=39368"
" -> Index Scan using i_titulo_base on
pg_temp_6.tt_eotb1 at (cost=0.14..0.26 rows=1 width=72) (actual
time=0.002..0.003 rows=1 loops=19684)"
" Output: at.operador, at.titulo_base,
at.eotb_etapas"
" Index Cond: (at.titulo_base = n2v_1.titulo_base)"
" Filter: (at_2.operador = at.operador)"
" Buffers: local hit=39368"
" -> Index Scan using i_titulo_base on pg_temp_6.tt_eotb1
(cost=0.14..0.26 rows=1 width=72) (actual time=0.002..0.003 rows=1
loops=19684)"
" Output: tt_eotb1.operador, tt_eotb1.titulo_base,
tt_eotb1.eotb_etapas"
" Index Cond: (tt_eotb1.titulo_base = n2v_1.titulo_base)"
" Filter: (at_2.operador = tt_eotb1.operador)"
" Buffers: local hit=39368"
" -> GroupAggregate (cost=1329.36..1336.22 rows=1 width=96)
(actual time=6.702..318.563 rows=9 loops=19684)"
" Output: at_4.operador, n2v_3.titulo_base,
sum((((count(*)))::numeric * ((k_1.etapa_km -
((sum((((count(*)))::numeric * k_2.etapa_km))) /
(tt_eotb1_2.eotb_etapas)::numeric)) ^ '2'::numeric)))"
" Group Key: at_4.operador, n2v_3.titulo_base"
" Buffers: local hit=417506055"
" -> Nested Loop (cost=1329.36..1336.19 rows=1 width=128)
(actual time=1.359..292.219 rows=19684 loops=19684)"
" Output: at_4.operador, n2v_3.titulo_base,
(count(*)), k_1.etapa_km, (sum((((count(*)))::numeric * k_2.etapa_km))),
tt_eotb1_2.eotb_etapas"
" Buffers: local hit=417506055"
" -> Nested Loop (cost=1329.22..1335.92 rows=1
width=322) (actual time=1.356..258.881 rows=19684 loops=19684)"
" Output: at_4.operador, n2v_3.titulo_base,
(count(*)), k_1.etapa_km, k_1.operador, (sum((((count(*)))::numeric *
k_2.etapa_km))), at_3.operador, n2v_2.titulo_base"
" -> Merge Join (cost=1328.95..1333.64 rows=1
width=210) (actual time=1.349..49.552 rows=19684 loops=19684)"
" Output: at_4.operador,
n2v_3.titulo_base, (count(*)), n2v_3.cod_titulo,
(sum((((count(*)))::numeric * k_2.etapa_km))), at_3.operador,
n2v_2.titulo_base"
" Merge Cond: (at_3.operador =
at_4.operador)"
" Join Filter: (n2v_3.titulo_base =
n2v_2.titulo_base)"
" Rows Removed by Join Filter: 157472"
" Buffers: local hit=22563"
" -> GroupAggregate (cost=672.74..674.98
rows=1 width=96) (actual time=0.042..9.129 rows=9 loops=19684)"
" Output: at_3.operador,
n2v_2.titulo_base, sum((((count(*)))::numeric * k_2.etapa_km))"
" Group Key: at_3.operador,
n2v_2.titulo_base"
" Buffers: local hit=11295"
" -> Merge Join
(cost=672.74..674.96 rows=1 width=88) (actual time=0.013..9.091 rows=41
loops=19684)"
" Output: at_3.operador,
n2v_2.titulo_base, (count(*)), k_2.etapa_km"
" Merge Cond: (at_3.operador
= (k_2.operador)::text)"
" Join Filter:
((n2v_2.cod_titulo)::text = k_2.cod_titulo)"
" Rows Removed by Join
Filter: 3649"
" Buffers: local hit=11295"
" -> GroupAggregate
(cost=656.21..657.63 rows=57 width=146) (actual time=0.009..8.138
rows=41 loops=19684)"
" Output:
at_3.operador, at_3.titulo, n2v_2.cod_titulo, n2v_2.titulo_base, count(*)"
" Group Key:
at_3.operador, n2v_2.titulo_base, at_3.titulo, n2v_2.cod_titulo"
" Buffers: local hit=11268"
" -> Sort
(cost=656.21..656.35 rows=57 width=138) (actual time=0.007..2.025
rows=25270 loops=19684)"
" Output:
at_3.operador, at_3.titulo, n2v_2.cod_titulo, n2v_2.titulo_base"
" Sort Key:
at_3.operador, n2v_2.titulo_base, at_3.titulo, n2v_2.cod_titulo"
" Sort Method:
quicksort Memory: 3783kB"
" Buffers: local
hit=11268"
" -> Nested Loop
(cost=5.20..654.54 rows=57 width=138) (actual time=0.915..84.991
rows=25270 loops=1)"
" Output: at_3.operador, at_3.titulo, n2v_2.cod_titulo, n2v_2.titulo_base"
" Buffers: local hit=11268"
" -> Seq Scan on pg_temp_6.tt_titulos_n2v_2017 n2v_2 (cost=0.00..3.90
rows=90 width=138) (actual time=0.018..0.037 rows=90 loops=1)"
" Output: n2v_2.ticket_operator_code, n2v_2.ticket_code,
n2v_2.cod_titulo, n2v_2.desig_titulo_aml,
n2v_2.desig_titulo_otlis_antigo, n2v_2.desig_titulo_otlis_novo,
n2v_2.titulo_base, n2v_2.mo (...)"
" Buffers: local hit=3"
" -> Bitmap Heap Scan on pg_temp_6.tt_analise_transac_oper_mes at_3
(cost=5.20..7.22 rows=1 width=148) (actual time=0.769..0.867 rows=281
loops=90)"
" Output: at_3.ctrl_cod_valida, at_3.cod_controlo, at_3.causa,
at_3.operador, at_3.ordem, at_3.num_serie, at_3.titulo,
at_3.tipo_valida, at_3.data_dia_hora, at_3.cod_carreira, at_3.cod_parag
(...)"
" Recheck Cond: (((at_3.ticket_operator_code)::text =
n2v_2.ticket_operator_code) AND ((at_3.ticket_code)::text =
n2v_2.ticket_code))"
" Heap Blocks: exact=8900"
" Buffers: local hit=11265"
" -> BitmapAnd (cost=5.20..5.20 rows=1 width=0) (actual
time=0.754..0.754 rows=0 loops=90)"
" Buffers: local hit=2365"
" -> Bitmap Index Scan on
tt_analise_transac_oper_mes_ticket_operator_code (cost=0.00..2.48
rows=126 width=0) (actual time=0.662..0.662 rows=8149 loops=90)"
" Index Cond: ((at_3.ticket_operator_code)::text =
n2v_2.ticket_operator_code)"
" Buffers: local hit=2156"
" -> Bitmap Index Scan on tt_analise_transac_oper_mes_ticket_code
(cost=0.00..2.48 rows=126 width=0) (actual time=0.041..0.041 rows=361
loops=70)"
" Index Cond: ((at_3.ticket_code)::text = n2v_2.ticket_code)"
" Buffers: local hit=209"
" -> Sort (cost=16.53..16.55
rows=9 width=186) (actual time=0.000..0.267 rows=3691 loops=19684)"
" Output: k_2.etapa_km,
k_2.operador, k_2.cod_titulo"
" Sort Key: k_2.operador"
" Sort Method:
quicksort Memory: 93kB"
" Buffers: local hit=27"
" -> Bitmap Heap Scan
on pg_temp_6.tt_km_por_etapa_2017 k_2 (cost=2.35..16.39 rows=9
width=186) (actual time=0.088..0.298 rows=900 loops=1)"
" Output:
k_2.etapa_km, k_2.operador, k_2.cod_titulo"
" Recheck Cond:
((k_2.mes_ref)::text = 'maio'::text)"
" Heap Blocks:
exact=23"
" Buffers: local
hit=27"
" -> Bitmap Index
Scan on i_km_por_etapa_mes_ref (cost=0.00..2.34 rows=9 width=0) (actual
time=0.078..0.078 rows=900 loops=1)"
" Index Cond: ((k_2.mes_ref)::text = 'maio'::text)"
" Buffers: local hit=4"
" -> Materialize (cost=656.21..658.49
rows=57 width=114) (actual time=0.012..6.667 rows=177148 loops=19684)"
" Output: at_4.operador,
(NULL::text), at_4.titulo, n2v_3.cod_titulo, n2v_3.titulo_base,
(count(*)), at_4.num_serie"
" Buffers: local hit=11268"
" -> GroupAggregate
(cost=656.21..657.77 rows=57 width=210) (actual time=226.933..238.760
rows=19684 loops=1)"
" Output: at_4.operador,
NULL::text, at_4.titulo, n2v_3.cod_titulo, n2v_3.titulo_base, count(*),
at_4.num_serie"
" Group Key: at_4.operador,
at_4.num_serie, n2v_3.titulo_base, at_4.titulo, n2v_3.cod_titulo"
" Buffers: local hit=11268"
" -> Sort
(cost=656.21..656.35 rows=57 width=170) (actual time=226.928..228.778
rows=25270 loops=1)"
" Output:
at_4.operador, at_4.titulo, n2v_3.cod_titulo, n2v_3.titulo_base,
at_4.num_serie"
" Sort Key:
at_4.operador, at_4.num_serie, n2v_3.titulo_base, at_4.titulo,
n2v_3.cod_titulo"
" Sort Method:
quicksort Memory: 4018kB"
" Buffers: local hit=11268"
" -> Nested Loop
(cost=5.20..654.54 rows=57 width=170) (actual time=0.847..81.830
rows=25270 loops=1)"
" Output:
at_4.operador, at_4.titulo, n2v_3.cod_titulo, n2v_3.titulo_base,
at_4.num_serie"
" Buffers: local
hit=11268"
" -> Seq Scan on
pg_temp_6.tt_titulos_n2v_2017 n2v_3 (cost=0.00..3.90 rows=90 width=138)
(actual time=0.009..0.025 rows=90 loops=1)"
" Output: n2v_3.ticket_operator_code, n2v_3.ticket_code,
n2v_3.cod_titulo, n2v_3.desig_titulo_aml,
n2v_3.desig_titulo_otlis_antigo, n2v_3.desig_titulo_otlis_novo,
n2v_3.titulo_base, n2v_3.modalida (...)"
" Buffers: local hit=3"
" -> Bitmap Heap
Scan on pg_temp_6.tt_analise_transac_oper_mes at_4 (cost=5.20..7.22
rows=1 width=180) (actual time=0.754..0.838 rows=281 loops=90)"
" Output: at_4.ctrl_cod_valida, at_4.cod_controlo, at_4.causa,
at_4.operador, at_4.ordem, at_4.num_serie, at_4.titulo,
at_4.tipo_valida, at_4.data_dia_hora, at_4.cod_carreira,
at_4.cod_parage, at_ (...)"
" Recheck Cond: (((at_4.ticket_operator_code)::text =
n2v_3.ticket_operator_code) AND ((at_4.ticket_code)::text =
n2v_3.ticket_code))"
" Heap Blocks: exact=8900"
" Buffers: local hit=11265"
" -> BitmapAnd (cost=5.20..5.20 rows=1 width=0) (actual
time=0.740..0.740 rows=0 loops=90)"
" Buffers: local hit=2365"
" -> Bitmap Index Scan on
tt_analise_transac_oper_mes_ticket_operator_code (cost=0.00..2.48
rows=126 width=0) (actual time=0.648..0.648 rows=8149 loops=90)"
" Index Cond: ((at_4.ticket_operator_code)::text =
n2v_3.ticket_operator_code)"
" Buffers: local hit=2156"
" -> Bitmap Index Scan on tt_analise_transac_oper_mes_ticket_code
(cost=0.00..2.48 rows=126 width=0) (actual time=0.040..0.040 rows=361
loops=70)"
" Index Cond: ((at_4.ticket_code)::text = n2v_3.ticket_code)"
" Buffers: local hit=209"
" -> Index Scan using
i_km_por_etapa_cod_titulo on pg_temp_6.tt_km_por_etapa_2017 k_1
(cost=0.28..2.27 rows=1 width=186) (actual time=0.004..0.010 rows=1
loops=387459856)"
" Output: k_1.cod_titulo, k_1.titulo,
k_1.titulo_amtl, k_1.operador, k_1.etapa_km, k_1.mes_ref,
k_1.utilizador, k_1.data, k_1.notas, k_1.caracter, k_1.ticket_code,
k_1.ticket_operator_code"
" Index Cond: (k_1.cod_titulo =
(n2v_3.cod_titulo)::text)"
" Filter: (((k_1.mes_ref)::text =
'maio'::text) AND (at_4.operador = (k_1.operador)::text))"
" Rows Removed by Filter: 19"
" -> Index Scan using i_titulo_base on
pg_temp_6.tt_eotb1 tt_eotb1_2 (cost=0.14..0.26 rows=1 width=72) (actual
time=0.001..0.001 rows=1 loops=387459856)"
" Output: tt_eotb1_2.operador,
tt_eotb1_2.titulo_base, tt_eotb1_2.eotb_etapas"
" Index Cond: (tt_eotb1_2.titulo_base =
n2v_3.titulo_base)"
" Filter: (at_4.operador = tt_eotb1_2.operador)"
" Buffers: local hit=774919712"
" -> GroupAggregate (cost=677.03..679.22 rows=1 width=96) (actual
time=0.075..9.010 rows=9 loops=19684)"
" Output: at_5.operador, n2v_4.titulo_base,
sum((((count(*)))::numeric * k_3.etapa_km))"
" Group Key: at_5.operador, n2v_4.titulo_base"
" Buffers: local hit=11298"
" -> Merge Join (cost=677.03..679.20 rows=1 width=88) (actual
time=0.046..8.977 rows=41 loops=19684)"
" Output: at_5.operador, n2v_4.titulo_base, (count(*)),
k_3.etapa_km"
" Merge Cond: (at_5.operador = (k_3.operador)::text)"
" Join Filter: (k_3.cod_titulo = (n2v_4.cod_titulo)::text)"
" Rows Removed by Join Filter: 3649"
" Buffers: local hit=11298"
" -> GroupAggregate (cost=656.21..657.63 rows=57
width=146) (actual time=0.010..8.042 rows=41 loops=19684)"
" Output: at_5.operador, at_5.titulo,
n2v_4.cod_titulo, n2v_4.titulo_base, count(*)"
" Group Key: at_5.operador, n2v_4.titulo_base,
at_5.titulo, n2v_4.cod_titulo"
" Buffers: local hit=11268"
" -> Sort (cost=656.21..656.35 rows=57 width=138)
(actual time=0.007..1.920 rows=25270 loops=19684)"
" Output: at_5.operador, at_5.titulo,
n2v_4.cod_titulo, n2v_4.titulo_base"
" Sort Key: at_5.operador, n2v_4.titulo_base,
at_5.titulo, n2v_4.cod_titulo"
" Sort Method: quicksort Memory: 3783kB"
" Buffers: local hit=11268"
" -> Nested Loop (cost=5.20..654.54 rows=57
width=138) (actual time=0.887..83.363 rows=25270 loops=1)"
" Output: at_5.operador, at_5.titulo,
n2v_4.cod_titulo, n2v_4.titulo_base"
" Buffers: local hit=11268"
" -> Seq Scan on
pg_temp_6.tt_titulos_n2v_2017 n2v_4 (cost=0.00..3.90 rows=90 width=138)
(actual time=0.011..0.028 rows=90 loops=1)"
" Output:
n2v_4.ticket_operator_code, n2v_4.ticket_code, n2v_4.cod_titulo,
n2v_4.desig_titulo_aml, n2v_4.desig_titulo_otlis_antigo,
n2v_4.desig_titulo_otlis_novo, n2v_4.titulo_base, n2v_4.modalidade,
n2v_4.tipo_aml, n2v_ (...)"
" Buffers: local hit=3"
" -> Bitmap Heap Scan on
pg_temp_6.tt_analise_transac_oper_mes at_5 (cost=5.20..7.22 rows=1
width=148) (actual time=0.754..0.850 rows=281 loops=90)"
" Output: at_5.ctrl_cod_valida,
at_5.cod_controlo, at_5.causa, at_5.operador, at_5.ordem,
at_5.num_serie, at_5.titulo, at_5.tipo_valida, at_5.data_dia_hora,
at_5.cod_carreira, at_5.cod_parage, at_5.val_inval_excl, at_5.m (...)"
" Recheck Cond:
(((at_5.ticket_operator_code)::text = n2v_4.ticket_operator_code) AND
((at_5.ticket_code)::text = n2v_4.ticket_code))"
" Heap Blocks: exact=8900"
" Buffers: local hit=11265"
" -> BitmapAnd (cost=5.20..5.20
rows=1 width=0) (actual time=0.741..0.741 rows=0 loops=90)"
" Buffers: local hit=2365"
" -> Bitmap Index Scan on
tt_analise_transac_oper_mes_ticket_operator_code (cost=0.00..2.48
rows=126 width=0) (actual time=0.648..0.648 rows=8149 loops=90)"
" Index Cond:
((at_5.ticket_operator_code)::text = n2v_4.ticket_operator_code)"
" Buffers: local hit=2156"
" -> Bitmap Index Scan on
tt_analise_transac_oper_mes_ticket_code (cost=0.00..2.48 rows=126
width=0) (actual time=0.041..0.041 rows=361 loops=70)"
" Index Cond:
((at_5.ticket_code)::text = n2v_4.ticket_code)"
" Buffers: local hit=209"
" -> Sort (cost=20.82..20.83 rows=4 width=228) (actual
time=0.001..0.283 rows=3691 loops=19684)"
" Output: k_3.etapa_km, k_3.cod_titulo, k_3.operador,
t.cod_titulo"
" Sort Key: k_3.operador"
" Sort Method: quicksort Memory: 102kB"
" Buffers: local hit=30"
" -> Hash Join (cost=16.50..20.78 rows=4 width=228)
(actual time=0.463..0.648 rows=900 loops=1)"
" Output: k_3.etapa_km, k_3.cod_titulo,
k_3.operador, t.cod_titulo"
" Hash Cond: ((t.cod_titulo)::text =
k_3.cod_titulo)"
" Buffers: local hit=30"
" -> Seq Scan on pg_temp_6.tt_titulos_n2v_2017
t (cost=0.00..3.90 rows=90 width=42) (actual time=0.010..0.017 rows=90
loops=1)"
" Output: t.ticket_operator_code,
t.ticket_code, t.cod_titulo, t.desig_titulo_aml,
t.desig_titulo_otlis_antigo, t.desig_titulo_otlis_novo, t.titulo_base,
t.modalidade, t.tipo_aml, t.tipo_otlis, t.tarifa, t.ultima_actualizacao,
(...)"
" Buffers: local hit=3"
" -> Hash (cost=16.39..16.39 rows=9
width=186) (actual time=0.439..0.439 rows=900 loops=1)"
" Output: k_3.etapa_km, k_3.cod_titulo,
k_3.operador"
" Buckets: 1024 Batches: 1 Memory
Usage: 61kB"
" Buffers: local hit=27"
" -> Bitmap Heap Scan on
pg_temp_6.tt_km_por_etapa_2017 k_3 (cost=2.35..16.39 rows=9 width=186)
(actual time=0.087..0.283 rows=900 loops=1)"
" Output: k_3.etapa_km,
k_3.cod_titulo, k_3.operador"
" Recheck Cond:
((k_3.mes_ref)::text = 'maio'::text)"
" Heap Blocks: exact=23"
" Buffers: local hit=27"
" -> Bitmap Index Scan on
i_km_por_etapa_mes_ref (cost=0.00..2.34 rows=9 width=0) (actual
time=0.079..0.079 rows=900 loops=1)"
" Index Cond:
((k_3.mes_ref)::text = 'maio'::text)"
" Buffers: local hit=4"
"Planning time: 46.737 ms"
"Execution time: 6452692.457 ms"
On 20-06-2018 23:46, David G. Johnston wrote:
On Wed, Jun 20, 2018 at 3:34 PM, Fabrízio de Royes Mello
<fabrizio@timbira.com.br <mailto:fabrizio@timbira.com.br>>wrote:And use some external service like pastebin.com
<http://pastebin.com> to send long SQL statements.Or just attach a text file - those are allowed on these lists.
David J.
--
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.
DiasCosta <diascosta@diascosta.org> writes:
This is the query plan for only 19684 rows.
I think you're getting a bad query plan, mostly as a result of two
factors:
* Poor row estimates. It looks like the bottom-most misestimations
are on temp tables, which makes me wonder whether you've ANALYZEd
those tables. Your application has to do that explicitly after
populating the tables; auto-analyze can't help on temp tables.
* Too many tables --- I count 33 table scans in this query. You
might get better planning results by raising join_collapse_limit
and/or from_collapse_limit, but it will come at a cost in planning
time, and in any case a query with this many tables is never likely
to be cheap. You might want to think about restructuring your schema
to not need so many tables, or maybe just do some hand optimization
of the query to eliminate unnecessary joins. (It looks to me like
at least some of the joins to tt_eotb1 might be unnecessary?)
regards, tom lane
Hello Tom, good evening.
Thank you for your prompt answer and help.
It was enough to ANALYZE the temp tables to achieve a magnificent
result: 37 seconds.
I'm portuguese.
It's a shame you live so far from me. So I can't put a box of bottles of
Porto wine at your door.
I also thank David and Fabrízio for their willingness to help me.
They deserve a bottle of Porto wine each.
Best regards
Dias Costa
On 21-06-2018 22:08, Tom Lane wrote:
DiasCosta <diascosta@diascosta.org> writes:
This is the query plan for only 19684 rows.
I think you're getting a bad query plan, mostly as a result of two
factors:* Poor row estimates. It looks like the bottom-most misestimations
are on temp tables, which makes me wonder whether you've ANALYZEd
those tables. Your application has to do that explicitly after
populating the tables; auto-analyze can't help on temp tables.* Too many tables --- I count 33 table scans in this query. You
might get better planning results by raising join_collapse_limit
and/or from_collapse_limit, but it will come at a cost in planning
time, and in any case a query with this many tables is never likely
to be cheap. You might want to think about restructuring your schema
to not need so many tables, or maybe just do some hand optimization
of the query to eliminate unnecessary joins. (It looks to me like
at least some of the joins to tt_eotb1 might be unnecessary?)regards, tom lane
--
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.