Em ter., 28 de set. de 2021 às 12:40, Daniel Diniz <dan...@flashcourier.com.br> escreveu: > > 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. > > > > Postgres 13 > > > > "QUERY PLAN" > > "Limit (cost=1.13..26855.48 rows=30 width=137) (actual > > time=10886.585..429803.463 rows=4 loops=1)" > > " -> Nested Loop (cost=1.13..19531164.71 rows=21819 width=137) (actual > > time=10886.584..429803.457 rows=4 loops=1)" > > " Join Filter: (h.ult_eve_id = ev.evento_id)" > > " Rows Removed by Join Filter: 252" > > " -> Nested Loop (cost=1.13..19457514.32 rows=21819 width=62) > > (actual time=10886.326..429803.027 rows=4 loops=1)" > > " -> Nested Loop (cost=0.85..19450780.70 rows=21819 > > width=55) (actual time=10886.259..429802.908 rows=4 loops=1)" > > " -> Index Scan Backward using hawbs_pkey on hawbs h > > (cost=0.57..19444209.67 rows=21819 width=46) (actual > > time=10886.119..429802.676 rows=4 loops=1)" > > " Filter: ((tipo_hawb_id = ANY > > ('{1,10,3}'::integer[])) AND ((nome_des)::text ~~* > > convert_from('\x255354455048414e592053544f4557204c45414e44524f25'::bytea, > > 'LATIN1'::name)))" > > " Rows Removed by Filter: 239188096" > > Index Scan Backward looks suspicious to me. > 239,188,096 rows removed by filter it's a lot of work. > > Do you, run analyze?
PostgreSQL has an unfortunate love of scanning the pkey index backwards when you use LIMIT. Try pushing your actual query into a subquery (with an offset 0 to prevent it being optimized out) and then do the LIMIT outside it.