RE: Problem with indices from 10 to 13

Ranier,
ran vacuumdb -U postgres -j100 -p5434 -azv
and even so it didn't improve.
Now ir running for 1h10min and not finished de explain after run the comand up.😥

[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:QR6626a761-6a9c-427c-bd7e-09a0ad5c91a8.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: Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>
Enviado: terça-feira, 28 de setembro de 2021 14:27
Para: Daniel Diniz <daniel(at)flashcourier(dot)com(dot)br>
Cc: 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

Em ter., 28 de set. de 2021 às 12:40, Daniel Diniz <daniel(at)flashcourier(dot)com(dot)br<mailto:daniel(at)flashcourier(dot)com(dot)br>> escreveu:
Hello I migrated from postgres 10 to 13 and I noticed that there was a big increase in a querie that I use, I did explain in 10 and 13 and the difference is absurd, the indices and data are the same in 2. I've re-created and re-indexed but I don't know what changed from 10 to 13 which made the performance so bad, I don't know if it needs some extra parameter in some conf on 13.

Postgres 13

"QUERY PLAN"
"Limit (cost=1.13..26855.48 rows=30 width=137) (actual time=10886.585..429803.463 rows=4 loops=1)"
" -> Nested Loop (cost=1.13..19531164.71 rows=21819 width=137) (actual time=10886.584..429803.457 rows=4 loops=1)"
" Join Filter: (h.ult_eve_id = ev.evento_id)"
" Rows Removed by Join Filter: 252"
" -> Nested Loop (cost=1.13..19457514.32 rows=21819 width=62) (actual time=10886.326..429803.027 rows=4 loops=1)"
" -> Nested Loop (cost=0.85..19450780.70 rows=21819 width=55) (actual time=10886.259..429802.908 rows=4 loops=1)"
" -> Index Scan Backward using hawbs_pkey on hawbs h (cost=0.57..19444209.67 rows=21819 width=46) (actual time=10886.119..429802.676 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 Backward looks suspicious to me.
239,188,096 rows removed by filter it's a lot of work.

Do you, run analyze?

regards,
Ranier Vilela