explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mekx

Settings
# exclusive inclusive rows x rows loops node
1. 6.532 1,697.400 ↓ 143.6 4,738 1

Subquery Scan on tb (cost=134,559.61..134,659.39 rows=33 width=1,253) (actual time=1,684.511..1,697.400 rows=4,738 loops=1)

  • Output: tb.numemp, tb.codfil, tb.numcad, tb.codfic, tb.postra, tb.diasatraso, tb.vencimento, tb.datate, tb.nome, tb.dataexame, tb.tipoexame, tb.conselho, tb.crm, tb.codate, tb.numate, tb.tipaso, tb.codpar, tb.atendente, tb.postotrabalho, tb.situacaotrabalho, tb.dataafastamento, tb.datatermino, tb.numcpf, tb.idade, tb.dias, tb.ordem
  • Filter: (tb.ordem = 1)
  • Rows Removed by Filter: 14531
2. 84.027 1,690.868 ↓ 2.9 19,269 1

Sort (cost=134,559.61..134,576.24 rows=6,652 width=1,703) (actual time=1,684.483..1,690.868 rows=19,269 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 (?))
  • Sort Key: f.numemp, f.codfil, ((m.datate)::date), (upper((f.nomfun)::text))
  • Sort Method: external merge Disk: 4672kB
3. 93.488 1,606.841 ↓ 2.9 19,269 1

WindowAgg (cost=128,541.39..129,223.22 rows=6,652 width=1,703) (actual time=1,504.374..1,606.841 rows=19,269 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 (?)
4. 65.547 1,513.353 ↓ 2.9 19,269 1

Sort (cost=128,541.39..128,558.02 rows=6,652 width=1,703) (actual time=1,504.301..1,513.353 rows=19,269 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: 4048kB
5. 31.620 1,447.806 ↓ 2.9 19,269 1

Nested Loop (cost=9,379.54..123,205.00 rows=6,652 width=1,703) (actual time=79.737..1,447.806 rows=19,269 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
6. 22.731 330.376 ↓ 3.0 19,742 1

Hash Join (cost=9,364.59..23,607.93 rows=6,652 width=1,699) (actual time=79.515..330.376 rows=19,742 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))
7. 12.033 286.118 ↓ 3.6 19,742 1

Hash Left Join (cost=7,704.43..21,542.18 rows=5,425 width=1,703) (actual time=57.600..286.118 rows=19,742 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)
8. 13.403 274.057 ↓ 3.6 19,742 1

Hash Left Join (cost=7,693.30..21,456.46 rows=5,425 width=673) (actual time=57.545..274.057 rows=19,742 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)
9. 10.838 254.269 ↓ 3.6 19,742 1

Hash Left Join (cost=7,136.87..20,825.44 rows=5,425 width=643) (actual time=51.041..254.269 rows=19,742 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)
10. 14.027 243.415 ↓ 3.6 19,742 1

Hash Left Join (cost=7,135.49..20,749.46 rows=5,425 width=127) (actual time=51.006..243.415 rows=19,742 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)
11. 25.337 229.238 ↓ 3.6 19,742 1

Nested Loop Left Join (cost=7,123.82..20,663.20 rows=5,425 width=108) (actual time=50.837..229.238 rows=19,742 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
12. 19.625 104.149 ↓ 2.3 12,469 1

Hash Join (cost=7,123.40..9,226.16 rows=5,425 width=92) (actual time=50.746..104.149 rows=12,469 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))
13. 34.033 34.033 ↓ 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.033..34.033 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
14. 4.532 50.491 ↑ 2.0 8,227 1

Hash (cost=6,872.09..6,872.09 rows=16,754 width=66) (actual time=50.491..50.491 rows=8,227 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.tipcol, f.sitafa
  • Buckets: 32768 Batches: 1 Memory Usage: 1108kB
15. 45.959 45.959 ↑ 2.0 8,227 1

Seq Scan on public.view_fdw_senior_r034fun f (cost=0.00..6,872.09 rows=16,754 width=66) (actual time=0.124..45.959 rows=8,227 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.tipcol, f.sitafa
  • Filter: ((f.sitafa <> 7) AND (f.tipcol = 1))
  • Rows Removed by Filter: 46379
16. 99.752 99.752 ↑ 1.0 1 12,469

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

  • 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) AND (a.tipcol = 1))
17. 0.062 0.150 ↑ 1.0 252 1

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

  • Output: s.dessit, s.codsit
  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
18. 0.088 0.088 ↑ 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.012..0.088 rows=252 loops=1)

  • Output: s.dessit, s.codsit
19. 0.009 0.016 ↑ 1.0 17 1

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

  • Output: o.desori, o.tipori
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.007 0.007 ↑ 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.005..0.007 rows=17 loops=1)

  • Output: o.desori, o.tipori
21. 2.429 6.385 ↑ 1.0 8,819 1

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

  • Output: t.regcon, t.codate, t.nomate, t.codprf
  • Buckets: 16384 Batches: 1 Memory Usage: 725kB
22. 3.956 3.956 ↑ 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.010..3.956 rows=8,819 loops=1)

  • Output: t.regcon, t.codate, t.nomate, t.codprf
23. 0.017 0.028 ↑ 1.0 49 1

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

  • Output: p.desprf, p.sigprf, p.codprf
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
24. 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
25. 9.835 21.527 ↓ 1.0 37,496 1

Hash (cost=1,097.79..1,097.79 rows=37,492 width=8) (actual time=21.527..21.527 rows=37,496 loops=1)

  • Output: fc.codfic, fc.numemp, fc.tipcol
  • Buckets: 65536 Batches: 1 Memory Usage: 1977kB
26. 11.692 11.692 ↓ 1.0 37,496 1

Seq Scan on public.view_fdw_senior_r110fic fc (cost=0.00..1,097.79 rows=37,492 width=8) (actual time=0.014..11.692 rows=37,496 loops=1)

  • Output: fc.codfic, fc.numemp, fc.tipcol
  • Filter: (fc.tipcol = 1)
  • Rows Removed by Filter: 487
27. 19.742 1,085.810 ↑ 1.0 1 19,742

Limit (cost=14.95..14.95 rows=1 width=8) (actual time=0.055..0.055 rows=1 loops=19,742)

  • Output: (1), p_1.mes
28. 78.968 1,066.068 ↑ 4.0 1 19,742

Sort (cost=14.95..14.96 rows=4 width=8) (actual time=0.054..0.054 rows=1 loops=19,742)

  • Output: (1), p_1.mes
  • Sort Key: (1)
  • Sort Method: top-N heapsort Memory: 25kB
29. 78.968 987.100 ↑ 1.3 3 19,742

HashAggregate (cost=14.85..14.89 rows=4 width=4) (actual time=0.049..0.050 rows=3 loops=19,742)

  • Output: (1), p_1.mes
  • Group Key: (1), p_1.mes
30. 39.484 908.132 ↑ 1.3 3 19,742

Append (cost=0.15..14.83 rows=4 width=4) (actual time=0.029..0.046 rows=3 loops=19,742)

31. 65.638 335.614 ↓ 0.0 0 19,742

Nested Loop (cost=0.15..9.32 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=19,742)

  • Output: 1, p_1.mes
32. 197.420 197.420 ↓ 2.0 2 19,742

Seq Scan on gazin.aso_regra_idade i (cost=0.00..1.14 rows=1 width=4) (actual time=0.007..0.010 rows=2 loops=19,742)

  • 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
33. 72.556 72.556 ↓ 0.0 0 36,278

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,278)

  • 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))
34. 26.724 59.226 ↓ 0.0 0 19,742

Nested Loop (cost=0.00..2.16 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=19,742)

  • Output: 2, fl.mes
  • Join Filter: (fl.idaso_regra_idade = i_1.idaso_regra_idade)
  • Rows Removed by Join Filter: 0
35. 19.742 19.742 ↓ 0.0 0 19,742

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=19,742)

  • 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
36. 12.760 12.760 ↓ 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.008 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
37. 87.390 315.872 ↑ 1.0 1 19,742

Nested Loop (cost=0.00..2.17 rows=1 width=4) (actual time=0.007..0.016 rows=1 loops=19,742)

  • Output: 3, e.mes
  • Join Filter: (e.idaso_regra_idade = i_2.idaso_regra_idade)
  • Rows Removed by Join Filter: 1
38. 19.742 19.742 ↓ 2.0 2 19,742

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

  • Output: e.idaso_regra_idade, e.idempresa, e.mes
  • Filter: (f.numemp = e.idempresa)
  • Rows Removed by Filter: 0
39. 208.740 208.740 ↓ 2.0 2 29,820

Seq Scan on gazin.aso_regra_idade i_2 (cost=0.00..1.14 rows=1 width=4) (actual time=0.005..0.007 rows=2 loops=29,820)

  • 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
40. 157.936 157.936 ↓ 2.0 2 19,742

Seq Scan on gazin.aso_regra_idade i_3 (cost=0.00..1.14 rows=1 width=4) (actual time=0.005..0.008 rows=2 loops=19,742)

  • 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