|
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 using empresas_pkey on empresas e (cost=0.28..0.30 rows=1 width=17) (actual time=0.028..0.028 rows=1 loops=4)"
" Index Cond: (empresa_id = h.cliente_id)"
" -> Index Scan using contratos_pkey on contratos c (cost=0.28..0.31 rows=1 width=15) (actual time=0.014..0.014 rows=1 loops=4)"
" Index Cond: (ctt_id = h.ctt_id)"
" -> Materialize (cost=0.00..7.23 rows=215 width=27) (actual time=0.009..0.025 rows=64 loops=4)"
" -> Seq Scan on eventos ev (cost=0.00..6.15 rows=215 width=27) (actual time=0.029..0.066 rows=67 loops=1)"
"Planning Time: 11.690 ms"
"Execution Time: 429803.611 ms"
Postgres 10
"QUERY PLAN"
"Limit (cost=28489.06..28494.39 rows=30 width=137) (actual time=211.568..211.581 rows=4 loops=1)"
" -> Result (cost=28489.06..32296.61 rows=21451 width=137) (actual time=211.566..211.578 rows=4 loops=1)"
" -> Sort (cost=28489.06..28542.69 rows=21451 width=105) (actual time=211.548..211.551 rows=4 loops=1)"
" Sort Key: h.hawb_id DESC"
" Sort Method: quicksort Memory: 25kB"
" -> Hash Join (cost=2428.77..27855.52 rows=21451 width=105) (actual time=211.520..211.537 rows=4 loops=1)"
" Hash Cond: (h.ult_eve_id = ev.evento_id)"
" -> Hash Join (cost=2419.93..27735.63 rows=21451 width=62) (actual time=211.315..211.329 rows=4 loops=1)"
" Hash Cond: (h.ctt_id = c.ctt_id)"
" -> Hash Join (cost=2085.82..27345.18 rows=21451 width=55) (actual time=206.516..206.529 rows=4 loops=1)"
" Hash Cond: (h.cliente_id = e.empresa_id)"
" -> 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))"
" -> Hash (cost=982.77..982.77 rows=3577 width=17) (actual time=4.542..4.542 rows=3577 loops=1)"
" Buckets: 4096 Batches: 1 Memory Usage: 211kB"
" -> Seq Scan on empresas e (cost=0.00..982.77 rows=3577 width=17) (actual time=0.007..3.189 rows=3577 loops=1)"
" -> Hash (cost=255.16..255.16 rows=6316 width=15) (actual time=4.777..4.777 rows=6316 loops=1)"
" Buckets: 8192 Batches: 1 Memory Usage: 361kB"
" -> Seq Scan on contratos c (cost=0.00..255.16 rows=6316 width=15) (actual time=0.006..2.420 rows=6316 loops=1)"
" -> Hash (cost=6.15..6.15 rows=215 width=27) (actual time=0.186..0.186 rows=215 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 21kB"
" -> Seq Scan on eventos ev (cost=0.00..6.15 rows=215 width=27) (actual time=0.008..0.103 rows=215 loops=1)"
"Planning time: 2.267 ms"
"Execution time: 211.776 ms"
Comand:
explain analyse
select*
from hawbs h
inner join empresas e on h.cliente_id = e.empresa_id
inner join contratos c on h.ctt_id = c.ctt_id
inner join eventos ev on h.ult_eve_id = ev.evento_id
where h.nome_des ilike '%STEPHANY STOEW LEANDRO%'
and h.tipo_hawb_id in (1,10,3) order by h.hawb_id desc limit 30;
|
