Re: [pgbr-geral] Melhorar desempenho de uma função
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..239.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)
Re: [pgbr-geral] Melhorar desempenho de uma função
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). A última linha da função estava como *LANGUAGE plpgsql VOLATILE, alterei de VOLATILE para IMMUTABLE para criar o index: CREATE INDEX historico_tb011_function_idx ON historico_tb011 (f_soma_diautil(datahist,horahist,CURRENT_DATE,LOCALTIME(0),codtb001,'','')); Mas ocorre o erro ERROR: functions in index expression must be marked IMMUTABLE. Estou errando em algum lugar? []s Danilo ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Melhorar desempenho de uma função
Em 23-07-2013 16:53, Danilo Silva escreveu: 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). A última linha da função estava como *LANGUAGE plpgsql VOLATILE, alterei de VOLATILE para IMMUTABLE para criar o index: CREATE INDEX historico_tb011_function_idx ON historico_tb011 (f_soma_diautil(datahist,horahist,CURRENT_DATE,LOCALTIME(0),codtb001,'','')); Mas ocorre o erro ERROR: functions in index expression must be marked IMMUTABLE. Estou errando em algum lugar? Sim. Você não pode criar um índice funcional com uma expressão variável (como LOCALTIME) pois isso depende... do momento atual :) []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
Re: [pgbr-geral] Melhorar desempenho de uma função
Em 18 de julho de 2013 14:37, Flavio Henrique Araque Gurgel
fla...@4linux.com.br escreveu:
Pessoal,
Tenho uma função
CREATE OR REPLACE FUNCTION f_soma_diautil(vdtini date, vhrini time
without
time zone, vdtfim date, vhrfim time without time zone, vcodemp integer,
vuf
text, vcidade text) RETURNS double precision
que retorna a diferença de tempo entre vdtfim e vdtini, no caso essa
função
serve para saber se um objeto está pendente ou não (a mais de 24 horas,
ou
86400::double precision).
Tenho uma view que retorna a quantidade de objetos pendentes separados
por
filial, a tabela em sí possui mais de 8 milhões de registros e o select
sem
a função leva em torno de 118ms. A função, executada isoladamente leva em
torno de 29ms, mas quando eu adiciono a função no WHERE da view (AND
funcao() = 86400), o select está levando em torno de 5ms. O que pode
estar errado?
Um select sobre visão reescreve a consulta.
EXPLAIN ANALYZE em cada uma (na que vai rápido e na que vai demorado) deve
te dar a resposta (ou nos ajudar a ajudá-lo).
[]s
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)
- 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)
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..239.91 rows=652072 width=36)
(actual
[pgbr-geral] Melhorar desempenho de uma função
Pessoal, Tenho uma função CREATE OR REPLACE FUNCTION f_soma_diautil(vdtini date, vhrini time without time zone, vdtfim date, vhrfim time without time zone, vcodemp integer, vuf text, vcidade text) RETURNS double precision que retorna a diferença de tempo entre vdtfim e vdtini, no caso essa função serve para saber se um objeto está pendente ou não (a mais de 24 horas, ou 86400::double precision). Tenho uma view que retorna a quantidade de objetos pendentes separados por filial, a tabela em sí possui mais de 8 milhões de registros e o select sem a função leva em torno de 118ms. A função, executada isoladamente leva em torno de 29ms, mas quando eu adiciono a função no WHERE da view (AND funcao() = 86400), o select está levando em torno de 5ms. O que pode estar errado? []s Danilo ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Melhorar desempenho de uma função
Pessoal, Tenho uma função CREATE OR REPLACE FUNCTION f_soma_diautil(vdtini date, vhrini time without time zone, vdtfim date, vhrfim time without time zone, vcodemp integer, vuf text, vcidade text) RETURNS double precision que retorna a diferença de tempo entre vdtfim e vdtini, no caso essa função serve para saber se um objeto está pendente ou não (a mais de 24 horas, ou 86400::double precision). Tenho uma view que retorna a quantidade de objetos pendentes separados por filial, a tabela em sí possui mais de 8 milhões de registros e o select sem a função leva em torno de 118ms. A função, executada isoladamente leva em torno de 29ms, mas quando eu adiciono a função no WHERE da view (AND funcao() = 86400), o select está levando em torno de 5ms. O que pode estar errado? Um select sobre visão reescreve a consulta. EXPLAIN ANALYZE em cada uma (na que vai rápido e na que vai demorado) deve te dar a resposta (ou nos ajudar a ajudá-lo). []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
