explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QiwR : aaaaaa

Settings
# exclusive inclusive rows x rows loops node
1. 15,505.438 15,505.438 ↓ 2.2 20,955 1

CTE Scan on t1 (cost=10,888,922.96..10,889,117.78 rows=9,741 width=711) (actual time=2.525..15,505.438 rows=20,955 loops=1)

  • Buffers: shared hit=8,958,130 read=55,667, temp written=1,237
2.          

CTE params

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=24) (actual time=0.001..0.002 rows=1 loops=1)

4.          

CTE t1

5. 388.167 15,417.720 ↓ 2.2 20,955 1

Nested Loop (cost=0.85..10,888,922.94 rows=9,741 width=358) (actual time=2.521..15,417.720 rows=20,955 loops=1)

  • Buffers: shared hit=8,958,130 read=55,667
6. 26.970 88.638 ↓ 2.2 20,955 1

Nested Loop (cost=0.43..9,790.03 rows=9,741 width=295) (actual time=0.616..88.638 rows=20,955 loops=1)

  • Buffers: shared hit=21,159 read=321
7. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on params (cost=0.00..0.02 rows=1 width=24) (actual time=0.003..0.005 rows=1 loops=1)

8. 61.663 61.663 ↓ 2.2 20,955 1

Index Scan using cadastro_extra_produto_idfornecedor_idx on cadastro_extra_produto cep (cost=0.43..9,692.60 rows=9,741 width=287) (actual time=0.606..61.663 rows=20,955 loops=1)

  • Index Cond: (idfornecedor = params.idfornecedor)
  • Buffers: shared hit=21,159 read=321
9. 167.640 167.640 ↑ 1.0 1 20,955

Index Scan using produto_pkey on produto p (cost=0.42..0.46 rows=1 width=25) (actual time=0.008..0.008 rows=1 loops=20,955)

  • Index Cond: (idproduto = cep.idproduto)
  • Buffers: shared hit=83,941
10.          

SubPlan (for Nested Loop)

11. 62.865 4,756.785 ↑ 1.0 1 20,955

Aggregate (cost=326.18..326.19 rows=1 width=32) (actual time=0.227..0.227 rows=1 loops=20,955)

  • Buffers: shared hit=1,377,397 read=33,651
12. 4,693.920 4,693.920 ↑ 5.5 2 20,955

CTE Scan on setores setores_1 (cost=325.82..326.04 rows=11 width=32) (actual time=0.167..0.224 rows=2 loops=20,955)

  • Buffers: shared hit=1,377,397 read=33,651
13.          

CTE setores

14. 67.061 4,652.010 ↑ 5.5 2 20,955

Recursive Union (cost=0.72..325.82 rows=11 width=32) (actual time=0.166..0.222 rows=2 loops=20,955)

  • Buffers: shared hit=1,377,397 read=33,651
15. 238.347 4,505.325 ↑ 1.0 1 20,955

Nested Loop (cost=0.72..92.55 rows=1 width=32) (actual time=0.165..0.215 rows=1 loops=20,955)

  • Buffers: shared hit=1,319,855 read=33,650
16. 3,185.160 3,185.160 ↑ 1.6 17 20,955

Index Scan using setor_produto_idproduto_idx on setor_produto (cost=0.43..29.91 rows=28 width=16) (actual time=0.101..0.152 rows=17 loops=20,955)

  • Index Cond: (idproduto = cep.idproduto)
  • Buffers: shared hit=229,977 read=33,519
17. 1,081.818 1,081.818 ↓ 0.0 0 360,606

Index Scan using setor_pkey on setor (cost=0.29..2.24 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=360,606)

  • Index Cond: (idsetor = setor_produto.idsetor)
  • Filter: (ativo AND (idfornecedor = cep.idfornecedor))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1,089,878 read=131
18. 41.130 79.624 ↓ 0.0 0 39,812

Nested Loop (cost=0.29..23.30 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=39,812)

  • Buffers: shared hit=57,542 read=1
19. 0.000 0.000 ↑ 10.0 1 39,812

WorkTable Scan on setores (cost=0.00..0.20 rows=10 width=16) (actual time=0.000..0.000 rows=1 loops=39,812)

20. 38.494 38.494 ↓ 0.0 0 38,494

Index Scan using setor_pkey on setor setor_1 (cost=0.29..2.31 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=38,494)

  • Index Cond: (idsetor = setores.idsetorpai)
  • Filter: (ativo AND (idfornecedor = cep.idfornecedor))
  • Buffers: shared hit=57,542 read=1
21. 41.910 9,890.760 ↑ 1.0 1 20,955

Aggregate (cost=787.72..787.73 rows=1 width=32) (actual time=0.472..0.472 rows=1 loops=20,955)

  • Buffers: shared hit=7,412,671 read=21,695
22. 630.461 9,848.850 ↓ 0.0 0 20,955

Hash Semi Join (cost=734.41..787.72 rows=1 width=16) (actual time=0.206..0.470 rows=0 loops=20,955)

  • Hash Cond: (pc.idpoliticacabecalho = pi2.idpoliticacabecalho)
  • Buffers: shared hit=7,412,671 read=21,695
23. 6,221.824 6,221.824 ↓ 32.0 1,346 3,136

Index Scan using politicadesconto_cabecalho_idfornecedor_idx on politicadesconto_cabecalho pc (cost=0.41..52.77 rows=42 width=16) (actual time=0.009..1.984 rows=1,346 loops=3,136)

  • Index Cond: ((idfornecedor = cep.idfornecedor) AND (params.data >= vigencia_inicial) AND (params.data <= vigencia_final))
  • Buffers: shared hit=7,144,406 read=264
24. 62.865 2,996.565 ↑ 42.1 17 20,955

Hash (cost=725.05..725.05 rows=716 width=16) (actual time=0.143..0.143 rows=17 loops=20,955)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=268,265 read=21,431
25. 2,933.700 2,933.700 ↑ 42.1 17 20,955

Index Scan using politicadesconto_itens_idprodutoerp_idx_1 on politicadesconto_itens pi2 (cost=0.56..725.05 rows=716 width=16) (actual time=0.051..0.140 rows=17 loops=20,955)

  • Index Cond: ((idprodutoerp)::text = (cep.id_produto_erp)::text)
  • Buffers: shared hit=268,265 read=21,431
26. 20.955 125.730 ↑ 1.0 1 20,955

Aggregate (cost=2.44..2.45 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=20,955)

  • Buffers: shared hit=62,962
27. 104.775 104.775 ↓ 0.0 0 20,955

Index Scan using tag_pesquisa_vinculo_produto_idcadastroextraproduto_idx_2 on tag_pesquisa_vinculo_produto tpvp (cost=0.41..2.43 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=20,955)

  • Index Cond: (idcadastroextraproduto = cep.idcadastroextraproduto)
  • Buffers: shared hit=62,962
Planning time : 1.275 ms
Execution time : 15,512.548 ms