explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jBO7

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=2,301,624,280.86..2,301,624,342.39 rows=24,611 width=80) (actual rows= loops=)

  • Sort Key: d.num_cpf, d.tipo_bloco, d.num_mes_pgto, (CASE WHEN ((d.tipo_linha)::text = 'RTRT'::text) THEN 2 WHEN ((d.tipo_linha)::text = 'RTIRF'::text) THEN 3 WHEN ((d.tipo_linha)::text = 'RTPO'::text) THEN 6 WHEN ((d.tipo_linha)::text = 'RTPP'::text) THE (...)
2. 0.000 0.000 ↓ 0.0

WindowAgg (cost=2,301,620,824.61..2,301,622,485.86 rows=24,611 width=80) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Sort (cost=2,301,620,824.61..2,301,620,886.14 rows=24,611 width=80) (actual rows= loops=)

  • Sort Key: d.num_cpf, d.num_mes_pgto
4. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,301,617,614.48..2,301,619,029.61 rows=24,611 width=80) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=222,088.67..2,301,609,615.90 rows=246,110 width=80) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

HashAggregate (cost=184,907.66..185,156.36 rows=24,870 width=16) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tb_carga_dirf d1 (cost=23,639.41..183,664.18 rows=248,696 width=16) (actual rows= loops=)

  • Recheck Cond: (isn_orgao_envio = 33)
  • Filter: ((vlr_enviado > 0) AND (num_ano_pgto = 2,019) AND ((tipo_linha)::text = ANY ('{RTRT,RIMOG,RIBMR,RTIRF,RIP65,RTPA_DEP}'::text[])))
8. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_orgao_env (cost=0.00..23,577.23 rows=1,276,311 width=0) (actual rows= loops=)

  • Index Cond: (isn_orgao_envio = 33)
9. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tb_carga_dirf d (cost=37,181.00..92,537.75 rows=16 width=80) (actual rows= loops=)

  • Recheck Cond: (((num_cpf)::text = (d1.num_cpf)::text) AND (isn_orgao_envio = 33) AND (num_ano_pgto = 2,019))
  • Filter: ((vlr_enviado > 0) AND ((tipo_linha)::text <> 'LINHA'::text) AND ((tipo_bloco)::text <> 'BLOCO'::text) AND ((tipo_bloco)::text <> '1889'::text) AND (NOT (SubPlan 1)) AND ((SubPlan 2) OR (SubPlan 3) OR (SubPlan 4) OR (...)
10. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=37,181.00..37,181.00 rows=16 width=0) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_cpf (cost=0.00..2.46 rows=123 width=0) (actual rows= loops=)

  • Index Cond: ((num_cpf)::text = (d1.num_cpf)::text)
12. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_orgao_env (cost=0.00..9,573.50 rows=1,276,311 width=0) (actual rows= loops=)

  • Index Cond: (isn_orgao_envio = 33)
13. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_anomes (cost=0.00..27,481.49 rows=3,664,044 width=0) (actual rows= loops=)

  • Index Cond: (num_ano_pgto = 2,019)
14.          

SubPlan (for Bitmap Heap Scan)

15. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=517.29..517.33 rows=1 width=12) (actual rows= loops=)

  • Filter: (count(*) > 1)
16. 0.000 0.000 ↓ 0.0

HashAggregate (cost=517.29..517.30 rows=1 width=23) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6.09..517.28 rows=2 width=23) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6.09..497.15 rows=1 width=35) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tb_carga_dirf d2 (cost=6.09..488.81 rows=1 width=25) (actual rows= loops=)

  • Recheck Cond: ((num_cpf)::text = (d.num_cpf)::text)
  • Filter: ((dat_nascimento IS NOT NULL) AND (num_ano_pgto = 2,019) AND (isn_orgao_envio = d.isn_orgao_envio))
20. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_cpf (cost=0.00..6.09 rows=123 width=0) (actual rows= loops=)

  • Index Cond: ((num_cpf)::text = (d.num_cpf)::text)
21. 0.000 0.000 ↓ 0.0

Index Scan using co_pk_servidor on tb_servidor_congelado s (cost=0.00..8.33 rows=1 width=10) (actual rows= loops=)

  • Index Cond: ((isn_servidor = d2.isn_servidor) AND (num_ano = 2,019))
22. 0.000 0.000 ↓ 0.0

Index Scan using idx_co_fk_servidor_tb_vinculo on tb_vinculo_congelado v (cost=0.00..20.08 rows=4 width=8) (actual rows= loops=)

  • Index Cond: ((isn_servidor = s.isn_servidor) AND (num_ano = 2,019))
  • Filter: (cod_situacao_funcional <> 7)
23. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6.09..490.98 rows=1 width=24) (actual rows= loops=)

  • Filter: (sum(d1.vlr_enviado) > 600000::numeric)
24. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tb_carga_dirf d1 (cost=6.09..490.96 rows=1 width=24) (actual rows= loops=)

  • Recheck Cond: ((num_cpf)::text = (d.num_cpf)::text)
  • Filter: ((isn_orgao_envio = d.isn_orgao_envio) AND ((tipo_linha)::text = 'RTRT'::text) AND (num_ano_pgto = 2,019) AND (num_mes_pgto = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12}'::integer[])))
25. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_cpf (cost=0.00..6.09 rows=123 width=0) (actual rows= loops=)

  • Index Cond: ((num_cpf)::text = (d.num_cpf)::text)
26. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6.09..490.98 rows=1 width=24) (actual rows= loops=)

  • Filter: (sum(d1.vlr_enviado) > 600000::numeric)
27. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tb_carga_dirf d1 (cost=6.09..490.96 rows=1 width=24) (actual rows= loops=)

  • Recheck Cond: ((num_cpf)::text = (d.num_cpf)::text)
  • Filter: ((isn_orgao_envio = d.isn_orgao_envio) AND ((tipo_linha)::text = 'RIMOG'::text) AND (num_ano_pgto = 2,019) AND (num_mes_pgto = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12}'::integer[])))
28. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_cpf (cost=0.00..6.09 rows=123 width=0) (actual rows= loops=)

  • Index Cond: ((num_cpf)::text = (d.num_cpf)::text)
29. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6.09..490.98 rows=1 width=24) (actual rows= loops=)

  • Filter: (sum(d1.vlr_enviado) > 600000::numeric)
30. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tb_carga_dirf d1 (cost=6.09..490.96 rows=1 width=24) (actual rows= loops=)

  • Recheck Cond: ((num_cpf)::text = (d.num_cpf)::text)
  • Filter: ((isn_orgao_envio = d.isn_orgao_envio) AND ((tipo_linha)::text = 'RIBMR'::text) AND (num_ano_pgto = 2,019) AND (num_mes_pgto = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12}'::integer[])))
31. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_cpf (cost=0.00..6.09 rows=123 width=0) (actual rows= loops=)

  • Index Cond: ((num_cpf)::text = (d.num_cpf)::text)
32. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6.09..490.98 rows=1 width=24) (actual rows= loops=)

  • Filter: (sum(d1.vlr_enviado) > 600000::numeric)
33. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tb_carga_dirf d1 (cost=6.09..490.96 rows=1 width=24) (actual rows= loops=)

  • Recheck Cond: ((num_cpf)::text = (d.num_cpf)::text)
  • Filter: ((isn_orgao_envio = d.isn_orgao_envio) AND ((tipo_linha)::text = 'RIP65'::text) AND (num_ano_pgto = 2,019) AND (num_mes_pgto = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12}'::integer[])))
34. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_cpf (cost=0.00..6.09 rows=123 width=0) (actual rows= loops=)

  • Index Cond: ((num_cpf)::text = (d.num_cpf)::text)
35. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6.09..489.14 rows=1 width=24) (actual rows= loops=)

  • Filter: (sum(d1.vlr_enviado) > 0::numeric)
36. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tb_carga_dirf d1 (cost=6.09..489.12 rows=1 width=24) (actual rows= loops=)

  • Recheck Cond: ((num_cpf)::text = (d.num_cpf)::text)
  • Filter: ((isn_orgao_envio = d.isn_orgao_envio) AND ((tipo_linha)::text = 'RTIRF'::text) AND (num_ano_pgto = 2,019))
37. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_cpf (cost=0.00..6.09 rows=123 width=0) (actual rows= loops=)

  • Index Cond: ((num_cpf)::text = (d.num_cpf)::text)
38. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6.09..489.14 rows=1 width=24) (actual rows= loops=)

  • Filter: (sum(d1.vlr_enviado) > 0::numeric)
39. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tb_carga_dirf d1 (cost=6.09..489.12 rows=1 width=24) (actual rows= loops=)

  • Recheck Cond: ((num_cpf)::text = (d.num_cpf)::text)
  • Filter: ((isn_orgao_envio = d.isn_orgao_envio) AND ((tipo_linha)::text = 'RTPA_DEP'::text) AND (num_ano_pgto = 2,019))
40. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_cpf (cost=0.00..6.09 rows=123 width=0) (actual rows= loops=)

  • Index Cond: ((num_cpf)::text = (d.num_cpf)::text)