explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 82ro

Settings
# exclusive inclusive rows x rows loops node
1. 11.392 23,148.864 ↓ 61.0 1,281 1

Subquery Scan on t (cost=2,482.68..2,767.49 rows=21 width=120) (actual time=225.510..23,148.864 rows=1,281 loops=1)

  • Output: t.cdassuntoext, t.deassunto, t.cdassuntounif, (date_part('year'::text, (t.first_day)::timestamp without time zone))::bigint, (date_part('month'::text, (t.first_day)::timestamp without time zone))::bigint, (SubPlan 1)
  • Buffers: shared hit=1768849 read=2
2. 46.331 262.655 ↓ 61.0 1,281 1

Group (cost=2,482.68..2,493.65 rows=21 width=116) (actual time=207.291..262.655 rows=1,281 loops=1)

  • Output: fnfirst_day(((p.dtmovimento)::date)), fnlast_day(((p.dtmovimento)::date)), p.cdlocal, "*SELECT* 1".cdassuntoext, "*SELECT* 1".deassunto, "*SELECT* 1".cdassuntounif, ((p.dtmovimento)::date), p.cdlocal
  • Group Key: ((p.dtmovimento)::date), p.cdlocal, "*SELECT* 1".cdassuntoext, "*SELECT* 1".deassunto, "*SELECT* 1".cdassuntounif
  • Buffers: shared hit=30532 read=2
3. 39.241 216.324 ↓ 641.0 13,461 1

Sort (cost=2,482.68..2,482.73 rows=21 width=112) (actual time=207.100..216.324 rows=13,461 loops=1)

  • Output: "*SELECT* 1".cdassuntoext, "*SELECT* 1".deassunto, "*SELECT* 1".cdassuntounif, ((p.dtmovimento)::date), p.cdlocal, p.dtmovimento
  • Sort Key: ((p.dtmovimento)::date), p.cdlocal, "*SELECT* 1".cdassuntoext, "*SELECT* 1".deassunto, "*SELECT* 1".cdassuntounif
  • Sort Method: quicksort Memory: 2318kB
  • Buffers: shared hit=30532 read=2
4. 18.796 177.083 ↓ 641.0 13,461 1

Nested Loop (cost=44.32..2,482.22 rows=21 width=112) (actual time=2.399..177.083 rows=13,461 loops=1)

  • Output: "*SELECT* 1".cdassuntoext, "*SELECT* 1".deassunto, "*SELECT* 1".cdassuntounif, (p.dtmovimento)::date, p.cdlocal, p.dtmovimento
  • Buffers: shared hit=30523 read=2
5. 3.828 76.880 ↓ 641.0 641 1

Nested Loop Semi Join (cost=0.00..2,406.71 rows=1 width=12) (actual time=2.272..76.880 rows=641 loops=1)

  • Output: p.dtmovimento, p.cdlocal
  • Join Filter: (p.cdtipoprocesso = (((unnest(string_to_array(replace((fngetvaluejsonparam('51524'::numeric, 'AnexoIIC'::character varying))::text, ' '::text, ''::text), ','::text))))::integer))
  • Rows Removed by Join Filter: 2206
  • Buffers: shared hit=2960 read=2
6. 26.936 26.936 ↓ 1,647.0 1,647 1

Seq Scan on saj.efmpconjunto p (cost=0.00..2,376.44 rows=1 width=16) (actual time=1.347..26.936 rows=1,647 loops=1)

  • Output: p.conjuntoid, p.cdclasseprocesso, p.cdassuntoprinc, p.cdassuntoext, p.cdtipolocal, p.detipolocal, p.cdlocal, p.delocal, p.cdprocesso, p.nuprocesso, p.cdtipomvprocesso, p.nuseqprocessomv, p.dtmovimento, p.cdtipoprocesso, p.cdtipomvext
  • Filter: ((date_part('year'::text, p.dtmovimento) = '2019'::double precision) AND (date_part('month'::text, p.dtmovimento) = '1'::double precision))
  • Rows Removed by Filter: 54575
  • Buffers: shared hit=1252
7. 4.941 46.116 ↑ 50.0 2 1,647

Result (cost=0.00..28.02 rows=100 width=4) (actual time=0.025..0.028 rows=2 loops=1,647)

  • Output: ((unnest(string_to_array(replace((fngetvaluejsonparam('51524'::numeric, 'AnexoIIC'::character varying))::text, ' '::text, ''::text), ','::text))))::integer
  • Buffers: shared hit=1708 read=2
8. 39.528 41.175 ↑ 50.0 2 1,647

ProjectSet (cost=0.00..0.77 rows=100 width=32) (actual time=0.024..0.025 rows=2 loops=1,647)

  • Output: unnest(string_to_array(replace((fngetvaluejsonparam('51524'::numeric, 'AnexoIIC'::character varying))::text, ' '::text, ''::text), ','::text))
  • Buffers: shared hit=1708 read=2
9. 1.647 1.647 ↑ 1.0 1 1,647

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1,647)

10. 16.025 81.407 ↑ 1.0 21 641

Append (cost=44.32..75.24 rows=21 width=96) (actual time=0.035..0.127 rows=21 loops=641)

  • Buffers: shared hit=27563
11. 10.256 53.844 ↑ 1.0 15 641

Subquery Scan on *SELECT* 1 (cost=44.32..75.12 rows=15 width=96) (actual time=0.034..0.084 rows=15 loops=641)

  • Output: "*SELECT* 1".cdassuntoext, "*SELECT* 1".deassunto, "*SELECT* 1".cdassuntounif
  • Buffers: shared hit=27563
12. 26.281 43.588 ↑ 1.0 15 641

Bitmap Heap Scan on saj.esajassunto a (cost=44.32..74.97 rows=15 width=96) (actual time=0.033..0.068 rows=15 loops=641)

  • Output: (a.cdassuntoext)::character varying, (a.cdassuntounif)::character varying, initcap((a.deassunto)::text)
  • Recheck Cond: (a.cdassuntoext = ANY ('{12218,3463,3400,3618,3415,11410,3631,3632,3633,3613,12221,12219,12220,930421,10949}'::numeric[]))
  • Heap Blocks: exact=7692
  • Buffers: shared hit=27563
13. 17.307 17.307 ↑ 1.0 15 641

Bitmap Index Scan on idx_esajassunto_cdassuntoext (cost=0.00..44.31 rows=15 width=0) (actual time=0.027..0.027 rows=15 loops=641)

  • Index Cond: (a.cdassuntoext = ANY ('{12218,3463,3400,3618,3415,11410,3631,3632,3633,3613,12221,12219,12220,930421,10949}'::numeric[]))
  • Buffers: shared hit=19871
14. 1.282 1.923 ↑ 1.0 1 641

Subquery Scan on *SELECT* 2 (cost=0.00..0.02 rows=1 width=96) (actual time=0.002..0.003 rows=1 loops=641)

  • Output: "*SELECT* 2"."?column?", "*SELECT* 2"."?column?_2", "*SELECT* 2"."?column?_1
15. 0.641 0.641 ↑ 1.0 1 641

Result (cost=0.00..0.01 rows=1 width=96) (actual time=0.001..0.001 rows=1 loops=641)

  • Output: '5873, 5872, 3547'::character varying, '5873), (5872), (3547'::character varying, 'Crimes contra a Administra��o P�blica'::text
16. 1.282 1.923 ↑ 1.0 1 641

Subquery Scan on *SELECT* 3 (cost=0.00..0.02 rows=1 width=96) (actual time=0.002..0.003 rows=1 loops=641)

  • Output: "*SELECT* 3"."?column?", "*SELECT* 3"."?column?_2", "*SELECT* 3"."?column?_1
17. 0.641 0.641 ↑ 1.0 1 641

Result (cost=0.00..0.01 rows=1 width=96) (actual time=0.001..0.001 rows=1 loops=641)

  • Output: '10984, 10983, 10982, 3628'::character varying, '10984), (10983), (10982), (3628'::character varying, 'Crimes de "Lavagem" ou Oculta��o de Bens, Direitos ou Valores'::text
18. 1.282 1.923 ↑ 1.0 1 641

Subquery Scan on *SELECT* 4 (cost=0.00..0.02 rows=1 width=96) (actual time=0.002..0.003 rows=1 loops=641)

  • Output: "*SELECT* 4"."?column?", "*SELECT* 4"."?column?_2", "*SELECT* 4"."?column?_1
19. 0.641 0.641 ↑ 1.0 1 641

Result (cost=0.00..0.01 rows=1 width=96) (actual time=0.001..0.001 rows=1 loops=641)

  • Output: '5897, 3608'::character varying, '5897), (3608'::character varying, 'Crimes de Tr�fico Il�cito e Associa��o para a Produ��o e Tr�fico'::text
20. 1.282 1.923 ↑ 1.0 1 641

Subquery Scan on *SELECT* 5 (cost=0.00..0.02 rows=1 width=96) (actual time=0.002..0.003 rows=1 loops=641)

  • Output: "*SELECT* 5"."?column?", "*SELECT* 5"."?column?_2", "*SELECT* 5"."?column?_1
21. 0.641 0.641 ↑ 1.0 1 641

Result (cost=0.00..0.01 rows=1 width=96) (actual time=0.001..0.001 rows=1 loops=641)

  • Output: '11429, 3654'::character varying, '11429), (3654'::character varying, 'Crimes Eleitorais'::text
22. 1.282 1.923 ↑ 1.0 1 641

Subquery Scan on *SELECT* 6 (cost=0.00..0.02 rows=1 width=96) (actual time=0.002..0.003 rows=1 loops=641)

  • Output: "*SELECT* 6"."?column?", "*SELECT* 6"."?column?_2", "*SELECT* 6"."?column?_1
23. 0.641 0.641 ↑ 1.0 1 641

Result (cost=0.00..0.01 rows=1 width=96) (actual time=0.001..0.001 rows=1 loops=641)

  • Output: '900142, 12091'::character varying, '900142), (12091'::character varying, 'Feminic�dio'::text
24. 1.282 1.923 ↑ 1.0 1 641

Subquery Scan on *SELECT* 7 (cost=0.00..0.02 rows=1 width=96) (actual time=0.002..0.003 rows=1 loops=641)

  • Output: "*SELECT* 7"."?column?", "*SELECT* 7"."?column?_2", "*SELECT* 7"."?column?_1
25. 0.641 0.641 ↑ 1.0 1 641

Result (cost=0.00..0.01 rows=1 width=96) (actual time=0.001..0.001 rows=1 loops=641)

  • Output: '3372, 3370'::character varying, '3372), (3370'::character varying, 'Homic�dio Simples e Qualificado'::text
26.          

SubPlan (for Subquery Scan)

27. 23.058 22,874.817 ↑ 1.0 1 1,281

Aggregate (cost=13.00..13.01 rows=1 width=8) (actual time=17.856..17.857 rows=1 loops=1,281)

  • Output: count(DISTINCT fnresolucao195baseassunto.cdprocesso)
  • Buffers: shared hit=1738317
28. 22,851.759 22,851.759 ↑ 1,000.0 1 1,281

Function Scan on saj.fnresolucao195baseassunto (cost=0.50..10.50 rows=1,000 width=32) (actual time=17.838..17.839 rows=1 loops=1,281)

  • Output: fnresolucao195baseassunto.cdprocesso
  • Function Call: fnresolucao195baseassunto(fngetvaluejsonparam('51524'::numeric, 'AnexoIIC'::character varying), (t.cdlocal)::character varying, '920246'::character varying, t.cdassuntoext, t.first_day, t.last_day, NULL::character varying, NULL::integer, NULL::character varying)
  • Buffers: shared hit=1738317
Planning time : 0.918 ms
Execution time : 23,150.252 ms