RE: Problem with indices from 10 to 13

[Daniel Diniz <daniel@xxxxxxxxxxxxxxxxxxx>]

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

Daniel Diniz Desenvolvimento Cel.: 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: Tom Lane <tgl@xxxxxxxxxxxxx>
Enviado: terça-feira, 28 de setembro de 2021 19:41
Para: Daniel Diniz <daniel@xxxxxxxxxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>
Assunto: Re: Problem with indices from 10 to 13

 

Daniel Diniz <daniel@xxxxxxxxxxxxxxxxxxx> 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