Em 19-07-2013 11:12, Danilo Silva escreveu:
Segue os explains
CREATE OR REPLACE VIEW vcons_pend_documento AS
SELECT ht011.codtb011, ht011.codtb001 AS codempresa, ht011.codtb002 AS
codcto, tb002.dscr AS ctonome, ht011.codtb004 AS codcli, ht011.codtb012
AS codprod, count(*) AS total
FROM historico_tb011 ht011
JOIN tb002ctrodtrb tb002 ON tb002.codtb001 = ht011.codtb001 AND
tb002.codtb002 = ht011.codtb002
JOIN tb005unnegsrv tb005 ON tb005.codtb001 = ht011.codtb001 AND
tb005.codtb004 = ht011.codtb004 AND tb005.codtb005 = ht011.coddest_uni
JOIN tb012tpmltcxa tb012 ON tb012.codtb001 = ht011.codtb001 AND
tb012.codtb004 = ht011.codtb004 AND tb012.codtb012 = ht011.codtb012
WHERE ht011.baixado = false AND ht011.saida = false AND ht011.datahist >
'2012-11-05'::date
AND f_soma_diautil(ht011.datahist, ht011.horahist, 'now'::text::date,
'now'::text::time(0) without time zone, ht011.codtb001, tb005.uf::text,
tb005.cdde::text) >= 86400::double precision
AND (ht011.idhistorico_tb011 IN ( SELECT h11.idhistorico_tb011 FROM
historico_tb011 h11 WHERE h11.codtb011 = ht011.codtb011 ORDER BY
h11.datahist DESC, h11.horahist DESC LIMIT 1))
GROUP BY ht011.codtb011, ht011.codtb001, ht011.codtb002, tb002.dscr,
ht011.codtb004, ht011.codtb012;
EXPLAIN ANALYSE SELECT ctonome, SUM(total) AS total,codcto FROM
vcons_pend_documento
WHERE ((SELECT 1::text = ANY(string_to_array((SELECT confsis_valor FROM
configuracao_sistema WHERE (confsis_variavel = 'MOSTRAPEND24HPERFIL')),
','))) IS TRUE)
GROUP BY codempresa, codcto, ctonome, codcli ORDER BY total DESC LIMIT
15 OFFSET 0;
"Limit (cost=3111275.63..3111275.66 rows=15 width=38) (actual
time=66561.745..66561.746 rows=5 loops=1)"
" InitPlan 2 (returns $1)"
" -> Result (cost=1.18..1.20 rows=1 width=0) (actual
time=0.032..0.032 rows=1 loops=1)"
" InitPlan 1 (returns $0)"
" -> Seq Scan on configuracao_sistema (cost=0.00..1.18
rows=1 width=9) (actual time=0.012..0.012 rows=1 loops=1)"
" Filter: (confsis_variavel = 'MOSTRAPEND24HPERFIL'::text)"
" -> Sort (cost=3111274.43..3111328.53 rows=21643 width=38) (actual
time=66561.744..66561.744 rows=5 loops=1)"
" Sort Key: (sum((count(*))))"
" Sort Method: quicksort Memory: 25kB"
" -> HashAggregate (cost=3110527.00..3110743.43 rows=21643
width=38) (actual time=66561.669..66561.701 rows=5 loops=1)"
Este HashAggregate aqui foi caro.
" -> Result (cost=3103493.25..3107821.71 rows=216423
width=38) (actual time=66561.225..66561.537 rows=237 loops=1)"
" One-Time Filter: ($1 IS TRUE)"
" -> HashAggregate (cost=3103493.25..3105657.48
rows=216423 width=38) (actual time=66561.191..66561.483 rows=237 loops=1)"
" -> Hash Join (cost=7429.24..3099705.85
rows=216423 width=38) (actual time=38486.447..66560.231 rows=237 loops=1)"
" Hash Cond: ((ht011.codtb001 =
tb012.codtb001) AND (ht011.codtb004 = tb012.codtb004) AND
(ht011.codtb012 = tb012.codtb012))"
" -> Hash Join
(cost=7423.74..3095101.36 rows=216423 width=50) (actual
time=390.452..66559.164 rows=401 loops=1)"
Por causa dos demais HashAggregates acima.
" Hash Cond: ((ht011.codtb001 =
tb002.codtb001) AND (ht011.codtb002 = tb002.codtb002))"
" -> Hash Join
(cost=7422.36..3091312.58 rows=216423 width=28) (actual
time=390.427..66557.919 rows=401 loops=1)"
" Hash Cond: ((ht011.codtb001
= tb005.codtb001) AND (ht011.codtb004 = tb005.codtb004) AND
(ht011.coddest_uni = tb005.codtb005))"
" Join Filter:
(f_soma_diautil(ht011.datahist, ht011.horahist, ('now'::text)::date,
('now'::text)::time(0) without time zone, ht011.codtb001,
(tb005.uf)::text, (tb005.cdde)::text) >= 86400::double precision)"
" -> Bitmap Heap Scan on
historico_tb011 ht011 (cost=7128.99..2888839.91 rows=652072 width=36)
(actual time=10.110..174.309 rows=13844 loops=1)"
" Recheck Cond:
(datahist > '2012-11-05'::date)"
" Filter: ((NOT
baixado) AND (NOT saida) AND (SubPlan 3))"
" -> Bitmap Index Scan
on historico_tb011_saida_idx (cost=0.00..6965.98 rows=184375 width=0)
(actual time=9.871..9.871 rows=22293 loops=1)"
" Index Cond:
((baixado = false) AND (saida = false) AND (datahist > '2012-11-05'::date))"
" SubPlan 3"
" -> Limit
(cost=14.23..14.23 rows=1 width=16) (actual time=0.009..0.009 rows=1
loops=13847)"
" -> Sort
(cost=14.23..14.25 rows=9 width=16) (actual time=0.008..0.008 rows=1
loops=13847)"
" Sort
Key: h11.datahist, h11.horahist"
" Sort
Method: top-N heapsort Memory: 25kB"
" ->
Index Scan using historico_tb011_codtb011_idx on historico_tb011 h11
(cost=0.00..14.19 rows=9 width=16) (actual time=0.004..0.005 rows=1
loops=13847)"
"
Index Cond: (codtb011 = ht011.codtb011)"
" -> Hash
(cost=176.68..176.68 rows=6668 width=26) (actual time=4.888..4.888
rows=6613 loops=1)"
" Buckets: 1024
Batches: 1 Memory Usage: 363kB"
" -> Seq Scan on
tb005unnegsrv tb005 (cost=0.00..176.68 rows=6668 width=26) (actual
time=0.011..2.418 rows=6613 loops=1)"
" -> Hash (cost=1.15..1.15
rows=15 width=26) (actual time=0.012..0.012 rows=15 loops=1)"
" Buckets: 1024 Batches: 1
Memory Usage: 1kB"
" -> Seq Scan on
tb002ctrodtrb tb002 (cost=0.00..1.15 rows=15 width=26) (actual
time=0.004..0.007 rows=15 loops=1)"
" -> Hash (cost=3.91..3.91 rows=91
width=12) (actual time=0.075..0.075 rows=91 loops=1)"
" Buckets: 1024 Batches: 1 Memory
Usage: 4kB"
" -> Seq Scan on tb012tpmltcxa
tb012 (cost=0.00..3.91 rows=91 width=12) (actual time=0.011..0.049
rows=91 loops=1)"
"Total runtime: 66563.047 ms"
O resto do Explain está rápido.
EXPLAIN ANALYSE SELECT * FROM
f_soma_diautil('2013-07-17'::date,'01:08:23'::time,CURRENT_DATE,LOCALTIME(0),1::integer,'SP'::text,'SAO
PAULO'::text)
"Function Scan on f_soma_diautil (cost=0.26..0.27 rows=1 width=8)
(actual time=7.956..7.956 rows=1 loops=1)"
"Total runtime: 7.970 ms"
Tirando a função da condição da view
CREATE OR REPLACE VIEW vcons_pend_documento AS
SELECT ht011.codtb011, ht011.codtb001 AS codempresa, ht011.codtb002 AS
codcto, tb002.dscr AS ctonome, ht011.codtb004 AS codcli, ht011.codtb012
AS codprod, count(*) AS total
FROM historico_tb011 ht011
JOIN tb002ctrodtrb tb002 ON tb002.codtb001 = ht011.codtb001 AND
tb002.codtb002 = ht011.codtb002
JOIN tb005unnegsrv tb005 ON tb005.codtb001 = ht011.codtb001 AND
tb005.codtb004 = ht011.codtb004 AND tb005.codtb005 = ht011.coddest_uni
JOIN tb012tpmltcxa tb012 ON tb012.codtb001 = ht011.codtb001 AND
tb012.codtb004 = ht011.codtb004 AND tb012.codtb012 = ht011.codtb012
WHERE ht011.baixado = false AND ht011.saida = false AND ht011.datahist >
'2012-11-05'::date
/*AND f_soma_diautil(ht011.datahist, ht011.horahist, 'now'::text::date,
'now'::text::time(0) without time zone, ht011.codtb001, tb005.uf::text,
tb005.cdde::text) >= 86400::double precision*/
AND (ht011.idhistorico_tb011 IN ( SELECT h11.idhistorico_tb011 FROM
historico_tb011 h11 WHERE h11.codtb011 = ht011.codtb011 ORDER BY
h11.datahist DESC, h11.horahist DESC LIMIT 1))
GROUP BY ht011.codtb011, ht011.codtb001, ht011.codtb002, tb002.dscr,
ht011.codtb004, ht011.codtb012;
EXPLAIN ANALYSE SELECT ctonome, SUM(total) AS total,codcto FROM
vcons_pend_documento
WHERE ((SELECT 1::text = ANY(string_to_array((SELECT confsis_valor FROM
configuracao_sistema WHERE (confsis_variavel = 'MOSTRAPEND24HPERFIL')),
','))) IS TRUE)
GROUP BY codempresa, codcto, ctonome, codcli ORDER BY total DESC LIMIT
15 OFFSET 0;
"Limit (cost=2977561.31..2977561.35 rows=15 width=38) (actual
time=184.882..184.884 rows=10 loops=1)"
" InitPlan 2 (returns $1)"
" -> Result (cost=1.18..1.20 rows=1 width=0) (actual
time=0.028..0.028 rows=1 loops=1)"
" InitPlan 1 (returns $0)"
" -> Seq Scan on configuracao_sistema (cost=0.00..1.18
rows=1 width=9) (actual time=0.009..0.010 rows=1 loops=1)"
" Filter: (confsis_variavel = 'MOSTRAPEND24HPERFIL'::text)"
" -> Sort (cost=2977560.11..2977722.43 rows=64928 width=38) (actual
time=184.881..184.883 rows=10 loops=1)"
" Sort Key: (sum((count(*))))"
" Sort Method: quicksort Memory: 25kB"
" -> HashAggregate (cost=2975317.86..2975967.14 rows=64928
width=38) (actual time=184.770..184.865 rows=10 loops=1)"
" -> Result (cost=2954216.33..2967201.89 rows=649278
width=38) (actual time=163.348..171.999 rows=13710 loops=1)"
" One-Time Filter: ($1 IS TRUE)"
" -> HashAggregate (cost=2954216.33..2960709.11
rows=649278 width=38) (actual time=163.318..170.066 rows=13710 loops=1)"
" -> Hash Join (cost=7436.47..2942853.96
rows=649278 width=38) (actual time=10.286..149.182 rows=13710 loops=1)"
" Hash Cond: ((ht011.codtb001 =
tb012.codtb001) AND (ht011.codtb004 = tb012.codtb004) AND
(ht011.codtb012 = tb012.codtb012))"
" -> Hash Join
(cost=7430.97..2929051.30 rows=649278 width=50) (actual
time=8.914..141.270 rows=13874 loops=1)"
" Hash Cond: ((ht011.codtb001 =
tb002.codtb001) AND (ht011.codtb002 = tb002.codtb002))"
" -> Hash Join
(cost=7429.59..2917687.56 rows=649278 width=28) (actual
time=8.891..134.780 rows=13874 loops=1)"
" Hash Cond: ((ht011.codtb001
= tb005.codtb001) AND (ht011.codtb004 = tb005.codtb004) AND
(ht011.coddest_uni = tb005.codtb005))"
" -> Bitmap Heap Scan on
historico_tb011 ht011 (cost=7136.22..2888893.64 rows=652082 width=24)
(actual time=5.561..117.485 rows=13874 loops=1)"
" Recheck Cond:
(datahist > '2012-11-05'::date)"
" Filter: ((NOT
baixado) AND (NOT saida) AND (SubPlan 3))"
" -> Bitmap Index Scan
on historico_tb011_saida_idx (cost=0.00..6973.20 rows=184378 width=0)
(actual time=5.366..5.366 rows=22374 loops=1)"
" Index Cond:
((baixado = false) AND (saida = false) AND (datahist > '2012-11-05'::date))"
" SubPlan 3"
" -> Limit
(cost=14.23..14.23 rows=1 width=16) (actual time=0.007..0.007 rows=1
loops=13877)"
" -> Sort
(cost=14.23..14.25 rows=9 width=16) (actual time=0.006..0.006 rows=1
loops=13877)"
" Sort
Key: h11.datahist, h11.horahist"
" Sort
Method: top-N heapsort Memory: 25kB"
" ->
Index Scan using historico_tb011_codtb011_idx on historico_tb011 h11
(cost=0.00..14.19 rows=9 width=16) (actual time=0.003..0.004 rows=1
loops=13877)"
"
Index Cond: (codtb011 = ht011.codtb011)"
" -> Hash
(cost=176.68..176.68 rows=6668 width=12) (actual time=3.319..3.319
rows=6613 loops=1)"
" Buckets: 1024
Batches: 1 Memory Usage: 285kB"
" -> Seq Scan on
tb005unnegsrv tb005 (cost=0.00..176.68 rows=6668 width=12) (actual
time=0.007..1.435 rows=6613 loops=1)"
" -> Hash (cost=1.15..1.15
rows=15 width=26) (actual time=0.014..0.014 rows=15 loops=1)"
" Buckets: 1024 Batches: 1
Memory Usage: 1kB"
" -> Seq Scan on
tb002ctrodtrb tb002 (cost=0.00..1.15 rows=15 width=26) (actual
time=0.003..0.006 rows=15 loops=1)"
" -> Hash (cost=3.91..3.91 rows=91
width=12) (actual time=0.054..0.054 rows=91 loops=1)"
" Buckets: 1024 Batches: 1 Memory
Usage: 4kB"
" -> Seq Scan on tb012tpmltcxa
tb012 (cost=0.00..3.91 rows=91 width=12) (actual time=0.003..0.030
rows=91 loops=1)"
"Total runtime: 188.431 ms"
Quando você coloca a função na visão e ainda filtra por essa coluna na
sua consulta, o planejador não consegue otimizar.
Prefira manter a visão sem a função na coluna que você vai filtrar e faça:
1) a função na cláusula WHERE final;
2) talvez, um índice por função (functional index).
Refaça os EXPLAIN ANALYZE no final. Compare custos e duração da consulta.
[]s
__________________________________
Flavio Henrique A. Gurgel
Líder de Projetos Especiais
Consultoria, Projetos & Treinamentos 4LINUX
Tel1: +55-11.2125-4747 ou 2125-4748
www.4linux.com.br
email: fla...@4linux.com.br
______________________________
FREE SOFTWARE SOLUTIONS
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
