Re: Indexes being ignored after upgrade to 9.5

On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan <nbrennan02(at)gmail(dot)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