pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with indices from 10 to 13
11+ messages / 6 participants
[nested] [flat]
* Problem with indices from 10 to 13
@ 2021-09-28 15:39 Daniel Diniz <daniel@flashcourier.com.br>
0 siblings, 2 replies; 11+ messages in thread
From: Daniel Diniz @ 2021-09-28 15:39 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with indices from 10 to 13
@ 2021-09-28 17:27 Ranier Vilela <ranier.vf@gmail.com>
parent: Daniel Diniz <daniel@flashcourier.com.br>
1 sibling, 1 reply; 11+ messages in thread
From: Ranier Vilela @ 2021-09-28 17:27 UTC (permalink / raw)
To: Daniel Diniz <daniel@flashcourier.com.br>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Em ter., 28 de set. de 2021 às 12:40, Daniel Diniz <
daniel@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?
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with indices from 10 to 13
@ 2021-09-28 17:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Daniel Diniz <daniel@flashcourier.com.br>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2021-09-28 17:45 UTC (permalink / raw)
To: Daniel Diniz <daniel@flashcourier.com.br>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: Problem with indices from 10 to 13
@ 2021-09-28 19:02 Daniel Diniz <daniel@flashcourier.com.br>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Daniel Diniz @ 2021-09-28 19:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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)
[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:QRf774d402-83ce-43c8-af9e-d16f09c62123.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."
________________________________
De: Tom Lane <tgl@sss.pgh.pa.us>
Enviado: terça-feira, 28 de setembro de 2021 14:45
Para: Daniel Diniz <daniel@flashcourier.com.br>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Assunto: Re: Problem with indices from 10 to 13
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
Attachments:
[image/png] flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png (33.2K, ../../CP2P152MB027497F5EBE88A1491AB833985A89@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, ../../CP2P152MB027497F5EBE88A1491AB833985A89@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, ../../CP2P152MB027497F5EBE88A1491AB833985A89@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, ../../CP2P152MB027497F5EBE88A1491AB833985A89@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, ../../CP2P152MB027497F5EBE88A1491AB833985A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/7-whatsappimage2021-08-31at18.36.01_c1d35f2c-7adc-42cd-98ff-acbc6b165aa6.jpeg)
download | view image
[image/png] QRf774d402-83ce-43c8-af9e-d16f09c62123.png (5.8K, ../../CP2P152MB027497F5EBE88A1491AB833985A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/8-QRf774d402-83ce-43c8-af9e-d16f09c62123.png)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: Problem with indices from 10 to 13
@ 2021-09-28 19:05 Daniel Diniz <daniel@flashcourier.com.br>
parent: Ranier Vilela <ranier.vf@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Daniel Diniz @ 2021-09-28 19:05 UTC (permalink / raw)
To: Ranier Vilela <ranier.vf@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Ranier,
ran vacuumdb -U postgres -j100 -p5434 -azv
and even so it didn't improve.
Now ir running for 1h10min and not finished de explain after run the comand up.😥
[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."
________________________________
De: Ranier Vilela <ranier.vf@gmail.com>
Enviado: terça-feira, 28 de setembro de 2021 14:27
Para: Daniel Diniz <daniel@flashcourier.com.br>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Assunto: Re: Problem with indices from 10 to 13
Em ter., 28 de set. de 2021 às 12:40, Daniel Diniz <daniel@flashcourier.com.br<mailto:daniel@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?
regards,
Ranier Vilela
Attachments:
[image/png] flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png (33.2K, ../../CP2P152MB027472D49F8202799C26E51785A89@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, ../../CP2P152MB027472D49F8202799C26E51785A89@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, ../../CP2P152MB027472D49F8202799C26E51785A89@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, ../../CP2P152MB027472D49F8202799C26E51785A89@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, ../../CP2P152MB027472D49F8202799C26E51785A89@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, ../../CP2P152MB027472D49F8202799C26E51785A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/8-QR6626a761-6a9c-427c-bd7e-09a0ad5c91a8.png)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with indices from 10 to 13
@ 2021-09-28 19:26 Alan Hodgson <ahodgson@lists.simkin.ca>
parent: Daniel Diniz <daniel@flashcourier.com.br>
0 siblings, 0 replies; 11+ messages in thread
From: Alan Hodgson @ 2021-09-28 19:26 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Em ter., 28 de set. de 2021 às 12:40, Daniel Diniz <daniel@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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with indices from 10 to 13
@ 2021-09-28 22:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Daniel Diniz <daniel@flashcourier.com.br>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2021-09-28 22:41 UTC (permalink / raw)
To: Daniel Diniz <daniel@flashcourier.com.br>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Daniel Diniz <daniel@flashcourier.com.br> writes:
> The index I use is the GIN.
pg_trgm, you mean? That answers one question, but you still didn't
explain what type h.nome_des is, nor how bytea and convert_from()
are getting into the picture.
The second part of that is probably not critical, since the planner
should be willing to reduce the convert_from() call to a constant
for planning purposes, so I'm unclear as to why the estimate for
the ilike clause is so bad. Have you tried increasing the statistics
target for h.nome_des to see if the estimate gets better?
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: Problem with indices from 10 to 13
@ 2021-09-29 02:11 Daniel Diniz <daniel@flashcourier.com.br>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Daniel Diniz @ 2021-09-29 02:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Tom,
"pg_trgm, you mean? That answers one question, but you still didn't
explain what type h.nome_des is, nor how bytea and convert_from()
are getting into the picture."
The column type is: nome_des | character varying(60)
"The second part of that is probably not critical, since the planner
should be willing to reduce the convert_from() call to a constant
for planning purposes, so I'm unclear as to why the estimate for
the ilike clause is so bad. Have you tried increasing the statistics
target for h.nome_des to see if the estimate gets better?"
How do i increase the statistics target for h.nome_des?
And why uploading the dump at 10 and at 13 is there this difference?
Thanks
[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:QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.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."
________________________________
De: Tom Lane <tgl@sss.pgh.pa.us>
Enviado: terça-feira, 28 de setembro de 2021 19:41
Para: Daniel Diniz <daniel@flashcourier.com.br>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Assunto: Re: Problem with indices from 10 to 13
Daniel Diniz <daniel@flashcourier.com.br> writes:
> The index I use is the GIN.
pg_trgm, you mean? That answers one question, but you still didn't
explain what type h.nome_des is, nor how bytea and convert_from()
are getting into the picture.
The second part of that is probably not critical, since the planner
should be willing to reduce the convert_from() call to a constant
for planning purposes, so I'm unclear as to why the estimate for
the ilike clause is so bad. Have you tried increasing the statistics
target for h.nome_des to see if the estimate gets better?
regards, tom lane
Attachments:
[image/png] flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png (33.2K, ../../CP2P152MB0274CC07F4D81971F642FBA185A99@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, ../../CP2P152MB0274CC07F4D81971F642FBA185A99@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, ../../CP2P152MB0274CC07F4D81971F642FBA185A99@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, ../../CP2P152MB0274CC07F4D81971F642FBA185A99@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, ../../CP2P152MB0274CC07F4D81971F642FBA185A99@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/7-whatsappimage2021-08-31at18.36.01_c1d35f2c-7adc-42cd-98ff-acbc6b165aa6.jpeg)
download | view image
[image/png] QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.png (5.8K, ../../CP2P152MB0274CC07F4D81971F642FBA185A99@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM/8-QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.png)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with indices from 10 to 13
@ 2021-09-29 02:18 Justin Pryzby <pryzby@telsasoft.com>
parent: Daniel Diniz <daniel@flashcourier.com.br>
0 siblings, 1 reply; 11+ messages in thread
From: Justin Pryzby @ 2021-09-29 02:18 UTC (permalink / raw)
To: Daniel Diniz <daniel@flashcourier.com.br>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
On Wed, Sep 29, 2021 at 02:11:15AM +0000, Daniel Diniz wrote:
> How do i increase the statistics target for h.nome_des?
> And why uploading the dump at 10 and at 13 is there this difference?
It's like ALTER TABLE h ALTER nome_des SET STATISTICS 2000; ANALYZE h;
https://www.postgresql.org/docs/current/sql-altertable.html
--
Justin
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: Problem with indices from 10 to 13
@ 2021-09-29 04:00 Daniel Diniz <daniel@flashcourier.com.br>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 11+ messages in thread
From: Daniel Diniz @ 2021-09-29 04:00 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Justin tested it with some parameters 200, 2000, 10000, -1 and the 3 spent more or less the same time
exemple ALTER TABLE hawbs ALTER nome_des SET STATISTICS 2000; ANALYZE hawbs;:
"QUERY PLAN"
"Limit (cost=1.13..28049.86 rows=30 width=137) (actual time=5462.123..363089.923 rows=4 loops=1)"
" -> Nested Loop (cost=1.13..19523788.64 rows=20882 width=137) (actual time=5462.122..363089.915 rows=4 loops=1)"
" Join Filter: (h.ult_eve_id = ev.evento_id)"
" Rows Removed by Join Filter: 252"
" -> Nested Loop (cost=1.13..19453301.90 rows=20882 width=62) (actual time=5461.844..363089.429 rows=4 loops=1)"
" -> Nested Loop (cost=0.85..19446849.38 rows=20882 width=55) (actual time=5461.788..363089.261 rows=4 loops=1)"
" -> Index Scan Backward using hawbs_pkey on hawbs h (cost=0.57..19440557.11 rows=20882 width=46) (actual time=5461.644..363088.839 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.037..0.038 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.021..0.021 rows=1 loops=4)"
" Index Cond: (ctt_id = h.ctt_id)"
" -> Materialize (cost=0.00..7.23 rows=215 width=27) (actual time=0.011..0.023 rows=64 loops=4)"
" -> Seq Scan on eventos ev (cost=0.00..6.15 rows=215 width=27) (actual time=0.033..0.052 rows=67 loops=1)"
"Planning Time: 10.452 ms"
"Execution Time: 363090.127 ms"
[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:QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.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."
________________________________
De: Justin Pryzby <pryzby@telsasoft.com>
Enviado: terça-feira, 28 de setembro de 2021 23:18
Para: Daniel Diniz <daniel@flashcourier.com.br>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Assunto: Re: Problem with indices from 10 to 13
On Wed, Sep 29, 2021 at 02:11:15AM +0000, Daniel Diniz wrote:
> How do i increase the statistics target for h.nome_des?
> And why uploading the dump at 10 and at 13 is there this difference?
It's like ALTER TABLE h ALTER nome_des SET STATISTICS 2000; ANALYZE h;
https://www.postgresql.org/docs/current/sql-altertable.html
--
Justin
Attachments:
[image/png] flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png (33.2K, ../../RO1P152MB0282CC38925E6B8194714BE785A99@RO1P152MB0282.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, ../../RO1P152MB0282CC38925E6B8194714BE785A99@RO1P152MB0282.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, ../../RO1P152MB0282CC38925E6B8194714BE785A99@RO1P152MB0282.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, ../../RO1P152MB0282CC38925E6B8194714BE785A99@RO1P152MB0282.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, ../../RO1P152MB0282CC38925E6B8194714BE785A99@RO1P152MB0282.LAMP152.PROD.OUTLOOK.COM/7-whatsappimage2021-08-31at18.36.01_c1d35f2c-7adc-42cd-98ff-acbc6b165aa6.jpeg)
download | view image
[image/png] QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.png (5.8K, ../../RO1P152MB0282CC38925E6B8194714BE785A99@RO1P152MB0282.LAMP152.PROD.OUTLOOK.COM/8-QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.png)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Problem with indices from 10 to 13
@ 2021-09-29 04:59 Pavel Stehule <pavel.stehule@gmail.com>
parent: Daniel Diniz <daniel@flashcourier.com.br>
0 siblings, 0 replies; 11+ messages in thread
From: Pavel Stehule @ 2021-09-29 04:59 UTC (permalink / raw)
To: Daniel Diniz <daniel@flashcourier.com.br>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
st 29. 9. 2021 v 6:01 odesílatel Daniel Diniz <daniel@flashcourier.com.br>
napsal:
> Justin tested it with some parameters 200, 2000, 10000, -1 and the 3 spent
> more or less the same time
>
> exemple ALTER TABLE hawbs ALTER nome_des SET STATISTICS 2000; ANALYZE
> hawbs;:
> "QUERY PLAN"
> "Limit (cost=1.13..28049.86 rows=30 width=137) (actual
> time=5462.123..363089.923 rows=4 loops=1)"
> " -> Nested Loop (cost=1.13..19523788.64 rows=20882 width=137) (actual
> time=5462.122..363089.915 rows=4 loops=1)"
> " Join Filter: (h.ult_eve_id = ev.evento_id)"
> " Rows Removed by Join Filter: 252"
> " -> Nested Loop (cost=1.13..19453301.90 rows=20882 width=62)
> (actual time=5461.844..363089.429 rows=4 loops=1)"
> " -> Nested Loop (cost=0.85..19446849.38 rows=20882
> width=55) (actual time=5461.788..363089.261 rows=4 loops=1)"
> " -> Index Scan Backward using hawbs_pkey on hawbs h
> (cost=0.57..19440557.11 rows=20882 width=46) (actual
> time=5461.644..363088.839 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.037..0.038 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.021..0.021 rows=1
> loops=4)"
> " Index Cond: (ctt_id = h.ctt_id)"
> " -> Materialize (cost=0.00..7.23 rows=215 width=27) (actual
> time=0.011..0.023 rows=64 loops=4)"
> " -> Seq Scan on eventos ev (cost=0.00..6.15 rows=215
> width=27) (actual time=0.033..0.052 rows=67 loops=1)"
> "Planning Time: 10.452 ms"
> "Execution Time: 363090.127 ms"
>
Maybe you can try composite index based on hawbs_pkey, and tipo_hawb_id
the second problem can be the low value of LIMIT - got you faster result
without LIMIT clause?
Regards
Pavel
>
>
>
> *Daniel Diniz*Desenvolvimento
>
> Cel.: *11981464923*
>
>
> *www.flashcourier.com.br* <http://www.flashcourier.com.br;
>
> <https://www.facebook.com/flashcourieroficial;
> <https://www.instagram.com/flashcourieroficial;
> <https://www.linkedin.com/company/flashcourieroficial;
>
> #SomosTodosFlash #GrupoMOVE3
>
>
> <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."*
> ------------------------------
> *De:* Justin Pryzby <pryzby@telsasoft.com>
> *Enviado:* terça-feira, 28 de setembro de 2021 23:18
> *Para:* Daniel Diniz <daniel@flashcourier.com.br>
> *Cc:* Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
> <pgsql-performance@lists.postgresql.org>
> *Assunto:* Re: Problem with indices from 10 to 13
>
> On Wed, Sep 29, 2021 at 02:11:15AM +0000, Daniel Diniz wrote:
> > How do i increase the statistics target for h.nome_des?
> > And why uploading the dump at 10 and at 13 is there this difference?
>
> It's like ALTER TABLE h ALTER nome_des SET STATISTICS 2000; ANALYZE h;
> https://www.postgresql.org/docs/current/sql-altertable.html
>
> --
> Justin
>
Attachments:
[image/png] flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png (33.2K, ../../CAFj8pRA0S+9mmNoLnJjk0yv4j9JOs4NfaREOS9Y35ks2mvFcHA@mail.gmail.com/3-flashcourier_6bc44896-f19b-4119-a728-f70d866e7cdd.png)
download | view image
[image/png] SocialLink_Facebook_32x32_11ddcb69-c640-49e0-88b2-e1038ba38ffa.png (365B, ../../CAFj8pRA0S+9mmNoLnJjk0yv4j9JOs4NfaREOS9Y35ks2mvFcHA@mail.gmail.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, ../../CAFj8pRA0S+9mmNoLnJjk0yv4j9JOs4NfaREOS9Y35ks2mvFcHA@mail.gmail.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, ../../CAFj8pRA0S+9mmNoLnJjk0yv4j9JOs4NfaREOS9Y35ks2mvFcHA@mail.gmail.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, ../../CAFj8pRA0S+9mmNoLnJjk0yv4j9JOs4NfaREOS9Y35ks2mvFcHA@mail.gmail.com/7-whatsappimage2021-08-31at18.36.01_c1d35f2c-7adc-42cd-98ff-acbc6b165aa6.jpeg)
download | view image
[image/png] QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.png (5.8K, ../../CAFj8pRA0S+9mmNoLnJjk0yv4j9JOs4NfaREOS9Y35ks2mvFcHA@mail.gmail.com/8-QR7a1d4c80-9fed-4206-a9dd-ab8cda250534.png)
download | view image
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2021-09-29 04:59 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-28 15:39 Problem with indices from 10 to 13 Daniel Diniz <daniel@flashcourier.com.br>
2021-09-28 17:27 ` Ranier Vilela <ranier.vf@gmail.com>
2021-09-28 19:05 ` Daniel Diniz <daniel@flashcourier.com.br>
2021-09-28 19:26 ` Alan Hodgson <ahodgson@lists.simkin.ca>
2021-09-28 17:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-09-28 19:02 ` Daniel Diniz <daniel@flashcourier.com.br>
2021-09-28 22:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-09-29 02:11 ` Daniel Diniz <daniel@flashcourier.com.br>
2021-09-29 02:18 ` Justin Pryzby <pryzby@telsasoft.com>
2021-09-29 04:00 ` Daniel Diniz <daniel@flashcourier.com.br>
2021-09-29 04:59 ` Pavel Stehule <pavel.stehule@gmail.com>
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