public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter Geoghegan <[email protected]>
To: Daniel Blanch Bataller <[email protected]>
Cc: Flávio Henrique <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: Slow query after 9.3 to 9.6 migration
Date: Wed, 26 Jul 2017 14:44:03 -0700
Message-ID: <CAH2-Wz=K2BmveW+qYb_yKHA=jbBvKFvn43AE6SkgQJdhouc+Tg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAOGex3nXTRPZTD-KeoSwD=bj62hQrMK+6h30u09srV71sePqUA@mail.gmail.com>
	<CAHyXU0wDi9VjfGC8aQeLsBq4ncLVOKJ=1QR6iRq71U2HXQso4Q@mail.gmail.com>
	<CAOGex3=0DB-R9V558CkeoSOuU4KG_RyNh5etzo85o43xGcuVvQ@mail.gmail.com>
	<[email protected]>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

On Thu, Jan 5, 2017 at 9:51 AM, Daniel Blanch Bataller
<[email protected]> 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’ve seen another case recently where postgres 9.6 wasn’t 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 =
> ‘index_name'
>
> This might help to see if this is the problem, that indexes are growing too
> much for some reason.

Are these unique indexes or not? Did Flavio have a workload with many UPDATEs?

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=SfAKVMv1x9Jh19EJ8am8TZn9f-yECipS9HrrRqSswnA@mail.gmail.com

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

-- 
Peter Geoghegan


-- 
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Slow query after 9.3 to 9.6 migration
  In-Reply-To: <CAH2-Wz=K2BmveW+qYb_yKHA=jbBvKFvn43AE6SkgQJdhouc+Tg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox