explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pYmh

Settings
# exclusive inclusive rows x rows loops node
1. 24.377 301,297.650 ↑ 1.0 1 1

Aggregate (cost=160,442.02..160,442.03 rows=1 width=8) (actual time=301,297.650..301,297.650 rows=1 loops=1)

2. 349.045 301,273.273 ↓ 39,762.0 39,762 1

Nested Loop Semi Join (cost=22.76..160,442.02 rows=1 width=8) (actual time=12.635..301,273.273 rows=39,762 loops=1)

3. 93,106.878 299,197.278 ↓ 287,825.0 287,825 1

Nested Loop (cost=22.34..160,441.47 rows=1 width=12) (actual time=6.450..299,197.278 rows=287,825 loops=1)

  • Join Filter: (dp.codvara = v.cod_vara)
  • Rows Removed by Join Filter: 1333781050
4. 247.812 31,092.800 ↓ 287,825.0 287,825 1

Nested Loop (cost=22.34..160,336.15 rows=1 width=20) (actual time=6.326..31,092.800 rows=287,825 loops=1)

5. 184.988 27,391.088 ↓ 287,825.0 287,825 1

Nested Loop (cost=21.90..160,335.69 rows=1 width=36) (actual time=6.289..27,391.088 rows=287,825 loops=1)

6. 388.053 17,657.121 ↓ 31.2 289,363 1

Nested Loop (cost=21.47..151,542.05 rows=9,281 width=36) (actual time=6.146..17,657.121 rows=289,363 loops=1)

7. 113.390 10,615.720 ↓ 4.9 289,276 1

Nested Loop (cost=21.03..122,532.69 rows=58,983 width=32) (actual time=6.070..10,615.720 rows=289,276 loops=1)

8. 7.541 9.941 ↓ 1.4 1,017 1

Hash Join (cost=20.60..96.37 rows=726 width=4) (actual time=0.830..9.941 rows=1,017 loops=1)

  • Hash Cond: (vav.codareavara = av.cod_area)
9. 1.674 1.674 ↑ 1.0 4,974 1

Seq Scan on vara_area vav (cost=0.00..49.86 rows=4,974 width=8) (actual time=0.015..1.674 rows=4,974 loops=1)

10. 0.206 0.726 ↓ 1.8 925 1

Hash (cost=14.06..14.06 rows=523 width=4) (actual time=0.726..0.726 rows=925 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
11. 0.145 0.520 ↓ 1.8 925 1

Nested Loop (cost=0.56..14.06 rows=523 width=4) (actual time=0.142..0.520 rows=925 loops=1)

12. 0.093 0.093 ↓ 2.0 6 1

Index Only Scan using pk_indicador_competencia on indicador_competencia _ic (cost=0.28..0.33 rows=3 width=4) (actual time=0.092..0.093 rows=6 loops=1)

  • Index Cond: (codindicador = 78)
  • Heap Fetches: 0
13. 0.282 0.282 ↑ 1.0 154 6

Index Only Scan using idx_areadevara_tipocompetencia_codarea on areadevaras av (cost=0.28..3.01 rows=156 width=8) (actual time=0.015..0.047 rows=154 loops=6)

  • Index Cond: (tipocompetencia = _ic.tipocompetencia)
  • Heap Fetches: 0
14. 10,492.389 10,492.389 ↓ 1.8 284 1,017

Index Scan using idx618_fk_vara_distribuicao on distribuicao_processo dp (cost=0.43..167.07 rows=158 width=28) (actual time=3.995..10.317 rows=284 loops=1,017)

  • Index Cond: ((codvara = vav.codvara) AND (codvara IS NOT NULL))
  • Filter: ((datadistribuicao >= '2016-01-01 00:00:00'::timestamp without time zone) AND (datadistribuicao <= '2018-12-31 23:59:59'::timestamp without time zone) AND (tipodistribuicao = ANY ('{5,6,7,8,9}'::integer[] (...)
  • Rows Removed by Filter: 2374
15. 6,653.348 6,653.348 ↑ 1.0 1 289,276

Index Scan using idx683_fk_historico_processo_classe_processo on historico_processo_classe hpc (cost=0.43..0.48 rows=1 width=28) (actual time=0.020..0.023 rows=1 loops=289,276)

  • Index Cond: (numeroprocesso = dp.numeroprocesso)
  • Filter: ((dp.datadistribuicao >= datainicio) AND (dp.datadistribuicao <= COALESCE(datafim, '2018-12-31 23:59:59'::timestamp without time zone)))
  • Rows Removed by Filter: 1
16. 5,632.773 9,548.979 ↑ 1.0 1 289,363

Index Scan using idx620_fk_processo_distribuicao on distribuicao_processo distrant (cost=0.43..0.94 rows=1 width=20) (actual time=0.026..0.033 rows=1 loops=289,363)

  • Index Cond: (numeroprocesso = dp.numeroprocesso)
  • Filter: ((codvara IS NOT NULL) AND (dp.codvara <> codvara) AND ((SubPlan 1) = coddistribuicaoprocesso))
  • Rows Removed by Filter: 2
17.          

SubPlan (for Index Scan)

18. 559.458 3,916.206 ↑ 1.0 1 559,458

Aggregate (cost=0.47..0.48 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=559,458)

19. 3,356.748 3,356.748 ↓ 2.0 2 559,458

Index Scan using idx620_fk_processo_distribuicao on distribuicao_processo dp2 (cost=0.43..0.47 rows=1 width=8) (actual time=0.003..0.006 rows=2 loops=559,458)

  • Index Cond: (numeroprocesso = dp.numeroprocesso)
  • Filter: (coddistribuicaoprocesso < dp.coddistribuicaoprocesso)
  • Rows Removed by Filter: 2
20. 3,453.900 3,453.900 ↑ 1.0 1 287,825

Index Only Scan using processo_pkey on processo p (cost=0.43..0.45 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=287,825)

  • Index Cond: (numeroprocesso = dp.numeroprocesso)
  • Heap Fetches: 93920
21. 174,997.600 174,997.600 ↑ 1.0 4,635 287,825

Seq Scan on vara v (cost=0.00..47.38 rows=4,635 width=4) (actual time=0.002..0.608 rows=4,635 loops=287,825)

22. 287.825 1,726.950 ↓ 0.0 0 287,825

Nested Loop (cost=0.43..0.49 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=287,825)

23. 575.650 575.650 ↑ 1.0 1 287,825

Index Only Scan using pk_indicador_grupo on indicador_grupo _ig (cost=0.14..0.17 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=287,825)

  • Index Cond: (codindicador = 78)
  • Heap Fetches: 0
24. 863.475 863.475 ↓ 0.0 0 287,825

Index Only Scan using uk_grupo_classe_classe on grupo_classe_classe_processual _gccp (cost=0.28..0.31 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=287,825)

  • Index Cond: ((codgrupoclasse = _ig.codgrupoclasse) AND (codclasse = hpc.codclasseprocessual))
  • Heap Fetches: 39762
Planning time : 9.606 ms
Execution time : 301,298.009 ms