explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kYH

Settings
# exclusive inclusive rows x rows loops node
1. 100.054 1,770.924 ↓ 3.8 19,626 1

WindowAgg (cost=104,094.77..104,628.18 rows=5,204 width=1,704) (actual time=1,660.833..1,770.924 rows=19,626 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, m.codfic, f.postra, (('now'::cstring)::date - ((m.datate)::date)), (((m.datate)::date) + (((p_1.mes)::text || ' months'::text))::interval), ((m.datate)::date), upper((f.nomfun)::text), ((m.datate)::date), o.desori, p.desprf, CASE WHEN ((p.sigprf)::text <> ''::text) THEN (((p.sigprf)::text || ':'::text) || (t.regcon)::text) ELSE ''::text END, t.codate, m.numate, m.tipaso, m.codpar, upper((t.nomate)::text), f.postra, s.dessit, ((a.datafa)::date), CASE WHEN ((a.datter)::date = '1900-12-31'::date) THEN NULL::date ELSE (a.datter)::date END, f.numcpf, date_part('year'::text, age((('now'::cstring)::date)::timestamp without time zone, f.datnas)), (('now'::cstring)::date - ((m.datate)::date)), row_number() OVER (?)
2. 71.491 1,670.870 ↓ 3.8 19,626 1

Sort (cost=104,094.77..104,107.78 rows=5,204 width=1,704) (actual time=1,660.759..1,670.870 rows=19,626 loops=1)

  • Output: f.numemp, f.numcad, ((m.datate)::date), ((a.datafa)::date), f.codfil, m.codfic, f.postra, m.datate, p_1.mes, f.nomfun, o.desori, p.desprf, p.sigprf, t.regcon, t.codate, m.numate, m.tipaso, m.codpar, t.nomate, s.dessit, a.datter, f.numcpf, f.datnas
  • Sort Key: f.numemp, f.numcad, ((m.datate)::date) DESC, ((a.datafa)::date) DESC
  • Sort Method: external merge Disk: 4128kB
3. 35.857 1,599.379 ↓ 3.8 19,626 1

Nested Loop (cost=3,382.98..99,930.55 rows=5,204 width=1,704) (actual time=37.933..1,599.379 rows=19,626 loops=1)

  • Output: f.numemp, f.numcad, (m.datate)::date, (a.datafa)::date, f.codfil, m.codfic, f.postra, m.datate, p_1.mes, f.nomfun, o.desori, p.desprf, p.sigprf, t.regcon, t.codate, m.numate, m.tipaso, m.codpar, t.nomate, s.dessit, a.datter, f.numcpf, f.datnas
4. 25.112 297.285 ↓ 3.9 20,099 1

Hash Join (cost=3,368.03..22,013.66 rows=5,204 width=1,700) (actual time=37.726..297.285 rows=20,099 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, m.codfic, m.datate, m.numate, m.tipaso, m.codpar, a.datafa, a.datter, s.dessit, o.desori, t.regcon, t.codate, t.nomate, p.desprf, p.sigprf
  • Hash Cond: ((f.numemp = fc.numemp) AND (f.numcad = fc.codfic) AND (f.tipcol = fc.tipcol))
5. 13.455 252.652 ↓ 3.9 20,099 1

Hash Left Join (cost=1,700.50..19,813.90 rows=5,122 width=1,704) (actual time=17.828..252.652 rows=20,099 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.tipcol, m.codfic, m.datate, m.numate, m.tipaso, m.codpar, m.numemp, a.datafa, a.datter, s.dessit, o.desori, t.regcon, t.codate, t.nomate, p.desprf, p.sigprf
  • Hash Cond: (t.codprf = p.codprf)
6. 14.721 239.171 ↓ 3.9 20,099 1

Hash Left Join (cost=1,689.38..19,732.34 rows=5,122 width=674) (actual time=17.784..239.171 rows=20,099 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.tipcol, m.codfic, m.datate, m.numate, m.tipaso, m.codpar, m.numemp, a.datafa, a.datter, s.dessit, o.desori, t.regcon, t.codate, t.nomate, t.codprf
  • Hash Cond: (m.codate = t.codate)
7. 12.393 218.274 ↓ 3.9 20,099 1

Hash Left Join (cost=1,132.95..19,105.49 rows=5,122 width=644) (actual time=11.508..218.274 rows=20,099 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.tipcol, m.codfic, m.datate, m.numate, m.tipaso, m.codpar, m.numemp, m.codate, a.datafa, a.datter, s.dessit, o.desori
  • Hash Cond: (m.tipaso = o.tipori)
8. 15.507 205.867 ↓ 3.9 20,099 1

Hash Left Join (cost=1,131.57..19,033.68 rows=5,122 width=128) (actual time=11.481..205.867 rows=20,099 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.tipcol, m.codfic, m.datate, m.numate, m.tipaso, m.codpar, m.numemp, m.codate, a.datafa, a.datter, s.dessit
  • Hash Cond: (f.sitafa = s.codsit)
9. 20.506 190.237 ↓ 3.9 20,099 1

Nested Loop Left Join (cost=1,119.90..18,951.58 rows=5,122 width=109) (actual time=11.337..190.237 rows=20,099 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.tipcol, f.sitafa, m.codfic, m.datate, m.numate, m.tipaso, m.codpar, m.numemp, m.codate, a.datafa, a.datter
10. 20.574 70.075 ↓ 2.4 12,457 1

Hash Join (cost=1,119.48..2,536.97 rows=5,122 width=93) (actual time=11.298..70.075 rows=12,457 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.tipcol, f.sitafa, m.codfic, m.datate, m.numate, m.tipaso, m.codpar, m.numemp, m.codate
  • Hash Cond: ((m.numemp = f.numemp) AND (m.codfic = f.numcad))
11. 38.355 38.355 ↓ 2.7 23,787 1

Seq Scan on public.view_fdw_senior_r110mam m (cost=0.00..1,080.18 rows=8,803 width=26) (actual time=0.028..38.355 rows=23,787 loops=1)

  • Output: m.numemp, m.codate, m.numate, m.codfic, m.datate, m.tipate, m.tipmam, m.diaate, m.codoco, m.codpar, m.desate, m.coddoe, m.tipaso, m.oriate, m.codusu, m.datafa, m.sitate, m.horafa, m.horter, m.obsate, m.fimate, m.horate, m.codsub, m.datinc, m.ultalt, m.acitra, m.codpos
  • Filter: ((('now'::cstring)::date - (m.datate)::date) > 364)
  • Rows Removed by Filter: 2621
12. 3.205 11.146 ↑ 1.0 8,219 1

Hash (cost=996.19..996.19 rows=8,219 width=67) (actual time=11.146..11.146 rows=8,219 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.tipcol, f.sitafa
  • Buckets: 16384 Batches: 1 Memory Usage: 979kB
13. 7.941 7.941 ↑ 1.0 8,219 1

Seq Scan on public.view_fdw_senior_r034fun f (cost=0.00..996.19 rows=8,219 width=67) (actual time=0.007..7.941 rows=8,219 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.tipcol, f.sitafa
14. 99.656 99.656 ↑ 1.0 1 12,457

Index Scan using view_fdw_senior_r038afa_idx on public.view_fdw_senior_r038afa a (cost=0.42..3.19 rows=1 width=26) (actual time=0.006..0.008 rows=1 loops=12,457)

  • Output: a.numemp, a.tipcol, a.numcad, a.datafa, a.horafa, a.datter, a.horter, a.prvter, a.sitafa, a.caudem, a.diajus, a.qhrafa, a.oriafa, a.exmret, a.contov, a.obsafa, a.staatu, a.motrai, a.nroaut, a.codoem, a.codsub, a.datper, a.sitini, a.risnex, a.datnex, a.diaprv, a.codcua, a.tmacua, a.datpar, a.diablq, a.seqreg, a.hrtrcs, a.coddoe, a.codate, a.acitra, a.cgcces, a.onuces, a.cgcsin, a.onusin, a.eferet, a.encafa, a.cgcsuc, a.datalt, a.sitori, a.motalt, a.usu_numcat, a.aciant, a.nomate, a.regcon, a.estcon, a.orgcla, a.orimot, a.numpro, a.msmmot, a.tipsuc, a.fimqua
  • Index Cond: ((a.numemp = f.numemp) AND (a.numcad = f.numcad) AND (a.sitafa = f.sitafa) AND (a.tipcol = f.tipcol))
15. 0.052 0.123 ↑ 1.0 252 1

Hash (cost=8.52..8.52 rows=252 width=23) (actual time=0.123..0.123 rows=252 loops=1)

  • Output: s.dessit, s.codsit
  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
16. 0.071 0.071 ↑ 1.0 252 1

Seq Scan on public.view_fdw_senior_r010sit s (cost=0.00..8.52 rows=252 width=23) (actual time=0.006..0.071 rows=252 loops=1)

  • Output: s.dessit, s.codsit
17. 0.008 0.014 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=518) (actual time=0.014..0.014 rows=17 loops=1)

  • Output: o.desori, o.tipori
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.006 0.006 ↑ 1.0 17 1

Seq Scan on public.view_fdw_senior_r108ori o (cost=0.00..1.17 rows=17 width=518) (actual time=0.003..0.006 rows=17 loops=1)

  • Output: o.desori, o.tipori
19. 2.306 6.176 ↑ 1.0 8,819 1

Hash (cost=446.19..446.19 rows=8,819 width=34) (actual time=6.176..6.176 rows=8,819 loops=1)

  • Output: t.regcon, t.codate, t.nomate, t.codprf
  • Buckets: 16384 Batches: 1 Memory Usage: 725kB
20. 3.870 3.870 ↑ 1.0 8,819 1

Seq Scan on public.view_fdw_senior_r108tat t (cost=0.00..446.19 rows=8,819 width=34) (actual time=0.009..3.870 rows=8,819 loops=1)

  • Output: t.regcon, t.codate, t.nomate, t.codprf
21. 0.015 0.026 ↑ 1.0 49 1

Hash (cost=10.50..10.50 rows=50 width=1,034) (actual time=0.026..0.026 rows=49 loops=1)

  • Output: p.desprf, p.sigprf, p.codprf
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
22. 0.011 0.011 ↑ 1.0 49 1

Seq Scan on public.view_fdw_senior_r022prf p (cost=0.00..10.50 rows=50 width=1,034) (actual time=0.006..0.011 rows=49 loops=1)

  • Output: p.desprf, p.sigprf, p.codprf
23. 10.003 19.521 ↑ 1.0 37,981 1

Hash (cost=1,002.83..1,002.83 rows=37,983 width=8) (actual time=19.521..19.521 rows=37,981 loops=1)

  • Output: fc.codfic, fc.numemp, fc.tipcol
  • Buckets: 65536 Batches: 1 Memory Usage: 1996kB
24. 9.518 9.518 ↑ 1.0 37,983 1

Seq Scan on public.view_fdw_senior_r110fic fc (cost=0.00..1,002.83 rows=37,983 width=8) (actual time=0.009..9.518 rows=37,983 loops=1)

  • Output: fc.codfic, fc.numemp, fc.tipcol
25. 20.099 1,266.237 ↑ 1.0 1 20,099

Limit (cost=14.95..14.95 rows=1 width=8) (actual time=0.063..0.063 rows=1 loops=20,099)

  • Output: (1), p_1.mes
26. 100.495 1,246.138 ↑ 4.0 1 20,099

Sort (cost=14.95..14.96 rows=4 width=8) (actual time=0.062..0.062 rows=1 loops=20,099)

  • Output: (1), p_1.mes
  • Sort Key: (1)
  • Sort Method: top-N heapsort Memory: 25kB
27. 80.396 1,145.643 ↑ 1.3 3 20,099

HashAggregate (cost=14.85..14.89 rows=4 width=4) (actual time=0.056..0.057 rows=3 loops=20,099)

  • Output: (1), p_1.mes
  • Group Key: (1), p_1.mes
28. 60.297 1,065.247 ↑ 1.3 3 20,099

Append (cost=0.15..14.83 rows=4 width=4) (actual time=0.034..0.053 rows=3 loops=20,099)

29. 86.806 381.881 ↓ 0.0 0 20,099

Nested Loop (cost=0.15..9.32 rows=1 width=4) (actual time=0.019..0.019 rows=0 loops=20,099)

  • Output: 1, p_1.mes
30. 221.089 221.089 ↓ 2.0 2 20,099

Seq Scan on gazin.aso_regra_idade i (cost=0.00..1.14 rows=1 width=4) (actual time=0.008..0.011 rows=2 loops=20,099)

  • Output: i.idaso_regra_idade, i.idademin, i.idademax, i.mes
  • Filter: ((date_part('year'::text, age((('now'::cstring)::date)::timestamp without time zone, f.datnas)) >= (i.idademin)::double precision) AND (date_part('year'::text, age((('now'::cstring)::date)::timestamp without time zone, f.datnas)) <= (i.idademax)::double precision))
  • Rows Removed by Filter: 1
31. 73.986 73.986 ↓ 0.0 0 36,993

Index Scan using pk_idaso_regra_posto on gazin.aso_regra_posto p_1 (cost=0.15..8.18 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=36,993)

  • Output: p_1.idaso_regra_idade, p_1.idempresa, p_1.idfilial, p_1.posto_trabalho, p_1.mes
  • Index Cond: ((p_1.idaso_regra_idade = i.idaso_regra_idade) AND (f.numemp = p_1.idempresa) AND (f.codfil = p_1.idfilial) AND ((f.postra)::text = (p_1.posto_trabalho)::text))
32. 45.942 80.396 ↓ 0.0 0 20,099

Nested Loop (cost=0.00..2.16 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=20,099)

  • Output: 2, fl.mes
  • Join Filter: (fl.idaso_regra_idade = i_1.idaso_regra_idade)
  • Rows Removed by Join Filter: 0
33. 20.099 20.099 ↓ 0.0 0 20,099

Seq Scan on gazin.aso_regra_filial fl (cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=20,099)

  • Output: fl.idaso_regra_idade, fl.idempresa, fl.idfilial, fl.mes
  • Filter: ((f.numemp = fl.idempresa) AND (f.codfil = fl.idfilial))
  • Rows Removed by Filter: 1
34. 14.355 14.355 ↓ 2.0 2 1,595

Seq Scan on gazin.aso_regra_idade i_1 (cost=0.00..1.14 rows=1 width=4) (actual time=0.006..0.009 rows=2 loops=1,595)

  • Output: i_1.idaso_regra_idade, i_1.idademin, i_1.idademax, i_1.mes
  • Filter: ((date_part('year'::text, age((('now'::cstring)::date)::timestamp without time zone, f.datnas)) >= (i_1.idademin)::double precision) AND (date_part('year'::text, age((('now'::cstring)::date)::timestamp without time zone, f.datnas)) <= (i_1.idademax)::double precision))
  • Rows Removed by Filter: 1
35. 101.203 361.782 ↑ 1.0 1 20,099

Nested Loop (cost=0.00..2.17 rows=1 width=4) (actual time=0.008..0.018 rows=1 loops=20,099)

  • Output: 3, e.mes
  • Join Filter: (e.idaso_regra_idade = i_2.idaso_regra_idade)
  • Rows Removed by Join Filter: 1
36. 20.099 20.099 ↑ 1.0 1 20,099

Seq Scan on gazin.aso_regra_empresa e (cost=0.00..1.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=20,099)

  • Output: e.idaso_regra_idade, e.idempresa, e.mes
  • Filter: (f.numemp = e.idempresa)
  • Rows Removed by Filter: 1
37. 240.480 240.480 ↓ 2.0 2 30,060

Seq Scan on gazin.aso_regra_idade i_2 (cost=0.00..1.14 rows=1 width=4) (actual time=0.006..0.008 rows=2 loops=30,060)

  • Output: i_2.idaso_regra_idade, i_2.idademin, i_2.idademax, i_2.mes
  • Filter: ((date_part('year'::text, age((('now'::cstring)::date)::timestamp without time zone, f.datnas)) >= (i_2.idademin)::double precision) AND (date_part('year'::text, age((('now'::cstring)::date)::timestamp without time zone, f.datnas)) <= (i_2.idademax)::double precision))
  • Rows Removed by Filter: 1
38. 180.891 180.891 ↓ 2.0 2 20,099

Seq Scan on gazin.aso_regra_idade i_3 (cost=0.00..1.14 rows=1 width=4) (actual time=0.006..0.009 rows=2 loops=20,099)

  • Output: 4, i_3.mes
  • Filter: ((date_part('year'::text, age((('now'::cstring)::date)::timestamp without time zone, f.datnas)) >= (i_3.idademin)::double precision) AND (date_part('year'::text, age((('now'::cstring)::date)::timestamp without time zone, f.datnas)) <= (i_3.idademax)::double precision))
  • Rows Removed by Filter: 1