RE: Problem with indices from 10 to 13

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"

[cid:flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png]

Daniel Diniz
Desenvolvimento

Cel.: 11981464923

www.flashcourier.com.br<http://www.flashcourier.com.br>

[cid:SocialLink_Facebook_32x32_11ddcb69-c640-49e0-88b2-e1038ba38ffa.png]<https://www.facebook.com/flashcourieroficial> [cid:SocialLink_Instagram_32x32_1a56219d-8d68-474a-8e29-0e536d8241d4.png] <https://www.instagram.com/flashcourieroficial> [cid:SocialLink_Linkedin_32x32_6bb30d6c-bdcd-4446-ace2-9daa6eeb5e16.png] <https://www.linkedin.com/company/flashcourieroficial>

#SomosTodosFlash #GrupoMOVE3
[cid:QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.png]

[cid:whatsappimage2021-08-31at18.36.01_c1d35f2c-7adc-42cd-98ff-acbc6b165aa6.jpeg]<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