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

[Nick Brennan]

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  wrote:
> 
>> On Wed, Jul 26, 2017 at 2:05 PM, Peter Geoghegan  wrote:
>>> On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jeff Janes]

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

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

[Peter Geoghegan]

On Wed, Jul 26, 2017 at 2:05 PM, Peter Geoghegan  wrote:
> On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Peter Geoghegan]

On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan  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.
>
>
> 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?

Are the indexes bloated? Are they larger than before, as indicated by
psql's \di+ or similar? Did you notice that this happened immediately,
or did it take a while? Are these unique indexes or not? Do you have a
workload with many UPDATEs?

I ask all these questions because I think it's possible that this is
explained by a regression in 9.5's handling of index bloat, described
here:

http://postgr.es/m/CAH2-Wz=sfakvmv1x9jh19ej8am8tzn9f-yecips9hrrrqss...@mail.gmail.com

-- 
Peter Geoghegan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Indexes being ignored after upgrade to 9.5

[Nick Brennan]

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