Re: [GENERAL] Indexes being ignored after upgrade to 9.5

On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan <nbrennan02@gmail.com> wrote:
> We've added duplicate indexes and analyzing, however the new indexes are
> still ignored unless we force using enable_seqscan=no or reduce
> random_page_cost to 2. The query response times using the new indexes are
> still as slow when we do this. Checking pg_stat_user_indexes the number of
> tuples returned per idx_scan is far greater after the upgrade than before.
> All indexes show valid in pg_indexes.
>
>
> We have tried increasing effective_cache_size but no effect (the queries
> appear to go slower). The DB is 24x7 so we cannot reindex the tables/
> partitions.
>
>
> Can anyone suggest why this would be happening?

Are the indexes bloated? Are they larger than before, as indicated by
psql's \di+ or similar? Did you notice that this happened immediately,
or did it take a while? Are these unique indexes or not? Do you have a
workload with many UPDATEs?

I ask all 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

--
Peter Geoghegan