> Só por curiosidade, qual a diferença do de tempo de execução do SQL
original com o compilado (com os casts) agora, depois do VACUUM?
Agora depois do vacuum ficou instantâneo os 2 sql's, segue ae os 2 planos
de execução depois do vacuum.
Em termos de tempo, os sqls sem cast eram praticamente iguais antes e
depois do vacuum (404.419 ms), o problema era no que havia casts
(Consequentemente nas view's), antes do vacuum demorava 195556.620 ms
(pouco mais de 3 minutos), agora demora 261.405 ms (Menos de 1 segundo).
O plano de execução mudou totalmente depois do vacuum.
Para outro teste, fiz um backup e restore desta base, após o restore
executei o sql e estava lento novamente, fazendo o vacuum novamente voltou
a ficar rápido.
Conclusão: Sempre depois de fazer um retore faça um vacuum também.
*========================== PLANO DE EXECUÇÃO SEM O CAST
=============================*
Hash Left Join (cost=7388.52..10174.38 rows=25044 width=377) (actual
time=157.818..250.086 rows=25044 loops=1)
Hash Cond: (((l.cd_loja)::integer = (pl.cd_loja)::integer) AND
((l.cd_produto)::integer = (pfi.cd_produto)::integer))
-> Hash Join (cost=1426.10..3335.42 rows=25044 width=339) (actual
time=40.026..104.254 rows=25044 loops=1)
Hash Cond: ((p.cd_produto)::integer = (l.cd_produto)::integer)
-> Seq Scan on tb_produto p (cost=0.00..1408.44 rows=25044
width=131) (actual time=0.012..26.460 rows=25044 loops=1)
-> Hash (cost=1113.05..1113.05 rows=25044 width=212) (actual
time=39.955..39.955 rows=25044 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 2622kB
-> Seq Scan on tb_produto_loja l (cost=0.00..1113.05
rows=25044 width=212) (actual time=0.018..23.521 rows=25044 loops=1)
Filter: ((cd_loja)::integer = 1)
-> Hash (cost=5586.76..5586.76 rows=25044 width=46) (actual
time=117.740..117.740 rows=25044 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 1212kB
-> Nested Loop (cost=2325.70..5586.76 rows=25044 width=46)
(actual time=28.974..108.834 rows=25044 loops=1)
-> Seq Scan on tb_uf u (cost=0.00..1.35 rows=1 width=0)
(actual time=0.011..0.017 rows=1 loops=1)
Filter: ((cd_uf)::bpchar = 'MT'::bpchar)
-> Nested Loop (cost=2325.70..5334.97 rows=25044 width=46)
(actual time=28.959..104.463 rows=25044 loops=1)
-> Index Scan using tb_loja_pkey on tb_loja l
(cost=0.00..4.27 rows=1 width=4) (actual time=0.017..0.020 rows=1 loops=1)
Index Cond: ((cd_loja)::integer = 1)
-> Hash Left Join (cost=2325.70..5080.26 rows=25044
width=46) (actual time=28.934..100.686 rows=25044 loops=1)
Hash Cond: ((pfi.cd_figura_icms)::integer =
(fi.cd_figura_icms)::integer)
-> Hash Join (cost=2311.20..4721.40 rows=25044
width=12) (actual time=28.655..84.291 rows=25044 loops=1)
Hash Cond: ((p.cd_produto)::integer =
(pfi.cd_produto)::integer)
-> Hash Join (cost=1426.10..3335.42
rows=25044 width=12) (actual time=12.675..45.880 rows=25044 loops=1)
Hash Cond: ((p.cd_produto)::integer =
(pl.cd_produto)::integer)
-> Seq Scan on tb_produto p
(cost=0.00..1408.44 rows=25044 width=4) (actual time=0.005..9.801
rows=25044 loops=1)
-> Hash (cost=1113.05..1113.05
rows=25044 width=8) (actual time=12.613..12.613 rows=25044 loops=1)
Buckets: 4096 Batches: 1
Memory Usage: 979kB
-> Seq Scan on tb_produto_loja
pl (cost=0.00..1113.05 rows=25044 width=8) (actual time=0.009..7.001
rows=25044 loops=1)
Filter:
((cd_loja)::integer = 1)
-> Hash (cost=572.05..572.05 rows=25044
width=16) (actual time=15.953..15.953 rows=25044 loops=1)
Buckets: 4096 Batches: 1 Memory
Usage: 979kB
-> Seq Scan on
tb_produto_uf_figura_icms pfi (cost=0.00..572.05 rows=25044 width=16)
(actual time=0.010..9.638 rows=25044 loops=1)
Filter: (((cd_figura_icms IS
NOT NULL) OR (cd_figura_icms_producao_propria_up IS NOT NULL)) AND
((cd_uf)::bpchar = 'MT'::bpchar))
-> Hash (cost=9.78..9.78 rows=378 width=13)
(actual time=0.232..0.232 rows=378 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
18kB
-> Seq Scan on tb_figura_icms fi
(cost=0.00..9.78 rows=378 width=13) (actual time=0.027..0.125 rows=378
loops=1)
Total runtime: 253.011 ms
*========================== PLANO DE EXECUÇÃO COM O CAST
=============================*
Hash Left Join (cost=7447.87..10233.73 rows=25044 width=377) (actual
time=166.024..258.455 rows=25044 loops=1)
Hash Cond: (((l.cd_loja)::integer = (pl.cd_loja)::integer) AND
((l.cd_produto)::integer = (pfi.cd_produto)::integer))
-> Hash Join (cost=1426.10..3335.42 rows=25044 width=339) (actual
time=45.780..109.989 rows=25044 loops=1)
Hash Cond: ((p.cd_produto)::integer = (l.cd_produto)::integer)
-> Seq Scan on tb_produto p (cost=0.00..1408.44 rows=25044
width=131) (actual time=0.011..26.463 rows=25044 loops=1)
-> Hash (cost=1113.05..1113.05 rows=25044 width=212) (actual
time=45.715..45.715 rows=25044 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 2622kB
-> Seq Scan on tb_produto_loja l (cost=0.00..1113.05
rows=25044 width=212) (actual time=0.017..23.723 rows=25044 loops=1)
Filter: ((cd_loja)::integer = 1)
-> Hash (cost=5646.11..5646.11 rows=25044 width=46) (actual
time=120.191..120.191 rows=25044 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 1212kB
-> Nested Loop (cost=2325.70..5646.11 rows=25044 width=46)
(actual time=29.348..111.242 rows=25044 loops=1)
-> Seq Scan on tb_uf u (cost=0.00..1.35 rows=1 width=0)
(actual time=0.011..0.017 rows=1 loops=1)
Filter: ((cd_uf)::bpchar = 'MT'::bpchar)
-> Hash Left Join (cost=2325.70..5394.32 rows=25044
width=46) (actual time=29.329..106.709 rows=25044 loops=1)
Hash Cond: ((pfi.cd_figura_icms)::integer =
(fi.cd_figura_icms)::integer)
-> Nested Loop (cost=2311.20..5035.46 rows=25044
width=12) (actual time=29.055..90.346 rows=25044 loops=1)
Join Filter: ((pl.cd_loja)::integer =
(l.cd_loja)::integer)
-> Seq Scan on tb_loja l (cost=0.00..1.01
rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)
-> Hash Join (cost=2311.20..4721.40 rows=25044
width=12) (actual time=29.045..84.745 rows=25044 loops=1)
Hash Cond: ((p.cd_produto)::integer =
(pfi.cd_produto)::integer)
-> Hash Join (cost=1426.10..3335.42
rows=25044 width=12) (actual time=13.077..45.532 rows=25044 loops=1)
Hash Cond: ((p.cd_produto)::integer =
(pl.cd_produto)::integer)
-> Seq Scan on tb_produto p
(cost=0.00..1408.44 rows=25044 width=4) (actual time=0.006..9.590
rows=25044 loops=1)
-> Hash (cost=1113.05..1113.05
rows=25044 width=8) (actual time=13.022..13.022 rows=25044 loops=1)
Buckets: 4096 Batches: 1
Memory Usage: 979kB
-> Seq Scan on tb_produto_loja
pl (cost=0.00..1113.05 rows=25044 width=8) (actual time=0.008..7.458
rows=25044 loops=1)
Filter:
((cd_loja)::integer = 1)
-> Hash (cost=572.05..572.05 rows=25044
width=16) (actual time=15.943..15.943 rows=25044 loops=1)
Buckets: 4096 Batches: 1 Memory
Usage: 979kB
-> Seq Scan on
tb_produto_uf_figura_icms pfi (cost=0.00..572.05 rows=25044 width=16)
(actual time=0.009..9.583 rows=25044 loops=1)
Filter: (((cd_figura_icms IS
NOT NULL) OR (cd_figura_icms_producao_propria_up IS NOT NULL)) AND
((cd_uf)::bpchar = 'MT'::bpchar))
-> Hash (cost=9.78..9.78 rows=378 width=13) (actual
time=0.225..0.225 rows=378 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 18kB
-> Seq Scan on tb_figura_icms fi
(cost=0.00..9.78 rows=378 width=13) (actual time=0.027..0.123 rows=378
loops=1)
Total runtime: 261.405 ms
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
