explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 764A

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3,862.582 118,030.555 ↑ 1.0 462,749 1

Sort (cost=1,556,307.92..1,557,464.79 rows=462,749 width=300) (actual time=117,684.953..118,030.555 rows=462,749 loops=1)

  • Sort Key: tp_filter_sold_itens.asv_id_associado, ((tp_filter_sold_itens.asv_numero_nota)::character varying), (row_number() OVER (?))
  • Sort Method: external merge Disk: 120064kB
2. 78,632.245 114,167.973 ↑ 1.0 462,749 1

WindowAgg (cost=941,653.21..1,436,794.64 rows=462,749 width=300) (actual time=35,274.941..114,167.973 rows=462,749 loops=1)

3. 846.978 35,535.728 ↑ 1.0 462,749 1

Sort (cost=941,653.21..942,810.08 rows=462,749 width=300) (actual time=35,272.384..35,535.728 rows=462,749 loops=1)

  • Sort Key: tp_filter_sold_itens.asi_id_venda, tp_filter_sold_itens.asi_id
  • Sort Method: external sort Disk: 78216kB
4. 936.184 34,688.750 ↑ 1.0 462,749 1

WindowAgg (cost=807,100.59..822,139.93 rows=462,749 width=300) (actual time=33,517.304..34,688.750 rows=462,749 loops=1)

5. 1,096.594 33,752.566 ↑ 1.0 462,749 1

Sort (cost=807,100.59..808,257.46 rows=462,749 width=300) (actual time=33,517.260..33,752.566 rows=462,749 loops=1)

  • Sort Key: tp_filter_sold_itens.asi_id_venda
  • Sort Method: external merge Disk: 72600kB
6. 377.440 32,655.972 ↑ 1.0 462,749 1

Hash Join (cost=5,212.16..687,587.30 rows=462,749 width=300) (actual time=28.869..32,655.972 rows=462,749 loops=1)

  • Hash Cond: (associado.asd_id_cidade = cidade.cid_id)
7. 566.703 32,272.126 ↑ 1.0 462,749 1

Nested Loop (cost=4,956.78..680,969.13 rows=462,749 width=300) (actual time=22.430..32,272.126 rows=462,749 loops=1)

8. 313.274 3,940.483 ↑ 1.0 462,749 1

Hash Join (cost=4,956.35..76,334.75 rows=462,749 width=282) (actual time=22.401..3,940.483 rows=462,749 loops=1)

  • Hash Cond: (produto.prd_id_ncm = ncm.ncm_id)
9. 307.407 3,621.555 ↑ 1.0 462,749 1

Hash Join (cost=4,630.42..69,646.02 rows=462,749 width=282) (actual time=16.686..3,621.555 rows=462,749 loops=1)

  • Hash Cond: (produto.prd_id_unidade_medida = unidademedida.pru_id)
10. 387.313 3,314.140 ↑ 1.0 462,749 1

Hash Join (cost=4,629.31..63,282.11 rows=462,749 width=279) (actual time=16.666..3,314.140 rows=462,749 loops=1)

  • Hash Cond: (tp_filter_sold_itens.asv_id_associado = associado.asd_id)
11. 612.704 2,910.248 ↑ 1.0 462,749 1

Merge Join (cost=0.85..52,290.85 rows=462,749 width=260) (actual time=0.046..2,910.248 rows=462,749 loops=1)

  • Merge Cond: (tp_filter_sold_itens.asi_id_produto = produto.prd_id)
12. 1,613.345 1,613.345 ↑ 1.0 462,749 1

Index Scan using tfsi_asi_id_produto_associado_idx on tp_filter_sold_itens (cost=0.42..16,539.16 rows=462,749 width=252) (actual time=0.020..1,613.345 rows=462,749 loops=1)

13. 684.199 684.199 ↓ 1.7 1,031,545 1

Index Scan using idx_111409_primary on produto (cost=0.42..28,423.50 rows=617,530 width=12) (actual time=0.022..684.199 rows=1,031,545 loops=1)

14. 2.272 16.579 ↑ 1.0 6,537 1

Hash (cost=4,546.75..4,546.75 rows=6,537 width=23) (actual time=16.579..16.579 rows=6,537 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 422kB
15. 14.307 14.307 ↑ 1.0 6,537 1

Index Scan using idx_110642_primary on associado (cost=0.28..4,546.75 rows=6,537 width=23) (actual time=0.014..14.307 rows=6,537 loops=1)

16. 0.005 0.008 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=11) (actual time=0.008..0.008 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.003 0.003 ↑ 1.0 5 1

Seq Scan on unidademedida (cost=0.00..1.05 rows=5 width=11) (actual time=0.001..0.003 rows=5 loops=1)

18. 2.752 5.654 ↑ 1.0 9,419 1

Hash (cost=208.19..208.19 rows=9,419 width=8) (actual time=5.654..5.654 rows=9,419 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 496kB
19. 2.902 2.902 ↑ 1.0 9,419 1

Seq Scan on ncm (cost=0.00..208.19 rows=9,419 width=8) (actual time=0.002..2.902 rows=9,419 loops=1)

20. 27,764.940 27,764.940 ↑ 1.0 1 462,749

Index Scan using idx_111373_primary on pessoa (cost=0.44..1.30 rows=1 width=22) (actual time=0.059..0.060 rows=1 loops=462,749)

  • Index Cond: (pes_id = tp_filter_sold_itens.asv_id_consumidor)
21. 1.616 6.406 ↑ 1.0 5,566 1

Hash (cost=185.80..185.80 rows=5,566 width=8) (actual time=6.406..6.406 rows=5,566 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 282kB
22. 3.264 4.790 ↑ 1.0 5,566 1

Hash Join (cost=1.61..185.80 rows=5,566 width=8) (actual time=0.037..4.790 rows=5,566 loops=1)

  • Hash Cond: ((cidade.cid_uf)::text = (ibge_uf.uf)::text)
23. 1.505 1.505 ↑ 1.0 5,566 1

Seq Scan on cidade (cost=0.00..107.66 rows=5,566 width=7) (actual time=0.002..1.505 rows=5,566 loops=1)

24. 0.012 0.021 ↑ 1.0 27 1

Hash (cost=1.27..1.27 rows=27 width=7) (actual time=0.021..0.021 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
25. 0.009 0.009 ↑ 1.0 27 1

Seq Scan on ibge_uf (cost=0.00..1.27 rows=27 width=7) (actual time=0.002..0.009 rows=27 loops=1)