explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fAII

Settings
# exclusive inclusive rows x rows loops node
1. 0.537 7,424.633 ↑ 1.0 500 1

Hash Left Join (cost=736,149.30..741,769.28 rows=512 width=842) (actual time=6,913.979..7,424.633 rows=500 loops=1)

  • Hash Cond: ((aliastransacionadores.codigotransacionador = aliasdebitosconvenioextra.codigotransacionadordebito) AND (aliasconvenioextra.codigoportadorconvenioextra = aliasdebitosconvenioextra.codigoportadordebito))
  • Join Filter: (aliastransacionadores.codigotransacionador <> '0'::numeric)
2.          

CTE aliastransacionadores

3. 3.070 3.070 ↑ 1.0 500 1

CTE Scan on codigostransacionadorcompletos (cost=5,295.52..5,305.52 rows=500 width=250) (actual time=0.286..3.070 rows=500 loops=1)

4.          

CTE transacionadores_pendentes

5. 0.042 0.379 ↑ 1.0 500 1

Limit (cost=0.42..17.77 rows=500 width=9) (actual time=0.236..0.379 rows=500 loops=1)

6. 0.337 0.337 ↑ 153.9 500 1

Index Only Scan using transacionadorcargapendpdv_pk on tb_transacionadorcargapendpdv transacionadorcargapendentepdv (cost=0.42..2,670.58 rows=76,944 width=9) (actual time=0.235..0.337 rows=500 loops=1)

  • Heap Fetches: 0
7.          

CTE codigostransacionadorcompletos

8. 0.359 2.890 ↑ 1.0 500 1

Nested Loop Left Join (cost=0.70..5,277.75 rows=500 width=250) (actual time=0.285..2.890 rows=500 loops=1)

  • Join Filter: (transacionadores_pendentes.codigotransacionador = '0'::numeric)
9. 0.026 1.531 ↑ 1.0 500 1

Nested Loop Left Join (cost=0.29..2,476.75 rows=500 width=94) (actual time=0.260..1.531 rows=500 loops=1)

10. 0.505 0.505 ↑ 1.0 500 1

CTE Scan on transacionadores_pendentes (cost=0.00..10.00 rows=500 width=40) (actual time=0.238..0.505 rows=500 loops=1)

11. 1.000 1.000 ↓ 0.0 0 500

Index Scan using transacionadorbasicopdv_pk on tb_transacionadorbasicopdv transacionadorbasicopdv (cost=0.29..4.92 rows=1 width=54) (actual time=0.002..0.002 rows=0 loops=500)

  • Index Cond: (transacionadores_pendentes.codigotransacionador = cd_transacionador)
  • Filter: (dt_exclusao IS NULL)
12. 1.000 1.000 ↓ 0.0 0 500

Index Scan using transacionadorbasicopdv_idx001 on tb_transacionadorbasicopdv transacionadordocumento (cost=0.41..5.58 rows=1 width=54) (actual time=0.002..0.002 rows=0 loops=500)

  • Index Cond: (transacionadores_pendentes.numerocpfcnpj = nr_cpfcnpj)
  • Filter: (dt_exclusao IS NULL)
13.          

CTE aliaslimites

14. 0.001 2.922 ↓ 0.0 0 1

Nested Loop (cost=0.42..6,427.48 rows=1,959 width=17) (actual time=2.922..2.922 rows=0 loops=1)

15. 2.921 2.921 ↓ 0.0 0 1

CTE Scan on aliastransacionadores aliastransacionadores_1 (cost=0.00..11.25 rows=498 width=32) (actual time=2.921..2.921 rows=0 loops=1)

  • Filter: (codigotransacionador <> '0'::numeric)
  • Rows Removed by Filter: 500
16. 0.000 0.000 ↓ 0.0 0

Index Scan using transacionadorlimitepdv_pk on tb_transacionadorlimitepdv transacionadorlimitepdv (cost=0.42..12.84 rows=4 width=17) (never executed)

  • Index Cond: (cd_transacionador = aliastransacionadores_1.codigotransacionador)
  • Filter: (dt_exclusao IS NULL)
17.          

CTE aliasdebitostransacionador

18. 0.276 6,906.495 ↑ 18.6 1,000 1

Append (cost=0.42..723,469.15 rows=18,560 width=28) (actual time=0.038..6,906.495 rows=1,000 loops=1)

19. 2.263 6,906.136 ↑ 16.4 1,000 1

Nested Loop (cost=0.42..716,475.28 rows=16,431 width=28) (actual time=0.038..6,906.136 rows=1,000 loops=1)

20. 1.873 1.873 ↓ 1.0 500 1

CTE Scan on aliastransacionadores aliastransacionadores_2 (cost=0.00..11.25 rows=498 width=32) (actual time=0.001..1.873 rows=500 loops=1)

  • Filter: (numerocpfcnpj <> '0'::numeric)
21. 6,902.000 6,902.000 ↑ 16.5 2 500

Index Scan using transacionadordebitopdv_pk on tb_transacionadordebitopdv transacionadordebitopdv (cost=0.42..1,438.35 rows=33 width=28) (actual time=0.041..13.804 rows=2 loops=500)

  • Index Cond: (nr_cpfcnpj = aliastransacionadores_2.numerocpfcnpj)
  • Filter: (dt_exclusao IS NULL)
22. 0.000 0.083 ↓ 0.0 0 1

Nested Loop (cost=0.42..6,808.27 rows=2,129 width=28) (actual time=0.083..0.083 rows=0 loops=1)

23. 0.083 0.083 ↓ 0.0 0 1

CTE Scan on aliastransacionadores aliastransacionadores_3 (cost=0.00..11.25 rows=498 width=32) (actual time=0.083..0.083 rows=0 loops=1)

  • Filter: (codigotransacionador <> '0'::numeric)
  • Rows Removed by Filter: 500
24. 0.000 0.000 ↓ 0.0 0

Index Scan using transacionadordebitopdv_pk on tb_transacionadordebitopdv transacionadordebitopdv_1 (cost=0.42..13.61 rows=4 width=28) (never executed)

  • Index Cond: (cd_transacionador = aliastransacionadores_3.codigotransacionador)
  • Filter: (dt_exclusao IS NULL)
25.          

CTE aliasconvenioextra

26. 0.065 0.123 ↓ 0.0 0 1

Hash Join (cost=8.15..20.05 rows=2 width=42) (actual time=0.123..0.123 rows=0 loops=1)

  • Hash Cond: (aliastransacionadores_4.codigotransacionador = transacionadorconvenioextrapdv.cd_transacionador)
27. 0.035 0.035 ↑ 1.0 500 1

CTE Scan on aliastransacionadores aliastransacionadores_4 (cost=0.00..10.00 rows=500 width=32) (actual time=0.000..0.035 rows=500 loops=1)

28. 0.007 0.023 ↑ 1.0 1 1

Hash (cost=8.14..8.14 rows=1 width=42) (actual time=0.023..0.023 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.016 0.016 ↑ 1.0 1 1

Index Scan using transacionadorconvextrapdv_pk on tb_transacionadorconvextrapdv transacionadorconvenioextrapdv (cost=0.13..8.14 rows=1 width=42) (actual time=0.016..0.016 rows=1 loops=1)

  • Filter: (dt_exclusao IS NULL)
30. 0.182 7,423.899 ↑ 1.0 500 1

Hash Left Join (cost=508.10..6,111.43 rows=512 width=599) (actual time=6,913.757..7,423.899 rows=500 loops=1)

  • Hash Cond: ((aliastransacionadores.codigotransacionador = aliasdebitosconvenio.codigotransacionadordebito) AND (transacionadorcartaopdv.cd_portador = aliasdebitosconvenio.codigoportadordebito))
  • Join Filter: (aliastransacionadores.codigotransacionador <> '0'::numeric)
31. 27.890 7,423.526 ↑ 1.0 500 1

Nested Loop Left Join (cost=89.11..5,678.34 rows=512 width=545) (actual time=6,913.546..7,423.526 rows=500 loops=1)

  • Join Filter: ((aliastransacionadores.codigotransacionador <> '0'::numeric) AND (aliasdebitoscrediario.codigotransacionadordebito = aliastransacionadores.codigotransacionador))
  • Rows Removed by Join Filter: 250000
32. 54.350 357.636 ↑ 1.0 500 1

Nested Loop Left Join (cost=89.11..5,201.55 rows=512 width=491) (actual time=3.478..357.636 rows=500 loops=1)

  • Join Filter: (((aliastransacionadores.codigotransacionador <> '0'::numeric) AND (aliasdebitoscheque.codigotransacionadordebito = aliastransacionadores.codigotransacionador)) OR ((aliasdebitoscheque.numerocpfcnpjdebito = aliastransaciona (...)
  • Rows Removed by Join Filter: 249500
33. 34.365 170.286 ↑ 1.0 500 1

Nested Loop Left Join (cost=89.11..4,722.20 rows=512 width=437) (actual time=3.475..170.286 rows=500 loops=1)

  • Join Filter: (aliastransacionadores.numerocpfcnpj = aliasdebitossemtransacionador.numerocpfcnpjdebito)
  • Rows Removed by Join Filter: 249500
34. 0.147 6.421 ↑ 1.0 500 1

Hash Left Join (cost=89.11..4,246.69 rows=512 width=397) (actual time=3.383..6.421 rows=500 loops=1)

  • Hash Cond: ((aliastransacionadores.codigotransacionador = aliaslimiteconvenioextra.codigotransacionadorlimite) AND (aliasconvenioextra.codigoportadorconvenioextra = aliaslimiteconvenioextra.codigoportadorlimite))
35. 0.139 6.274 ↑ 1.0 500 1

Nested Loop Left Join (cost=44.88..4,198.61 rows=512 width=377) (actual time=3.372..6.274 rows=500 loops=1)

  • Join Filter: (aliaslimitecrediario.codigotransacionadorlimite = aliastransacionadores.codigotransacionador)
36. 0.153 6.135 ↑ 1.0 500 1

Nested Loop Left Join (cost=44.88..4,138.13 rows=512 width=357) (actual time=3.372..6.135 rows=500 loops=1)

  • Join Filter: (aliaslimitecheque.codigotransacionadorlimite = aliastransacionadores.codigotransacionador)
37. 0.135 5.982 ↑ 1.0 500 1

Hash Left Join (cost=44.88..4,077.64 rows=512 width=337) (actual time=3.372..5.982 rows=500 loops=1)

  • Hash Cond: ((aliastransacionadores.codigotransacionador = aliasconvenioextra.codigotransacionadorextra) AND (transacionadorcartaopdv.cd_portador = aliasconvenioextra.codigoportadorextra))
38. 0.183 5.724 ↑ 1.0 500 1

Hash Left Join (cost=44.81..4,073.72 rows=512 width=323) (actual time=3.235..5.724 rows=500 loops=1)

  • Hash Cond: ((aliastransacionadores.codigotransacionador = aliaslimiteconvenio.codigotransacionadorlimite) AND (transacionadorcartaopdv.cd_portador = aliaslimiteconvenio.codigoportadorlimite))
39. 0.000 2.619 ↑ 1.0 500 1

Nested Loop Left Join (cost=0.58..4,025.65 rows=512 width=303) (actual time=0.301..2.619 rows=500 loops=1)

  • Join Filter: (transacionadorcartaotitular.cd_portador = transacionadorcartaopdv.cd_portador)
40. 0.280 1.663 ↑ 1.0 500 1

Nested Loop Left Join (cost=0.29..1,992.75 rows=512 width=296) (actual time=0.298..1.663 rows=500 loops=1)

41. 0.383 0.383 ↑ 1.0 500 1

CTE Scan on aliastransacionadores (cost=0.00..10.00 rows=500 width=246) (actual time=0.287..0.383 rows=500 loops=1)

42. 1.000 1.000 ↓ 0.0 0 500

Index Scan using transacionadorcartaopdv_idx003 on tb_transacionadorcartaopdv transacionadorcartaopdv (cost=0.29..3.96 rows=1 width=50) (actual time=0.002..0.002 rows=0 loops=500)

  • Index Cond: (cd_transacionador = aliastransacionadores.codigotransacionador)
  • Filter: (dt_exclusao IS NULL)
43. 1.000 1.000 ↓ 0.0 0 500

Index Scan using transacionadorcartaopdv_idx003 on tb_transacionadorcartaopdv transacionadorcartaotitular (cost=0.29..3.96 rows=1 width=19) (actual time=0.002..0.002 rows=0 loops=500)

  • Index Cond: (cd_transacionador = aliastransacionadores.codigotransacionador)
  • Filter: ((dt_exclusao IS NULL) AND (id_titulardependente = '0'::numeric))
44. 0.000 2.922 ↓ 0.0 0 1

Hash (cost=44.08..44.08 rows=10 width=48) (actual time=2.922..2.922 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
45. 2.922 2.922 ↓ 0.0 0 1

CTE Scan on aliaslimites aliaslimiteconvenio (cost=0.00..44.08 rows=10 width=48) (actual time=2.922..2.922 rows=0 loops=1)

  • Filter: (identificadortipolimite = '1'::numeric)
46. 0.000 0.123 ↓ 0.0 0 1

Hash (cost=0.04..0.04 rows=2 width=42) (actual time=0.123..0.123 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
47. 0.123 0.123 ↓ 0.0 0 1

CTE Scan on aliasconvenioextra (cost=0.00..0.04 rows=2 width=42) (actual time=0.123..0.123 rows=0 loops=1)

48. 0.000 0.000 ↓ 0.0 0 500

CTE Scan on aliaslimites aliaslimitecheque (cost=0.00..48.98 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=500)

  • Filter: ((codigoportadorlimite = '0'::numeric) AND (identificadortipolimite = '2'::numeric))
49. 0.000 0.000 ↓ 0.0 0 500

CTE Scan on aliaslimites aliaslimitecrediario (cost=0.00..48.98 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=500)

  • Filter: ((codigoportadorlimite = '0'::numeric) AND (identificadortipolimite = '3'::numeric))
50. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=44.08..44.08 rows=10 width=48) (actual time=0.000..0.000 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
51. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on aliaslimites aliaslimiteconvenioextra (cost=0.00..44.08 rows=10 width=48) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (identificadortipolimite = '4'::numeric)
52. 129.500 129.500 ↓ 500.0 500 500

CTE Scan on aliasdebitostransacionador aliasdebitossemtransacionador (cost=0.00..464.00 rows=1 width=60) (actual time=0.000..0.259 rows=500 loops=500)

  • Filter: ((codigotransacionadordebito = '0'::numeric) AND (identificadortipolimitedebito = '2'::numeric))
  • Rows Removed by Filter: 500
53. 133.000 133.000 ↓ 500.0 500 500

CTE Scan on aliasdebitostransacionador aliasdebitoscheque (cost=0.00..464.00 rows=1 width=88) (actual time=0.000..0.266 rows=500 loops=500)

  • Filter: ((codigoportadordebito = '0'::numeric) AND (identificadortipolimitedebito = '2'::numeric))
  • Rows Removed by Filter: 500
54. 7,038.000 7,038.000 ↓ 500.0 500 500

CTE Scan on aliasdebitostransacionador aliasdebitoscrediario (cost=0.00..464.00 rows=1 width=68) (actual time=0.004..14.076 rows=500 loops=500)

  • Filter: ((codigoportadordebito = '0'::numeric) AND (identificadortipolimitedebito = '3'::numeric))
  • Rows Removed by Filter: 500
55. 0.000 0.191 ↓ 0.0 0 1

Hash (cost=417.60..417.60 rows=93 width=82) (actual time=0.191..0.191 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
56. 0.191 0.191 ↓ 0.0 0 1

CTE Scan on aliasdebitostransacionador aliasdebitosconvenio (cost=0.00..417.60 rows=93 width=82) (actual time=0.191..0.191 rows=0 loops=1)

  • Filter: (identificadortipolimitedebito = '1'::numeric)
  • Rows Removed by Filter: 1000
57. 0.000 0.197 ↓ 0.0 0 1

Hash (cost=417.60..417.60 rows=93 width=82) (actual time=0.197..0.197 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
58. 0.197 0.197 ↓ 0.0 0 1

CTE Scan on aliasdebitostransacionador aliasdebitosconvenioextra (cost=0.00..417.60 rows=93 width=82) (actual time=0.197..0.197 rows=0 loops=1)

  • Filter: (identificadortipolimitedebito = '4'::numeric)
  • Rows Removed by Filter: 1000
Planning time : 8.737 ms
Execution time : 7,425.500 ms