explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XQ4K

Settings
# exclusive inclusive rows x rows loops node
1. 11.992 23,552.373 ↓ 61.0 1,281 1

Subquery Scan on t (cost=2,482.68..2,767.49 rows=21 width=120) (actual time=452.343..23,552.373 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=1768779
2. 47.488 475.976 ↓ 61.0 1,281 1

Group (cost=2,482.68..2,493.65 rows=21 width=116) (actual time=418.693..475.976 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=30462
3. 68.359 428.488 ↓ 641.0 13,461 1

Sort (cost=2,482.68..2,482.73 rows=21 width=112) (actual time=418.443..428.488 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=30462
4. 39.152 360.129 ↓ 641.0 13,461 1

Nested Loop (cost=44.32..2,482.22 rows=21 width=112) (actual time=3.403..360.129 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=30462
5. 7.426 153.676 ↓ 641.0 641 1

Nested Loop Semi Join (cost=0.00..2,406.71 rows=1 width=12) (actual time=3.237..153.676 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=2899
6. 42.489 42.489 ↓ 1,647.0 1,647 1

Seq Scan on saj.efmpconjunto p (cost=0.00..2,376.44 rows=1 width=16) (actual time=2.881..42.489 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. 9.882 103.761 ↑ 50.0 2 1,647

Result (cost=0.00..28.02 rows=100 width=4) (actual time=0.057..0.063 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=1647
8. 90.585 93.879 ↑ 50.0 2 1,647

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

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

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

10. 32.050 167.301 ↑ 1.0 21 641

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

  • Buffers: shared hit=27563
11. 20.512 112.175 ↑ 1.0 15 641

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

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

Bitmap Heap Scan on saj.esajassunto a (cost=44.32..74.97 rows=15 width=96) (actual time=0.068..0.143 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. 35.896 35.896 ↑ 1.0 15 641

Bitmap Index Scan on idx_esajassunto_cdassuntoext (cost=0.00..44.31 rows=15 width=0) (actual time=0.056..0.056 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. 2.564 3.846 ↑ 1.0 1 641

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

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

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

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

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

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

Result (cost=0.00..0.01 rows=1 width=96) (actual time=0.001..0.002 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. 2.564 3.846 ↑ 1.0 1 641

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

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

Result (cost=0.00..0.01 rows=1 width=96) (actual time=0.001..0.002 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. 2.564 3.846 ↑ 1.0 1 641

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

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

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

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

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

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

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

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

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

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

Result (cost=0.00..0.01 rows=1 width=96) (actual time=0.001..0.002 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. 21.777 23,064.405 ↑ 1.0 1 1,281

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

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

Function Scan on saj.fnresolucao195baseassunto (cost=0.50..10.50 rows=1,000 width=32) (actual time=17.987..17.988 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 : 1.978 ms
Execution time : 23,553.784 ms