Alexsander, boa noite
Criei meu próprio exemplo para entender seu problema e chegar em uma
conclusão do que está ocorrendo.
--=========== Criei uma tabela para teste a partir do generate_series
create table analyze_query as select
gn::character(20),'SomeTextExample'::text from generate_series(1,10000000)
gn;
postgres=# \d analyze_query
Tabela "public.analyze_query"
Coluna | Tipo | Modificadores
--------+---------------+---------------
gn | character(10) |
text | text |
--=========== verificando o tamanho da tabela
postgres=# \dt+ analyze_query
Lista de relações
Esquema | Nome | Tipo | Dono | Tamanho | Descrição
---------+---------------+--------+----------+---------+-----------
public | analyze_query | tabela | postgres | 574 MB |
--=========== Criei um index na coluna gn
create unique index on analyze_query(gn);
--=========== Criei uma FUNCTION parecida com a sua com o tipo do
argumento text
CREATE OR REPLACE FUNCTION sp_teste1(chave text)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
PERFORM gn FROM analyze_query WHERE gn = chave;
Return 'OK';
END;
$$;
--=========== Executei o explain em um select parecido com o seu, para
comprovar que o índice seria usado
postgres=# explain select gn FROM analyze_query WHERE gn ='9000000';
QUERY PLAN
------------------------------------------------------------------------------------------------
Index Only Scan using analyze_query_gn_idx on analyze_query
(cost=0.43..8.45 rows=1 width=14)
Index Cond: (gn = '9000000'::bpchar)
(2 registros)
--=========== Query com tempo de execução
postgres=# select gn FROM analyze_query WHERE gn ='9000000';
gn
------------
9000000
Tempo: 0,240 ms
--=========== FUNCTION com tempo de execução, nesse caso o tempo foi muito
maior
postgres=# select sp_teste1('9000000');
sp_teste1
-----------
OK
(1 registro)
Tempo: 3144,483 ms
--=========== Fiz o load do módulo auto explain para gerar plano de
execução automatico, tanto na tela como log do PostgreSQL.
LOAD 'auto_explain';
SET auto_explain.log_analyze TO on;
SET auto_explain.log_min_duration TO 0;
SET auto_explain.log_nested_statements TO on;
SET client_min_messages TO log;
--=========== Executei a FUNCTION sp_teste1
Tempo: 16,121 ms
postgres=# select sp_teste1('9000000');
LOG: duration: 3123.277 ms plan:
Query Text: SELECT gn FROM analyze_query WHERE gn = chave
Seq Scan on analyze_query (cost=0.00..223530.00 rows=50000 width=14)
(actual time=2819.195..3123.258 rows=1 loops=1)
Filter: ((gn)::text = '9000000'::text)
Rows Removed by Filter: 9999999
CONTEXTO: comando SQL "SELECT gn FROM analyze_query WHERE gn = chave"
função PL/pgSQL sp_teste1(text) linha 4 em PERFORM
LOG: duration: 3123.736 ms plan:
Query Text: select sp_teste1('9000000');
Result (cost=0.00..0.26 rows=1 width=0) (actual time=3123.713..3123.716
rows=1 loops=1)
sp_teste1
-----------
OK
(1 registro)
Tempo: 3124,263 ms
--=========== Plano da query da FUNCTION sp_teste1, comprovando que
houve o seq scan
Seq Scan on analyze_query (cost=0.00..223530.00 rows=50000 width=14)
(actual time=2819.195..3123.258 rows=1 loops=1)
Filter: ((gn)::text = '9000000'::text)
--=========== Alterei o argumento de (chave text) para (chave character(10))
CREATE OR REPLACE FUNCTION sp_teste2(chave character(10))
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
PERFORM gn FROM analyze_query WHERE gn = chave;
Return 'OK';
END;
$$;
--=========== Fiz o teste também fazendo o CAST explicito e funcionou,
perfeitamente também como no exemplo anterior
CREATE OR REPLACE FUNCTION sp_teste1(chave text)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
PERFORM gn FROM analyze_query WHERE gn = chave::bpchar; <<--- CAST
explicito
Return 'OK';
END;
$$;
--=========== Executei novamente (Novo caso)
postgres=# select sp_teste2('9000000');
LOG: duration: 0.292 ms plan:
Query Text: SELECT gn FROM analyze_query WHERE gn = chave
Index Only Scan using analyze_query_gn_idx on analyze_query
(cost=0.43..8.45 rows=1 width=14) (actual time=0.264..0.267 rows=1 loops=1)
Index Cond: (gn = '9000000'::bpchar)
Heap Fetches: 1
CONTEXTO: comando SQL "SELECT gn FROM analyze_query WHERE gn = chave"
função PL/pgSQL sp_teste3(character) linha 4 em PERFORM
LOG: duration: 0.955 ms plan:
Query Text: select sp_teste2('9000000');
Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.934..0.936 rows=1
loops=1)
sp_teste3
-----------
OK
(1 registro)
--=========== duration com a mudança tanto no argumento, como no CAST
explicito
LOG: duration: 0.955 ms plan:
Index Only Scan using analyze_query_gn_idx on analyze_query
(cost=0.43..8.45 rows=1 width=14) (actual time=0.264..0.267 rows=1 loops=1)
Index Cond: (gn = '9000000'::bpchar)
--=========== Executar novamente (Caso antigo)
postgres=# select sp_teste1('9000000');
LOG: duration: 3141.335 ms plan:
Query Text: SELECT gn FROM analyze_query WHERE gn = chave
Seq Scan on analyze_query (cost=0.00..223530.00 rows=50000 width=14)
(actual time=2836.698..3141.317 rows=1 loops=1)
Filter: ((gn)::text = '9000000'::text)
Rows Removed by Filter: 9999999
CONTEXTO: comando SQL "SELECT gn FROM analyze_query WHERE gn = chave"
função PL/pgSQL sp_teste1(text) linha 4 em PERFORM
LOG: duration: 3141.826 ms plan:
Query Text: select sp_teste1('9000000');
Result (cost=0.00..0.26 rows=1 width=0) (actual time=3141.804..3141.807
rows=1 loops=1)
sp_teste1
-----------
OK
(1 registro)
--=========== duration do caso antigo, onde o índice é ignorado por causa
do CAST
LOG: duration: 3141.826 ms plan:
Seq Scan on analyze_query (cost=0.00..223530.00 rows=50000 width=14)
(actual time=2836.698..3141.317 rows=1 loops=1)
Filter: ((gn)::text = '9000000'::text)
--=========== Como acredito que você irá resolver seu problema, seque
considerações
O problema encontrado é que o PostgreSQL fez um CAST da coluna (gn)
(Filter: ((gn)::text = '9000000'::text)) para atender o tipo de argumento
da FUNCTION que era do tipo text e no caso a coluna que ele comparava era
do tipo char. Como o tamanho do campo tipo text pode ser muito maior do que
um campo tipo char, houve então o CAST implícito pelo PostgreSQL e com
isso única alternativa que ele tinha nesse caso era fazer um seq scan, pois
o índice não atendia para esse caso.
--=========== -->> Sugestões:
1 - Qual o tipo da coluna nfce_chave_acesso_fk e chave_acesso para entender
melhor o problema?
2 - Colocar o tipo do argumento da FUNCTION com o mesmo tipo da coluna,
para não ocorrer CAST implícito das colunas nfce_chave_acesso_fk ou
chave_acesso
3 - Caso não tenha como mudar o tipo do argumento da FUNCTION, então
realizar o CAST explicito na variável (chave) no corpo FUNCTION na linha (
PERFORM num_cupom FROM cf_cupom WHERE nfce_chave_acesso_fk = chave;)
Ex: PERFORM num_cupom FROM cf_cupom WHERE nfce_chave_acesso_fk =
chave::bpchar;
4 - Executar e nos passar o resultado
Em 2 de junho de 2017 17:15, Alexsander Rosa <[email protected]>
escreveu:
> Em 2 de junho de 2017 16:40, Matheus de Oliveira <
> [email protected]> escreveu:
>
>> Isso aí pra mim tá com cara de plano de execução genérico. Mas pra ter
>> certeza seria legal você instalar e habilitar o auto_explain, daí você
>> configura `auto_explain.log_nested_statements = on` e executa a função
>> novamente, ele vai logar o plano de execução só daquela consulta no log.
>>
>> postgres=# SELECT sp_teste('431706055638680001136570100000
>> 04061895261728');
>> ... <poste o resultado> ...
>>
>>
> O banco um Seq Scan... ignorou o índice.
>
> central-rd540:5432:rnge2=# SELECT sp_teste('431706055638680001136570100000
> 04061895261728');
> LOG: duration: 1810.362 ms plan:
> Query Text: SELECT num_cupom FROM cf_cupom WHERE nfce_chave_acesso_fk =
> chave
> Seq Scan on cf_cupom (cost=0.00..305178.52 rows=54145 width=4) (actual
> time=1806.082..1810.358 rows=1 loops=1)
> Filter: ((nfce_chave_acesso_fk)::text = '431706055638680001136570100000
> 04061895261728'::text)
> Rows Removed by Filter: 10793976
> LOG: duration: 1834.088 ms plan:
> Query Text: SELECT sp_teste('431706055638680001136570100000
> 04061895261728');
> Result (cost=0.00..0.26 rows=1 width=0) (actual time=1834.080..1834.080
> rows=1 loops=1)
> sp_teste
> ----------
> OK
>
>
> --
> Atenciosamente,
> Alexsander da Rosa
>
>
> _______________________________________________
> pgbr-geral mailing list
> [email protected]
> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
