pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Daniel Diniz <daniel@flashcourier.com.br>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Problem with indices from 10 to 13
Date: Tue, 28 Sep 2021 15:39:52 +0000
Message-ID: <CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM> (raw )
References: <8d2ba21d-2318-4f02-a8fd-04c7d97e22ab.e854021a-9da0-4eba-a51f-e5ff782985ef.17ca0caa-f521-44ce-817a-17cbc713d135@emailsignatures365.codetwo.com >
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;
[cid:flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png]
Daniel Diniz
Desenvolvimento
Cel.: 11981464923
www.flashcourier.com.br<http://www.flashcourier.com.br ;
[cid:SocialLink_Facebook_32x32_11ddcb69-c640-49e0-88b2-e1038ba38ffa.png]<https://www.facebook.com/flashcourieroficial ; [cid:SocialLink_Instagram_32x32_1a56219d-8d68-474a-8e29-0e536d8241d4.png] <https://www.instagram.com/flashcourieroficial ; [cid:SocialLink_Linkedin_32x32_6bb30d6c-bdcd-4446-ace2-9daa6eeb5e16.png] <https://www.linkedin.com/company/flashcourieroficial ;
#SomosTodosFlash #GrupoMOVE3
[cid:QR6626a761-6a9c-427c-bd7e-09a0ad5c91a8.png]
[cid:whatsappimage2021-08-31at18.36.01_c1d35f2c-7adc-42cd-98ff-acbc6b165aa6.jpeg]<https://premio.reclameaqui.com.br/votacao ;
"Esta mensagem e seus anexos são dirigidos exclusivamente para os seus destinatários, podendo conter informação confidencial e/ou legalmente privilegiada. Se você não for destinatário desta mensagem, não deve revelar, copiar, distribuir ou de qualquer forma utilizá-la. A empresa não se responsabiliza por alterações no conteúdo desta mensagem depois do seu envio."
Attachments:
[image/png] flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png (33.2K, ../CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/3-flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png)
download | view image
[image/png] SocialLink_Facebook_32x32_11ddcb69-c640-49e0-88b2-e1038ba38ffa.png (365B, ../CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/4-SocialLink_Facebook_32x32_11ddcb69-c640-49e0-88b2-e1038ba38ffa.png)
download | view image
[image/png] SocialLink_Instagram_32x32_1a56219d-8d68-474a-8e29-0e536d8241d4.png (753B, ../CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/5-SocialLink_Instagram_32x32_1a56219d-8d68-474a-8e29-0e536d8241d4.png)
download | view image
[image/png] SocialLink_Linkedin_32x32_6bb30d6c-bdcd-4446-ace2-9daa6eeb5e16.png (468B, ../CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/6-SocialLink_Linkedin_32x32_6bb30d6c-bdcd-4446-ace2-9daa6eeb5e16.png)
download | view image
[image/jpeg] whatsappimage2021-08-31at18.36.01_c1d35f2c-7adc-42cd-98ff-acbc6b165aa6.jpeg (74.9K, ../CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/7-whatsappimage2021-08-31at18.36.01_c1d35f2c-7adc-42cd-98ff-acbc6b165aa6.jpeg)
download | view image
[image/png] QR6626a761-6a9c-427c-bd7e-09a0ad5c91a8.png (5.8K, ../CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/8-QR6626a761-6a9c-427c-bd7e-09a0ad5c91a8.png)
download | view image
view thread (11+ messages) latest in thread
Message-ID: <CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM>
Permalink: ../CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/
Also on: postgresql.org/message-id/CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM
copy link · copy postgr.es
reply 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: daniel@flashcourier.com.br, pgsql-performance@lists.postgresql.org
Subject: Re: Problem with indices from 10 to 13
In-Reply-To: <CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM>
* 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