st 29. 9. 2021 v 6:01 odesílatel Daniel Diniz <daniel@xxxxxxxxxxxxxxxxxxx> napsal:
Justin tested it with some parameters 200, 2000, 10000, -1 and the 3 spent more or less the same time
exemple ALTER TABLE hawbs ALTER nome_des SET STATISTICS 2000; ANALYZE hawbs;:"QUERY PLAN""Limit (cost=1.13..28049.86 rows=30 width=137) (actual time=5462.123..363089.923 rows=4 loops=1)"" -> Nested Loop (cost=1.13..19523788.64 rows=20882 width=137) (actual time=5462.122..363089.915 rows=4 loops=1)"" Join Filter: (h.ult_eve_id = ev.evento_id)"" Rows Removed by Join Filter: 252"" -> Nested Loop (cost=1.13..19453301.90 rows=20882 width=62) (actual time=5461.844..363089.429 rows=4 loops=1)"" -> Nested Loop (cost=0.85..19446849.38 rows=20882 width=55) (actual time=5461.788..363089.261 rows=4 loops=1)"" -> Index Scan Backward using hawbs_pkey on hawbs h (cost=0.57..19440557.11 rows=20882 width=46) (actual time=5461.644..363088.839 rows=4 loops=1)"" Filter: ((tipo_hawb_id = ANY ('{1,10,3}'::integer[])) AND ((nome_des)::text ~~* convert_from('\x255354455048414e592053544f4557204c45414e44524f25'::bytea, 'LATIN1'::name)))"" Rows Removed by Filter: 239188096"" -> Index Scan using empresas_pkey on empresas e (cost=0.28..0.30 rows=1 width=17) (actual time=0.037..0.038 rows=1 loops=4)"" Index Cond: (empresa_id = h.cliente_id)"" -> Index Scan using contratos_pkey on contratos c (cost=0.28..0.31 rows=1 width=15) (actual time=0.021..0.021 rows=1 loops=4)"" Index Cond: (ctt_id = h.ctt_id)"" -> Materialize (cost=0.00..7.23 rows=215 width=27) (actual time=0.011..0.023 rows=64 loops=4)"" -> Seq Scan on eventos ev (cost=0.00..6.15 rows=215 width=27) (actual time=0.033..0.052 rows=67 loops=1)""Planning Time: 10.452 ms""Execution Time: 363090.127 ms"
Maybe you can try composite index based on hawbs_pkey, and tipo_hawb_id
the second problem can be the low value of LIMIT - got you faster result without LIMIT clause?
Regards
Pavel
Daniel Diniz
DesenvolvimentoCel.: 11981464923
www.flashcourier.com.br
#SomosTodosFlash #GrupoMOVE3
"Esta mensagem e seus anexos são dirigidos exclusivamente para os seus destinatários, podendo conter informação confidencial e/ou legalmente privilegiada. Se você não for destinatário desta mensagem, não deve revelar, copiar, distribuir ou de qualquer forma utilizá-la. A empresa não se responsabiliza por alterações no conteúdo desta mensagem depois do seu envio."
De: Justin Pryzby <pryzby@xxxxxxxxxxxxx>
Enviado: terça-feira, 28 de setembro de 2021 23:18
Para: Daniel Diniz <daniel@xxxxxxxxxxxxxxxxxxx>
Cc: Tom Lane <tgl@xxxxxxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>
Assunto: Re: Problem with indices from 10 to 13On Wed, Sep 29, 2021 at 02:11:15AM +0000, Daniel Diniz wrote:
> How do i increase the statistics target for h.nome_des?
> And why uploading the dump at 10 and at 13 is there this difference?
It's like ALTER TABLE h ALTER nome_des SET STATISTICS 2000; ANALYZE h;
https://www.postgresql.org/docs/current/sql-altertable.html
--
Justin
