explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z3nG : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #DUSv; plan #ifSL; plan #TNu7; plan #j38u; plan #XrW7; plan #NjvE; plan #dYZ4; plan #VRJ9; plan #edib

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 39.104 4,477.799 ↑ 100.0 2 1

HashAggregate (cost=394,189.44..394,195.94 rows=200 width=68) (actual time=4,477.796..4,477.799 rows=2 loops=1)

  • Group Key: t.tipo
2.          

CTE pessoa

3. 0.017 0.017 ↑ 1.0 1 1

Index Scan using ix_documento on pessoas p (cost=0.29..2.51 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: ((documento)::text = '13178904000153'::text)
4.          

CTE pessoas_id

5. 0.066 0.307 ↑ 2.5 81 1

Recursive Union (cost=2.57..435.10 rows=202 width=16) (actual time=0.083..0.307 rows=81 loops=1)

6. 0.000 0.081 ↑ 1.0 2 1

Unique (cost=2.57..2.58 rows=2 width=16) (actual time=0.081..0.081 rows=2 loops=1)

7. 0.054 0.081 ↑ 1.0 2 1

Sort (cost=2.57..2.57 rows=2 width=16) (actual time=0.080..0.081 rows=2 loops=1)

  • Sort Key: pessoa_1.id
  • Sort Method: quicksort Memory: 25kB
8. 0.002 0.027 ↑ 1.0 2 1

Append (cost=0.00..2.56 rows=2 width=16) (actual time=0.018..0.027 rows=2 loops=1)

9. 0.018 0.018 ↑ 1.0 1 1

CTE Scan on pessoa pessoa_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.018..0.018 rows=1 loops=1)

10. 0.007 0.007 ↑ 1.0 1 1

Index Scan using ix_grupo_economico_pessoa_id on grupo_economico ge (cost=0.30..2.52 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (pessoa_id = $2)
  • Filter: (situacao_registro = 1)
11.          

Initplan (forIndex Scan)

12. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on pessoa (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)

13. 0.000 0.160 ↑ 20.0 1 80

Nested Loop (cost=0.28..42.85 rows=20 width=16) (actual time=0.002..0.002 rows=1 loops=80)

14. 0.000 0.000 ↑ 20.0 1 80

WorkTable Scan on pessoas_id pid (cost=0.00..0.40 rows=20 width=16) (actual time=0.000..0.000 rows=1 loops=80)

15. 0.162 0.162 ↑ 1.0 1 81

Index Scan using ix_grupo_economico_pessoa_participante_grupo_id on grupo_economico ge_1 (cost=0.28..2.11 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=81)

  • Index Cond: (pessoa_participante_grupo_id = pid.id)
  • Filter: (situacao_registro = 1)
16.          

CTE pessoas_participantes_grupo_id

17. 0.339 2.300 ↑ 10.3 2,000 1

Recursive Union (cost=9.10..2,224.03 rows=20,503 width=16) (actual time=0.038..2.300 rows=2,000 loops=1)

18. 0.027 0.041 ↑ 2.5 81 1

HashAggregate (cost=9.10..11.13 rows=203 width=16) (actual time=0.033..0.041 rows=81 loops=1)

  • Group Key: pessoas_id.id
19. 0.001 0.014 ↑ 2.5 82 1

Append (cost=0.00..8.59 rows=203 width=16) (actual time=0.001..0.014 rows=82 loops=1)

20. 0.007 0.007 ↑ 2.5 81 1

CTE Scan on pessoas_id (cost=0.00..4.04 rows=202 width=16) (actual time=0.001..0.007 rows=81 loops=1)

21. 0.006 0.006 ↑ 1.0 1 1

Index Scan using ix_grupo_economico_pessoa_participante_grupo_id on grupo_economico ge_2 (cost=0.30..2.52 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (pessoa_participante_grupo_id = $6)
  • Filter: (situacao_registro = 1)
22.          

Initplan (forIndex Scan)

23. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on pessoa pessoa_2 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)

24. 1.336 1.920 ↑ 2,030.0 1 1,920

Hash Join (cost=111.97..180.49 rows=2,030 width=16) (actual time=0.001..0.001 rows=1 loops=1,920)

  • Hash Cond: (ppgid.id = ge_3.pessoa_id)
25. 0.000 0.000 ↑ 2,030.0 1 1,920

WorkTable Scan on pessoas_participantes_grupo_id ppgid (cost=0.00..40.60 rows=2,030 width=16) (actual time=0.000..0.000 rows=1 loops=1,920)

26. 0.264 0.584 ↑ 1.0 1,999 1

Hash (cost=86.99..86.99 rows=1,999 width=32) (actual time=0.584..0.584 rows=1,999 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 141kB
27. 0.320 0.320 ↑ 1.0 1,999 1

Seq Scan on grupo_economico ge_3 (cost=0.00..86.99 rows=1,999 width=32) (actual time=0.009..0.320 rows=1,999 loops=1)

  • Filter: (situacao_registro = 1)
28.          

CTE documentos

29. 1.015 13.088 ↑ 10.4 2,000 1

HashAggregate (cost=2,610.52..2,817.58 rows=20,706 width=46) (actual time=12.696..13.088 rows=2,000 loops=1)

  • Group Key: p_1.documento
30. 0.087 12.073 ↑ 10.3 2,001 1

Append (cost=1,345.83..2,558.75 rows=20,706 width=46) (actual time=4.111..12.073 rows=2,001 loops=1)

31. 4.745 11.974 ↑ 10.4 2,000 1

Hash Join (cost=1,345.83..2,349.16 rows=20,705 width=14) (actual time=4.110..11.974 rows=2,000 loops=1)

  • Hash Cond: (p_1.id = pessoas_id_1.id)
32. 3.242 3.242 ↑ 1.0 45,729 1

Seq Scan on pessoas p_1 (cost=0.00..883.29 rows=45,729 width=30) (actual time=0.013..3.242 rows=45,729 loops=1)

33. 0.238 3.987 ↑ 10.4 2,000 1

Hash (cost=1,087.01..1,087.01 rows=20,705 width=16) (actual time=3.987..3.987 rows=2,000 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 350kB
34. 0.726 3.749 ↑ 10.4 2,000 1

HashAggregate (cost=672.91..879.96 rows=20,705 width=16) (actual time=3.489..3.749 rows=2,000 loops=1)

  • Group Key: pessoas_id_1.id
35. 0.078 3.023 ↑ 9.9 2,081 1

Append (cost=0.00..621.15 rows=20,705 width=16) (actual time=0.083..3.023 rows=2,081 loops=1)

36. 0.325 0.325 ↑ 2.5 81 1

CTE Scan on pessoas_id pessoas_id_1 (cost=0.00..4.04 rows=202 width=16) (actual time=0.083..0.325 rows=81 loops=1)

37. 2.620 2.620 ↑ 10.3 2,000 1

CTE Scan on pessoas_participantes_grupo_id (cost=0.00..410.06 rows=20,503 width=16) (actual time=0.039..2.620 rows=2,000 loops=1)

38. 0.010 0.012 ↑ 1.0 1 1

Index Scan using pk_pessoas on pessoas p_2 (cost=0.31..2.53 rows=1 width=14) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (id = $8)
39.          

Initplan (forIndex Scan)

40. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on pessoa pessoa_3 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=1)

41.          

CTE data_atual

42. 0.004 0.004 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1)

43.          

CTE parametros

44. 0.000 0.070 ↑ 1.0 5 1

Hash Join (cost=3.18..4.27 rows=5 width=44) (actual time=0.068..0.070 rows=5 loops=1)

  • Hash Cond: (ps.parametrizacao_id = p_3.id)
45.          

Initplan (forHash Join)

46. 0.002 0.020 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.07 rows=1 width=16) (actual time=0.019..0.020 rows=1 loops=1)

  • Join Filter: (a.id = u.associado_id)
  • Rows Removed by Join Filter: 1
47. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on usuarios u (cost=0.00..1.02 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)

  • Filter: (id = '1be4fde8-6f56-41cb-a6e8-58770b175555'::uuid)
  • Rows Removed by Filter: 1
48. 0.010 0.010 ↑ 1.0 2 1

Seq Scan on associados a (cost=0.00..1.02 rows=2 width=32) (actual time=0.010..0.010 rows=2 loops=1)

49. 0.035 0.035 ↑ 1.0 5 1

Seq Scan on parametrizacao_segmento ps (cost=0.00..1.06 rows=5 width=18) (actual time=0.035..0.035 rows=5 loops=1)

  • Filter: (segmento_id = $11)
50. 0.015 0.023 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=58) (actual time=0.023..0.023 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.008 0.008 ↑ 1.0 5 1

Seq Scan on parametrizacao p_3 (cost=0.00..1.05 rows=5 width=58) (actual time=0.008..0.008 rows=5 loops=1)

52.          

CTE pool_titulos

53. 542.246 4,329.716 ↑ 3.9 209,519 1

Hash Join (cost=2,412.86..349,602.06 rows=823,239 width=20) (actual time=27.737..4,329.716 rows=209,519 loops=1)

  • Hash Cond: (t_1.pessoa_id = p_6.id)
54.          

Initplan (forHash Join)

55. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on data_atual (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

56. 0.079 0.079 ↑ 1.0 1 1

CTE Scan on parametros p_4 (cost=0.00..0.12 rows=1 width=16) (actual time=0.077..0.079 rows=1 loops=1)

  • Filter: ((nome)::text = 'Dias de validade da informação de títulos'::text)
  • Rows Removed by Filter: 4
57. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on data_atual data_atual_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

58. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on parametros p_5 (cost=0.00..0.12 rows=1 width=16) (actual time=0.001..0.003 rows=1 loops=1)

  • Filter: ((nome)::text = 'Dias após vencimento para considerar o título vencido'::text)
  • Rows Removed by Filter: 4
59. 0.006 0.006 ↑ 1.0 1 1

CTE Scan on data_atual data_atual_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)

60. 3,760.385 3,760.385 ↓ 3.1 5,598,019 1

Seq Scan on titulos t_1 (cost=0.00..332,138.88 rows=1,818,116 width=36) (actual time=0.068..3,760.385 rows=5,598,019 loops=1)

  • Filter: (((saldo > '0'::numeric) AND (data_atualizacao >= ($13 - $14)) AND (data_vencimento < ($15 - $16))) OR ((saldo = '0'::numeric) AND (data_emissao > ($17 - '1 year'::interval)) AND (situacao = ANY ('{1,3,4}'::integer[])) AND (situacao_registro = 1)))
  • Rows Removed by Filter: 98695
61. 0.421 26.996 ↑ 10.4 2,000 1

Hash (cost=2,153.73..2,153.73 rows=20,706 width=16) (actual time=26.996..26.996 rows=2,000 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 350kB
62. 1.123 26.575 ↑ 10.4 2,000 1

Hash Join (cost=1,454.90..2,153.73 rows=20,706 width=16) (actual time=24.889..26.575 rows=2,000 loops=1)

  • Hash Cond: ((documentos.documento)::text = (p_6.documento)::text)
63. 13.455 13.455 ↑ 10.4 2,000 1

CTE Scan on documentos (cost=0.00..414.12 rows=20,706 width=46) (actual time=12.697..13.455 rows=2,000 loops=1)

64. 7.500 11.997 ↑ 1.0 45,729 1

Hash (cost=883.29..883.29 rows=45,729 width=30) (actual time=11.997..11.997 rows=45,729 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3322kB
65. 4.497 4.497 ↑ 1.0 45,729 1

Seq Scan on pessoas p_6 (cost=0.00..883.29 rows=45,729 width=30) (actual time=0.021..4.497 rows=45,729 loops=1)

66.          

CTE total

67. 10.973 34.233 ↑ 1.0 1 1

Aggregate (cost=18,522.88..18,522.89 rows=1 width=8) (actual time=34.233..34.233 rows=1 loops=1)

68. 23.260 23.260 ↑ 3.9 209,519 1

CTE Scan on pool_titulos (cost=0.00..16,464.78 rows=823,239 width=0) (actual time=0.050..23.260 rows=209,519 loops=1)

69.          

Initplan (forHashAggregate)

70. 34.235 34.235 ↑ 1.0 1 1

CTE Scan on total (cost=0.00..0.02 rows=1 width=8) (actual time=34.235..34.235 rows=1 loops=1)

71. 4,404.460 4,404.460 ↑ 3.9 209,519 1

CTE Scan on pool_titulos t (cost=0.00..16,464.78 rows=823,239 width=4) (actual time=27.739..4,404.460 rows=209,519 loops=1)