From: Alan Hodgson <ahodgson@lists.simkin.ca>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Problem with indices from 10 to 13
Date: Tue, 28 Sep 2021 12:26:23 -0700
Message-ID: <df211760b94da7654d8f2045f3dd67222a3bcd4b.camel@lists.simkin.ca> (raw)
In-Reply-To: <CP2P152MB027472D49F8202799C26E51785A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM>
References: <8d2ba21d-2318-4f02-a8fd-04c7d97e22ab.e854021a-9da0-4eba-a51f-e5ff782985ef.17ca0caa-f521-44ce-817a-17cbc713d135@emailsignatures365.codetwo.com>
<CP2P152MB0274080DF43233A4DD9AE14685A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM>
<CAEudQAryHBN-55mFnWDN+CaLjbsDZ0X6raQZG86j+ZV-8Lp-pg@mail.gmail.com>
<CP2P152MB027472D49F8202799C26E51785A89@CP2P152MB0274.LAMP152.PROD.OUTLOOK.COM>
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.
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: ahodgson@lists.simkin.ca, pgsql-performance@lists.postgresql.org
Subject: Re: Problem with indices from 10 to 13
In-Reply-To: <df211760b94da7654d8f2045f3dd67222a3bcd4b.camel@lists.simkin.ca>
* 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