explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p6Td

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 100,136.262 ↓ 5.5 11 1

Append (cost=0.00..6,283.44 rows=2 width=736) (actual time=0.002..100,136.262 rows=11 loops=1)

2. 0.002 0.002 ↑ 1.0 1 1

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

3. 0.040 100,136.256 ↓ 10.0 10 1

Subquery Scan on x (cost=6,283.24..6,283.42 rows=1 width=736) (actual time=100,134.339..100,136.256 rows=10 loops=1)

4. 0.000 100,136.216 ↓ 10.0 10 1

Limit (cost=6,283.24..6,283.40 rows=1 width=104) (actual time=100,134.329..100,136.216 rows=10 loops=1)

5.          

Initplan (forLimit)

6. 0.017 0.017 ↑ 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.016..0.017 rows=1 loops=1)

  • Index Cond: ((segcod = 472) AND (segfilcod = 48))
  • Heap Fetches: 1
7. 2.039 100,136.211 ↓ 10.0 10 1

GroupAggregate (cost=6,278.95..6,279.11 rows=1 width=104) (actual time=100,134.328..100,136.211 rows=10 loops=1)

  • Group Key: dev.devcod, ''::text, (upper((dev.devnom)::text))
8. 6.023 100,134.172 ↓ 19.0 19 1

Sort (cost=6,278.95..6,278.95 rows=1 width=91) (actual time=100,134.165..100,134.172 rows=19 loops=1)

  • Sort Key: dev.devcod, (upper((dev.devnom)::text))
  • Sort Method: quicksort Memory: 613kB
9. 201.551 100,128.149 ↓ 3,940.0 3,940 1

Nested Loop Semi Join (cost=21.08..6,278.94 rows=1 width=91) (actual time=56.703..100,128.149 rows=3,940 loops=1)

10. 679.803 98,344.808 ↓ 316,358.0 316,358 1

Nested Loop (cost=20.81..6,274.99 rows=1 width=52) (actual time=0.503..98,344.808 rows=316,358 loops=1)

11. 6,261.283 84,061.439 ↓ 316,362.0 316,362 1

Nested Loop (cost=2.99..6,257.14 rows=1 width=56) (actual time=0.466..84,061.439 rows=316,362 loops=1)

12. 504.312 75,901.984 ↓ 316,362.0 316,362 1

Nested Loop (cost=1.82..6,251.96 rows=1 width=54) (actual time=0.429..75,901.984 rows=316,362 loops=1)

13. 803.109 4,206.422 ↓ 27,381.2 219,050 1

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

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

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

15. 653.846 653.846 ↓ 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.048..653.846 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.001 0.007 ↑ 1.0 1 1

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

19. 0.006 0.006 ↑ 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.006..0.006 rows=1 loops=1)

  • Index Cond: ((segcod = 472) AND (segfilcod = 42))
  • Heap Fetches: 1
20. 71,191.250 71,191.250 ↑ 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.202..0.325 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 1,898.172 ↑ 1.0 1 316,362

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

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

SubPlan (forIndex Scan)

23. 632.724 4,745.430 ↑ 1.0 1 316,362

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

24. 4,112.706 4,112.706 ↑ 868.0 1 316,362

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,362)

  • Index Cond: ((devcod = dev.devcod) AND (carcod = dev.carcod))
  • Heap Fetches: 61261
25. 949.086 13,603.566 ↑ 1.0 1 316,362

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

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

Nested Loop (cost=0.99..17.82 rows=1 width=4) (actual time=0.024..0.040 rows=9 loops=316,362)

27. 2,214.534 2,214.534 ↑ 5.0 1 316,362

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,362)

  • Index Cond: ((carcod = dev.carcod) AND (devcod = dev.devcod))
  • Heap Fetches: 119846
28. 9,364.933 9,364.933 ↓ 2.3 7 407,171

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

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

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,358)

  • Index Cond: ((segcod = 472) AND (segfilcod = 1) AND (segfili01 = ret.sitcod))
  • Heap Fetches: 3940
Planning time : 6.327 ms
Execution time : 100,136.547 ms