explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TLDw

Settings
# exclusive inclusive rows x rows loops node
1. 4,199.124 44,718.897 ↓ 8,181.0 8,181 1

GroupAggregate (cost=658,826.94..658,850.94 rows=1 width=628) (actual time=36,822.418..44,718.897 rows=8,181 loops=1)

  • Output: CASE WHEN (max(ce.item_id) IS NOT NULL) THEN 'ITEM'::text WHEN (max(ce.bill_id) IS NOT NULL) THEN 'GUIA'::text WHEN (max(ce.batch_id) IS NOT NULL) THEN 'LOTE'::text WHEN (max(ce.recommendation_id) IS NOT NULL) THEN 'RECOMENDAÇÂO'::text ELSE ''::text END, ee.type, ee.error_origin, CASE max(ee.billing_critic_category) WHEN 'CODIFICATION'::text THEN 'CODIFICAÇÃO'::text WHEN 'PRECIFICATION'::text THEN 'PRECIFICAÇÂO'::text WHEN 'BILLING'::text THEN 'FATURAMENTO'::text WHEN 'AUTHORIZATION'::text THEN 'AUTORIZAÇÂO'::text WHEN 'ELIGIBILITY'::text THEN 'ELEGIBILIDADE'::text ELSE ''::text END, max(ee.billing_critic_category), count(1), count(DISTINCT COALESCE(itm_lt.id, g_lt.id, lt.id)), count(DISTINCT COALESCE(itm_g.id, g.id)), sum(ee.criticized_value), sum(itm.valor_total), array_agg(ee.id), ee.billing_critic_subcategory, max(itm_lt.tipo_guia), max(itm_lt.numero_lote), max(itm_lt.numero_remessa), max(itm_lt.data_transmissao), max(itm_g.numero_guia_prestador), max(itm_g.numero_guia_operadora), max(itm_g.senha_autorizacao), max(itm_g.numero_carteira_beneficiario), max(itm_g.nome_beneficiario), CASE max(itm.discriminador) WHEN 'PROCEDIMENTO'::text THEN 'PROCEDURE'::text WHEN 'OUTRA_DESPESA'::text THEN 'OTHER_EXPENSE'::text ELSE NULL::text END, CASE itm.codigo_despesa WHEN '01'::text THEN 'TAXAS'::text WHEN '02'::text THEN 'MEDICAMENTOS'::text WHEN '03'::text THEN 'MATERIAIS'::text WHEN '05'::text THEN 'TAXAS'::text WHEN '07'::text THEN 'TAXAS'::text WHEN '08'::text THEN 'OPMES'::text ELSE 'PROCEDIMENTOS'::text END, itm.codigo_despesa, itm.codigo_tabela, itm.codigo_item, max(itm.descricao)
  • Group Key: ee.type, ee.error_origin, ee.billing_critic_subcategory, itm.codigo_despesa, itm.codigo_tabela, itm.codigo_item
  • Buffers: shared hit=21005988, temp read=41479 written=41479
2. 12,073.197 40,519.773 ↓ 1,215,211.0 1,215,211 1

Sort (cost=658,826.94..658,827.94 rows=1 width=303) (actual time=36,822.314..40,519.773 rows=1,215,211 loops=1)

  • Output: ee.type, ee.error_origin, ee.billing_critic_subcategory, itm.codigo_despesa, itm.codigo_tabela, itm.codigo_item, ce.item_id, ce.bill_id, ce.batch_id, ce.recommendation_id, ee.billing_critic_category, itm_lt.id, g_lt.id, lt.id, itm_g.id, g.id, ee.criticized_value, itm.valor_total, ee.id, itm_lt.tipo_guia, itm_lt.numero_lote, itm_lt.numero_remessa, itm_lt.data_transmissao, itm_g.numero_guia_prestador, itm_g.numero_guia_operadora, itm_g.senha_autorizacao, itm_g.numero_carteira_beneficiario, itm_g.nome_beneficiario, itm.discriminador, itm.descricao
  • Sort Key: ee.type, ee.error_origin, ee.billing_critic_subcategory, itm.codigo_despesa, itm.codigo_tabela, itm.codigo_item
  • Sort Method: external merge Disk: 331752kB
  • Buffers: shared hit=21005988, temp read=41479 written=41479
3. 926.940 28,446.576 ↓ 1,215,211.0 1,215,211 1

Nested Loop Left Join (cost=1,787.50..658,824.94 rows=1 width=303) (actual time=4.854..28,446.576 rows=1,215,211 loops=1)

  • Output: ee.type, ee.error_origin, ee.billing_critic_subcategory, itm.codigo_despesa, itm.codigo_tabela, itm.codigo_item, ce.item_id, ce.bill_id, ce.batch_id, ce.recommendation_id, ee.billing_critic_category, itm_lt.id, g_lt.id, lt.id, itm_g.id, g.id, ee.criticized_value, itm.valor_total, ee.id, itm_lt.tipo_guia, itm_lt.numero_lote, itm_lt.numero_remessa, itm_lt.data_transmissao, itm_g.numero_guia_prestador, itm_g.numero_guia_operadora, itm_g.senha_autorizacao, itm_g.numero_carteira_beneficiario, itm_g.nome_beneficiario, itm.discriminador, itm.descricao
  • Buffers: shared hit=21005988
4. 844.488 27,519.636 ↓ 1,215,211.0 1,215,211 1

Nested Loop Left Join (cost=1,703.50..658,738.94 rows=1 width=295) (actual time=4.851..27,519.636 rows=1,215,211 loops=1)

  • Output: ee.type, ee.error_origin, ee.billing_critic_category, ee.criticized_value, ee.id, ee.billing_critic_subcategory, ce.item_id, ce.bill_id, ce.batch_id, ce.recommendation_id, itm.valor_total, itm.discriminador, itm.codigo_despesa, itm.codigo_tabela, itm.codigo_item, itm.descricao, itm_g.id, itm_g.numero_guia_prestador, itm_g.numero_guia_operadora, itm_g.senha_autorizacao, itm_g.numero_carteira_beneficiario, itm_g.nome_beneficiario, itm_lt.id, itm_lt.tipo_guia, itm_lt.numero_lote, itm_lt.numero_remessa, itm_lt.data_transmissao, g.id, g_lt.id
  • Buffers: shared hit=21005988
5. 790.898 26,675.148 ↓ 1,215,211.0 1,215,211 1

Nested Loop Left Join (cost=1,619.50..658,652.94 rows=1 width=295) (actual time=4.849..26,675.148 rows=1,215,211 loops=1)

  • Output: ee.type, ee.error_origin, ee.billing_critic_category, ee.criticized_value, ee.id, ee.billing_critic_subcategory, ce.item_id, ce.bill_id, ce.batch_id, ce.recommendation_id, itm.valor_total, itm.discriminador, itm.codigo_despesa, itm.codigo_tabela, itm.codigo_item, itm.descricao, itm_g.id, itm_g.numero_guia_prestador, itm_g.numero_guia_operadora, itm_g.senha_autorizacao, itm_g.numero_carteira_beneficiario, itm_g.nome_beneficiario, itm_lt.id, itm_lt.tipo_guia, itm_lt.numero_lote, itm_lt.numero_remessa, itm_lt.data_transmissao, g.id, g.lote_id
  • Buffers: shared hit=21005959
6. 477.992 25,884.250 ↓ 1,215,211.0 1,215,211 1

Nested Loop Left Join (cost=1,533.50..658,564.89 rows=1 width=279) (actual time=4.847..25,884.250 rows=1,215,211 loops=1)

  • Output: ee.type, ee.error_origin, ee.billing_critic_category, ee.criticized_value, ee.id, ee.billing_critic_subcategory, ce.item_id, ce.bill_id, ce.batch_id, ce.recommendation_id, itm.valor_total, itm.discriminador, itm.codigo_despesa, itm.codigo_tabela, itm.codigo_item, itm.descricao, itm_g.id, itm_g.numero_guia_prestador, itm_g.numero_guia_operadora, itm_g.senha_autorizacao, itm_g.numero_carteira_beneficiario, itm_g.nome_beneficiario, itm_lt.id, itm_lt.tipo_guia, itm_lt.numero_lote, itm_lt.numero_remessa, itm_lt.data_transmissao
  • Buffers: shared hit=21005927
7. 834.474 21,760.625 ↓ 1,215,211.0 1,215,211 1

Nested Loop Left Join (cost=1,449.50..658,478.89 rows=1 width=258) (actual time=4.837..21,760.625 rows=1,215,211 loops=1)

  • Output: ee.type, ee.error_origin, ee.billing_critic_category, ee.criticized_value, ee.id, ee.billing_critic_subcategory, ce.item_id, ce.bill_id, ce.batch_id, ce.recommendation_id, itm.valor_total, itm.discriminador, itm.codigo_despesa, itm.codigo_tabela, itm.codigo_item, itm.descricao, itm_g.id, itm_g.numero_guia_prestador, itm_g.numero_guia_operadora, itm_g.senha_autorizacao, itm_g.numero_carteira_beneficiario, itm_g.nome_beneficiario, itm_g.lote_id
  • Buffers: shared hit=16080221
8. 434.221 17,280.518 ↓ 1,215,211.0 1,215,211 1

Nested Loop Left Join (cost=1,363.50..658,390.88 rows=1 width=181) (actual time=4.825..17,280.518 rows=1,215,211 loops=1)

  • Output: ee.type, ee.error_origin, ee.billing_critic_category, ee.criticized_value, ee.id, ee.billing_critic_subcategory, ce.item_id, ce.bill_id, ce.batch_id, ce.recommendation_id, itm.valor_total, itm.discriminador, itm.codigo_despesa, itm.codigo_tabela, itm.codigo_item, itm.descricao, itm.guia_id
  • Buffers: shared hit=11136788
9. 1,302.102 11,985.453 ↓ 1,215,211.0 1,215,211 1

Nested Loop (cost=1,251.00..658,276.13 rows=1 width=99) (actual time=4.809..11,985.453 rows=1,215,211 loops=1)

  • Output: ee.type, ee.error_origin, ee.billing_critic_category, ee.criticized_value, ee.id, ee.billing_critic_subcategory, ce.item_id, ce.bill_id, ce.batch_id, ce.recommendation_id
  • Buffers: shared hit=5031068
10. 5,907.796 7,037.718 ↓ 1,215,211.0 1,215,211 1

Gather (cost=1,164.50..658,187.61 rows=1 width=75) (actual time=4.797..7,037.718 rows=1,215,211 loops=1)

  • Output: ee.type, ee.error_origin, ee.billing_critic_category, ee.criticized_value, ee.id, ee.billing_critic_subcategory, ee.criticized_entity_id
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=162798
11. 136.996 1,129.922 ↓ 405,070.0 405,070 3

Hash Anti Join (cost=164.50..657,187.51 rows=1 width=75) (actual time=13.402..1,129.922 rows=405,070 loops=3)

  • Output: ee.type, ee.error_origin, ee.billing_critic_category, ee.criticized_value, ee.id, ee.billing_critic_subcategory, ee.criticized_entity_id
  • Hash Cond: (ee.treatment_id = eet.id)
  • Buffers: shared hit=162798
  • Worker 0: actual time=14.641..1674.558 rows=580511 loops=1
  • Buffers: shared hit=88239
  • Worker 1: actual time=22.174..1683.553 rows=634681 loops=1
  • Buffers: shared hit=72598
12. 942.788 992.877 ↓ 35.5 405,130 3

Nested Loop (cost=86.50..645,423.51 rows=11,416 width=83) (actual time=13.240..992.877 rows=405,130 loops=3)

  • Output: ee.type, ee.error_origin, ee.billing_critic_category, ee.criticized_value, ee.id, ee.billing_critic_subcategory, ee.criticized_entity_id, ee.treatment_id
  • Buffers: shared hit=162732
  • Worker 0: actual time=14.423..1477.544 rows=580620 loops=1
  • Buffers: shared hit=88207
  • Worker 1: actual time=21.974..1469.510 rows=634750 loops=1
  • Buffers: shared hit=72566
13. 49.823 49.823 ↓ 3.8 3,174 3

Parallel Seq Scan on public.review_execution re (cost=0.00..153,769.12 rows=840 width=8) (actual time=11.606..49.823 rows=3,174 loops=3)

  • Output: re.id, re.version, re.last_updated_at, re.review_status, re.progress_status, re.started_at, re.accreditation_id, re.billing_file_id, re.health_insurance_company_id, re.health_insurance_company_key, re.failed_message, re.process_status, re.tiss_version, re.errors_origin, re.serializable_file_id, re.batch_number, re.finished_at, re.organization_id, re.healthcare_provider_id, re.batch_criticized_value, re.bill_type, re.batch_hidden_criticized_value, re.ignored_review_status, re.reason_to_ignore_review, re.review_execution_scope, re.force_eligibility_reconsult, re.healthcare_provider_name, re.billing_file_cancelled, re.file_is_from_re_state, re.last_execution_id, re.previous_review_execution_id, re.leaf_review, re.submission_status, re.remittance_number, re.submission_date
  • Filter: (re.leaf_review AND (NOT re.billing_file_cancelled) AND (re.review_execution_scope = 'FATURAMENTO_EFICIENTE'::text) AND (re.organization_id = 399423))
  • Rows Removed by Filter: 72494
  • Buffers: shared hit=8005
  • Worker 0: actual time=14.342..61.388 rows=6137 loops=1
  • Buffers: shared hit=2988
  • Worker 1: actual time=17.173..56.786 rows=3373 loops=1
  • Buffers: shared hit=3132
14. 0.266 0.266 ↓ 1.6 128 9,522

Index Scan using execution_error_review_execution_id_idx on public.execution_error ee (cost=86.50..545.30 rows=80 width=91) (actual time=0.022..0.266 rows=128 loops=9,522)

  • Output: ee.type, ee.error_origin, ee.billing_critic_category, ee.criticized_value, ee.id, ee.billing_critic_subcategory, ee.review_execution_id, ee.criticized_entity_id, ee.treatment_id
  • Index Cond: (ee.review_execution_id = re.id)
  • Filter: (ee.billing_critic_category = 'CODIFICATION'::text)
  • Rows Removed by Filter: 34
  • Buffers: shared hit=154727
  • Worker 0: actual time=0.021..0.207 rows=95 loops=6137
  • Buffers: shared hit=85219
  • Worker 1: actual time=0.025..0.374 rows=188 loops=3373
  • Buffers: shared hit=69434
15. 0.013 0.049 ↓ 1.1 54 3

Hash (cost=28.00..28.00 rows=50 width=8) (actual time=0.049..0.049 rows=54 loops=3)

  • Output: eet.id
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=6
  • Worker 0: actual time=0.063..0.063 rows=54 loops=1
  • Buffers: shared hit=2
  • Worker 1: actual time=0.048..0.048 rows=54 loops=1
  • Buffers: shared hit=2
16. 0.036 0.036 ↓ 1.1 54 3

Seq Scan on public.execution_error_treatment eet (cost=0.00..28.00 rows=50 width=8) (actual time=0.018..0.036 rows=54 loops=3)

  • Output: eet.id
  • Buffers: shared hit=6
  • Worker 0: actual time=0.028..0.050 rows=54 loops=1
  • Buffers: shared hit=2
  • Worker 1: actual time=0.020..0.036 rows=54 loops=1
  • Buffers: shared hit=2
17. 3,645.633 3,645.633 ↑ 1.0 1 1,215,211

Index Scan using criticized_entity_pkey on public.criticized_entity ce (cost=86.50..88.03 rows=1 width=40) (actual time=0.003..0.003 rows=1 loops=1,215,211)

  • Output: ce.id, ce.version, ce.item_id, ce.bill_id, ce.batch_id, ce.recommendation_id
  • Index Cond: (ce.id = ee.criticized_entity_id)
  • Buffers: shared hit=4868270
18. 4,860.844 4,860.844 ↑ 1.0 1 1,215,211

Index Scan using item_pkey on public.item itm (cost=112.50..114.24 rows=1 width=90) (actual time=0.003..0.004 rows=1 loops=1,215,211)

  • Output: itm.id, itm.version, itm.codigo_tabela, itm.quantidade, itm.codigo_item, itm.guia_id, itm.fim_execucao, itm.reducao_acrescimo, itm.descricao, itm.inicio_execucao, itm.valor_total, itm.valor_unitario, itm.discriminador, itm.codigo_ref_fabricante, itm.unidade_medida, itm.autorizacao_funcionamento, itm.codigo_despesa, itm.registro_anvisa, itm.via_acesso, itm.tecnica_utilizada, itm.itens_idx, itm.criticized_value, itm.codigo_item_formatado, itm.hidden_criticized_value, itm.efficient_billing_hidden_criticized_value, itm.efficient_billing_criticized_value
  • Index Cond: (itm.id = ce.item_id)
  • Buffers: shared hit=6105720
19. 3,645.633 3,645.633 ↑ 1.0 1 1,215,211

Index Scan using guia_pkey on public.guia itm_g (cost=86.00..87.51 rows=1 width=85) (actual time=0.003..0.003 rows=1 loops=1,215,211)

  • Output: itm_g.id, itm_g.version, itm_g.numero_guia_prestador, itm_g.identificador_beneficiario, itm_g.nome_contratado_executante, itm_g.valor_total_geral, itm_g.atendimento_rn_beneficiario, itm_g.nome_beneficiario, itm_g.registro_ans_operadora, itm_g.numero_cns_beneficiario, itm_g.cnpj_contratado_executante, itm_g.cpf_contratado_executante, itm_g.cnes_contratado_executante, itm_g.codigo_contratado_executante_na_operadora, itm_g.numero_guia_operadora, itm_g.lote_id, itm_g.numero_carteira_beneficiario, itm_g.inicio_faturamento, itm_g.observacao, itm_g.final_faturamento, itm_g.discriminador, itm_g.cbos_profissional_executante, itm_g.indicacao_acidente, itm_g.uf_profissional_executante, itm_g.conselho_profissional_executante, itm_g.tipo_consulta, itm_g.numero_conselho_profissional_executante, itm_g.nome_profissional_executante, itm_g.valor_total_medicamentos, itm_g.valor_total_procedimentos, itm_g.numero_guia_solicitacao, itm_g.data_autorizacao, itm_g.valor_total_opme, itm_g.tipo_internacao, itm_g.senha_autorizacao, itm_g.regime_internacao, itm_g.tipo_faturamento, itm_g.valor_total_taxas_alugueis, itm_g.valor_total_materiais, itm_g.valor_total_diarias, itm_g.valor_total_gases_medicinais, itm_g.motivo_encerramento, itm_g.carater_atendimento, itm_g.validade_autorizacao, itm_g.numero_guia_solicitacao_internacao, itm_g.cnes_local_contratado, itm_g.nome_local_contratado, itm_g.data_emissao, itm_g.cnpj_local_contratado, itm_g.codigo_contratado_na_operadora, itm_g.codigo_contratado_solicitante_na_operadora, itm_g.indicacao_clinica, itm_g.tipo_atendimento, itm_g.cpf_contratado_solicitante, itm_g.numero_guia_principal, itm_g.nome_contratado_solicitante, itm_g.cnpj_contratado_solicitante, itm_g.data_solicitacao, itm_g.numero_conselho_profissional_solicitante, itm_g.conselho_profissional_solicitante, itm_g.nome_profissional_solicitante, itm_g.uf_profissional_solicitante, itm_g.cbos_profissional_solicitante, itm_g.guias_idx, itm_g.criticized_value, itm_g.hidden_criticized_value, itm_g.efficient_billing_hidden_criticized_value, itm_g.efficient_billing_criticized_value
  • Index Cond: (itm_g.id = itm.guia_id)
  • Buffers: shared hit=4943433
20. 3,645.633 3,645.633 ↑ 1.0 1 1,215,211

Index Scan using lote_pkey on public.lote itm_lt (cost=84.00..85.50 rows=1 width=29) (actual time=0.002..0.003 rows=1 loops=1,215,211)

  • Output: itm_lt.id, itm_lt.version, itm_lt.tiss_version_enum, itm_lt.billing_file_id, itm_lt.cpf_prestador, itm_lt.tipo_transacao, itm_lt.tipo_guia, itm_lt.cnpj_operadora, itm_lt.codigo_prestador_na_operadora, itm_lt.numero_lote, itm_lt.registro_ans_operadora, itm_lt.cnpj_prestador, itm_lt.cpf_operadora, itm_lt.sequencial_transacao, itm_lt.data_registro_transacao, itm_lt.hash, itm_lt.criticized_value, itm_lt.valor_total_lote, itm_lt.hidden_criticized_value, itm_lt.cancelled, itm_lt.efficient_billing_hidden_criticized_value, itm_lt.efficient_billing_criticized_value, itm_lt.from_re_state, itm_lt.data_transmissao, itm_lt.numero_remessa
  • Index Cond: (itm_lt.id = itm_g.lote_id)
  • Buffers: shared hit=4925706
21. 0.000 0.000 ↓ 0.0 0 1,215,211

Index Scan using guia_pkey on public.guia g (cost=86.00..87.55 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1,215,211)

  • Output: g.id, g.version, g.numero_guia_prestador, g.identificador_beneficiario, g.nome_contratado_executante, g.valor_total_geral, g.atendimento_rn_beneficiario, g.nome_beneficiario, g.registro_ans_operadora, g.numero_cns_beneficiario, g.cnpj_contratado_executante, g.cpf_contratado_executante, g.cnes_contratado_executante, g.codigo_contratado_executante_na_operadora, g.numero_guia_operadora, g.lote_id, g.numero_carteira_beneficiario, g.inicio_faturamento, g.observacao, g.final_faturamento, g.discriminador, g.cbos_profissional_executante, g.indicacao_acidente, g.uf_profissional_executante, g.conselho_profissional_executante, g.tipo_consulta, g.numero_conselho_profissional_executante, g.nome_profissional_executante, g.valor_total_medicamentos, g.valor_total_procedimentos, g.numero_guia_solicitacao, g.data_autorizacao, g.valor_total_opme, g.tipo_internacao, g.senha_autorizacao, g.regime_internacao, g.tipo_faturamento, g.valor_total_taxas_alugueis, g.valor_total_materiais, g.valor_total_diarias, g.valor_total_gases_medicinais, g.motivo_encerramento, g.carater_atendimento, g.validade_autorizacao, g.numero_guia_solicitacao_internacao, g.cnes_local_contratado, g.nome_local_contratado, g.data_emissao, g.cnpj_local_contratado, g.codigo_contratado_na_operadora, g.codigo_contratado_solicitante_na_operadora, g.indicacao_clinica, g.tipo_atendimento, g.cpf_contratado_solicitante, g.numero_guia_principal, g.nome_contratado_solicitante, g.cnpj_contratado_solicitante, g.data_solicitacao, g.numero_conselho_profissional_solicitante, g.conselho_profissional_solicitante, g.nome_profissional_solicitante, g.uf_profissional_solicitante, g.cbos_profissional_solicitante, g.guias_idx, g.criticized_value, g.hidden_criticized_value, g.efficient_billing_hidden_criticized_value, g.efficient_billing_criticized_value
  • Index Cond: (g.id = ce.bill_id)
  • Buffers: shared hit=32
22. 0.000 0.000 ↓ 0.0 0 1,215,211

Index Only Scan using lote_pkey on public.lote g_lt (cost=84.00..85.50 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1,215,211)

  • Output: g_lt.id
  • Index Cond: (g_lt.id = g.lote_id)
  • Heap Fetches: 4
  • Buffers: shared hit=29
23. 0.000 0.000 ↓ 0.0 0 1,215,211

Index Only Scan using lote_pkey on public.lote lt (cost=84.00..85.50 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1,215,211)

  • Output: lt.id
  • Index Cond: (lt.id = ce.batch_id)
  • Heap Fetches: 0
Planning time : 8.696 ms
Execution time : 44,799.160 ms