explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kkkx

Settings
# exclusive inclusive rows x rows loops node
1. 1,614.335 15,264.477 ↓ 64.0 32,000 1

Nested Loop Left Join (cost=60,981.23..110,011.45 rows=500 width=843) (actual time=1,439.313..15,264.477 rows=32,000 loops=1)

  • Join Filter: ((aliastransacionadores.codigotransacionador <> '0'::numeric) AND (aliasdebitoscrediario.codigotransacionadordebito = aliastransacionadores.codigotransacionador))
  • Rows Removed by Join Filter: 7968000
2.          

CTE aliastransacionadores

3. 320.628 320.628 ↑ 1.0 500 1

CTE Scan on codigostransacionadorcompletos (cost=6,062.06..6,072.06 rows=500 width=250) (actual time=5.701..320.628 rows=500 loops=1)

4.          

CTE transacionadores_pendentes

5. 0.157 3.308 ↑ 1.0 500 1

Limit (cost=226.55..254.81 rows=500 width=11) (actual time=2.621..3.308 rows=500 loops=1)

6. 3.151 3.151 ↑ 28.7 4,500 1

Index Only Scan using transacionadorcargapendpdv_pk on tb_transacionadorcargapendpdv transacionadorcargapendentepdv (cost=0.42..7,305.47 rows=129,220 width=11) (actual time=0.105..3.151 rows=4,500 loops=1)

  • Heap Fetches: 4500
7.          

CTE codigostransacionadorcompletos

8. 70.653 320.147 ↑ 1.0 500 1

Nested Loop Left Join (cost=0.71..5,807.25 rows=500 width=250) (actual time=5.698..320.147 rows=500 loops=1)

  • Join Filter: (transacionadores_pendentes.codigotransacionador = '0'::numeric)
  • Rows Removed by Join Filter: 676000
9. 0.447 5.994 ↑ 1.0 500 1

Nested Loop Left Join (cost=0.29..2,773.00 rows=500 width=86) (actual time=2.707..5.994 rows=500 loops=1)

10. 3.547 3.547 ↑ 1.0 500 1

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

11. 2.000 2.000 ↑ 1.0 1 500

Index Scan using transacionadorbasicopdv_pk on tb_transacionadorbasicopdv transacionadorbasicopdv (cost=0.29..5.53 rows=1 width=46) (actual time=0.004..0.004 rows=1 loops=500)

  • Index Cond: (transacionadores_pendentes.codigotransacionador = cd_transacionador)
  • Filter: (dt_exclusao IS NULL)
12. 243.500 243.500 ↓ 1,352.0 1,352 500

Index Scan using transacionadorbasicopdv_idx001 on tb_transacionadorbasicopdv transacionadordocumento (cost=0.42..6.05 rows=1 width=46) (actual time=0.014..0.487 rows=1,352 loops=500)

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

CTE aliaslimites

14. 0.448 2.591 ↓ 1.0 2,000 1

Nested Loop (cost=0.42..7,682.82 rows=1,996 width=17) (actual time=0.033..2.591 rows=2,000 loops=1)

15. 0.143 0.143 ↓ 1.0 500 1

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

  • Filter: (codigotransacionador <> '0'::numeric)
16. 2.000 2.000 ↑ 1.0 4 500

Index Scan using transacionadorlimitepdv_pk on tb_transacionadorlimitepdv transacionadorlimitepdv (cost=0.42..15.36 rows=4 width=17) (actual time=0.003..0.004 rows=4 loops=500)

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

CTE aliasdebitostransacionador

18. 0.096 89.755 ↑ 329.9 1,500 1

Append (cost=33.98..29,935.11 rows=494,784 width=28) (actual time=87.264..89.755 rows=1,500 loops=1)

19. 33.530 87.225 ↓ 0.0 0 1

Merge Join (cost=33.98..16,129.50 rows=493,279 width=28) (actual time=87.225..87.225 rows=0 loops=1)

  • Merge Cond: (transacionadordebitopdv.nr_cpfcnpj = aliastransacionadores_2.numerocpfcnpj)
20. 53.423 53.423 ↑ 1.0 196,955 1

Index Scan using transacionadordebitopdv_idx002 on tb_transacionadordebitopdv transacionadordebitopdv (cost=0.42..8,201.50 rows=198,104 width=28) (actual time=0.028..53.423 rows=196,955 loops=1)

  • Filter: (dt_exclusao IS NULL)
21. 0.180 0.272 ↑ 1.0 489 1

Sort (cost=33.56..34.81 rows=498 width=32) (actual time=0.240..0.272 rows=489 loops=1)

  • Sort Key: aliastransacionadores_2.numerocpfcnpj
  • Sort Method: quicksort Memory: 48kB
22. 0.092 0.092 ↓ 1.0 500 1

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

  • Filter: (numerocpfcnpj <> '0'::numeric)
23. 0.284 2.434 ↑ 1.0 1,500 1

Nested Loop (cost=0.42..6,383.85 rows=1,505 width=28) (actual time=0.036..2.434 rows=1,500 loops=1)

24. 0.150 0.150 ↓ 1.0 500 1

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

  • Filter: (codigotransacionador <> '0'::numeric)
25. 2.000 2.000 ↑ 1.0 3 500

Index Scan using transacionadordebitopdv_pk on tb_transacionadordebitopdv transacionadordebitopdv_1 (cost=0.42..12.77 rows=3 width=28) (actual time=0.003..0.004 rows=3 loops=500)

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

CTE aliasconvenioextra

27. 0.014 0.030 ↓ 0.0 0 1

Hash Join (cost=17.15..29.13 rows=10 width=42) (actual time=0.030..0.030 rows=0 loops=1)

  • Hash Cond: (aliastransacionadores_4.codigotransacionador = transacionadorconvenioextrapdv.cd_transacionador)
28. 0.000 0.000 ↑ 500.0 1 1

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

29. 0.001 0.016 ↓ 0.0 0 1

Hash (cost=17.10..17.10 rows=4 width=42) (actual time=0.015..0.016 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
30. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on tb_transacionadorconvextrapdv transacionadorconvenioextrapdv (cost=0.00..17.10 rows=4 width=42) (actual time=0.015..0.015 rows=0 loops=1)

  • Filter: (dt_exclusao IS NULL)
31. 1,326.207 7,858.142 ↓ 32.0 16,000 1

Nested Loop Left Join (cost=17,262.11..53,770.35 rows=500 width=586) (actual time=1,439.309..7,858.142 rows=16,000 loops=1)

  • Join Filter: (((aliastransacionadores.codigotransacionador <> '0'::numeric) AND (aliasdebitoscheque.codigotransacionadordebito = aliastransacionadores.codigotransacionador)) OR ((aliasdebitoscheque.numerocpfcnpjdebito = aliastransacionadores.numero (...)
  • Rows Removed by Join Filter: 3984000
32. 9.163 3,587.935 ↓ 16.0 8,000 1

Nested Loop Left Join (cost=17,262.11..41,220.87 rows=500 width=532) (actual time=1,439.306..3,587.935 rows=8,000 loops=1)

  • Join Filter: (aliastransacionadores.numerocpfcnpj = aliasdebitossemtransacionador.numerocpfcnpjdebito)
33. 11.223 1,450.772 ↓ 16.0 8,000 1

Hash Right Join (cost=17,262.11..28,716.39 rows=500 width=492) (actual time=1,439.032..1,450.772 rows=8,000 loops=1)

  • Hash Cond: ((aliasdebitosconvenio.codigotransacionadordebito = aliastransacionadores.codigotransacionador) AND (aliasdebitosconvenio.codigoportadordebito = transacionadorcartaopdv.cd_portador))
  • Join Filter: (aliastransacionadores.codigotransacionador <> '0'::numeric)
34. 0.532 0.532 ↑ 4.9 500 1

CTE Scan on aliasdebitostransacionador aliasdebitosconvenio (cost=0.00..11,132.64 rows=2,474 width=82) (actual time=0.002..0.532 rows=500 loops=1)

  • Filter: (identificadortipolimitedebito = '1'::numeric)
  • Rows Removed by Filter: 1000
35. 3.774 1,439.017 ↓ 8.0 4,000 1

Hash (cost=17,254.61..17,254.61 rows=500 width=438) (actual time=1,439.017..1,439.017 rows=4,000 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 619kB
36. 0.870 1,435.243 ↓ 8.0 4,000 1

Merge Left Join (cost=11,445.51..17,254.61 rows=500 width=438) (actual time=416.949..1,435.243 rows=4,000 loops=1)

  • Merge Cond: (aliastransacionadores.codigotransacionador = aliasdebitosconvenioextra.codigotransacionadordebito)
  • Join Filter: ((aliastransacionadores.codigotransacionador <> '0'::numeric) AND (aliasdebitosconvenioextra.codigoportadordebito = aliasconvenioextra.codigoportadorconvenioextra))
37. 0.707 1,343.728 ↓ 8.0 4,000 1

Merge Left Join (cost=173.42..5,857.58 rows=500 width=398) (actual time=326.302..1,343.728 rows=4,000 loops=1)

  • Merge Cond: (aliastransacionadores.codigotransacionador = aliaslimiteconvenioextra.codigotransacionadorlimite)
  • Join Filter: (aliaslimiteconvenioextra.codigoportadorlimite = aliasconvenioextra.codigoportadorconvenioextra)
38. 140.245 1,342.728 ↓ 8.0 4,000 1

Nested Loop Left Join (cost=128.35..5,810.82 rows=500 width=378) (actual time=326.008..1,342.728 rows=4,000 loops=1)

  • Join Filter: (aliaslimitecrediario.codigotransacionadorlimite = aliastransacionadores.codigotransacionador)
  • Rows Removed by Join Filter: 996000
39. 1.443 336.483 ↓ 4.0 2,000 1

Merge Left Join (cost=128.35..5,749.68 rows=500 width=358) (actual time=326.005..336.483 rows=2,000 loops=1)

  • Merge Cond: (aliastransacionadores.codigotransacionador = aliaslimiteconvenio.codigotransacionadorlimite)
  • Join Filter: (aliaslimiteconvenio.codigoportadorlimite = transacionadorcartaopdv.cd_portador)
40. 0.259 334.351 ↓ 2.0 1,000 1

Merge Left Join (cost=83.27..5,702.91 rows=500 width=338) (actual time=325.519..334.351 rows=1,000 loops=1)

  • Merge Cond: (aliastransacionadores.codigotransacionador = aliasconvenioextra.codigotransacionadorextra)
  • Join Filter: (aliasconvenioextra.codigoportadorextra = transacionadorcartaopdv.cd_portador)
41. 1.156 334.055 ↓ 2.0 1,000 1

Nested Loop Left Join (cost=82.90..5,700.86 rows=500 width=324) (actual time=325.481..334.055 rows=1,000 loops=1)

  • Join Filter: (transacionadorcartaotitular.cd_portador = transacionadorcartaopdv.cd_portador)
42. 0.561 330.899 ↓ 2.0 1,000 1

Nested Loop Left Join (cost=82.61..2,891.61 rows=500 width=317) (actual time=325.468..330.899 rows=1,000 loops=1)

43. 0.632 326.338 ↓ 2.0 1,000 1

Merge Left Join (cost=82.32..84.86 rows=500 width=266) (actual time=325.447..326.338 rows=1,000 loops=1)

  • Merge Cond: (aliastransacionadores.codigotransacionador = aliaslimitecheque.codigotransacionadorlimite)
44. 0.708 321.658 ↑ 1.0 500 1

Sort (cost=32.41..33.66 rows=500 width=246) (actual time=321.613..321.658 rows=500 loops=1)

  • Sort Key: aliastransacionadores.codigotransacionador
  • Sort Method: quicksort Memory: 68kB
45. 320.950 320.950 ↑ 1.0 500 1

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

46. 0.186 4.048 ↓ 999.0 999 1

Materialize (cost=49.91..49.92 rows=1 width=34) (actual time=3.831..4.048 rows=999 loops=1)

47. 0.243 3.862 ↓ 500.0 500 1

Sort (cost=49.91..49.92 rows=1 width=34) (actual time=3.823..3.862 rows=500 loops=1)

  • Sort Key: aliaslimitecheque.codigotransacionadorlimite
  • Sort Method: quicksort Memory: 48kB
48. 3.619 3.619 ↓ 500.0 500 1

CTE Scan on aliaslimites aliaslimitecheque (cost=0.00..49.90 rows=1 width=34) (actual time=0.036..3.619 rows=500 loops=1)

  • Filter: ((codigoportadorlimite = '0'::numeric) AND (identificadortipolimite = '2'::numeric))
  • Rows Removed by Filter: 1500
49. 4.000 4.000 ↑ 1.0 1 1,000

Index Scan using transacionadorcartaopdv_idx003 on tb_transacionadorcartaopdv transacionadorcartaopdv (cost=0.29..5.60 rows=1 width=51) (actual time=0.004..0.004 rows=1 loops=1,000)

  • Index Cond: (cd_transacionador = aliastransacionadores.codigotransacionador)
  • Filter: (dt_exclusao IS NULL)
50. 2.000 2.000 ↑ 1.0 1 1,000

Index Scan using transacionadorcartaopdv_idx003 on tb_transacionadorcartaopdv transacionadorcartaotitular (cost=0.29..5.61 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=1,000)

  • Index Cond: (cd_transacionador = aliastransacionadores.codigotransacionador)
  • Filter: ((dt_exclusao IS NULL) AND (id_titulardependente = '0'::numeric))
51. 0.006 0.037 ↓ 0.0 0 1

Sort (cost=0.37..0.39 rows=10 width=42) (actual time=0.037..0.037 rows=0 loops=1)

  • Sort Key: aliasconvenioextra.codigotransacionadorextra
  • Sort Method: quicksort Memory: 25kB
52. 0.031 0.031 ↓ 0.0 0 1

CTE Scan on aliasconvenioextra (cost=0.00..0.20 rows=10 width=42) (actual time=0.031..0.031 rows=0 loops=1)

53. 0.335 0.689 ↓ 199.7 1,997 1

Sort (cost=45.08..45.10 rows=10 width=48) (actual time=0.484..0.689 rows=1,997 loops=1)

  • Sort Key: aliaslimiteconvenio.codigotransacionadorlimite
  • Sort Method: quicksort Memory: 48kB
54. 0.354 0.354 ↓ 50.0 500 1

CTE Scan on aliaslimites aliaslimiteconvenio (cost=0.00..44.91 rows=10 width=48) (actual time=0.002..0.354 rows=500 loops=1)

  • Filter: (identificadortipolimite = '1'::numeric)
  • Rows Removed by Filter: 1500
55. 866.000 866.000 ↓ 500.0 500 2,000

CTE Scan on aliaslimites aliaslimitecrediario (cost=0.00..49.90 rows=1 width=34) (actual time=0.001..0.433 rows=500 loops=2,000)

  • Filter: ((codigoportadorlimite = '0'::numeric) AND (identificadortipolimite = '3'::numeric))
  • Rows Removed by Filter: 1500
56. 0.007 0.293 ↓ 0.0 0 1

Sort (cost=45.08..45.10 rows=10 width=48) (actual time=0.293..0.293 rows=0 loops=1)

  • Sort Key: aliaslimiteconvenioextra.codigotransacionadorlimite
  • Sort Method: quicksort Memory: 25kB
57. 0.286 0.286 ↓ 0.0 0 1

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

  • Filter: (identificadortipolimite = '4'::numeric)
  • Rows Removed by Filter: 2000
58. 0.011 90.645 ↓ 0.0 0 1

Sort (cost=11,272.08..11,278.27 rows=2,474 width=82) (actual time=90.645..90.645 rows=0 loops=1)

  • Sort Key: aliasdebitosconvenioextra.codigotransacionadordebito
  • Sort Method: quicksort Memory: 25kB
59. 90.634 90.634 ↓ 0.0 0 1

CTE Scan on aliasdebitostransacionador aliasdebitosconvenioextra (cost=0.00..11,132.64 rows=2,474 width=82) (actual time=90.633..90.634 rows=0 loops=1)

  • Filter: (identificadortipolimitedebito = '4'::numeric)
  • Rows Removed by Filter: 1500
60. 2,128.000 2,128.000 ↓ 0.0 0 8,000

CTE Scan on aliasdebitostransacionador aliasdebitossemtransacionador (cost=0.00..12,369.60 rows=12 width=60) (actual time=0.266..0.266 rows=0 loops=8,000)

  • Filter: ((codigotransacionadordebito = '0'::numeric) AND (identificadortipolimitedebito = '2'::numeric))
  • Rows Removed by Filter: 1500
61. 2,944.000 2,944.000 ↓ 41.7 500 8,000

CTE Scan on aliasdebitostransacionador aliasdebitoscheque (cost=0.00..12,369.60 rows=12 width=88) (actual time=0.000..0.368 rows=500 loops=8,000)

  • Filter: ((codigoportadordebito = '0'::numeric) AND (identificadortipolimitedebito = '2'::numeric))
  • Rows Removed by Filter: 1000
62. 5,792.000 5,792.000 ↓ 41.7 500 16,000

CTE Scan on aliasdebitostransacionador aliasdebitoscrediario (cost=0.00..12,369.60 rows=12 width=68) (actual time=0.001..0.362 rows=500 loops=16,000)

  • Filter: ((codigoportadordebito = '0'::numeric) AND (identificadortipolimitedebito = '3'::numeric))
  • Rows Removed by Filter: 1000
Planning time : 14.089 ms
Execution time : 15,266.629 ms