Re: Problem with indices from 10 to 13

Daniel Diniz <daniel@flashcourier.com.br> writes:
> 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
explainin 10 and 13 and the difference is absurd, the indices and data are the same in 2. I've re-created and
re-indexedbut I don't know what changed from 10 to 13 which made the performance so bad, I don't know if it needs some
extraparameter in some conf on 13. 

This complaint is missing an awful lot of supporting information.

> "                                ->  Bitmap Heap Scan on hawbs h  (cost=1058.34..26261.32 rows=21451 width=46)
(actualtime=201.956..201.966 rows=4 loops=1)" 
> "                                      Recheck Cond: ((nome_des)::text ~~*
convert_from('\x255354455048414e592053544f4557204c45414e44524f25'::bytea,'LATIN1'::name))" 
> "                                      Filter: (tipo_hawb_id = ANY ('{1,10,3}'::integer[]))"
> "                                      Heap Blocks: exact=4"
> "                                      ->  Bitmap Index Scan on idx_nome_des  (cost=0.00..1052.98 rows=22623 width=0)
(actualtime=201.942..201.943 rows=4 loops=1)" 
> "                                            Index Cond: ((nome_des)::text ~~*
convert_from('\x255354455048414e592053544f4557204c45414e44524f25'::bytea,'LATIN1'::name))" 

For starters, how in the world did you get that query condition out of

> where h.nome_des ilike '%STEPHANY STOEW LEANDRO%'

?  What data type is h.nome_des, anyway?  And what kind of index
is that --- it couldn't be a plain btree, because we wouldn't consider
~~* to be indexable by a btree.

However, the long and the short of it is that this rowcount estimate
is off by nearly four orders of magnitude (21451 estimated vs. 4
actual is pretty awful).  It's probably just luck that you got an
acceptable plan out of v10, and bad luck that you didn't get one
out of v13 --- v13's estimate is not better, but it's not much
worse either.  You need to do something about improving that
estimate if you'd like reliable query planning.  Since I'm not
too sure which operator you're actually invoking, it's hard to
offer good advice about how hard that might be.

            regards, tom lane