From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Diniz <daniel@flashcourier.com.br>
cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Problem with indices from 10 to 13
In-reply-to: 
 <CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM>
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>
Comments: In-reply-to Daniel Diniz <daniel@flashcourier.com.br>
	message dated "Tue, 28 Sep 2021 15:39:52 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2853351.1632851116.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 28 Sep 2021 13:45:16 -0400
Message-ID: <2853352.1632851116@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2853352.1632851116%40sss.pgh.pa.us>
Precedence: bulk

Daniel Diniz <daniel@flashcourier.com.br> writes:
> Hello I migrated from postgres 10 to 13 and I noticed that there was a b=
ig increase in a querie that I use, I did explain in 10 and 13 and the dif=
ference 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.

This complaint is missing an awful lot of supporting information.

> "                                ->  Bitmap Heap Scan on hawbs h  (cost=3D=
1058.34..26261.32 rows=3D21451 width=3D46) (actual time=3D201.956..201.966=
 rows=3D4 loops=3D1)"
> "                                      Recheck Cond: ((nome_des)::text ~=
~* convert_from('\x255354455048414e592053544f4557204c45414e44524f25'::byte=
a, 'LATIN1'::name))"
> "                                      Filter: (tipo_hawb_id =3D ANY ('{=
1,10,3}'::integer[]))"
> "                                      Heap Blocks: exact=3D4"
> "                                      ->  Bitmap Index Scan on idx_nome=
_des  (cost=3D0.00..1052.98 rows=3D22623 width=3D0) (actual time=3D201.942=
..201.943 rows=3D4 loops=3D1)"
> "                                            Index Cond: ((nome_des)::te=
xt ~~* 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