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
Date: Tue, 28 Sep 2021 13:45:16 -0400
Message-ID: <2853352.1632851116@sss.pgh.pa.us> (raw)
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>
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 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.
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) (actual time=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) (actual time=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
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: tgl@sss.pgh.pa.us, daniel@flashcourier.com.br, pgsql-performance@lists.postgresql.org
Subject: Re: Problem with indices from 10 to 13
In-Reply-To: <2853352.1632851116@sss.pgh.pa.us>
* 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