explain.depesz.com

PostgreSQL's explain analyze made readable

Result: umng

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 34,960.797 123,744.900 ↓ 3,426,518.0 250,135,814 1

Nested Loop (cost=2,250,971.47..2,412,936.31 rows=73 width=12) (actual time=33,908.806..123,744.900 rows=250,135,814 loops=1)

  • Output: guia_new.id, guia_motivo_glosa.motivo_glosa_id
  • Buffers: shared hit=41781732 read=340309 written=57, temp read=1155333 written=1063201
  • Functions: 129
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 22.726 ms, Inlining 239.034 ms, Optimization 1829.890 ms, Emission 1188.928 ms, Total 3280.578 ms
2. 1,748.706 61,371.959 ↓ 3,426,518.0 3,426,518 1

Nested Loop (cost=2,250,971.47..2,412,932.85 rows=1 width=12) (actual time=33,908.787..61,371.959 rows=3,426,518 loops=1)

  • Output: guia_motivo_glosa.motivo_glosa_id, guia_new.id
  • Inner Unique: true
  • Join Filter: ((pagamento.hospital_id = pagamento_new.hospital_id) AND (COALESCE(pagamento.protocolo, ''::text) = COALESCE(pagamento_new.protocolo, ''::text)) AND (COALESCE(pagamento.data_pagamento, '1900-01-01'::date) = COALESCE(pagamento_new.data_pagamento, '1900-01-01'::date)) AND (COALESCE(pagamento.operadora_id, '0'::bigint) = COALESCE(pagamento_new.operadora_id, '0'::bigint)) AND (COALESCE(pagamento.data_recebimento, '1900-01-01'::date) = COALESCE(pagamento_new.data_recebimento, '1900-01-01'::date)))
  • Rows Removed by Join Filter: 10952
  • Buffers: shared hit=34928698 read=340307 written=57, temp read=1155333 written=1063201
3. 2,270.111 52,748.313 ↓ 3,437,470.0 3,437,470 1

Nested Loop (cost=2,250,970.92..2,412,925.31 rows=1 width=54) (actual time=33,908.771..52,748.313 rows=3,437,470 loops=1)

  • Output: guia_motivo_glosa.motivo_glosa_id, pagamento.hospital_id, pagamento.protocolo, pagamento.data_pagamento, pagamento.operadora_id, pagamento.data_recebimento, guia_new.id, guia_new.pagamento_id
  • Inner Unique: true
  • Buffers: shared hit=17741348 read=340307 written=57, temp read=1155333 written=1063201
4. 0.000 43,603.262 ↓ 3,437,470.0 3,437,470 1

Gather (cost=2,250,970.37..2,412,917.80 rows=1 width=28) (actual time=33,908.681..43,603.262 rows=3,437,470 loops=1)

  • Output: guia_motivo_glosa.motivo_glosa_id, guia.pagamento_id, guia_new.id, guia_new.pagamento_id
  • Workers Planned: 2
  • Workers Launched: 2
  • JIT for worker 0:
  • Functions: 39
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 8.862 ms, Inlining 81.385 ms, Optimization 654.223 ms, Emission 400.692 ms, Total 1145.162 ms
  • JIT for worker 1:
  • Functions: 39
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.417 ms, Inlining 66.148 ms, Optimization 565.876 ms, Emission 370.640 ms, Total 1009.081 ms
  • Buffers: shared hit=554249 read=340056, temp read=1155333 written=1063201
5. 3,190.240 43,939.564 ↓ 1,145,823.0 1,145,823 3 / 3

Merge Join (cost=2,249,970.37..2,411,917.70 rows=1 width=28) (actual time=33,847.957..43,939.564 rows=1,145,823 loops=3)

  • Output: guia_motivo_glosa.motivo_glosa_id, guia.pagamento_id, guia_new.id, guia_new.pagamento_id
  • Merge Cond: (((COALESCE(guia_new.numero, ''::text)) = (COALESCE(guia.numero, ''::text))) AND ((COALESCE(guia_new.senha, ''::text)) = (COALESCE(guia.senha, ''::text))) AND ((COALESCE(guia_new.data_atendimento_fim, '1900-01-01'::date)) = (COALESCE(guia.data_atendimento_fim, '1900-01-01'::date))) AND ((COALESCE(guia_new.data_atendimento_inicio, '1900-01-01'::date)) = (COALESCE(guia.data_atendimento_inicio, '1900-01-01'::date))) AND ((COALESCE(guia_new.beneficiario_id, '0'::bigint)) = (COALESCE(guia.beneficiario_id, '0'::bigint))) AND ((COALESCE(guia_new.valor_total_pago, '0'::numeric)) = (COALESCE(guia.valor_total_pago, '0'::numeric))) AND ((COALESCE(guia_new.valor_total_apresentado, '0'::numeric)) = (COALESCE(guia.valor_total_apresentado, '0'::numeric))))
  • Buffers: shared hit=554249 read=340056, temp read=1155333 written=1063201
  • Worker 0: actual time=33796.021..44036.625 rows=1087423 loops=1
  • Buffers: shared hit=183371 read=111460, temp read=377522 written=347913
  • Worker 1: actual time=33840.069..45149.466 rows=1217009 loops=1
  • Buffers: shared hit=185446 read=113130, temp read=387514 written=354662
6. 36,547.822 39,605.012 ↑ 1.3 6,417,819 3 / 3

Sort (cost=1,891,954.21..1,912,081.84 rows=8,051,052 width=56) (actual time=33,324.176..39,605.012 rows=6,417,819 loops=3)

  • Output: guia_new.id, guia_new.pagamento_id, guia_new.numero, guia_new.senha, guia_new.data_atendimento_fim, guia_new.data_atendimento_inicio, guia_new.beneficiario_id, guia_new.valor_total_pago, guia_new.valor_total_apresentado, (COALESCE(guia_new.numero, ''::text)), (COALESCE(guia_new.senha, ''::text)), (COALESCE(guia_new.data_atendimento_fim, '1900-01-01'::date)), (COALESCE(guia_new.data_atendimento_inicio, '1900-01-01'::date)), (COALESCE(guia_new.beneficiario_id, '0'::bigint)), (COALESCE(guia_new.valor_total_pago, '0'::numeric)), (COALESCE(guia_new.valor_total_apresentado, '0'::numeric))
  • Sort Key: (COALESCE(guia_new.numero, ''::text)), (COALESCE(guia_new.senha, ''::text)), (COALESCE(guia_new.data_atendimento_fim, '1900-01-01'::date)), (COALESCE(guia_new.data_atendimento_inicio, '1900-01-01'::date)), (COALESCE(guia_new.beneficiario_id, '0'::bigint)), (COALESCE(guia_new.valor_total_pago, '0'::numeric)), (COALESCE(guia_new.valor_total_apresentado, '0'::numeric))
  • Sort Method: external merge Disk: 822376kB
  • Worker 0: Sort Method: external merge Disk: 778872kB
  • Worker 1: Sort Method: external merge Disk: 805584kB
  • Buffers: shared hit=44 read=337597, temp read=1056933 written=1059106
  • Worker 0: actual time=33186.238..39184.791 rows=6231006 loops=1
  • Buffers: shared hit=17 read=109259, temp read=345868 written=346548
  • Worker 1: actual time=33361.161..40142.381 rows=6446750 loops=1
  • Buffers: shared hit=18 read=113003, temp read=352544 written=353297
7. 3,057.190 3,057.190 ↑ 1.2 6,440,931 3 / 3

Parallel Seq Scan on public.guia guia_new (cost=0.00..418,100.53 rows=8,051,052 width=56) (actual time=1,087.448..3,057.190 rows=6,440,931 loops=3)

  • Output: guia_new.id, guia_new.pagamento_id, guia_new.numero, guia_new.senha, guia_new.data_atendimento_fim, guia_new.data_atendimento_inicio, guia_new.beneficiario_id, guia_new.valor_total_pago, guia_new.valor_total_apresentado, COALESCE(guia_new.numero, ''::text), COALESCE(guia_new.senha, ''::text), COALESCE(guia_new.data_atendimento_fim, '1900-01-01'::date), COALESCE(guia_new.data_atendimento_inicio, '1900-01-01'::date), COALESCE(guia_new.beneficiario_id, '0'::bigint), COALESCE(guia_new.valor_total_pago, '0'::numeric), COALESCE(guia_new.valor_total_apresentado, '0'::numeric)
  • Buffers: shared hit=1 read=337589
  • Worker 0: actual time=1137.901..3081.064 rows=6253232 loops=1
  • Buffers: shared read=109259
  • Worker 1: actual time=1003.988..2982.139 rows=6471238 loops=1
  • Buffers: shared hit=1 read=113003
8. 885.009 1,144.312 ↓ 25.1 1,163,037 3 / 3

Sort (cost=358,016.15..358,131.94 rows=46,314 width=60) (actual time=464.058..1,144.312 rows=1,163,037 loops=3)

  • Output: guia_motivo_glosa.motivo_glosa_id, guia.pagamento_id, guia.numero, guia.senha, guia.data_atendimento_fim, guia.data_atendimento_inicio, guia.beneficiario_id, guia.valor_total_pago, guia.valor_total_apresentado, (COALESCE(guia.numero, ''::text)), (COALESCE(guia.senha, ''::text)), (COALESCE(guia.data_atendimento_fim, '1900-01-01'::date)), (COALESCE(guia.data_atendimento_inicio, '1900-01-01'::date)), (COALESCE(guia.beneficiario_id, '0'::bigint)), (COALESCE(guia.valor_total_pago, '0'::numeric)), (COALESCE(guia.valor_total_apresentado, '0'::numeric))
  • Sort Key: (COALESCE(guia.numero, ''::text)), (COALESCE(guia.senha, ''::text)), (COALESCE(guia.data_atendimento_fim, '1900-01-01'::date)), (COALESCE(guia.data_atendimento_inicio, '1900-01-01'::date)), (COALESCE(guia.beneficiario_id, '0'::bigint)), (COALESCE(guia.valor_total_pago, '0'::numeric)), (COALESCE(guia.valor_total_apresentado, '0'::numeric))
  • Sort Method: external sort Disk: 5456kB
  • Worker 0: Sort Method: external sort Disk: 5456kB
  • Worker 1: Sort Method: external sort Disk: 5456kB
  • Buffers: shared hit=554205 read=2459, temp read=53263 written=4095
  • Worker 0: actual time=560.781..1339.623 rows=1104738 loops=1
  • Buffers: shared hit=183354 read=2201, temp read=17351 written=1365
  • Worker 1: actual time=410.874..1263.460 rows=1238314 loops=1
  • Buffers: shared hit=185428 read=127, temp read=19004 written=1365
9. 18.661 259.303 ↑ 1.0 46,314 3 / 3

Nested Loop (cost=0.44..354,427.01 rows=46,314 width=60) (actual time=0.380..259.303 rows=46,314 loops=3)

  • Output: guia_motivo_glosa.motivo_glosa_id, guia.pagamento_id, guia.numero, guia.senha, guia.data_atendimento_fim, guia.data_atendimento_inicio, guia.beneficiario_id, guia.valor_total_pago, guia.valor_total_apresentado, COALESCE(guia.numero, ''::text), COALESCE(guia.senha, ''::text), COALESCE(guia.data_atendimento_fim, '1900-01-01'::date), COALESCE(guia.data_atendimento_inicio, '1900-01-01'::date), COALESCE(guia.beneficiario_id, '0'::bigint), COALESCE(guia.valor_total_pago, '0'::numeric), COALESCE(guia.valor_total_apresentado, '0'::numeric)
  • Inner Unique: true
  • Buffers: shared hit=554205 read=2459
  • Worker 0: actual time=0.815..362.063 rows=46314 loops=1
  • Buffers: shared hit=183354 read=2201
  • Worker 1: actual time=0.164..208.091 rows=46314 loops=1
  • Buffers: shared hit=185428 read=127
10. 9.072 9.072 ↑ 1.0 46,314 3 / 3

Seq Scan on public.guia_motivo_glosa (cost=0.00..758.14 rows=46,314 width=16) (actual time=0.063..9.072 rows=46,314 loops=3)

  • Output: guia_motivo_glosa.id, guia_motivo_glosa.guia_id, guia_motivo_glosa.motivo_glosa_id
  • Buffers: shared hit=590 read=295
  • Worker 0: actual time=0.094..12.582 rows=46314 loops=1
  • Buffers: shared hit=35 read=260
  • Worker 1: actual time=0.068..7.499 rows=46314 loops=1
  • Buffers: shared hit=278 read=17
11. 231.570 231.570 ↑ 1.0 1 138,942 / 3

Index Scan using guia_id_pk on public.guia (cost=0.44..7.64 rows=1 width=56) (actual time=0.005..0.005 rows=1 loops=138,942)

  • Output: guia.id, guia.pagamento_id, guia.beneficiario_id, guia.status_analise_glosa, guia.numero, guia.data_atendimento_inicio, guia.data_atendimento_fim, guia.senha, guia.valor_total_apresentado, guia.valor_total_pago, guia.diferenca, guia.valor_total_glosa_informada, guia.quantidade_itens, guia.id_tenant, guia.id_nucleo, guia.guia_associada, guia.valor_total_recursado, guia.valor_total_aceito
  • Index Cond: (guia.id = guia_motivo_glosa.guia_id)
  • Buffers: shared hit=553615 read=2164
  • Worker 0: actual time=0.007..0.007 rows=1 loops=46314
  • Buffers: shared hit=183319 read=1941
  • Worker 1: actual time=0.004..0.004 rows=1 loops=46314
  • Buffers: shared hit=185150 read=110
12. 6,874.940 6,874.940 ↑ 1.0 1 3,437,470

Index Scan using pagamento_id_pk on public.pagamento (cost=0.55..7.52 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=3,437,470)

  • Output: pagamento.id, pagamento.operadora_id, pagamento.hospital_id, pagamento.convenio_id, pagamento.protocolo, pagamento.data_pagamento, pagamento.data_recebimento
  • Index Cond: (pagamento.id = guia.pagamento_id)
  • Buffers: shared hit=17187099 read=251 written=57
13. 6,874.940 6,874.940 ↑ 1.0 1 3,437,470

Index Scan using pagamento_id_pk on public.pagamento pagamento_new (cost=0.55..7.52 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=3,437,470)

  • Output: pagamento_new.id, pagamento_new.operadora_id, pagamento_new.hospital_id, pagamento_new.convenio_id, pagamento_new.protocolo, pagamento_new.data_pagamento, pagamento_new.data_recebimento
  • Index Cond: (pagamento_new.id = guia_new.pagamento_id)
  • Buffers: shared hit=17187350
14. 27,412.144 27,412.144 ↑ 1.0 73 3,426,518

Seq Scan on public.hospital (cost=0.00..2.73 rows=73 width=0) (actual time=0.002..0.008 rows=73 loops=3,426,518)

  • Output: hospital.id, hospital.nome, hospital.cnpj, hospital.id_tenant, hospital.id_nucleo, hospital.cliente_id
  • Buffers: shared hit=6853034 read=2
Execution time : 139,194.652 ms