Re: SQL Query never ending... - Mailing list pgsql-general
| From | DiasCosta |
|---|---|
| Subject | Re: SQL Query never ending... |
| Date | |
| Msg-id | fe5176de-f40b-9c87-738e-54e9a4f7aee9@diascosta.org Whole thread Raw |
| In response to | Re: SQL Query never ending... ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: SQL Query never ending...
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-general |
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:
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:
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.
-- 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.
pgsql-general by date: