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?
