|
Tom,
The index I use is the GIN. I've been using it for about 2 years in 10 it always gave me an almost immediate response with ilike.
But testing on 13 I don't know why it takes I already redid the index and reindexed but without significant improvement from 10 seconds to minutes or even hour on 13. The brtree indices has the same behavior only that I have GIN q this occurs.
Name de index : "idx_nome_des" gin (nome_des)
De: Tom Lane <tgl@xxxxxxxxxxxxx>
Enviado: terça-feira, 28 de setembro de 2021 14:45 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:
> 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 |
