Re: Problem with indices from 10 to 13

st 29. 9. 2021 v 6:01 odesílatel Daniel Diniz <daniel(at)flashcourier(dot)com(dot)br>
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*Desenvolvimento
>
> Cel.: *11981464923*
>
>
> *www.flashcourier.com.br* <http://www.flashcourier.com.br>
>
> <https://www.facebook.com/flashcourieroficial>
> <https://www.instagram.com/flashcourieroficial>
> <https://www.linkedin.com/company/flashcourieroficial>
>
> #SomosTodosFlash #GrupoMOVE3
>
>
> <https://premio.reclameaqui.com.br/votacao>
>
> *"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(at)telsasoft(dot)com>
> *Enviado:* terça-feira, 28 de setembro de 2021 23:18
> *Para:* Daniel Diniz <daniel(at)flashcourier(dot)com(dot)br>
> *Cc:* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-performance(at)lists(dot)postgresql(dot)org
> <pgsql-performance(at)lists(dot)postgresql(dot)org>
> *Assunto:* Re: Problem with indices from 10 to 13
>
> On 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
>