MIME-Version: 1.0
In-Reply-To: <C1FC53B7-DA51-4060-B1DE-6D6D14682DA3@gmail.com>
References: 
 <CAOGex3nXTRPZTD-KeoSwD=bj62hQrMK+6h30u09srV71sePqUA@mail.gmail.com>
 <CAHyXU0wDi9VjfGC8aQeLsBq4ncLVOKJ=1QR6iRq71U2HXQso4Q@mail.gmail.com>
 <CAOGex3=0DB-R9V558CkeoSOuU4KG_RyNh5etzo85o43xGcuVvQ@mail.gmail.com>
 <C1FC53B7-DA51-4060-B1DE-6D6D14682DA3@gmail.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Wed, 26 Jul 2017 14:44:03 -0700
Message-ID: 
 <CAH2-Wz=K2BmveW+qYb_yKHA=jbBvKFvn43AE6SkgQJdhouc+Tg@mail.gmail.com>
Subject: Re: Slow query after 9.3 to 9.6 migration
To: Daniel Blanch Bataller <daniel.blanch.bataller@gmail.com>
Cc: =?UTF-8?Q?Fl=C3=A1vio_Henrique?= <yoshimit@gmail.com>,
	postgres performance list <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Thu, Jan 5, 2017 at 9:51 AM, Daniel Blanch Bataller
<daniel.blanch.bataller@gmail.com> wrote:
> If just recreating the index now it uses it, it might mean that the index
> was bloated, that is, it grew so big that it was cheaper a seq scan.
>
> I=E2=80=99ve seen another case recently where postgres 9.6 wasn=E2=80=99t=
 using the right
> index in a query, I was able to reproduce the issue crafting index bigger,
> much bigger than it should be.
>
> Can you record index size as it is now? Keep this info, and If problem
> happens again check indexes size, and see if they have grow too much.
>
> i.e. SELECT relname, relpages, reltuples FROM pg_class WHERE relname =3D
> =E2=80=98index_name'
>
> This might help to see if this is the problem, that indexes are growing t=
oo
> much for some reason.

Are these unique indexes or not? Did Flavio have a workload with many UPDAT=
Es?

I ask these questions because I think it's possible that this is
explained by a regression in 9.5's handling of index bloat, described
here:

http://postgr.es/m/CAH2-Wz=3DSfAKVMv1x9Jh19EJ8am8TZn9f-yECipS9HrrRqSswnA@ma=
il.gmail.com

I'm trying to track down cases where this could be an issue, to get a
better sense of the problem.

--=20
Peter Geoghegan


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance