explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6nmn

Settings
# exclusive inclusive rows x rows loops node
1. 2,605.641 18,995.511 ↑ 12,625,891.1 40,478 1

Nested Loop Left Join (cost=10,000,024,323.61..37,796,299,336.36 rows=511,070,818,406 width=828) (actual time=112.023..18,995.511 rows=40,478 loops=1)

  • Join Filter: (transacionadordebitochequecpf.cd_transacionador <> '0'::numeric)
  • Rows Removed by Join Filter: 31303850
2. 32.477 2,505.916 ↑ 28,978.7 40,478 1

Nested Loop Left Join (cost=10,000,024,323.19..10,032,376,505.38 rows=1,173,001,712 width=377) (actual time=99.313..2,505.916 rows=40,478 loops=1)

3. 11.320 2,311.527 ↑ 955.7 40,478 1

Merge Left Join (cost=10,000,024,322.77..10,001,327,510.31 rows=38,683,412 width=364) (actual time=99.258..2,311.527 rows=40,478 loops=1)

  • Merge Cond: (transacionadorvendapdv.cd_transacionador = transacionadordebitoconvenioextra.cd_transacionador)
  • Join Filter: ((transacionadordebitocrediario.dt_exclusao IS NULL) AND (transacionadorvendapdv.cd_transacionador <> '0'::numeric) AND (transacionadordebitoconvenioextra.cd_portador = transacionadorconvenioextrapdv.cd_portadorextra))
4. 39.920 2,287.134 ↑ 955.7 40,478 1

Nested Loop Left Join (cost=10,000,024,322.35..10,001,219,070.03 rows=38,683,412 width=379) (actual time=94.746..2,287.134 rows=40,478 loops=1)

  • Join Filter: (transacionadorvendapdv.cd_portador = '0'::numeric)
  • Rows Removed by Join Filter: 35531
5. 28.371 2,166.258 ↑ 31.2 40,478 1

Nested Loop Left Join (cost=10,000,024,321.93..10,000,229,193.30 rows=1,264,786 width=364) (actual time=94.742..2,166.258 rows=40,478 loops=1)

6. 10.451 2,016.453 ↑ 1.3 40,478 1

Merge Left Join (cost=10,000,024,321.51..10,000,189,589.18 rows=50,603 width=357) (actual time=94.726..2,016.453 rows=40,478 loops=1)

  • Merge Cond: (transacionadorvendapdv.cd_transacionador = transacionadorlimiteconvenioextra.cd_transacionador)
  • Join Filter: ((transacionadorlimitecheque.dt_exclusao IS NULL) AND (transacionadorvendapdv.cd_portador = transacionadorconvenioextrapdv.cd_portadorextra))
  • Rows Removed by Join Filter: 1
7. 29.133 1,997.015 ↑ 1.3 40,478 1

Merge Left Join (cost=10,000,024,321.09..10,000,186,028.25 rows=50,603 width=362) (actual time=93.995..1,997.015 rows=40,478 loops=1)

  • Merge Cond: (transacionadorvendapdv.cd_transacionador = transacionadorlimitecheque.cd_transacionador)
  • Join Filter: (transacionadorvendapdv.cd_portador = '0'::numeric)
  • Rows Removed by Join Filter: 35531
8. 29.734 1,932.618 ↑ 1.3 40,478 1

Merge Left Join (cost=10,000,016,171.39..10,000,176,857.13 rows=50,603 width=351) (actual time=63.887..1,932.618 rows=40,478 loops=1)

  • Merge Cond: (transacionadorvendapdv.cd_transacionador = transacionadorlimitecrediario.cd_transacionador)
  • Join Filter: (transacionadorvendapdv.cd_portador = '0'::numeric)
  • Rows Removed by Join Filter: 35531
9. 34.386 1,868.667 ↑ 1.3 40,478 1

Merge Left Join (cost=10,000,007,965.65..10,000,167,615.62 rows=50,603 width=348) (actual time=35.217..1,868.667 rows=40,478 loops=1)

  • Merge Cond: (transacionadorvendapdv.cd_transacionador = transacionadorlimiteconvenio.cd_transacionador)
  • Join Filter: (transacionadorvendapdv.cd_portador = transacionadorlimiteconvenio.cd_portador)
  • Rows Removed by Join Filter: 49
10. 4.433 1,792.485 ↑ 1.3 40,478 1

Nested Loop Left Join (cost=10,000,000,003.97..10,000,158,680.82 rows=50,603 width=345) (actual time=0.472..1,792.485 rows=40,478 loops=1)

11. 0.000 1,585.662 ↓ 1.0 40,478 1

Nested Loop Left Join (cost=10,000,000,003.83..10,000,134,969.56 rows=39,018 width=336) (actual time=0.472..1,585.662 rows=40,478 loops=1)

12. 6.993 1,505.252 ↓ 1.0 40,478 1

Nested Loop Left Join (cost=10,000,000,003.54..10,000,122,178.03 rows=39,018 width=337) (actual time=0.471..1,505.252 rows=40,478 loops=1)

13. 0.000 1,417.303 ↓ 1.0 40,478 1

Nested Loop Left Join (cost=10,000,000,003.26..10,000,109,876.37 rows=39,018 width=325) (actual time=0.470..1,417.303 rows=40,478 loops=1)

14. 103.248 1,337.122 ↓ 1.0 40,478 1

Nested Loop Left Join (cost=10,000,000,002.99..10,000,098,036.90 rows=39,018 width=306) (actual time=0.470..1,337.122 rows=40,478 loops=1)

  • -> Index Scan using transacionadorenderecopdv_pk on tb_transacionadorenderecopdv transacionadorenderecopdv (cost=0.29..0.38 rows=1 width=51) (actual time=0.002..0.002 rows=1 (...)
15. 41.689 1,233.874 ↓ 1.0 40,478 1

Nested Loop Left Join (cost=10,000,000,002.70..10,000,082,883.54 rows=39,018 width=261) (actual time=0.469..1,233.874 rows=40,478 loops=1)

  • Join Filter: (transacionadorbasicopdv.cd_tipo = (transacionadortipopdv.cd_tipo)::bpchar)
  • Rows Removed by Join Filter: 528129
  • Index Cond: (cd_transacionador = transacionadorbasicopdv.cd_transacionador)
16. 94.774 1,151.707 ↓ 1.0 40,478 1

Nested Loop Left Join (cost=2.69..74,688.58 rows=39,018 width=249) (actual time=0.449..1,151.707 rows=40,478 loops=1)

  • -> Index Scan using transacionadorbasicopdv_pk on tb_transacionadorbasicopdv transacionadorportador (cost=0.29..0.44 rows=1 width=35) (actual time=0.002..0.002 ro (...)
17. 114.852 1,056.933 ↓ 1.0 40,478 1

Nested Loop Left Join (cost=2.40..57,028.37 rows=39,018 width=220) (actual time=0.447..1,056.933 rows=40,478 loops=1)

  • Join Filter: (transacionadorvendapdv.cd_transacionador <> '0'::numeric)
  • -> Index Scan using transacionadorconveniopdv_pk on tb_transacionadorconveniopdv convenioportador (cost=0.29..0.37 rows=1 width=32) (actual time=0.002..0.00 (...)
  • Index Cond: (transacionadorvendapdv.cd_portador = cd_transacionador)
18. 649.941 942.081 ↓ 1.0 40,478 1

Nested Loop Left Join (cost=2.11..42,113.06 rows=39,018 width=200) (actual time=0.443..942.081 rows=40,478 loops=1)

  • Join Filter: ((NOT transacionadorvendapdv.fl_titular) AND (transacionadorvendapdv.cd_transacionador <> '0'::numeric) AND (transacionadorvendapdv.cd_port (...)
  • Rows Removed by Join Filter: 36069
  • -> Index Scan using transacionadorvendapdv_pk on tb_transacionadorvendapdv transacionadorvendatitular (cost=0.41..0.53 rows=1 width=27) (actual time=0 (...)
  • Index Cond: ((cd_transacionador = transacionadorvendapdv.cd_portador) AND (cd_portador = transacionadorvendapdv.cd_portador))
  • Filter: (dt_exclusao IS NULL)
19. 33.630 292.140 ↓ 1.0 40,478 1

Merge Left Join (cost=1.70..20,962.96 rows=39,018 width=186) (actual time=0.042..292.140 rows=40,478 loops=1)

  • Merge Cond: (transacionadorvendapdv.cd_transacionador = transacionadorcartaopdv.cd_transacionador)
  • Join Filter: (transacionadorvendapdv.cd_portador = transacionadorcartaopdv.cd_portador)
  • Rows Removed by Join Filter: 57
  • Index Cond: (transacionadorvendapdv.cd_transacionador = cd_transacionador)
  • Filter: (fl_titular AND (transacionadorvendapdv.cd_portador = cd_portador))
  • Rows Removed by Filter: 81
20. 37.401 232.155 ↑ 1.0 39,018 1

Merge Left Join (cost=1.41..16,857.58 rows=39,018 width=147) (actual time=0.037..232.155 rows=39,018 loops=1)

  • Merge Cond: (transacionadorvendapdv.cd_transacionador = transacionadorbasicopdv.cd_transacionador)
  • Join Filter: (transacionadorvendapdv.cd_transacionador <> '0'::numeric)
21. 56.978 90.355 ↑ 1.0 39,018 1

Merge Left Join (cost=0.83..7,059.39 rows=39,018 width=96) (actual time=0.024..90.355 rows=39,018 loops=1)

  • Merge Cond: (transacionadorvendapdv.cd_transacionador = transacionadorconveniopdv.cd_transacionador)
  • Join Filter: ((transacionadorvendapdv.cd_transacionador <> '0'::numeric) AND (transacionadorvendapdv.cd_portador > '0'::numeric) AND ( (...)
  • Rows Removed by Join Filter: 49
  • -> Index Scan using transacionadorvendapdv_pk on tb_transacionadorvendapdv transacionadorvendapdv (cost=0.41..3072.37 rows=39018 wid (...)
  • Filter: (dt_exclusao IS NULL)
22. 5.458 33.377 ↓ 1.0 35,392 1

Materialize (cost=0.41..3,147.40 rows=34,596 width=38) (actual time=0.019..33.377 rows=35,392 loops=1)

23. 27.919 27.919 ↓ 1.0 34,675 1

Merge Left Join (cost=0.41..3,060.91 rows=34,596 width=38) (actual time=0.013..27.919 rows=34,675 loops=1)

  • Merge Cond: ((transacionadorconveniopdv.cd_transacionador = transacionadorconvenioextrapdv.cd_transacionador) AND (transac (...)
  • -> Index Scan using transacionadorconveniopdv_pk on tb_transacionadorconveniopdv transacionadorconveniopdv (cost=0.29..2 (...)
  • Filter: (dt_exclusao IS NULL)
  • Rows Removed by Filter: 1981
  • -> Index Scan using transacionadorconvextrapdv_pk on tb_transacionadorconvextrapdv transacionadorconvenioextrapdv (cost= (...)
24. 17.673 104.399 ↓ 1.0 60,545 1

Materialize (cost=0.58..9,094.36 rows=59,829 width=51) (actual time=0.013..104.399 rows=60,545 loops=1)

25. 86.726 86.726 ↑ 1.0 59,828 1

Merge Left Join (cost=0.58..8,944.78 rows=59,829 width=51) (actual time=0.008..86.726 rows=59,828 loops=1)

  • Merge Cond: (transacionadorbasicopdv.cd_transacionador = transacionadorchequepdv.cd_transacionador)
  • -> Index Scan using transacionadorbasicopdv_pk on tb_transacionadorbasicopdv transacionadorbasicopdv (cost=0.29..6154.53 rows= (...)
  • -> Index Scan using transacionadorchequepdv_pk on tb_transacionadorchequepdv transacionadorchequepdv (cost=0.29..2182.48 rows= (...)
  • Filter: (dt_exclusao IS NULL)
26. 26.355 26.355 ↓ 1.0 38,841 1

Materialize (cost=0.29..3,400.03 rows=37,373 width=39) (actual time=0.004..26.355 rows=38,841 loops=1)

  • -> Index Scan using transacionadorcartaopdv_idx003 on tb_transacionadorcartaopdv transacionadorcartaopdv (cost=0.29..3306.60 rows=37373 wi (...)
27. 40.468 40.478 ↑ 1.0 14 40,478

Materialize (cost=10,000,000,000.00..10,000,000,001.21 rows=14 width=20) (actual time=0.000..0.001 rows=14 loops=40,478)

28. 0.010 0.010 ↑ 1.0 14 1

Seq Scan on tb_transacionadortipopdv transacionadortipopdv (cost=10,000,000,000.00..10,000,000,001.14 rows=14 width=20) (actual time=0.008..0.010 rows=14 loops=1)

29. 80.956 80.956 ↑ 1.0 1 40,478

Index Scan using municipiopdv_pk on tb_municipiopdv municipiopdv (cost=0.28..0.29 rows=1 width=29) (actual time=0.001..0.002 rows=1 loops=40,478)

  • Index Cond: (cd_municipio = transacionadorenderecopdv.cd_municipio)
30. 80.956 80.956 ↑ 1.0 1 40,478

Index Scan using bairropdv_pk on tb_bairropdv bairropdv (cost=0.28..0.31 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=40,478)

  • Index Cond: (cd_bairro = transacionadorenderecopdv.cd_bairro)
31. 80.956 80.956 ↓ 0.0 0 40,478

Index Scan using transacionador_pk on tb_pontuacaocrmpdv pontuacaocrmpdv (cost=0.29..0.32 rows=1 width=11) (actual time=0.001..0.002 rows=0 loops=40,478)

  • Index Cond: (cd_transacionador = transacionadorbasicopdv.cd_transacionador)
32. 202.390 202.390 ↑ 1.0 1 40,478

Index Scan using transacionadorgrupopdv_pk on tb_transacionadorgrupopdv transacionadorgrupopdv (cost=0.14..0.60 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=40,478)

  • Index Cond: (transacionadorbasicopdv.cd_grupo = cd_grupo)
  • Filter: (transacionadorbasicopdv.cd_tipo = (cd_tipo)::bpchar)
  • Rows Removed by Filter: 8
33. 18.748 41.796 ↓ 1.1 36,852 1

Sort (cost=7,961.68..8,047.93 rows=34,501 width=12) (actual time=34.740..41.796 rows=36,852 loops=1)

  • Sort Key: transacionadorlimiteconvenio.cd_transacionador
  • Sort Method: quicksort Memory: 4242kB
34. 11.520 23.048 ↓ 1.0 34,675 1

Bitmap Heap Scan on tb_transacionadorlimitepdv transacionadorlimiteconvenio (cost=3,439.02..5,361.28 rows=34,501 width=12) (actual time=11.669..23.048 rows=34,675 loops=1)

  • Recheck Cond: (id_tipolimite = '1'::numeric)
  • Filter: (dt_exclusao IS NULL)
  • Heap Blocks: exact=1480
35. 11.528 11.528 ↓ 1.0 34,675 1

Bitmap Index Scan on transacionadorlimitepdv_pk (cost=0.00..3,430.39 rows=34,501 width=0) (actual time=11.528..11.528 rows=34,675 loops=1)

  • Index Cond: (id_tipolimite = '1'::numeric)
36. 17.875 34.217 ↓ 1.0 38,833 1

Sort (cost=8,205.74..8,298.37 rows=37,054 width=9) (actual time=28.664..34.217 rows=38,833 loops=1)

  • Sort Key: transacionadorlimitecrediario.cd_transacionador
  • Sort Method: quicksort Memory: 3255kB
37. 6.276 16.342 ↑ 1.0 36,656 1

Bitmap Heap Scan on tb_transacionadorlimitepdv transacionadorlimitecrediario (cost=3,439.66..5,393.83 rows=37,054 width=9) (actual time=10.238..16.342 rows=36,656 loops=1)

  • Recheck Cond: (id_tipolimite = '3'::numeric)
  • Filter: (dt_exclusao IS NULL)
  • Heap Blocks: exact=1491
38. 10.066 10.066 ↑ 1.0 36,656 1

Bitmap Index Scan on transacionadorlimitepdv_pk (cost=0.00..3,430.39 rows=37,054 width=0) (actual time=10.066..10.066 rows=36,656 loops=1)

  • Index Cond: (id_tipolimite = '3'::numeric)
39. 17.134 35.264 ↓ 1.1 38,833 1

Sort (cost=8,149.70..8,240.87 rows=36,469 width=17) (actual time=30.102..35.264 rows=38,833 loops=1)

  • Sort Key: transacionadorlimitecheque.cd_transacionador
  • Sort Method: quicksort Memory: 3255kB
40. 7.166 18.130 ↓ 1.0 36,656 1

Bitmap Heap Scan on tb_transacionadorlimitepdv transacionadorlimitecheque (cost=3,439.51..5,386.37 rows=36,469 width=17) (actual time=11.119..18.130 rows=36,656 loops=1)

  • Recheck Cond: (id_tipolimite = '2'::numeric)
  • Filter: (dt_exclusao IS NULL)
  • Heap Blocks: exact=1491
41. 10.964 10.964 ↓ 1.0 36,656 1

Bitmap Index Scan on transacionadorlimitepdv_pk (cost=0.00..3,430.39 rows=36,469 width=0) (actual time=10.964..10.964 rows=36,656 loops=1)

  • Index Cond: (id_tipolimite = '2'::numeric)
42. 8.987 8.987 ↑ 1.0 1 1

Index Scan using transacionadorlimitepdv_pk on tb_transacionadorlimitepdv transacionadorlimiteconvenioextra (cost=0.42..3,434.40 rows=1 width=9) (actual time=0.728..8.987 rows=1 loops=1)

  • Index Cond: (id_tipolimite = '4'::numeric)
43. 121.434 121.434 ↑ 1.0 1 40,478

Index Scan using transacionadordebitopdv_pk on tb_transacionadordebitopdv transacionadordebitoconvenio (cost=0.42..0.77 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=40,478)

  • Index Cond: ((transacionadorvendapdv.cd_transacionador = cd_transacionador) AND (id_tipolimite = '1'::numeric) AND (transacionadorvendapdv.cd_portador = cd_portador))
  • Filter: ((dt_exclusao IS NULL) AND (cd_transacionador <> '0'::numeric))
44. 80.956 80.956 ↑ 1.0 1 40,478

Index Scan using transacionadordebitopdv_pk on tb_transacionadordebitopdv transacionadordebitocrediario (cost=0.42..0.77 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=40,478)

  • Index Cond: ((transacionadorvendapdv.cd_transacionador = cd_transacionador) AND (id_tipolimite = '1'::numeric))
  • Filter: ((dt_exclusao IS NULL) AND (cd_transacionador <> '0'::numeric))
45. 0.004 13.073 ↑ 1.0 1 1

Materialize (cost=0.42..5,126.00 rows=1 width=16) (actual time=4.508..13.073 rows=1 loops=1)

46. 13.069 13.069 ↑ 1.0 1 1

Index Scan using transacionadordebitopdv_pk on tb_transacionadordebitopdv transacionadordebitoconvenioextra (cost=0.42..5,125.99 rows=1 width=16) (actual time=4.504..13.069 rows=1 loops=1)

  • Index Cond: (id_tipolimite = '4'::numeric)
47. 161.912 161.912 ↓ 0.0 0 40,478

Index Scan using transacionadordebitopdv_pk on tb_transacionadordebitopdv transacionadordebitochequecpf (cost=0.42..0.78 rows=2 width=17) (actual time=0.004..0.004 rows=0 loops=40,478)

  • Index Cond: ((cd_transacionador = '0'::numeric) AND (id_tipolimite = '2'::numeric) AND (nr_cpfcnpj = transacionadorvendapdv.nr_cpfcnpj) AND (cd_portador = '0'::numeric))
  • Filter: (dt_exclusao IS NULL)
48. 13,883.954 13,883.954 ↓ 773.0 773 40,478

Index Scan using transacionadordebitopdv_pk on tb_transacionadordebitopdv transacionadordebitocheque (cost=0.42..0.78 rows=1 width=13) (actual time=0.004..0.343 rows=773 loops=40,478)

  • Index Cond: ((cd_transacionador = transacionadorvendapdv.cd_transacionador) AND (id_tipolimite = '2'::numeric) AND (cd_portador = '0'::numeric))
  • Filter: (dt_exclusao IS NULL)
Planning time : 19.745 ms
Execution time : 19,001.129 ms