Indexes being ignored after upgrade to 9.5

From: Nick Brennan <nbrennan02(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Indexes being ignored after upgrade to 9.5
Date: 2017-07-26 05:34:15
Message-ID: CADN3jkZYdKz+Wxt9NfiwJugiqDCvk31hLJCZ1cCmBAtDuamiQw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

We have recently promoted our Prod DB slave (2TB) to migrate to new
hardware, and upgraded from v9.2.9.21 to 9.5.1.6 using pg_upgrade.

The upgrade went without incident and we have been running for a week, but
the optimizer is ignoring indexes on 2 of our largest partitioned tables
causing very slow response times.

The indexes are Btree indexes on BIGINT columns, which the optimizer used
to return queries with ms response times on 9.2. Post-upgrade the queries
sequential scan and do not use indexes unless we force them.

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?

Many thanks

Nick

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Paquier 2017-07-26 08:44:26 Re: Monitoring of a hot standby with a largely idle master
Previous Message Jov 2017-07-26 01:19:09 Re: Major Version Upgradation from 9.4 to 9.6 in Replication Environment