Em 18 de julho de 2013 14:37, Flavio Henrique Araque Gurgel <
[email protected]> 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 50000ms. 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..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"
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"
[]s
Danilo
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
