explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l2XB

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=14,881.07..14,881.08 rows=1 width=350) (actual rows= loops=)

  • Sort Key: tdt.dataconsulta DESC
2. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=13,964.10..14,881.05 rows=1 width=350) (actual rows= loops=)

  • Hash Cond: ((tdt.chave)::text = concat(reverse("substring"(reverse(concat('000000000000000', fa.cnpj)), 1, 15)), '_', reverse("substring"(reverse(concat('00000', fa.agencia)), 1, 5)), '_', reverse("substring"(reverse(concat('000000000', fa.conta)), 1, 9))))
3.          

CTE allvalidposition

4. 0.000 0.000 ↓ 0.0

HashAggregate (cost=13,939.03..13,941.03 rows=200 width=88) (actual rows= loops=)

  • Group Key: vp.chave
5.          

CTE validposition

6. 0.000 0.000 ↓ 0.0

Unique (cost=13,022.88..13,540.71 rows=15,933 width=1,340) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=13,022.88..13,062.72 rows=15,933 width=1,340) (actual rows= loops=)

  • Sort Key: tdt_2.chave, tdt_2.cnpj, tdt_2.nome, tdt_2.agencia, tdt_2.conta, tdt_2.saldoinicial, tdt_2.credito, tdt_2.debito, tdt_2.total, tdt_2.dataconsulta, tdt_2.loginbradesco, tdt_2.url_s3
8. 0.000 0.000 ↓ 0.0

Append (cost=0.00..2,597.28 rows=15,933 width=1,340) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on saldos tdt_2 (cost=0.00..759.54 rows=15,813 width=342) (actual rows= loops=)

  • Filter: (total IS NOT NULL)
10. 0.000 0.000 ↓ 0.0

Seq Scan on saldos tdt1 (cost=799.07..1,598.75 rows=120 width=342) (actual rows= loops=)

  • Filter: ((total IS NULL) AND (NOT (hashed SubPlan 1)))
11.          

SubPlan (for Seq Scan)

12. 0.000 0.000 ↓ 0.0

Seq Scan on saldos tdt_1 (cost=0.00..759.54 rows=15,813 width=31) (actual rows= loops=)

  • Filter: (total IS NOT NULL)
13. 0.000 0.000 ↓ 0.0

CTE Scan on validposition vp (cost=0.00..318.66 rows=15,933 width=88) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash Join (cost=11.56..128.55 rows=1 width=422) (actual rows= loops=)

  • Hash Cond: (((tdt.chave)::text = (avp.chave)::text) AND (tdt.dataconsulta = avp.lastvalidupdate))
15. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on saldos tdt (cost=4.56..119.61 rows=35 width=342) (actual rows= loops=)

  • Recheck Cond: ((cnpj)::text = '025080536000195'::text)
16. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_cnpj (cost=0.00..4.55 rows=35 width=0) (actual rows= loops=)

  • Index Cond: ((cnpj)::text = '025080536000195'::text)
17. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=88) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

CTE Scan on allvalidposition avp (cost=0.00..4.00 rows=200 width=88) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash (cost=9.56..9.56 rows=156 width=30) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on fundosatualizados fa (cost=0.00..9.56 rows=156 width=30) (actual rows= loops=)

  • Filter: ativo
21.          

SubPlan (for Hash Semi Join)

22. 0.000 0.000 ↓ 0.0

Aggregate (cost=799.92..799.93 rows=1 width=8) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on saldos tdt2 (cost=0.00..799.67 rows=99 width=8) (actual rows= loops=)