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

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: [GENERAL] Indexes being ignored after upgrade to 9.5
Дата
Msg-id CAMkU=1zLiHRT6ay90wkwCbQpzW_xeHQMG==8sj3Ze0f905FKSQ@mail.gmail.com
обсуждение исходный текст
Ответ на [GENERAL] Indexes being ignored after upgrade to 9.5  (Nick Brennan <nbrennan02@gmail.com>)
Список pgsql-general
Дерево обсуждения
On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan <nbrennan02@gmail.com> wrote:
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.


Can you show the explain (analyze) plans for both forcing and non-forcing?  And with both 9.5 and the old 9.2, if that is still available.
 


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.

Still as slow as what?  As slow as when you use the seq scan, or as slow as when you used index scans back under 9.2, or as slow as the the non-duplicate indexes were?

Cheers,

Jeff

В списке pgsql-general по дате отправления:

Предыдущее
От: vinny
Дата:
Сообщение: Re: [GENERAL] Developer GUI tools for PostgreSQL
Следующее
От: Jeff Janes
Дата:
Сообщение: Re: [GENERAL] Monitoring of a hot standby with a largely idle master