explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0HRk

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 101,042.745 ↑ 2.0 1 1

Append (cost=0.00..6,283.44 rows=2 width=736) (actual time=0.002..101,042.745 rows=1 loops=1)

2. 0.001 0.001 ↑ 1.0 1 1

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

3. 0.000 101,042.743 ↓ 0.0 0 1

Subquery Scan on x (cost=6,283.24..6,283.42 rows=1 width=736) (actual time=101,042.743..101,042.743 rows=0 loops=1)

4. 0.000 101,042.743 ↓ 0.0 0 1

Limit (cost=6,283.24..6,283.40 rows=1 width=104) (actual time=101,042.743..101,042.743 rows=0 loops=1)

5.          

Initplan (forLimit)

6. 0.012 0.012 ↑ 1.0 1 1

Index Only Scan using segmentacao_segcod on segmentacaolevel1level2 c_2 (cost=0.28..4.29 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: ((segcod = 472) AND (segfilcod = 48))
  • Heap Fetches: 1
7. 0.001 101,042.742 ↓ 0.0 0 1

GroupAggregate (cost=6,278.95..6,279.11 rows=1 width=104) (actual time=101,042.742..101,042.742 rows=0 loops=1)

  • Group Key: dev.devcod, ''::text, (upper((dev.devnom)::text))
8. 0.007 101,042.741 ↓ 0.0 0 1

Sort (cost=6,278.95..6,278.95 rows=1 width=91) (actual time=101,042.741..101,042.741 rows=0 loops=1)

  • Sort Key: dev.devcod, (upper((dev.devnom)::text))
  • Sort Method: quicksort Memory: 25kB
9. 195.033 101,042.734 ↓ 0.0 0 1

Nested Loop Semi Join (cost=21.08..6,278.94 rows=1 width=91) (actual time=101,042.734..101,042.734 rows=0 loops=1)

10. 642.317 99,265.916 ↓ 316,357.0 316,357 1

Nested Loop (cost=20.81..6,274.99 rows=1 width=52) (actual time=0.398..99,265.916 rows=316,357 loops=1)

11. 6,103.782 87,234.603 ↓ 316,361.0 316,361 1

Nested Loop (cost=2.99..6,257.14 rows=1 width=56) (actual time=0.343..87,234.603 rows=316,361 loops=1)

12. 483.870 78,916.294 ↓ 316,361.0 316,361 1

Nested Loop (cost=1.82..6,251.96 rows=1 width=54) (actual time=0.316..78,916.294 rows=316,361 loops=1)

13. 784.156 4,612.574 ↓ 27,381.2 219,050 1

Nested Loop Semi Join (cost=1.26..6,210.81 rows=8 width=43) (actual time=0.054..4,612.574 rows=219,050 loops=1)

  • Join Filter: (pes.tippescod = c_1.segfili01)
  • Rows Removed by Join Filter: 780864
14. 636.952 3,828.418 ↓ 15,623.7 999,914 1

Nested Loop (cost=0.98..6,205.55 rows=64 width=45) (actual time=0.046..3,828.418 rows=999,914 loops=1)

15. 1,004.046 1,004.046 ↓ 204.2 218,742 1

Index Scan using idevedor22 on devedor dev (cost=0.43..3,466.33 rows=1,071 width=35) (actual time=0.035..1,004.046 rows=218,742 loops=1)

  • Index Cond: ((carcod = $2) AND (devati = 0) AND (devsal > '0'::numeric))
16. 2,187.420 2,187.420 ↓ 5.0 5 218,742

Index Only Scan using pessoa_iu0002 on pessoa pes (cost=0.56..2.55 rows=1 width=22) (actual time=0.007..0.010 rows=5 loops=218,742)

  • Index Cond: ((carcod = $2) AND (devcod = dev.devcod))
  • Heap Fetches: 128702
17. 0.000 0.000 ↑ 1.0 1 999,914

Materialize (cost=0.28..4.31 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=999,914)

18. 0.000 0.005 ↑ 1.0 1 1

Limit (cost=0.28..4.29 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

19. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using segmentacao_segcod on segmentacaolevel1level2 c_1 (cost=0.28..4.29 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: ((segcod = 472) AND (segfilcod = 42))
  • Heap Fetches: 1
20. 73,819.850 73,819.850 ↑ 1.0 1 219,050

Index Scan using telefone_iu0005 on telefone tel (cost=0.56..5.13 rows=1 width=19) (actual time=0.212..0.337 rows=1 loops=219,050)

  • Index Cond: ((pescod = pes.pescod) AND (tiptelcod = 3))
  • Filter: ((telclas <> 6) AND (telsit <> ALL ('{2,5,88}'::integer[])) AND ((higiene_telefone2((regexp_replace((((teldddtel)::text || (teltel)::text))::text, '[^0-9]'::text, ''::text, 'g'::text))::character varying, pes.pescod))::text IS NOT NULL) AND (length(ltrim((higiene_telefone2((regexp_replace((((teldddtel)::text || (teltel)::text))::text, '[^0-9]'::text, ''::text, 'g'::text))::character varying, pes.pescod))::text, '0'::text)) <= 11))
  • Rows Removed by Filter: 0
21. 0.000 2,214.527 ↑ 1.0 1 316,361

Index Scan using retorno_pkey on retorno ret (cost=1.17..5.18 rows=1 width=10) (actual time=0.007..0.007 rows=1 loops=316,361)

  • Index Cond: (retseq = (SubPlan 1))
22.          

SubPlan (forIndex Scan)

23. 632.722 4,745.415 ↑ 1.0 1 316,361

Limit (cost=0.57..0.60 rows=1 width=16) (actual time=0.015..0.015 rows=1 loops=316,361)

24. 4,112.693 4,112.693 ↑ 868.0 1 316,361

Index Only Scan Backward using retorno_iu0001 on retorno o (cost=0.57..29.93 rows=868 width=16) (actual time=0.013..0.013 rows=1 loops=316,361)

  • Index Cond: ((devcod = dev.devcod) AND (carcod = dev.carcod))
  • Heap Fetches: 53995
25. 1,265.444 11,388.996 ↑ 1.0 1 316,361

Aggregate (cost=17.82..17.83 rows=1 width=4) (actual time=0.036..0.036 rows=1 loops=316,361)

  • Filter: ((min(par.conpardatven) >= '1992-05-23'::date) AND (min(par.conpardatven) <= '2019-10-07'::date))
  • Rows Removed by Filter: 0
26. 987.169 10,123.552 ↓ 9.0 9 316,361

Nested Loop (cost=0.99..17.82 rows=1 width=4) (actual time=0.021..0.032 rows=9 loops=316,361)

27. 2,214.527 2,214.527 ↑ 5.0 1 316,361

Index Only Scan using contrato_iu0005 on contrato con (cost=0.43..2.53 rows=5 width=8) (actual time=0.006..0.007 rows=1 loops=316,361)

  • Index Cond: ((carcod = dev.carcod) AND (devcod = dev.devcod))
  • Heap Fetches: 119904
28. 6,921.856 6,921.856 ↓ 2.3 7 407,168

Index Only Scan using contrato_parcela_iu0023 on contrato_parcela par (cost=0.56..3.03 rows=3 width=12) (actual time=0.010..0.017 rows=7 loops=407,168)

  • Index Cond: ((concod = con.concod) AND (conparati = 0) AND (conparvalsal > '0'::numeric))
  • Heap Fetches: 376531
29. 1,581.785 1,581.785 ↓ 0.0 0 316,357

Index Only Scan using segmentacao_segcod on segmentacaolevel1level2 c (cost=0.28..2.32 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=316,357)

  • Index Cond: ((segcod = 472) AND (segfilcod = 1) AND (segfili01 = ret.sitcod))
  • Heap Fetches: 0
Planning time : 7.570 ms
Execution time : 101,042.959 ms