Re: Indexes being ignored after upgrade to 9.5

Hi Peter,

Many thanks for your response. I tried to cancel the thread, it was unfortunately stupidity that was the issue. We'd been forced to manually analyze our tables due to time constraints, and one of the table partitions read in the query was missed. It was reporting a bitmap index scan on the parent so we thought all was ok, and was then causing other tables to sequential scan.

A further misunderstanding was that an explain analyze would initiate stats gathering on all queried tables, however this is not the case.

Thanks again for your response, we'll check the behaviour you report.

Best regards
Nick

> On 27 Jul 2017, at 00:40, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
>> On Wed, Jul 26, 2017 at 2:05 PM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>>> On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan <nbrennan02(at)gmail(dot)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.
>
> I assume that you mean that pg_stat_user_indexes.idx_tup_read is a lot
> higher than before, in proportion to pg_stat_user_indexes.idx_scan.
> What about the ratio between pg_stat_user_indexes.idx_tup_read and
> pg_stat_user_indexes.idx_tup_fetch? How much has that changed by?
>
> --
> Peter Geoghegan