From: | Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> |
---|---|
To: | Daniel Diniz <daniel(at)flashcourier(dot)com(dot)br> |
Cc: | "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Problem with indices from 10 to 13 |
Date: | 2021-09-28 17:27:01 |
Message-ID: | CAEudQAryHBN-55mFnWDN+CaLjbsDZ0X6raQZG86j+ZV-8Lp-pg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Em ter., 28 de set. de 2021 às 12:40, Daniel Diniz <
daniel(at)flashcourier(dot)com(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-09-28 17:45:16 | Re: Problem with indices from 10 to 13 |
Previous Message | Daniel Diniz | 2021-09-28 15:39:52 | Problem with indices from 10 to 13 |