explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NIgQ

Settings
# exclusive inclusive rows x rows loops node
1. 20.820 8,366.996 ↓ 0.0 0 1

Insert on unidade_negocio_registros_alterados (cost=80,907,239.89..80,927,183.12 rows=1,254 width=12) (actual time=8,366.996..8,366.996 rows=0 loops=1)

2.          

CTE tanque

3. 2.990 2,587.554 ↑ 5.7 1,319 1

Group (cost=0.56..57,638,746.11 rows=7,525 width=16) (actual time=4.513..2,587.554 rows=1,319 loops=1)

  • Group Key: tan.tan_cd_tanque
4. 2.715 9.876 ↑ 5.7 1,319 1

Nested Loop (cost=0.56..11,427.29 rows=7,525 width=12) (actual time=0.067..9.876 rows=1,319 loops=1)

5. 2.202 2.202 ↑ 6.0 1,653 1

Index Scan using tanque_pkey on tanque tan (cost=0.29..7,937.20 rows=9,882 width=12) (actual time=0.018..2.202 rows=1,653 loops=1)

6. 4.959 4.959 ↑ 1.0 1 1,653

Index Scan using unidade_negocio_pkey on unidade_negocio unn (cost=0.27..0.35 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,653)

  • Index Cond: (unn_cd_unidade_negocio = tan.tan_cd_unidade_negocio)
  • Filter: (unn_fl_ativo = 'S'::bpchar)
  • Rows Removed by Filter: 0
7.          

SubPlan (for Group)

8. 75.183 2,574.688 ↑ 1.0 1 1,319

Aggregate (cost=7,658.10..7,658.11 rows=1 width=4) (actual time=1.952..1.952 rows=1 loops=1,319)

9. 256.522 2,499.505 ↑ 1.1 745 1,319

Nested Loop (cost=0.85..7,656.05 rows=822 width=4) (actual time=0.014..1.895 rows=745 loops=1,319)

10. 278.309 278.309 ↑ 1.1 745 1,319

Index Only Scan using lmc_tanque_lmt_cd_tanque_lmt_cd_lmc_idx on lmc_tanque lmt (cost=0.43..996.31 rows=822 width=4) (actual time=0.008..0.211 rows=745 loops=1,319)

  • Index Cond: (lmt_cd_tanque = tan.tan_cd_tanque)
  • Heap Fetches: 310,738
11. 1,964.674 1,964.674 ↑ 1.0 1 982,337

Index Scan using lmc_pkey on lmc (cost=0.42..8.10 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=982,337)

  • Index Cond: (lmc_cd_lmc = lmt.lmt_cd_lmc)
12.          

CTE sel

13. 2,594.647 8,287.663 ↑ 2.5 987 1

CTE Scan on tanque (cost=0.00..23,266,389.03 rows=2,508 width=36) (actual time=15.297..8,287.663 rows=987 loops=1)

  • Filter: (lmc_dt_lmc > (CURRENT_DATE - 31))
  • Rows Removed by Filter: 332
14.          

SubPlan (for CTE Scan)

15. 3.555 57.246 ↑ 1.0 1 987

Nested Loop (cost=0.85..59.54 rows=1 width=6) (actual time=0.051..0.058 rows=1 loops=987)

16. 50.337 50.337 ↓ 2.0 2 987

Index Scan using lmc_idx on lmc lmc_1 (cost=0.42..51.07 rows=1 width=4) (actual time=0.039..0.051 rows=2 loops=987)

  • Index Cond: ((lmc_cd_produto_lmc = tanque.lmc_cd_produto_lmc) AND (lmc_dt_lmc = tanque.lmc_dt_lmc))
17. 3.354 3.354 ↑ 1.0 1 1,677

Index Scan using lmc_tanque_lmt_cd_tanque_lmt_cd_lmc_idx on lmc_tanque lmt_1 (cost=0.43..8.45 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=1,677)

  • Index Cond: ((lmt_cd_tanque = tanque.tan_cd_tanque) AND (lmt_cd_lmc = lmc_1.lmc_cd_lmc))
18. 1.974 5,623.926 ↑ 1.0 1 987

Aggregate (cost=8,327.77..8,327.78 rows=1 width=32) (actual time=5.698..5.698 rows=1 loops=987)

19. 240.545 5,621.952 ↑ 83.0 1 987

Nested Loop (cost=1.27..8,327.56 rows=83 width=5) (actual time=5.649..5.696 rows=1 loops=987)

20. 278.051 4,955.727 ↑ 1.1 216 987

Nested Loop (cost=0.85..8,202.49 rows=248 width=9) (actual time=1.997..5.021 rows=216 loops=987)

21. 4,251.996 4,251.996 ↑ 1.1 216 987

Index Scan using nota_entrada_item_tanque_unique on nota_entrada_item_tanque nit (cost=0.42..6,132.13 rows=248 width=9) (actual time=1.991..4.308 rows=216 loops=987)

  • Index Cond: (nit_cd_tanque = tanque.tan_cd_tanque)
22. 425.680 425.680 ↑ 1.0 1 212,840

Index Scan using nota_entrada_item_pkey on nota_entrada_item nei (cost=0.43..8.35 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=212,840)

  • Index Cond: (nei_cd_nota_entrada_item = nit.nit_cd_nota_entrada_item)
23. 425.680 425.680 ↓ 0.0 0 212,840

Index Scan using nota_entrada_pkey on nota_entrada nte (cost=0.42..0.50 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=212,840)

  • Index Cond: (nte_cd_nota_entrada = nei.nei_cd_nota_entrada)
  • Filter: (nte_dt_entrada > tanque.lmc_dt_lmc)
  • Rows Removed by Filter: 1
24. 2.961 11.844 ↑ 1.0 1 987

Aggregate (cost=889.46..889.47 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=987)

25. 8.883 8.883 ↑ 53.2 5 987

Index Scan using venda_tanque_vet_cd_tanque_vet_dt_fiscal_idx on venda_tanque vet (cost=0.43..888.79 rows=266 width=7) (actual time=0.005..0.009 rows=5 loops=987)

  • Index Cond: ((vet_cd_tanque = tanque.tan_cd_tanque) AND (vet_dt_fiscal > tanque.lmc_dt_lmc))
26.          

CTE upd

27. 34.609 8,330.684 ↑ 2.5 987 1

Update on tanque tanque_1 (cost=1,972.35..2,104.33 rows=2,508 width=224) (actual time=17.358..8,330.684 rows=987 loops=1)

28. 5.328 8,296.075 ↑ 2.5 987 1

Hash Join (cost=1,972.35..2,104.33 rows=2,508 width=224) (actual time=17.147..8,296.075 rows=987 loops=1)

  • Hash Cond: (sel.tanque = tanque_1.tan_cd_tanque)
29. 8,288.985 8,288.985 ↑ 2.5 987 1

CTE Scan on sel (cost=0.00..50.16 rows=2,508 width=96) (actual time=15.308..8,288.985 rows=987 loops=1)

30. 0.368 1.762 ↑ 6.0 1,653 1

Hash (cost=1,848.82..1,848.82 rows=9,882 width=48) (actual time=1.762..1.762 rows=1,653 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 265kB
31. 1.394 1.394 ↑ 6.0 1,653 1

Seq Scan on tanque tanque_1 (cost=0.00..1,848.82 rows=9,882 width=48) (actual time=0.006..1.394 rows=1,653 loops=1)

32. 4.035 8,346.176 ↑ 1.9 662 1

Nested Loop Anti Join (cost=0.41..19,943.64 rows=1,254 width=12) (actual time=17.460..8,346.176 rows=662 loops=1)

33. 8,331.284 8,331.284 ↑ 2.5 987 1

CTE Scan on upd (cost=0.00..50.16 rows=2,508 width=4) (actual time=17.359..8,331.284 rows=987 loops=1)

34. 10.857 10.857 ↓ 0.0 0 987

Index Only Scan using unidade_negocio_registros_alt_ura_cd_unidade_negocio_ura_c_idx1 on unidade_negocio_registros_alterados u (cost=0.41..15.65 rows=2 width=4) (actual time=0.011..0.011 rows=0 loops=987)

  • Index Cond: ((ura_cd_unidade_negocio = upd.tan_cd_unidade_negocio) AND (ura_cd_tipo = 7))
  • Heap Fetches: 2,346
Planning time : 1.285 ms
Execution time : 8,374.810 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint unidade_negocio_registros_alterados_ura_cd_unidade_negocio_fkey on unidade_negocio_registros_alterados 6.315 ms 662 0.010 ms