From: Daniel Diniz <daniel@flashcourier.com.br>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: RE: Problem with indices from 10 to 13
Date: Wed, 29 Sep 2021 02:11:15 +0000
Message-ID: <CP2P152MB0274CC07F4D81971F642FBA185A99@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <2920281.1632868873@sss.pgh.pa.us>
References: <8d2ba21d-2318-4f02-a8fd-04c7d97e22ab.e854021a-9da0-4eba-a51f-e5ff782985ef.17ca0caa-f521-44ce-817a-17cbc713d135@emailsignatures365.codetwo.com>
<CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM>
<2853352.1632851116@sss.pgh.pa.us>
<CP2P152MB027497F5EBE88A1491AB833985A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM>
<2920281.1632868873@sss.pgh.pa.us>
Tom,
"pg_trgm, you mean? That answers one question, but you still didn't
explain what type h.nome_des is, nor how bytea and convert_from()
are getting into the picture."
The column type is: nome_des | character varying(60)
"The second part of that is probably not critical, since the planner
should be willing to reduce the convert_from() call to a constant
for planning purposes, so I'm unclear as to why the estimate for
the ilike clause is so bad. Have you tried increasing the statistics
target for h.nome_des to see if the estimate gets better?"
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?
Thanks
[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: Tom Lane <tgl@sss.pgh.pa.us>
Enviado: terça-feira, 28 de setembro de 2021 19:41
Para: Daniel Diniz <daniel@flashcourier.com.br>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Assunto: Re: Problem with indices from 10 to 13
Daniel Diniz <daniel@flashcourier.com.br> writes:
> The index I use is the GIN.
pg_trgm, you mean? That answers one question, but you still didn't
explain what type h.nome_des is, nor how bytea and convert_from()
are getting into the picture.
The second part of that is probably not critical, since the planner
should be willing to reduce the convert_from() call to a constant
for planning purposes, so I'm unclear as to why the estimate for
the ilike clause is so bad. Have you tried increasing the statistics
target for h.nome_des to see if the estimate gets better?
regards, tom lane
Attachments:
[image/png] flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png (33.2K, ../CP2P152MB0274CC07F4D81971F642FBA185A99@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/3-flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png)
download | view image
[image/png] SocialLink_Facebook_32x32_11ddcb69-c640-49e0-88b2-e1038ba38ffa.png (365B, ../CP2P152MB0274CC07F4D81971F642FBA185A99@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/4-SocialLink_Facebook_32x32_11ddcb69-c640-49e0-88b2-e1038ba38ffa.png)
download | view image
[image/png] SocialLink_Instagram_32x32_1a56219d-8d68-474a-8e29-0e536d8241d4.png (753B, ../CP2P152MB0274CC07F4D81971F642FBA185A99@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/5-SocialLink_Instagram_32x32_1a56219d-8d68-474a-8e29-0e536d8241d4.png)
download | view image
[image/png] SocialLink_Linkedin_32x32_6bb30d6c-bdcd-4446-ace2-9daa6eeb5e16.png (468B, ../CP2P152MB0274CC07F4D81971F642FBA185A99@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/6-SocialLink_Linkedin_32x32_6bb30d6c-bdcd-4446-ace2-9daa6eeb5e16.png)
download | view image
[image/jpeg] whatsappimage2021-08-31at18.36.01_c1d35f2c-7adc-42cd-98ff-acbc6b165aa6.jpeg (74.9K, ../CP2P152MB0274CC07F4D81971F642FBA185A99@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/7-whatsappimage2021-08-31at18.36.01_c1d35f2c-7adc-42cd-98ff-acbc6b165aa6.jpeg)
download | view image
[image/png] QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.png (5.8K, ../CP2P152MB0274CC07F4D81971F642FBA185A99@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/8-QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.png)
download | view image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: daniel@flashcourier.com.br, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: RE: Problem with indices from 10 to 13
In-Reply-To: <CP2P152MB0274CC07F4D81971F642FBA185A99@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox