explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ptvs : Optimization for: plan #764A

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 78,050.670 87,134.031 ↑ 1.0 462,749 1

WindowAgg (cost=937,946.81..1,433,088.24 rows=462,749 width=296) (actual time=8,845.837..87,134.031 rows=462,749 loops=1)

2. 817.683 9,083.361 ↑ 1.0 462,749 1

Sort (cost=937,946.81..939,103.68 rows=462,749 width=296) (actual time=8,845.648..9,083.361 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: 75976kB
3. 947.290 8,265.678 ↑ 1.0 462,749 1

WindowAgg (cost=805,247.39..820,286.73 rows=462,749 width=296) (actual time=7,082.612..8,265.678 rows=462,749 loops=1)

4. 1,006.125 7,318.388 ↑ 1.0 462,749 1

Sort (cost=805,247.39..806,404.26 rows=462,749 width=296) (actual time=7,082.577..7,318.388 rows=462,749 loops=1)

  • Sort Key: tp_filter_sold_itens.asi_id_venda
  • Sort Method: external merge Disk: 70360kB
5. 352.842 6,312.263 ↑ 1.0 462,749 1

Hash Join (cost=5,212.16..687,587.30 rows=462,749 width=296) (actual time=26.121..6,312.263 rows=462,749 loops=1)

  • Hash Cond: (associado.asd_id_cidade = cidade.cid_id)
6. 368.707 5,953.026 ↑ 1.0 462,749 1

Nested Loop (cost=4,956.78..680,969.13 rows=462,749 width=296) (actual time=19.711..5,953.026 rows=462,749 loops=1)

7. 308.339 3,733.323 ↑ 1.0 462,749 1

Hash Join (cost=4,956.35..76,334.75 rows=462,749 width=278) (actual time=19.684..3,733.323 rows=462,749 loops=1)

  • Hash Cond: (produto.prd_id_ncm = ncm.ncm_id)
8. 294.291 3,419.290 ↑ 1.0 462,749 1

Hash Join (cost=4,630.42..69,646.02 rows=462,749 width=278) (actual time=13.968..3,419.290 rows=462,749 loops=1)

  • Hash Cond: (produto.prd_id_unidade_medida = unidademedida.pru_id)
9. 355.912 3,124.994 ↑ 1.0 462,749 1

Hash Join (cost=4,629.31..63,282.11 rows=462,749 width=275) (actual time=13.955..3,124.994 rows=462,749 loops=1)

  • Hash Cond: (tp_filter_sold_itens.asv_id_associado = associado.asd_id)
10. 611.606 2,755.199 ↑ 1.0 462,749 1

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

  • Merge Cond: (tp_filter_sold_itens.asi_id_produto = produto.prd_id)
11. 1,495.239 1,495.239 ↑ 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.029..1,495.239 rows=462,749 loops=1)

12. 648.354 648.354 ↓ 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.024..648.354 rows=1,031,545 loops=1)

13. 2.433 13.883 ↑ 1.0 6,537 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 422kB
14. 11.450 11.450 ↑ 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.016..11.450 rows=6,537 loops=1)

15. 0.003 0.005 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.002 0.002 ↑ 1.0 5 1

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

17. 2.767 5.694 ↑ 1.0 9,419 1

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

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

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

19. 1,850.996 1,850.996 ↑ 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.003..0.004 rows=1 loops=462,749)

  • Index Cond: (pes_id = tp_filter_sold_itens.asv_id_consumidor)
20. 1.605 6.395 ↑ 1.0 5,566 1

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

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

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

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

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

23. 0.010 0.019 ↑ 1.0 27 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 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.001..0.009 rows=27 loops=1)