explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fddn

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 101,121.811 ↓ 0.0 0 1

Limit (cost=28.87..2,372,877.84 rows=500 width=489) (actual time=101,121.811..101,121.811 rows=0 loops=1)

2. 0.835 101,121.809 ↓ 0.0 0 1

Nested Loop Left Join (cost=28.87..20,757,711.70 rows=4,374 width=489) (actual time=101,121.809..101,121.809 rows=0 loops=1)

  • Filter: (cp.conpardatven < COALESCE(op.opedatpag, '0001-01-01'::date))
  • Rows Removed by Filter: 491
3. 4,002.953 101,120.974 ↑ 26.7 491 1

Nested Loop Left Join (cost=28.43..20,526,780.69 rows=13,121 width=86) (actual time=74,750.178..101,120.974 rows=491 loops=1)

4. 0.254 97,118.021 ↑ 26.7 491 1

Nested Loop (cost=2.34..20,183,699.89 rows=13,121 width=78) (actual time=74,750.130..97,118.021 rows=491 loops=1)

5. 0.105 94,781.593 ↑ 82.1 17 1

Nested Loop (cost=1.78..17,358,282.89 rows=1,395 width=49) (actual time=74,469.586..94,781.593 rows=17 loops=1)

6. 1,781.280 94,505.918 ↑ 208.6 17 1

Seq Scan on devedor d (cost=1.35..17,334,820.60 rows=3,546 width=45) (actual time=74,460.526..94,505.918 rows=17 loops=1)

  • Filter: ((NOT (hashed SubPlan 14)) AND ((SubPlan 17) = 1))
  • Rows Removed by Filter: 1,416,608
7.          

SubPlan (for Seq Scan)

8. 0.008 0.008 ↑ 1.1 14 1

Seq Scan on unidade_operacional (cost=0.00..1.31 rows=15 width=32) (actual time=0.006..0.008 rows=14 loops=1)

  • Filter: (uniopecod < 900)
  • Rows Removed by Filter: 5
9. 0.000 92,724.630 ↓ 0.0 0 363,626

Limit (cost=0.56..12.13 rows=1 width=4) (actual time=0.255..0.255 rows=0 loops=363,626)

10. 92,724.630 92,724.630 ↓ 0.0 0 363,626

Index Scan using gestor_cliente_iu0001 on gestor_cliente gc (cost=0.56..12.13 rows=1 width=4) (actual time=0.255..0.255 rows=0 loops=363,626)

  • Index Cond: ((ges_hisassent = 20) AND (d.devcod = devcod))
  • Filter: ((ges_hisdatent <= '2020-09-03'::date) AND ((ges_hisdatsai > '2020-09-03'::date) OR (ges_hisdatsai = '0001-01-01'::date)))
  • Rows Removed by Filter: 1
11. 275.570 275.570 ↑ 1.0 1 17

Index Only Scan using contrato_iu0005 on contrato c (cost=0.43..6.61 rows=1 width=20) (actual time=16.209..16.210 rows=1 loops=17)

  • Index Cond: ((carcod = d.carcod) AND (devcod = d.devcod))
  • Heap Fetches: 17
12. 1,070.576 2,336.174 ↑ 2.3 29 17

Index Scan using contrato_parcela_iu0008 on contrato_parcela cp (cost=0.56..2,024.73 rows=66 width=45) (actual time=71.877..137.422 rows=29 loops=17)

  • Index Cond: (concod = c.concod)
  • Filter: ((conparvalsal > '0'::numeric) OR ((SubPlan 15) >= 1) OR ((SubPlan 16) >= 1))
  • Rows Removed by Filter: 3
13.          

SubPlan (for Index Scan)

14. 0.092 786.646 ↓ 0.0 0 46

Limit (cost=0.56..11.81 rows=1 width=4) (actual time=17.101..17.101 rows=0 loops=46)

15. 786.554 786.554 ↓ 0.0 0 46

Index Scan using contrato_parcela_retoma_iu0009 on contrato_parcela_retomada cpr_3 (cost=0.56..11.81 rows=1 width=4) (actual time=17.099..17.099 rows=0 loops=46)

  • Index Cond: (cp.conparseq = conparseq)
  • Filter: (conparretdatinc > '2020-09-16 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 1
16. 0.138 478.952 ↓ 0.0 0 46

Limit (cost=0.56..8.58 rows=1 width=4) (actual time=10.412..10.412 rows=0 loops=46)

17. 478.814 478.814 ↓ 0.0 0 46

Index Only Scan using contrato_parcela_morto_iu0007 on contrato_parcela_morto cpm_2 (cost=0.56..8.58 rows=1 width=4) (actual time=10.409..10.409 rows=0 loops=46)

  • Index Cond: ((conparseq = cp.conparseq) AND (conparmordatinc > '2020-09-16 00:00:00'::timestamp without time zone) AND (conparmoraca = 'D'::text))
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0 491

Index Only Scan using contrato_parcela_retoma_iu0003 on contrato_parcela_retomada cpr (cost=26.09..26.14 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=491)

  • Index Cond: ((opecod = (SubPlan 13)) AND (conparseq = cp.conparseq))
  • Heap Fetches: 0
19.          

SubPlan (for Index Only Scan)

20. 0.982 4,001.650 ↓ 0.0 0 491

Limit (cost=25.53..25.53 rows=1 width=16) (actual time=8.150..8.150 rows=0 loops=491)

21. 1.473 4,000.668 ↓ 0.0 0 491

Sort (cost=25.53..25.53 rows=2 width=16) (actual time=8.148..8.148 rows=0 loops=491)

  • Sort Key: op1.opedatinc DESC
  • Sort Method: quicksort Memory: 25kB
22. 0.491 3,999.195 ↓ 0.0 0 491

Nested Loop (cost=1.00..25.52 rows=2 width=16) (actual time=8.145..8.145 rows=0 loops=491)

23. 3,998.704 3,998.704 ↓ 0.0 0 491

Index Only Scan using contrato_parcela_retoma_iu0005 on contrato_parcela_retomada cpr1 (cost=0.56..8.59 rows=2 width=8) (actual time=8.144..8.144 rows=0 loops=491)

  • Index Cond: (conparseq = cp.conparseq)
  • Heap Fetches: 0
24. 0.000 0.000 ↓ 0.0 0

Index Scan using operacao_pkey on operacao op1 (cost=0.43..8.45 rows=1 width=16) (never executed)

  • Index Cond: (opecod = cpr1.opecod)
25. 0.000 0.000 ↓ 0.0 0 491

Index Scan using operacao_pkey on operacao op (cost=0.43..0.58 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=491)

  • Index Cond: (opecod = cpr.opecod)
26.          

SubPlan (for Nested Loop Left Join)

27. 0.000 0.000 ↓ 0.0 0

Seq Scan on unidade_operacional u (cost=0.00..1.33 rows=1 width=32) (never executed)

  • Filter: (uniopecod = (d.devuniope)::integer)
28. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=11.82..11.83 rows=1 width=32) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using contrato_parcela_retoma_iu0009 on contrato_parcela_retomada cpr2 (cost=0.56..11.81 rows=2 width=6) (never executed)

  • Index Cond: ((conparseq = cp.conparseq) AND (conparretati = 0) AND (tipenccod <= 3,999))
30. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=5.41..5.42 rows=1 width=32) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using icontrato_parcela_retomada1 on contrato_parcela_retomada cpr3 (cost=0.43..5.40 rows=1 width=6) (never executed)

  • Index Cond: (tipenccod >= 6,000)
  • Filter: ((conparseq = cp.conparseq) AND (conparretati = 0))
32. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.60..8.61 rows=1 width=32) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Index Scan using contrato_parcela_retoma_iu0009 on contrato_parcela_retomada cpr4 (cost=0.56..8.60 rows=1 width=6) (never executed)

  • Index Cond: ((conparseq = cp.conparseq) AND (tipenccod <= 5,999) AND (tipenccod >= 5,000))
34. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.60..8.61 rows=1 width=32) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Index Scan using contrato_parcela_retoma_iu0009 on contrato_parcela_retomada cpr4_1 (cost=0.56..8.60 rows=1 width=6) (never executed)

  • Index Cond: ((conparseq = cp.conparseq) AND (tipenccod >= 5,000) AND (tipenccod <= 5,999))
36. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.26 rows=1 width=32) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Seq Scan on carteira c1 (cost=0.00..4.17 rows=1 width=15) (never executed)

  • Filter: (carcod = op.carcod)
38. 0.000 0.000 ↓ 0.0 0

Result (cost=5.39..5.40 rows=1 width=8) (never executed)

39.          

Initplan (for Result)

40. 0.000 0.000 ↓ 0.0 0

Index Only Scan using contrato_parcela_retoma_iu0009 on contrato_parcela_retomada cpr_1 (cost=0.56..8.59 rows=2 width=0) (never executed)

  • Index Cond: (conparseq = cp.conparseq)
  • Heap Fetches: 0
41. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.56..0.81 rows=1 width=8) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Result (cost=0.56..9.36 rows=35 width=8) (never executed)

  • One-Time Filter: (NOT $12)
43. 0.000 0.000 ↓ 0.0 0

Index Only Scan Backward using contrato_parcela_morto_iu0007 on contrato_parcela_morto cpm (cost=0.56..9.36 rows=35 width=8) (never executed)

  • Index Cond: ((conparseq = cp.conparseq) AND (conparmordatinc IS NOT NULL) AND (conparmoraca = 'D'::text))
  • Heap Fetches: 0
44. 0.000 0.000 ↓ 0.0 0

Index Only Scan using contrato_parcela_retoma_iu0009 on contrato_parcela_retomada cpr_2 (cost=0.56..8.59 rows=2 width=0) (never executed)

  • Index Cond: (conparseq = cp.conparseq)
  • Heap Fetches: 0
45. 0.000 0.000 ↓ 0.0 0

Index Only Scan using contrato_parcela_morto_iu0007 on contrato_parcela_morto cpm_1 (cost=0.56..9.27 rows=35 width=0) (never executed)

  • Index Cond: ((conparseq = cp.conparseq) AND (conparmoraca = 'D'::text))
  • Heap Fetches: 0
Planning time : 3.610 ms
Execution time : 101,122.160 ms