laboratorio:rnge2=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT num_cupom FROM cf_cupom WHERE nfce_chave_acesso_fk = '43170605563868000113657010000004061895261728'; QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_cupom_chave on cf_cupom (cost=0.55..8.57 rows=1 width=4) (actual time=0.078..0.079 rows=1 loops=1) Index Cond: (nfce_chave_acesso_fk = '43170605563868000113657010000004061895261728'::bpchar) Buffers: shared hit=5 Total runtime: 0.106 ms (4 rows) 2017-06-02 11:28 GMT-03:00 Alexsander Rosa <alexsander.r...@gmail.com>: > laboratorio:rnge2=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT sp_teste(' > 43170605563868000113657010000004061895261728'); > QUERY PLAN > > ------------------------------------------------------------ > ------------------------------ > Result (cost=0.00..0.26 rows=1 width=0) (actual time=1523.013..1523.013 > rows=1 loops=1) > Buffers: shared hit=18998 read=123619 dirtied=3 > Total runtime: 1523.043 ms > (3 rows) > > > Em 2 de junho de 2017 11:11, Alexsander Rosa <alexsander.r...@gmail.com> > escreveu: > >> laboratorio:rnge2=# SELECT sp_teste('43170605563868000113 >> 657010000004061895261728'); >> sp_teste >> ---------- >> OK >> (1 row) >> >> Time: 1507,688 ms >> laboratorio:rnge2=# >> >> ---------------- >> -- Código "pelado" >> CREATE OR REPLACE FUNCTION rnx.sp_teste(chave text) >> RETURNS text >> LANGUAGE plpgsql >> AS $function$ >> DECLARE >> BEGIN >> PERFORM num_cupom FROM cf_cupom WHERE nfce_chave_acesso_fk = chave; >> Return 'OK'; >> END; >> $function$ >> ; >> >> ---------------- >> -- EXPLAIN: >> >> Index Scan using idx_cupom_chave on cf_cupom (cost=0.55..8.57 rows=1 >> width=4) >> Index Cond: (nfce_chave_acesso_fk = '43170605563868000113657010000 >> 004061895261728'::bpchar) >> >> >> Em 2 de junho de 2017 11:06, Flavio Henrique Araque Gurgel < >> fha...@gmail.com> escreveu: >> >>> Em sex, 2 de jun de 2017 às 15:55, Fabrízio de Royes Mello < >>> fabri...@timbira.com.br> escreveu: >>> >>>> >>>> >>>> Em 2 de junho de 2017 10:46, Alexsander Rosa <alexsander.r...@gmail.com> >>>> escreveu: >>>> > >>>> > A tabela tem cerca de 1 Gb: >>>> > SELECT pg_size_pretty(pg_relation_size('cf_cupom')); >>>> > pg_size_pretty >>>> > ---------------- >>>> > 1114 MB >>>> > (1 registro) >>>> > >>>> > Existe um índice UNIQUE no campo utilizado na query: >>>> > "idx_cupom_chave" UNIQUE, btree (nfce_chave_acesso_fk) WHERE >>>> nfce_chave_acesso_fk IS NOT NULL >>>> > >>>> > O campo utilizado também é FOREIGN KEY: >>>> > "cupom_chave_nfe_fkey" FOREIGN KEY (nfce_chave_acesso_fk) REFERENCES >>>> nfe_xml(chave_acesso) DEFERRABLE >>>> > >>>> > Código da procedure de teste: >>>> > CREATE OR REPLACE FUNCTION rnx.sp_teste(chave text) >>>> > RETURNS text >>>> > LANGUAGE plpgsql >>>> > AS $function$ >>>> > DECLARE >>>> > BEGIN >>>> > RAISE NOTICE '(%)', clock_timestamp()::timestamp(6); >>>> > -- EXECUTE 'SELECT cod_empresa_fk FROM cf_cupom WHERE >>>> nfce_chave_acesso_fk = $1' INTO empcupom USING chave; >>>> > PERFORM num_cupom FROM cf_cupom WHERE nfce_chave_acesso_fk = chave; >>>> > RAISE NOTICE '(%)', clock_timestamp()::timestamp(6); >>>> > -- demora 1617 ms >>>> > >>>> > PERFORM chave_acesso from nfe_xml where chave_acesso = chave; >>>> > RAISE NOTICE '(%)', clock_timestamp()::timestamp(6); >>>> > -- demora 21 ms >>>> > >>>> > Return 'OK'; >>>> > END; >>>> > $function$ >>>> > ; >>>> > >>>> > Comparativo: >>>> > select sp_teste('43170605563868000113657010000004061895261728'); >>>> > -- demora 1630 ms >>>> > >>>> > select num_cupom FROM cf_cupom WHERE nfce_chave_acesso_fk = >>>> '43170605563868000113657010000004061895261728'; >>>> > -- demora 11 ms >>>> > >>>> > Foi feito um VACUUM FULL ANALYZE na tabela. >>>> > Alguém tem alguma dica para ajudar em nossa investigação? >>>> > >>>> >>>> Faz o seguinte, remove aqueles "RAISE NOTICE" da sua PL e roda no psql >>>> com o "\timing on"... >>>> >>> >>> Ah, vou eleger 2 de junho como dia internacional da consulta lenta >>> inexplicável... só hoje foram duas no meu trampo. >>> >>> Ao OP, cadê os EXPLAIN (analyze, timing, buffers) SELECT... ? >>> >>> []s >>> Flavio Gurgel >>> >>> >>> _______________________________________________ >>> pgbr-geral mailing list >>> pgbr-geral@listas.postgresql.org.br >>> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral >>> >> >> >> >> -- >> Atenciosamente, >> Alexsander da Rosa >> >> > > > -- > Atenciosamente, > Alexsander da Rosa > > -- 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