explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5d34 : Frederasfc

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 55.012 ↑ 11.4 71 1

Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)

  • Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
  • Sort Method: quicksort Memory: 43kB
2. 1.378 54.854 ↑ 11.4 71 1

Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)

  • Hash Cond: (p.pronamespace = n.oid)
3. 53.465 53.465 ↓ 3.0 2,402 1

Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2,402 loops=1)

  • Filter: pg_function_is_visible(oid)
4. 0.004 0.011 ↑ 1.0 4 1

Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
5. 0.000 0.007 ↑ 1.0 4 1

Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)

6. 4,259,003.306 4,259,003.306 ↑ 6.1 772 1

Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))"Result (cost=145,107,304.77..217,650,042.14 rows=4,683 width=275) (actual time=4,239,984.731..4,259,003.306 rows=772 loops=1)

7. 3.045 4,239,961.195 ↑ 6.1 772 1

Sort (cost=145,107,304.77..145,107,316.48 rows=4,683 width=267) (actual time=4,239,960.614..4,239,961.195 rows=772 loops=1)

  • Sort Key: (btrim((vix_pro.pro_descricao_longa)::text))
  • Sort Method: quicksort Memory: 226kB
8. 17.071 4,239,958.150 ↑ 6.1 772 1

Group (cost=72,565,382.41..145,107,019.27 rows=4,683 width=267) (actual time=4,239,932.202..4,239,958.150 rows=772 loops=1)

  • Group Key: vix_ite.ite_pro_cod_pro, vix_ite.ite_cod_emp, vix_pro.pro_descricao_longa, vix_pro.pro_cod_bar, vix_pro.pro_unidade, vix_pro.pro_ncm, vix_pro.pro_preco5, vix_pro.pro_preco1, (CASE WHEN ((SubPlan 1) IS NULL) THEN vix_pro.pro_preco1 ELSE (SubPlan 2) END), (CASE WHEN ((SubPlan 3) IS NULL) THEN vix_pro.pro_preco1 ELSE (SubPlan 4) END)
9. 309.540 4,239,941.079 ↓ 7.6 35,814 1

Sort (cost=72,565,382.41..72,565,394.12 rows=4,683 width=139) (actual time=4,239,932.193..4,239,941.079 rows=35,814 loops=1)

  • Sort Key: vix_ite.ite_pro_cod_pro, vix_pro.pro_descricao_longa, vix_pro.pro_cod_bar, vix_pro.pro_unidade, vix_pro.pro_ncm, vix_pro.pro_preco5, vix_pro.pro_preco1, (CASE WHEN ((SubPlan 1) IS NULL) THEN vix_pro.pro_preco1 ELSE (SubPlan 2) END), (CASE WHEN ((SubPlan 3) IS NULL) THEN vix_pro.pro_preco1 ELSE (SubPlan 4) END)
  • Sort Method: external merge Disk: 3424kB
10. 824.595 4,239,631.539 ↓ 7.6 35,814 1

Hash Join (cost=252.65..72,565,096.90 rows=4,683 width=139) (actual time=273.639..4,239,631.539 rows=35,814 loops=1)

  • Hash Cond: (vix_ite.ite_pro_cod_pro = vix_pro.pro_cod_pro)
11. 1,095,902.546 1,095,902.546 ↓ 2.8 37,753 1

Seq Scan on vix_ite (cost=0.00..23,358.79 rows=13,671 width=8) (actual time=170.198..1,095,902.546 rows=37,753 loops=1)

  • Filter: ((ite_dat_cancelamento IS NULL) AND (ite_dat_lan <= '2019-01-31 23:59:59'::timestamp without time zone) AND (ite_cod_emp = 1) AND (ite_6020_natureza = ANY ('{1,2,3,4,5,6,15,16}'::integer[])) AND (get_qtde_prod_inventario(ite_pro_cod_pro, ite_cod_emp, '2019-01-31'::date) > '0'::double precision))
  • Rows Removed by Filter: 37194
12. 0.373 1.808 ↑ 1.1 1,295 1

Hash (cost=234.97..234.97 rows=1,415 width=75) (actual time=1.808..1.808 rows=1,295 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 162kB
13. 1.435 1.435 ↑ 1.1 1,295 1

Seq Scan on vix_pro (cost=0.00..234.97 rows=1,415 width=75) (actual time=0.009..1.435 rows=1,295 loops=1)

  • Filter: ((pro_dat_cancelamento IS NULL) AND (pro_cod_emp = 1) AND (pro_eh_inventariavel = 1))
  • Rows Removed by Filter: 2836
14.          

SubPlan (forHash Join)

15. 143.256 792,707.076 ↑ 1.0 1 35,814

Limit (cost=3,872.59..3,872.60 rows=1 width=14) (actual time=22.133..22.134 rows=1 loops=35,814)

16. 966.978 792,563.820 ↑ 4.0 1 35,814

Sort (cost=3,872.59..3,872.60 rows=4 width=14) (actual time=22.130..22.130 rows=1 loops=35,814)

  • Sort Key: vix_ite_1.ite_dat_lan DESC
  • Sort Method: top-N heapsort Memory: 25kB
17. 791,596.842 791,596.842 ↓ 2.5 10 35,814

Seq Scan on vix_ite vix_ite_1 (cost=0.00..3,872.57 rows=4 width=14) (actual time=4.048..22.103 rows=10 loops=35,814)

  • Filter: ((ite_dat_lan <= '2019-01-31 23:59:59'::timestamp without time zone) AND (ite_6020_natureza = 2) AND (ite_pro_cod_pro = vix_pro.pro_cod_pro))
  • Rows Removed by Filter: 74937
18. 175.820 778,284.812 ↑ 1.0 1 35,164

Limit (cost=3,872.59..3,872.60 rows=1 width=14) (actual time=22.132..22.133 rows=1 loops=35,164)

19. 949.428 778,108.992 ↑ 4.0 1 35,164

Sort (cost=3,872.59..3,872.60 rows=4 width=14) (actual time=22.128..22.128 rows=1 loops=35,164)

  • Sort Key: vix_ite_2.ite_dat_lan DESC
  • Sort Method: top-N heapsort Memory: 25kB
20. 777,159.564 777,159.564 ↓ 2.5 10 35,164

Seq Scan on vix_ite vix_ite_2 (cost=0.00..3,872.57 rows=4 width=14) (actual time=3.719..22.101 rows=10 loops=35,164)

  • Filter: ((ite_dat_lan <= '2019-01-31 23:59:59'::timestamp without time zone) AND (ite_6020_natureza = 2) AND (ite_pro_cod_pro = vix_pro.pro_cod_pro))
  • Rows Removed by Filter: 74937
21. 179.070 792,957.774 ↑ 1.0 1 35,814

Limit (cost=3,872.59..3,872.60 rows=1 width=14) (actual time=22.139..22.141 rows=1 loops=35,814)

22. 966.978 792,778.704 ↑ 4.0 1 35,814

Sort (cost=3,872.59..3,872.60 rows=4 width=14) (actual time=22.136..22.136 rows=1 loops=35,814)

  • Sort Key: vix_ite_3.ite_dat_lan DESC
  • Sort Method: top-N heapsort Memory: 25kB
23. 791,811.726 791,811.726 ↓ 2.5 10 35,814

Seq Scan on vix_ite vix_ite_3 (cost=0.00..3,872.57 rows=4 width=14) (actual time=4.043..22.109 rows=10 loops=35,814)

  • Filter: ((ite_dat_lan <= '2019-01-31 23:59:59'::timestamp without time zone) AND (ite_6020_natureza = 2) AND (ite_pro_cod_pro = vix_pro.pro_cod_pro))
  • Rows Removed by Filter: 74937
24. 175.820 778,952.928 ↑ 1.0 1 35,164

Limit (cost=3,872.59..3,872.60 rows=1 width=14) (actual time=22.150..22.152 rows=1 loops=35,164)

25. 949.428 778,777.108 ↑ 4.0 1 35,164

Sort (cost=3,872.59..3,872.60 rows=4 width=14) (actual time=22.147..22.147 rows=1 loops=35,164)

  • Sort Key: vix_ite_4.ite_dat_lan DESC
  • Sort Method: top-N heapsort Memory: 25kB
26. 777,827.680 777,827.680 ↓ 2.5 10 35,164

Seq Scan on vix_ite vix_ite_4 (cost=0.00..3,872.57 rows=4 width=14) (actual time=3.711..22.120 rows=10 loops=35,164)

  • Filter: ((ite_dat_lan <= '2019-01-31 23:59:59'::timestamp without time zone) AND (ite_6020_natureza = 2) AND (ite_pro_cod_pro = vix_pro.pro_cod_pro))
  • Rows Removed by Filter: 74937