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 <p...@bowt.ie> wrote:
>
>> On Wed, Jul 26, 2017 at 2:05 PM, Peter Geoghegan <p...@bowt.ie> wrote:
>>> 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.
>
> 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