Re: Slow query after 9.3 to 9.6 migration

On Thu, Jan 5, 2017 at 9:51 AM, Daniel Blanch Bataller
<daniel(dot)blanch(dot)bataller(at)gmail(dot)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’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