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 <alexsander.r...@gmail.com> escreveu: > Em 2 de junho de 2017 16:40, Matheus de Oliveira < > matioli.math...@gmail.com> 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 > pgbr-geral@listas.postgresql.org.br > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral >
_______________________________________________ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral