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