explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KB30 : Optimization for: plan #4X17

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 58.228 4,255.174 ↓ 64.1 38,643 1

GroupAggregate (cost=231,537.59..231,771.25 rows=603 width=91) (actual time=4,194.877..4,255.174 rows=38,643 loops=1)

  • Group Key: ecf.ecf_cd_ecf, ven.ven_cd_venda, cai.cai_fl_consolidado, (((ved.ved_cd_nota_saida IS NOT NULL) OR (ved.ved_cd_nota_saida > 0)))
2. 26.416 4,196.946 ↓ 64.1 38,643 1

Sort (cost=231,537.59..231,539.10 rows=603 width=43) (actual time=4,194.839..4,196.946 rows=38,643 loops=1)

  • Sort Key: ecf.ecf_cd_ecf, ven.ven_cd_venda, cai.cai_fl_consolidado, (((ved.ved_cd_nota_saida IS NOT NULL) OR (ved.ved_cd_nota_saida > 0)))
  • Sort Method: quicksort Memory: 4,555kB
3. 2.889 4,170.530 ↓ 64.1 38,643 1

Nested Loop (cost=2,107.88..231,509.74 rows=603 width=43) (actual time=5.973..4,170.530 rows=38,643 loops=1)

4. 2,753.560 4,128.998 ↓ 64.1 38,643 1

Gather (cost=2,107.60..230,613.99 rows=603 width=39) (actual time=5.967..4,128.998 rows=38,643 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 5.300 1,375.438 ↓ 51.3 12,881 3 / 3

Nested Loop (cost=1,107.60..229,553.69 rows=251 width=39) (actual time=2.549..1,375.438 rows=12,881 loops=3)

6. 57.523 1,357.257 ↓ 51.3 12,881 3 / 3

Hash Left Join (cost=1,107.18..229,132.48 rows=251 width=41) (actual time=2.541..1,357.257 rows=12,881 loops=3)

  • Hash Cond: (ven.ven_cd_venda = ved.ved_cd_venda_devolvida)
  • Filter: (((ven.ven_fl_cancelada = 'N'::bpchar) AND ((ved.ved_cd_nota_saida IS NULL) OR (ved.ved_cd_nota_saida = 0)) AND (ven.ven_cd_unidade_negocio = 1,588) AND (ven.ven_dt_fiscal >= LEAST('2020-01-01'::date, '2020-01-31'::date)) AND (ven.ven_dt_fiscal <= GREATEST('2020-01-01'::date, '2020-01-31'::date))) OR (ved.ved_cd_nota_saida = 0))
  • Rows Removed by Filter: 341,070
7. 38.433 1,299.107 ↓ 3.7 353,951 3 / 3

Nested Loop (cost=0.56..227,670.60 rows=94,705 width=33) (actual time=1.889..1,299.107 rows=353,951 loops=3)

8. 0.001 1.880 ↑ 12.0 1 3 / 3

Parallel Append (cost=0.00..4,020.51 rows=12 width=4) (actual time=1.879..1.880 rows=1 loops=3)

9. 0.000 0.000 ↑ 1.0 1 1 / 3

Result (cost=0.00..0.10 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

10. 0.275 1.879 ↓ 0.0 0 2 / 3

Nested Loop (cost=26.42..4,018.21 rows=9 width=4) (actual time=2.817..2.818 rows=0 loops=2)

11. 0.345 0.437 ↑ 1.2 876 2 / 3

Parallel Bitmap Heap Scan on cliente_unidade_negocio cun (cost=26.00..1,731.45 rows=1,023 width=4) (actual time=0.190..0.655 rows=876 loops=2)

  • Recheck Cond: (cun_cd_unidade_negocio = 1,588)
  • Heap Blocks: exact=475
12. 0.092 0.092 ↓ 1.0 1,751 1 / 3

Bitmap Index Scan on cliente_unidade_negocio_idx_unidade_negocio (cost=0.00..25.56 rows=1,739 width=0) (actual time=0.275..0.275 rows=1,751 loops=1)

  • Index Cond: (cun_cd_unidade_negocio = 1,588)
13. 1.167 1.167 ↓ 0.0 0 1,751 / 3

Index Scan using cliente_pkey on cliente cli (cost=0.42..2.24 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,751)

  • Index Cond: (cli_cd_cliente = cun.cun_cd_cliente)
  • Filter: (cli_fl_identificado = 'N'::bpchar)
  • Rows Removed by Filter: 1
14. 1,258.794 1,258.794 ↓ 37.4 530,926 2 / 3

Index Scan using venda_idx_cliente on venda ven (cost=0.56..17,216.91 rows=14,206 width=37) (actual time=0.013..1,888.191 rows=530,926 loops=2)

  • Index Cond: (ven_cd_cliente = (336567))
15. 0.041 0.627 ↓ 1.1 415 2 / 3

Hash (cost=1,067.97..1,067.97 rows=377 width=18) (actual time=0.940..0.940 rows=415 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
16. 0.000 0.586 ↓ 1.1 415 2 / 3

Nested Loop (cost=0.70..1,067.97 rows=377 width=18) (actual time=0.021..0.879 rows=415 loops=2)

17. 0.043 0.043 ↓ 1.1 415 2 / 3

Index Only Scan using venda_devolucao_nota_saida_venda_devolvida on venda_devolucao ved (cost=0.27..55.26 rows=377 width=8) (actual time=0.009..0.064 rows=415 loops=2)

  • Heap Fetches: 214
18. 0.553 0.553 ↑ 1.0 1 830 / 3

Index Scan using nota_saida_pkey on nota_saida nts (cost=0.42..2.69 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=830)

  • Index Cond: (nts_cd_nota_saida = ved.ved_cd_nota_saida)
19. 12.881 12.881 ↑ 1.0 1 38,643 / 3

Index Scan using caixa_pkey on caixa cai (cost=0.42..1.68 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=38,643)

  • Index Cond: (cai_cd_caixa = ven.ven_cd_caixa)
20. 38.643 38.643 ↑ 1.0 1 38,643

Index Scan using ecf_pkey on ecf (cost=0.28..1.49 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=38,643)

  • Index Cond: (ecf_cd_ecf = ven.ven_cd_ecf)
Planning time : 3.647 ms
Execution time : 4,257.911 ms