explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YqW9 : Optimization for: plan #umng

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 17,320.332 96,738.287 ↑ 1.0 1 1

Aggregate (cost=903,771.55..903,771.56 rows=1 width=8) (actual time=96,738.287..96,738.287 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=41950890 read=4716
  • Functions: 53
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 7.241 ms, Inlining 216.411 ms, Optimization 362.174 ms, Emission 254.194 ms, Total 840.020 ms
2. 33,059.174 79,417.955 ↓ 3,426,518.0 250,135,814 1

Nested Loop (cost=1,028.87..903,771.37 rows=73 width=0) (actual time=366.309..79,417.955 rows=250,135,814 loops=1)

  • Buffers: shared hit=41950890 read=4716
3. 1,677.628 18,946.637 ↓ 3,426,518.0 3,426,518 1

Nested Loop (cost=1,028.87..903,767.91 rows=1 width=0) (actual time=366.250..18,946.637 rows=3,426,518 loops=1)

  • 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=35097856 read=4714
4. 2,193.034 10,394.069 ↓ 3,437,470.0 3,437,470 1

Nested Loop (cost=1,028.32..903,760.37 rows=1 width=42) (actual time=366.237..10,394.069 rows=3,437,470 loops=1)

  • Output: pagamento.hospital_id, pagamento.protocolo, pagamento.data_pagamento, pagamento.operadora_id, pagamento.data_recebimento, guia_new.pagamento_id
  • Inner Unique: true
  • Buffers: shared hit=17910508 read=4712
5. 0.000 1,326.095 ↓ 3,437,470.0 3,437,470 1

Gather (cost=1,027.77..903,752.85 rows=1 width=16) (actual time=366.181..1,326.095 rows=3,437,470 loops=1)

  • Output: guia.pagamento_id, guia_new.pagamento_id
  • Workers Planned: 2
  • Workers Launched: 2
  • JIT for worker 0:
  • Functions: 13
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 2.141 ms, Inlining 72.648 ms, Optimization 90.786 ms, Emission 60.145 ms, Total 225.720 ms
  • JIT for worker 1:
  • Functions: 13
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 2.034 ms, Inlining 72.765 ms, Optimization 110.967 ms, Emission 61.389 ms, Total 247.155 ms
  • Buffers: shared hit=723339 read=4531
6. 554.207 4,362.150 ↓ 1,145,823.0 1,145,823 3 / 3

Nested Loop (cost=27.77..902,752.75 rows=1 width=16) (actual time=279.557..4,362.150 rows=1,145,823 loops=3)

  • Output: guia.pagamento_id, guia_new.pagamento_id
  • Buffers: shared hit=723339 read=4531
  • Worker 0: actual time=225.688..12406.372 rows=3425193 loops=1
  • Buffers: shared hit=720792 read=4397
  • Worker 1: actual time=247.516..314.544 rows=12203 loops=1
  • Buffers: shared hit=2532 read=127
7. 323.994 565.963 ↑ 1.3 15,438 3 / 3

Nested Loop (cost=0.73..303,020.29 rows=19,298 width=52) (actual time=278.335..565.963 rows=15,438 loops=3)

  • Output: 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
  • Inner Unique: true
  • Buffers: shared hit=184826 read=1560
  • Worker 0: actual time=224.232..1081.424 rows=46149 loops=1
  • Buffers: shared hit=184198 read=1511
  • Worker 1: actual time=245.923..251.613 rows=164 loops=1
  • Buffers: shared hit=620 read=46
8. 56.713 56.713 ↑ 1.3 15,438 3 / 3

Parallel Index Only Scan using guia_motivo_glosa_guia_id_nucleo_motivo_glosa_id_uindex on public.guia_motivo_glosa (cost=0.29..155,654.44 rows=19,298 width=8) (actual time=0.052..56.713 rows=15,438 loops=3)

  • Output: guia_motivo_glosa.guia_id, guia_motivo_glosa.motivo_glosa_id
  • Heap Fetches: 46314
  • Buffers: shared hit=535 read=584
  • Worker 0: actual time=0.066..169.509 rows=46149 loops=1
  • Buffers: shared hit=529 read=580
  • Worker 1: actual time=0.072..0.609 rows=164 loops=1
  • Buffers: shared hit=2 read=4
9. 185.256 185.256 ↑ 1.0 1 46,314 / 3

Index Scan using guia_id_pk on public.guia (cost=0.44..7.64 rows=1 width=56) (actual time=0.012..0.012 rows=1 loops=46,314)

  • 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=184291 read=976
  • Worker 0: actual time=0.012..0.012 rows=1 loops=46149
  • Buffers: shared hit=183669 read=931
  • Worker 1: actual time=0.029..0.029 rows=1 loops=164
  • Buffers: shared hit=618 read=42
10. 1,466.610 3,241.980 ↓ 74.0 74 46,314 / 3

Bitmap Heap Scan on public.guia guia_new (cost=27.04..31.07 rows=1 width=52) (actual time=0.122..0.210 rows=74 loops=46,314)

  • Output: guia_new.id, guia_new.pagamento_id, guia_new.beneficiario_id, guia_new.status_analise_glosa, guia_new.numero, guia_new.data_atendimento_inicio, guia_new.data_atendimento_fim, guia_new.senha, guia_new.valor_total_apresentado, guia_new.valor_total_pago, guia_new.diferenca, guia_new.valor_total_glosa_informada, guia_new.quantidade_itens, guia_new.id_tenant, guia_new.id_nucleo, guia_new.guia_associada, guia_new.valor_total_recursado, guia_new.valor_total_aceito
  • Recheck Cond: ((guia_new.numero = guia.numero) AND (guia_new.beneficiario_id = guia.beneficiario_id))
  • Filter: ((COALESCE(guia.senha, ''::text) = COALESCE(guia_new.senha, ''::text)) AND (COALESCE(guia.data_atendimento_fim, '1900-01-01'::date) = COALESCE(guia_new.data_atendimento_fim, '1900-01-01'::date)) AND (COALESCE(guia.data_atendimento_inicio, '1900-01-01'::date) = COALESCE(guia_new.data_atendimento_inicio, '1900-01-01'::date)) AND (guia.valor_total_pago = guia_new.valor_total_pago) AND (guia.valor_total_apresentado = guia_new.valor_total_apresentado))
  • Rows Removed by Filter: 6
  • Heap Blocks: exact=3
  • Buffers: shared hit=538513 read=2971
  • Worker 0: actual time=0.122..0.209 rows=74 loops=46149
  • Buffers: shared hit=536594 read=2886
  • Worker 1: actual time=0.232..0.346 rows=74 loops=164
  • Buffers: shared hit=1912 read=81
11. 77.190 1,775.370 ↓ 0.0 0 46,314 / 3

BitmapAnd (cost=27.04..27.04 rows=1 width=0) (actual time=0.115..0.115 rows=0 loops=46,314)

  • Buffers: shared hit=373431 read=1668
  • Worker 0: actual time=0.115..0.115 rows=0 loops=46149
  • Buffers: shared hit=372113 read=1615
  • Worker 1: actual time=0.222..0.222 rows=0 loops=164
  • Buffers: shared hit=1314 read=49
12. 586.644 586.644 ↑ 6.3 80 46,314 / 3

Bitmap Index Scan on guia_numero (cost=0.00..11.07 rows=502 width=0) (actual time=0.038..0.038 rows=80 loops=46,314)

  • Index Cond: (guia_new.numero = guia.numero)
  • Buffers: shared hit=197610 read=739
  • Worker 0: actual time=0.038..0.038 rows=80 loops=46149
  • Buffers: shared hit=196917 read=717
  • Worker 1: actual time=0.060..0.060 rows=87 loops=164
  • Buffers: shared hit=691 read=19
13. 1,111.536 1,111.536 ↑ 1.4 497 46,314 / 3

Bitmap Index Scan on guia_beneficiario_id (cost=0.00..15.72 rows=688 width=0) (actual time=0.072..0.072 rows=497 loops=46,314)

  • Index Cond: (guia_new.beneficiario_id = guia.beneficiario_id)
  • Buffers: shared hit=175821 read=929
  • Worker 0: actual time=0.071..0.071 rows=497 loops=46149
  • Buffers: shared hit=175196 read=898
  • Worker 1: actual time=0.156..0.156 rows=566 loops=164
  • Buffers: shared hit=623 read=30
14. 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=17187169 read=181
15. 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=17187348 read=2
16. 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.001..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 : 96,791.677 ms