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 <[email protected]>:
> 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 <[email protected]>
> 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 <
>> [email protected]> escreveu:
>>
>>> Em sex, 2 de jun de 2017 às 15:55, Fabrízio de Royes Mello <
>>> [email protected]> escreveu:
>>>
>>>>
>>>>
>>>> Em 2 de junho de 2017 10:46, Alexsander Rosa <[email protected]>
>>>> 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
>>> [email protected]
>>> 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
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
