explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fc2

Settings
# exclusive inclusive rows x rows loops node
1. 3.851 5,205.682 ↓ 4,738.0 4,738 1

Subquery Scan on tb (cost=270,552.37..270,553.03 rows=1 width=1,748) (actual time=5,199.117..5,205.682 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: 7438
2. 32.517 5,201.831 ↓ 276.7 12,176 1

Sort (cost=270,552.37..270,552.48 rows=44 width=3,202) (actual time=5,199.104..5,201.831 rows=12,176 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: quicksort Memory: 3567kB
3. 54.413 5,169.314 ↓ 276.7 12,176 1

WindowAgg (cost=270,546.66..270,551.17 rows=44 width=3,202) (actual time=5,112.342..5,169.314 rows=12,176 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. 20.513 5,114.901 ↓ 276.7 12,176 1

Sort (cost=270,546.66..270,546.77 rows=44 width=3,202) (actual time=5,112.260..5,114.901 rows=12,176 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: quicksort Memory: 2413kB
5. 22.792 5,094.388 ↓ 276.7 12,176 1

Nested Loop (cost=36,677.62..270,545.45 rows=44 width=3,202) (actual time=3,496.676..5,094.388 rows=12,176 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. 7.564 4,286.049 ↓ 283.4 12,469 1

Hash Left Join (cost=36,662.67..269,886.66 rows=44 width=3,198) (actual time=3,496.406..4,286.049 rows=12,469 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: (t.codprf = p.codprf)
7. 14.168 4,277.742 ↓ 283.4 12,469 1

Nested Loop Left Join (cost=36,588.06..269,811.78 rows=44 width=2,168) (actual time=3,495.635..4,277.742 rows=12,469 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, t.codprf
  • Join Filter: (t.codate = m.codate)
  • Rows Removed by Join Filter: 8818
8. 8.471 4,026.663 ↓ 12,469.0 12,469 1

Nested Loop Left Join (cost=36,563.06..260,857.54 rows=1 width=1,134) (actual time=3,485.235..4,026.663 rows=12,469 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, m.codate, a.datafa, a.datter, s.dessit, o.desori
  • Join Filter: (o.tipori = m.tipaso)
  • Rows Removed by Join Filter: 16
9. 0.000 4,018.192 ↓ 12,469.0 12,469 1

Nested Loop Left Join (cost=36,538.06..260,815.33 rows=1 width=618) (actual time=3,484.544..4,018.192 rows=12,469 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, m.codate, a.datafa, a.datter, s.dessit
  • Join Filter: (s.codsit = f.sitafa)
  • Rows Removed by Join Filter: 251
10. 77.281 4,006.306 ↓ 12,469.0 12,469 1

Nested Loop Left Join (cost=36,513.06..260,535.18 rows=1 width=104) (actual time=3,483.069..4,006.306 rows=12,469 loops=1)

  • Output: f.numemp, f.codfil, f.numcad, f.postra, f.nomfun, f.numcpf, f.datnas, f.sitafa, m.codfic, m.datate, m.numate, m.tipaso, m.codpar, m.codate, a.datafa, a.datter
  • Join Filter: ((a.tipcol = f.tipcol) AND (a.numemp = f.numemp) AND (a.numcad = f.numcad) AND (a.sitafa = f.sitafa))
  • Rows Removed by Join Filter: 181270
11. 31.987 923.996 ↓ 12,469.0 12,469 1

Hash Join (cost=36,488.06..74,755.94 rows=1 width=90) (actual time=414.600..923.996 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.codate
  • Hash Cond: ((fc.numemp = f.numemp) AND (fc.codfic = f.numcad))
12. 478.437 478.437 ↑ 1.0 37,496 1

Foreign Scan on public.fdw_senior_r110fic fc (cost=25.00..38,008.00 rows=37,983 width=8) (actual time=0.952..478.437 rows=37,496 loops=1)

  • Output: fc.numemp, fc.codfic, fc.tipatn, fc.tipcol, fc.numcad, fc.coddep, fc.numcan, fc.nomatn, fc.grusan, fc.pesatn, fc.altatn, fc.tipfis, fc.coldoa, fc.ultdoa, fc.antpes, fc.antfam, fc.antpro, fc.obsfic, fc.codusu, fc.deffis, fc.coddef, fc.benrea, fc.temsub, fc.cotdef
  • Filter: (fc.tipcol = 1)
  • Rows Removed by Filter: 487
13. 10.266 413.572 ↓ 175.6 12,469 1

Hash (cost=36,461.99..36,461.99 rows=71 width=92) (actual time=413.572..413.572 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
  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1755kB
14. 20.295 403.306 ↓ 175.6 12,469 1

Hash Join (cost=7,148.40..36,461.99 rows=71 width=92) (actual time=46.603..403.306 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))
15. 337.505 337.505 ↑ 1.1 23,787 1

Foreign Scan on public.fdw_senior_r110mam m (cost=25.00..26,433.00 rows=26,408 width=26) (actual time=0.687..337.505 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
16. 4.339 45.506 ↑ 2.0 8,227 1

Hash (cost=6,872.09..6,872.09 rows=16,754 width=66) (actual time=45.506..45.506 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
17. 41.167 41.167 ↑ 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.221..41.167 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
18. 3,005.029 3,005.029 ↑ 12,140.8 15 12,469

Foreign Scan on public.fdw_senior_r038afa a (cost=25.00..182,137.00 rows=182,112 width=26) (actual time=0.001..0.241 rows=15 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
  • Filter: (a.tipcol = 1)
  • Rows Removed by Filter: 0
19. 12.469 12.469 ↓ 0.0 0 12,469

Foreign Scan on public.fdw_senior_r010sit s (cost=25.00..277.00 rows=252 width=518) (actual time=0.000..0.001 rows=0 loops=12,469)

  • Output: s.codsit, s.dessit, s.desabr, s.sitcmp, s.tipsit, s.sitdju, s.lethor, s.desrai, s.tposer, s.decter, s.dscfer, s.perfer, s.pervtr, s.perdih, s.perdim, s.perdsr, s.perfed, s.ignmar, s.connot, s.connpr, s.sitexc, s.peradt, s.bloace, s.excbnf, s.blobnf, s.incabs, s.incoci, s.perlic, s.perasd, s.perplr, s.exmret, s.afaqua, s.carqua, s.gerest, s.diaest, s.codetb, s.pertra, s.nummax, s.perapo, s.prrbhr, s.motobr, s.conpro, s.pergra, s.sitsij, s.codsin, s.peraco, s.ficreg, s.diablq, s.dsrpro, s.msitwf, s.sitdiu, s.corsit, s.arrnot, s.partre, s.descts, s.prosin, s.usu_sithre, s.conabs, s.obrane
20. 0.000 0.000 ↓ 0.0 0 12,469

Foreign Scan on public.fdw_senior_r108ori o (cost=25.00..42.00 rows=17 width=518) (actual time=0.000..0.000 rows=0 loops=12,469)

  • Output: o.oriexa, o.desori, o.tipori
21. 236.911 236.911 ↑ 8,819.0 1 12,469

Foreign Scan on public.fdw_senior_r108tat t (cost=25.00..8,844.00 rows=8,819 width=1,038) (actual time=0.000..0.019 rows=1 loops=12,469)

  • Output: t.codate, t.oriate, t.numemp, t.tipcol, t.numcad, t.nomate, t.areate, t.regcon, t.codprf, t.numtel, t.codusu, t.codpos, t.nombo1, t.nombo2, t.nombo3, t.nombo4, t.modbo1, t.modbo2, t.modbo3, t.modbo4, t.btnpro, t.btnppr, t.btnant, t.btnrec, t.btnaso, t.btnate, t.btnsol, t.btnres, t.btnque, t.btnenc, t.btnaud, t.btnaau, t.btnmed, t.btnexa, t.btnvac, t.btnaaf, t.cfgpcl, t.qtdzom, t.reaate, t.datagd, t.qtdmes, t.indpar, t.indsai, t.abrfil, t.abrtip, t.abrate, t.trfagd, t.conaso, t.endrua, t.endnum, t.endcpl, t.codcid, t.codbai, t.endcep, t.telcon, t.cplcon, t.sitreg, t.titate, t.reaexa, t.infsit, t.fecque, t.nombo5, t.nombo6, t.modbo5, t.modbo6, t.codrg1, t.codrg2, t.codrg3, t.codrg4, t.codrg5, t.codrg6, t.codoco, t.usaano, t.conate, t.aprcon, t.ultagd, t.botas1, t.botas2, t.mboas1, t.mboas2, t.rboas1, t.rboas2, t.botag1, t.botag2, t.mboag1, t.mboag2, t.rboag1, t.rboag2, t.reapos, t.aprexa, t.aprenc, t.aprmed, t.aprpro, t.botpa1, t.botpa2, t.mbopa1, t.mbopa2, t.rbopa1, t.rbopa2, t.indpos, t.btncat, t.aprcid, t.atuage, t.usapro, t.oriexa, t.sitexa, t.usorec, t.dddtel, t.dditel, t.dddcon, t.ddicon, t.concid, t.nsgage, t.tabpri, t.limagd, t.guiamb, t.gcbhpm, t.grecei, t.gimage, t.numcpf, t.pisate
22. 0.021 0.743 ↑ 1.0 49 1

Hash (cost=74.00..74.00 rows=49 width=1,034) (actual time=0.743..0.743 rows=49 loops=1)

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

Foreign Scan on public.fdw_senior_r022prf p (cost=25.00..74.00 rows=49 width=1,034) (actual time=0.644..0.722 rows=49 loops=1)

  • Output: p.desprf, p.sigprf, p.codprf
24. 12.469 785.547 ↑ 1.0 1 12,469

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

  • Output: (1), p_1.mes
25. 74.814 773.078 ↑ 4.0 1 12,469

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

  • Output: (1), p_1.mes
  • Sort Key: (1)
  • Sort Method: top-N heapsort Memory: 25kB
26. 37.407 698.264 ↑ 1.3 3 12,469

HashAggregate (cost=14.85..14.89 rows=4 width=4) (actual time=0.056..0.056 rows=3 loops=12,469)

  • Output: (1), p_1.mes
  • Group Key: (1), p_1.mes
27. 37.407 660.857 ↑ 1.3 3 12,469

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

28. 53.672 249.380 ↓ 0.0 0 12,469

Nested Loop (cost=0.15..9.32 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=12,469)

  • Output: 1, p_1.mes
29. 149.628 149.628 ↓ 2.0 2 12,469

Seq Scan on gazin.aso_regra_idade i (cost=0.00..1.14 rows=1 width=4) (actual time=0.009..0.012 rows=2 loops=12,469)

  • 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
30. 46.080 46.080 ↓ 0.0 0 23,040

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=23,040)

  • 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))
31. 30.015 49.876 ↓ 0.0 0 12,469

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

  • Output: 2, fl.mes
  • Join Filter: (fl.idaso_regra_idade = i_1.idaso_regra_idade)
  • Rows Removed by Join Filter: 0
32. 12.469 12.469 ↓ 0.0 0 12,469

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=12,469)

  • 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
33. 7.392 7.392 ↓ 2.0 2 924

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=924)

  • 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
34. 60.789 224.442 ↑ 1.0 1 12,469

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

  • Output: 3, e.mes
  • Join Filter: (e.idaso_regra_idade = i_2.idaso_regra_idade)
  • Rows Removed by Join Filter: 1
35. 12.469 12.469 ↓ 2.0 2 12,469

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=12,469)

  • Output: e.idaso_regra_idade, e.idempresa, e.mes
  • Filter: (f.numemp = e.idempresa)
  • Rows Removed by Filter: 0
36. 151.184 151.184 ↓ 2.0 2 18,898

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=18,898)

  • 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
37. 99.752 99.752 ↓ 2.0 2 12,469

Seq Scan on gazin.aso_regra_idade i_3 (cost=0.00..1.14 rows=1 width=4) (actual time=0.006..0.008 rows=2 loops=12,469)

  • 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