explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BRLV

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 0.948 ↑ 66.7 15 1

Limit (cost=35.59..69,108.96 rows=1,000 width=1,441) (actual time=0.126..0.948 rows=15 loops=1)

  • Output: it.cdintimacao, "*SELECT* 1".nmestado, "*SELECT* 1".nmcidade, it.dtdisponibilizacao, it.dtjornal, it.detexto, it.deoaborigem, it.fltiposituacao, ((SubPlan 1)), it.deidentificador, it.fltermoexcecao, it.flduplicada, it.cdprocesso, it.flativo, pf.deurlpublicafoto, p.nmpessoa, ((array_to_json(array_agg(ARRAY[(rur.cdusuario)::text, (p_1.cdpessoa)::text, (p_1.nmpessoa)::text, (pf_1.deurlpublicafoto)::text]) FILTER (WHERE (ARRAY[(rur.cdusuario)::text, (p_1.cdpessoa)::text, (p_1.nmpessoa)::te...
  • Buffers: shared hit=470
2. 0.033 0.940 ↑ 13,372.2 15 1

Nested Loop Left Join (cost=35.59..13,854,979.45 rows=200,583 width=1,441) (actual time=0.125..0.940 rows=15 loops=1)

  • Output: it.cdintimacao, "*SELECT* 1".nmestado, "*SELECT* 1".nmcidade, it.dtdisponibilizacao, it.dtjornal, it.detexto, it.deoaborigem, it.fltiposituacao, (SubPlan 1), it.deidentificador, it.fltermoexcecao, it.flduplicada, it.cdprocesso, it.flativo, pf.deurlpublicafoto, p.nmpessoa, ((array_to_json(array_agg(ARRAY[(rur.cdusuario)::text, (p_1.cdpessoa)::text, (p_1.nmpessoa)::text, (pf_1.deurlpublicafoto)::text]) FILTER (WHERE (ARRAY[(rur.cdusuario)::text, (p_1.cdpessoa)::text, (p_1.nmpessoa)...
  • Buffers: shared hit=470
3. 0.014 0.532 ↑ 13,372.2 15 1

Nested Loop Left Join (cost=10.30..7,109,256.88 rows=200,583 width=904) (actual time=0.073..0.532 rows=15 loops=1)

  • Output: it.cdintimacao, it.dtdisponibilizacao, it.dtjornal, it.detexto, it.deoaborigem, it.fltiposituacao, it.cdprocessoorgao, it.deidentificador, it.fltermoexcecao, it.flduplicada, it.cdprocesso, it.flativo, it.cdarrendatario, "*SELECT* 1".nmestado, "*SELECT* 1".nmcidade, pf.deurlpublicafoto, p.nmpessoa
  • Buffers: shared hit=230
4. 0.027 0.248 ↑ 13,372.2 15 1

Nested Loop Left Join (cost=8.87..2,626,977.54 rows=200,583 width=777) (actual time=0.041..0.248 rows=15 loops=1)

  • Output: it.cdintimacao, it.dtdisponibilizacao, it.dtjornal, it.detexto, it.deoaborigem, it.fltiposituacao, it.cdprocessoorgao, it.deidentificador, it.fltermoexcecao, it.flduplicada, it.cdprocesso, it.flativo, it.cdintimacaoassinantenovo, it.cdarrendatario, "*SELECT* 1".nmestado, "*SELECT* 1".nmcidade
  • Buffers: shared hit=35
5. 0.041 0.041 ↑ 13,372.2 15 1

Index Scan using idx20190715_intimacao_search on saj.intimacao it (cost=0.56..680,871.13 rows=200,583 width=753) (actual time=0.017..0.041 rows=15 loops=1)

  • Output: it.cdintimacao, it.dtdisponibilizacao, it.dtjornal, it.detexto, it.deoaborigem, it.fltiposituacao, it.cdprocessoorgao, it.deidentificador, it.fltermoexcecao, it.flduplicada, it.cdprocesso, it.flativo, it.cdcidade, it.deuf, it.cdintimacaoassinantenovo, it.cdarrendatario
  • Index Cond: (it.cdarrendatario = '23537'::numeric)
  • Buffers: shared hit=19
6. 0.060 0.180 ↑ 1.0 1 15

Limit (cost=8.31..9.68 rows=1 width=52) (actual time=0.009..0.012 rows=1 loops=15)

  • Output: "*SELECT* 1".nmcidade, "*SELECT* 1".nmestado, NULL::numeric(19,0)
  • Buffers: shared hit=16
7. 0.000 0.120 ↑ 2.0 1 15

Result (cost=8.31..11.05 rows=2 width=52) (actual time=0.008..0.008 rows=1 loops=15)

  • Output: "*SELECT* 1".nmcidade, "*SELECT* 1".nmestado, NULL::numeric(19,0)
  • Buffers: shared hit=16
8. 0.015 0.120 ↑ 2.0 1 15

Append (cost=8.31..11.03 rows=2 width=32) (actual time=0.008..0.008 rows=1 loops=15)

  • Buffers: shared hit=16
9. 0.030 0.075 ↓ 0.0 0 15

Subquery Scan on *SELECT* 1 (cost=8.31..9.67 rows=1 width=23) (actual time=0.004..0.005 rows=0 loops=15)

  • Output: "*SELECT* 1".nmcidade, "*SELECT* 1".nmestado
  • Buffers: shared hit=1
10. 0.026 0.045 ↓ 0.0 0 15

Hash Join (cost=8.31..9.66 rows=1 width=43) (actual time=0.003..0.003 rows=0 loops=15)

  • Output: cid.nmcidade, est.nmestado, NULL::numeric(19,0)
  • Inner Unique: true
  • Hash Cond: (est.cdestado = cid.cdestado)
  • Buffers: shared hit=1
11. 0.004 0.004 ↑ 27.0 1 1

Seq Scan on saj.estado est (cost=0.00..1.27 rows=27 width=15) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: est.cdestado, est.nmestado, est.nmsigla, est.cdcidadecapital, est.cdpais
  • Buffers: shared hit=1
12. 0.000 0.015 ↓ 0.0 0 15

Hash (cost=8.30..8.30 rows=1 width=18) (actual time=0.001..0.001 rows=0 loops=15)

  • Output: cid.nmcidade, cid.cdestado
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
13. 0.015 0.015 ↓ 0.0 0 15

Index Scan using cidade_pkey on saj.cidade cid (cost=0.28..8.30 rows=1 width=18) (actual time=0.000..0.001 rows=0 loops=15)

  • Output: cid.nmcidade, cid.cdestado
  • Index Cond: (it.cdcidade = cid.cdcidade)
14. 0.000 0.030 ↑ 1.0 1 15

Subquery Scan on *SELECT* 2 (cost=0.00..1.35 rows=1 width=42) (actual time=0.002..0.002 rows=1 loops=15)

  • Output: "*SELECT* 2"."?column?", "*SELECT* 2".nmestado
  • Buffers: shared hit=15
15. 0.030 0.030 ↑ 1.0 1 15

Seq Scan on saj.estado est_1 (cost=0.00..1.34 rows=1 width=62) (actual time=0.001..0.002 rows=1 loops=15)

  • Output: NULL::character varying, est_1.nmestado, NULL::numeric(19,0)
  • Filter: ((est_1.nmsigla)::text = (it.deuf)::text)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=15
16. 0.060 0.270 ↑ 1.0 1 15

Limit (cost=1.43..22.33 rows=1 width=172) (actual time=0.015..0.018 rows=1 loops=15)

  • Output: pf.deurlpublicafoto, p.nmpessoa, NULL::numeric(19,0), NULL::numeric(19,0)
  • Buffers: shared hit=195
17. 0.015 0.210 ↑ 1.0 1 15

Nested Loop Left Join (cost=1.43..22.33 rows=1 width=172) (actual time=0.014..0.014 rows=1 loops=15)

  • Output: pf.deurlpublicafoto, p.nmpessoa, NULL::numeric(19,0), NULL::numeric(19,0)
  • Buffers: shared hit=195
18. 0.015 0.165 ↑ 1.0 1 15

Nested Loop (cost=1.14..21.96 rows=1 width=34) (actual time=0.011..0.011 rows=1 loops=15)

  • Output: p.nmpessoa, p.cdpessoa
  • Inner Unique: true
  • Buffers: shared hit=165
19. 0.015 0.105 ↑ 1.0 1 15

Nested Loop (cost=0.71..16.74 rows=1 width=6) (actual time=0.007..0.007 rows=1 loops=15)

  • Output: u.cdpessoa
  • Inner Unique: true
  • Buffers: shared hit=105
20. 0.045 0.045 ↑ 1.0 1 15

Index Scan using intimacaoassinantenovo_pkey on saj.intimacaoassinantenovo ia (cost=0.42..8.44 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=15)

  • Output: ia.cdintimacaoassinante, ia.cdarrendatario, ia.flativo, ia.flhabilitado, ia.fltipoassinante, ia.fltipoprovedor, ia.flstatus, ia.determoprincipal, ia.cdusuariovinculado, ia.dtiniciovigencia, ia.deloginprovedor, ia.desenhaprovedor, ia.cdestadoprovedor, ia.deoab, ia.cdestadooab, ia.cdusuariocriador, ia.dtinclusao, ia.cdusuarioultimaatualizacao, ia.dtultimaatualizacao, ia.dtexclusao, ia.cdintimacaoassinanteantigo, ia.flcriacaotipo, ia.flexclusaotip...
  • Index Cond: (ia.cdintimacaoassinante = it.cdintimacaoassinantenovo)
  • Buffers: shared hit=60
21. 0.045 0.045 ↑ 1.0 1 15

Index Scan using usuario_pkey on saj.usuario u (cost=0.29..8.31 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=15)

  • Output: u.cdusuario, u.flativo, u.dtinclusao, u.delogin, u.desenha, u.fltipoaut, u.cdarrendatario, u.cdusuariocriador, u.cdpessoa, u.cdunidadeorganizacional, u.flhabilitado, u.cdusuarioultimaatualizacao, u.dtultimaatualizacao, u.fltrocarsenha, u.flcriacaotipo, u.flexclusaotipo, u.dtexclusao, u.cdusuarioexclusao, u.flultimaatualizacaotipo, u.flrealizaronboard, u.flonboardconfirmado
  • Index Cond: (u.cdusuario = ia.cdusuariovinculado)
  • Buffers: shared hit=45
22. 0.045 0.045 ↑ 1.0 1 15

Index Scan using pessoa_pkey on saj.pessoa p (cost=0.43..5.22 rows=1 width=34) (actual time=0.003..0.003 rows=1 loops=15)

  • Output: p.nmpessoa, p.cdpessoa
  • Index Cond: (p.cdpessoa = u.cdpessoa)
  • Buffers: shared hit=60
23. 0.030 0.030 ↓ 0.0 0 15

Index Scan using idx_pesfoto_cdpessoa on saj.pessoafoto pf (cost=0.29..0.35 rows=2 width=116) (actual time=0.002..0.002 rows=0 loops=15)

  • Output: pf.cdpessoafoto, pf.flativo, pf.cdpessoa, pf.dtinclusao, pf.deurlpublicafoto, pf.cdarquivo, pf.cdarrendatario, pf.cdusuariocriador, pf.cdusuarioultimaatualizacao, pf.dtultimaatualizacao, pf.flcriacaotipo, pf.flexclusaotipo, pf.dtexclusao, pf.cdusuarioexclusao, pf.flultimaatualizacaotipo
  • Index Cond: (pf.cdpessoa = p.cdpessoa)
  • Filter: (pf.flativo = 'S'::bpchar)
  • Buffers: shared hit=30
24. 0.075 0.330 ↑ 1.0 1 15

Aggregate (cost=25.29..25.31 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=15)

  • Output: (array_to_json(array_agg(ARRAY[(rur.cdusuario)::text, (p_1.cdpessoa)::text, (p_1.nmpessoa)::text, (pf_1.deurlpublicafoto)::text]) FILTER (WHERE (ARRAY[(rur.cdusuario)::text, (p_1.cdpessoa)::text, (p_1.nmpessoa)::text, (pf_1.deurlpublicafoto)::text] IS NOT NULL))))::text
  • Buffers: shared hit=195
25. 0.015 0.255 ↑ 1.0 1 15

Nested Loop Left Join (cost=1.44..25.27 rows=1 width=144) (actual time=0.015..0.017 rows=1 loops=15)

  • Output: rur.cdusuario, p_1.cdpessoa, p_1.nmpessoa, pf_1.deurlpublicafoto
  • Buffers: shared hit=195
26. 0.030 0.210 ↑ 1.0 1 15

Nested Loop (cost=1.15..22.20 rows=1 width=40) (actual time=0.012..0.014 rows=1 loops=15)

  • Output: rur.cdusuario, p_1.cdpessoa, p_1.nmpessoa
  • Inner Unique: true
  • Buffers: shared hit=165
27. 0.030 0.135 ↑ 1.0 1 15

Nested Loop (cost=0.72..16.76 rows=1 width=12) (actual time=0.008..0.009 rows=1 loops=15)

  • Output: rur.cdusuario, u_1.cdpessoa
  • Inner Unique: true
  • Buffers: shared hit=105
28. 0.075 0.075 ↑ 1.0 1 15

Index Scan using idx20191205_reintimacaousuarioresponsavel_intimacao on saj.reintimacaousuarioresponsavel rur (cost=0.43..8.45 rows=1 width=6) (actual time=0.005..0.005 rows=1 loops=15)

  • Output: rur.cdintimacaousuarioresponsavel, rur.cdintimacao, rur.cdusuario, rur.cdarrendatario, rur.flativo, rur.dtinclusao, rur.dtultimaatualizacao, rur.cdusuarioultimaatualizacao, rur.cdusuariocriador, rur.flcriacaotipo, rur.flexclusaotipo, rur.dtexclusao, rur.cdusuarioexclusao, rur.flultimaatualizacaotipo
  • Index Cond: (rur.cdintimacao = it.cdintimacao)
  • Filter: (rur.cdarrendatario = it.cdarrendatario)
  • Buffers: shared hit=60
29. 0.030 0.030 ↑ 1.0 1 15

Index Scan using usuario_pkey on saj.usuario u_1 (cost=0.29..8.31 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=15)

  • Output: u_1.cdusuario, u_1.flativo, u_1.dtinclusao, u_1.delogin, u_1.desenha, u_1.fltipoaut, u_1.cdarrendatario, u_1.cdusuariocriador, u_1.cdpessoa, u_1.cdunidadeorganizacional, u_1.flhabilitado, u_1.cdusuarioultimaatualizacao, u_1.dtultimaatualizacao, u_1.fltrocarsenha, u_1.flcriacaotipo, u_1.flexclusaotipo, u_1.dtexclusao, u_1.cdusuarioexclusao, u_1.flultimaatualizacaotipo, u_1.flrealizaronboard, u_1.flonboardconfirmado
  • Index Cond: (u_1.cdusuario = rur.cdusuario)
  • Buffers: shared hit=45
30. 0.045 0.045 ↑ 1.0 1 15

Index Scan using pessoa_pkey on saj.pessoa p_1 (cost=0.43..5.44 rows=1 width=34) (actual time=0.003..0.003 rows=1 loops=15)

  • Output: p_1.cdpessoa, p_1.nmpessoa
  • Index Cond: (p_1.cdpessoa = u_1.cdpessoa)
  • Buffers: shared hit=60
31. 0.030 0.030 ↓ 0.0 0 15

Index Scan using idx_pesfoto_cdpessoa on saj.pessoafoto pf_1 (cost=0.29..3.05 rows=2 width=116) (actual time=0.002..0.002 rows=0 loops=15)

  • Output: pf_1.cdpessoafoto, pf_1.flativo, pf_1.cdpessoa, pf_1.dtinclusao, pf_1.deurlpublicafoto, pf_1.cdarquivo, pf_1.cdarrendatario, pf_1.cdusuariocriador, pf_1.cdusuarioultimaatualizacao, pf_1.dtultimaatualizacao, pf_1.flcriacaotipo, pf_1.flexclusaotipo, pf_1.dtexclusao, pf_1.cdusuarioexclusao, pf_1.flultimaatualizacaotipo
  • Index Cond: (pf_1.cdpessoa = p_1.cdpessoa)
  • Filter: (pf_1.flativo = 'S'::bpchar)
  • Buffers: shared hit=30
32.          

SubPlan (for Nested Loop Left Join)

33. 0.045 0.045 ↑ 1.0 1 15

Index Scan using processoorgao_pkey on saj.processoorgao org (cost=0.29..8.30 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=15)

  • Output: org.nmorgao
  • Index Cond: (org.cdprocessoorgao = it.cdprocessoorgao)
  • Buffers: shared hit=45
Planning time : 2.571 ms
Execution time : 1.096 ms