explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jqL

Settings
# exclusive inclusive rows x rows loops node
1. 6.017 24,685.118 ↓ 61.0 1,281 1

Sort (cost=2,767.95..2,768.01 rows=21 width=120) (actual time=24,684.531..24,685.118 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))
  • Sort Key: ((date_part('year'::text, (t.first_day)::timestamp without time zone))::bigint), ((date_part('month'::text, (t.first_day)::timestamp without time zone))::bigint), t.cdassuntoext, t.deassunto, t.cdassuntounif
  • Sort Method: quicksort Memory: 244kB
  • Buffers: shared hit=1768779
2. 13.103 24,679.101 ↓ 61.0 1,281 1

Subquery Scan on t (cost=2,482.68..2,767.49 rows=21 width=120) (actual time=424.001..24,679.101 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
3. 49.844 451.255 ↓ 61.0 1,281 1

Group (cost=2,482.68..2,493.65 rows=21 width=116) (actual time=391.871..451.255 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
4. 68.424 401.411 ↓ 641.0 13,461 1

Sort (cost=2,482.68..2,482.73 rows=21 width=112) (actual time=391.598..401.411 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
5. 37.093 332.987 ↓ 641.0 13,461 1

Nested Loop (cost=44.32..2,482.22 rows=21 width=112) (actual time=3.009..332.987 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
6. 8.239 137.567 ↓ 641.0 641 1

Nested Loop Semi Join (cost=0.00..2,406.71 rows=1 width=12) (actual time=2.762..137.567 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
7. 42.037 42.037 ↓ 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.412..42.037 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
8. 8.235 87.291 ↑ 50.0 2 1,647

Result (cost=0.00..28.02 rows=100 width=4) (actual time=0.048..0.053 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
9. 75.762 79.056 ↑ 50.0 2 1,647

ProjectSet (cost=0.00..0.77 rows=100 width=32) (actual time=0.045..0.048 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
10. 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)

11. 30.768 158.327 ↑ 1.0 21 641

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

  • Buffers: shared hit=27563
12. 20.512 104.483 ↑ 1.0 15 641

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

  • Output: "*SELECT* 1".cdassuntoext, "*SELECT* 1".deassunto, "*SELECT* 1".cdassuntounif
  • Buffers: shared hit=27563
13. 50.639 83.971 ↑ 1.0 15 641

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

Bitmap Index Scan on idx_esajassunto_cdassuntoext (cost=0.00..44.31 rows=15 width=0) (actual time=0.052..0.052 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
15. 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
16. 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
17. 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
18. 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
19. 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
20. 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
21. 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
22. 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
23. 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
24. 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
25. 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
26. 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
27.          

SubPlan (for Subquery Scan)

28. 21.777 24,214.743 ↑ 1.0 1 1,281

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

  • Output: count(DISTINCT fnresolucao195baseassunto.cdprocesso)
  • Buffers: shared hit=1738317
29. 24,192.966 24,192.966 ↑ 1,000.0 1 1,281

Function Scan on saj.fnresolucao195baseassunto (cost=0.50..10.50 rows=1,000 width=32) (actual time=18.885..18.886 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.255 ms
Execution time : 24,686.159 ms