explain.depesz.com

PostgreSQL's explain analyze made readable

Result: asoK

Settings
# exclusive inclusive rows x rows loops node
1. 13.870 24,442.275 ↓ 0.0 0 1

Update on altera_preco_produto (cost=58,929.29..59,189.62 rows=100 width=154) (actual time=24,442.275..24,442.275 rows=0 loops=1)

2.          

CTE dados

3. 0.036 24,426.229 ↑ 1.0 100 1

Limit (cost=0.84..58,928.87 rows=100 width=48) (actual time=425.815..24,426.229 rows=100 loops=1)

4. 1.209 24,426.193 ↑ 1.2 100 1

LockRows (cost=0.84..71,303.75 rows=121 width=48) (actual time=425.814..24,426.193 rows=100 loops=1)

5. 0.000 24,424.984 ↑ 1.2 100 1

Nested Loop (cost=0.84..71,302.54 rows=121 width=48) (actual time=425.787..24,424.984 rows=100 loops=1)

6. 77.259 77.259 ↓ 168.5 234,178 1

Index Scan using app_dt_alteracao_preco_null on altera_preco_produto app (cost=0.42..1,168.88 rows=1,390 width=18) (actual time=0.048..77.259 rows=234,178 loops=1)

  • Filter: (app_vl_preco_custo IS NULL)
7. 468.356 468.356 ↓ 0.0 0 234,178

Index Scan using altera_preco_pkey on altera_preco alp (cost=0.42..1.60 rows=1 width=26) (actual time=0.002..0.002 rows=0 loops=234,178)

  • Index Cond: (alp_cd_altera_preco = app.app_cd_altera_preco)
  • Filter: (alp_dt_alteracao >= '2020-02-01'::date)
  • Rows Removed by Filter: 1
8.          

SubPlan (for Nested Loop)

9. 0.200 23,922.300 ↑ 1.0 1 100

Limit (cost=561.18..561.21 rows=1 width=40) (actual time=239.223..239.223 rows=1 loops=100)

10. 3.800 23,922.100 ↑ 1.0 1 100

WindowAgg (cost=561.18..561.21 rows=1 width=40) (actual time=239.221..239.221 rows=1 loops=100)

11. 439.500 23,918.300 ↓ 11.0 11 100

Sort (cost=561.18..561.19 rows=1 width=40) (actual time=239.182..239.183 rows=11 loops=100)

  • Sort Key: ven.ven_ts_venda DESC
  • Sort Method: quicksort Memory: 23707kB
12. 358.280 23,478.800 ↓ 9,774.0 9,774 100

Nested Loop (cost=556.91..561.17 rows=1 width=40) (actual time=112.218..234.788 rows=9,774 loops=100)

13. 342.898 17,763.100 ↓ 5,357.0 5,357 100

Nested Loop (cost=556.91..559.16 rows=1 width=1,425) (actual time=112.182..177.631 rows=5,357 loops=100)

14. 316.800 11,341.800 ↓ 5,526.0 5,526 100

HashAggregate (cost=556.34..556.35 rows=1 width=4) (actual time=112.132..113.418 rows=5,526 loops=100)

  • Group Key: vei_1.vei_cd_venda
15. 798.700 11,025.000 ↓ 10,089.0 10,089 100

Bitmap Heap Scan on venda_item vei_1 (cost=555.22..556.34 rows=1 width=4) (actual time=102.734..110.250 rows=10,089 loops=100)

  • Recheck Cond: ((vei_cd_unidade_negocio = alp.alp_cd_unidade_negocio) AND (vei_dt_fiscal >= (alp.alp_dt_alteracao - '15 days'::interval)) AND (vei_dt_fiscal <= alp.alp_dt_alteracao) AND (vei_cd_produto = app.app_cd_produto))
  • Heap Blocks: exact=383855
16. 167.280 10,226.300 ↓ 0.0 0 100

BitmapAnd (cost=555.22..555.22 rows=1 width=0) (actual time=102.263..102.263 rows=0 loops=100)

17. 133.100 133.100 ↓ 10.5 14,691 100

Bitmap Index Scan on vei_cd_unidade_negocio_vei_dt_fiscal (cost=0.00..22.46 rows=1,399 width=0) (actual time=1.331..1.331 rows=14,691 loops=100)

  • Index Cond: ((vei_cd_unidade_negocio = alp.alp_cd_unidade_negocio) AND (vei_dt_fiscal >= (alp.alp_dt_alteracao - '15 days'::interval)) AND (vei_dt_fiscal <= alp.alp_dt_alteracao))
18. 9,925.920 9,925.920 ↓ 31.8 1,607,780 96

Bitmap Index Scan on venda_item_idx_produto_rebuild_1 (cost=0.00..532.50 rows=50,538 width=0) (actual time=103.395..103.395 rows=1,607,780 loops=96)

  • Index Cond: (vei_cd_produto = app.app_cd_produto)
19. 6,078.402 6,078.402 ↑ 1.0 1 552,582

Index Scan using venda_pkey on venda ven (cost=0.56..2.80 rows=1 width=1,429) (actual time=0.011..0.011 rows=1 loops=552,582)

  • Index Cond: (ven_cd_venda = vei_1.vei_cd_venda)
  • Filter: ((ven_fl_cancelada = 'N'::bpchar) AND (ven_cd_unidade_negocio = alp.alp_cd_unidade_negocio) AND (ven_ts_venda >= (alp.alp_dt_alteracao - '15 days'::interval)) AND (ven_ts_venda <= (alp.alp_dt_alteracao + alp.alp_tm_alteracao)))
  • Rows Removed by Filter: 0
20. 5,357.420 5,357.420 ↓ 2.0 2 535,742

Function Scan on jsonb_array_elements vei (cost=0.01..2.00 rows=1 width=32) (actual time=0.010..0.010 rows=2 loops=535,742)

  • Filter: (((value ->> 'vei_cd_produto'::text))::integer = app.app_cd_produto)
  • Rows Removed by Filter: 0
21. 0.488 24,428.405 ↑ 1.0 100 1

Nested Loop (cost=0.42..260.75 rows=100 width=154) (actual time=426.030..24,428.405 rows=100 loops=1)

22. 24,426.517 24,426.517 ↑ 1.0 100 1

CTE Scan on dados dad (cost=0.00..2.00 rows=100 width=96) (actual time=425.822..24,426.517 rows=100 loops=1)

23. 1.400 1.400 ↑ 1.0 1 100

Index Scan using altera_preco_produto_pkey on altera_preco_produto (cost=0.42..2.58 rows=1 width=50) (actual time=0.014..0.014 rows=1 loops=100)

  • Index Cond: (app_cd_altera_preco_produto = dad.codigo)
Planning time : 9.708 ms
Execution time : 24,445.681 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
altera_preco_produto_custo 1.665 ms 100 0.017 ms