Re: SQL Query never ending...

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(at)timbira(dot)com(dot)br <mailto:fabrizio(at)timbira(dot)com(dot)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.