Re: BUG #18177: certain queries under certain contexts take multiple orders of magnitude longer compared to v10

So unfortunately, even though these custom autovacuum settings in my pull request were only applied to the test database (because that's the only use case we have where large boluses of data are loaded right before a query), and they roughly halve the time of our test suite to run (from ~1h to ~30m), my PR was essentially rejected. Instead, they want the entire test suite to be refactored to deal with the speed issue we had before the upgrade to postgres 13 (so that the tests run in 5m instead of originally 30m), which we've had as an issue since July '22, and I agree it should be done, and would likely also solve the problem, but it's always been lower priority than everything else, and it will take a lot of work.

Did you guys see anything in the query output that could suggest an alternate solution?

Thanks,
Rob

Robert William Leach
Research Software Engineer
133 Carl C. Icahn Lab
Lewis-Sigler Institute for Integrative Genomics
Princeton University
Princeton, NJ 08544

> On Nov 7, 2023, at 11:43 AM, Robert Leach <rleach(at)princeton(dot)edu> wrote:
>
> I haven't tried tweaking the `scale_factor` settings yet, but I did run the queries you requested. Attached are the results.
>
> <postgres_10v13_stats_queries.txt>
>
> Rob
>
> Robert William Leach
> Research Software Engineer
> 133 Carl C. Icahn Lab
> Lewis-Sigler Institute for Integrative Genomics
> Princeton University
> Princeton, NJ 08544
>
>
>> On Nov 6, 2023, at 5:38 PM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>>
>> On Tue, 7 Nov 2023 at 11:17, Robert Leach <rleach(at)princeton(dot)edu> wrote:
>>> ALTER TABLE "DataRepo_peakdata" SET (autovacuum_vacuum_scale_factor = 0.0);
>>> ALTER TABLE "DataRepo_peakdata" SET (autovacuum_vacuum_threshold = 5000);
>>> ALTER TABLE "DataRepo_peakdata" SET (autovacuum_analyze_scale_factor = 0.0);
>>> ALTER TABLE "DataRepo_peakdata" SET (autovacuum_analyze_threshold = 5000);
>>>
>>> I still don't like the fact that we have this database-architecture-specific code in our code-base that ties it to a specific database. I'm not sure if where I put it is the best place for it either. Is there a config file I can put these settings in?
>>
>> You can apply those changes globally in postgresql.conf, but having
>> the autovacuum_vacuum_threshold / autovacuum_analyze_threshold set to
>> that constant is unlikely to be very good for all tables. Perhaps
>> there some scale_factor above 0.0 and below 0.2 that you can find that
>> makes it run fast.
>>
>>> And I'm still curious why this wasn't necessary in postgres 10?
>>
>> If you show us the output of the following two queries:
>>
>> SELECT relname, reltuples,relpages,pg_relation_size(oid) from pg_class
>> where oid = '"DataRepo_peakdata"'::regclass;
>> select c.relname, c.reltuples, c.relpages, pg_relation_size(c.oid)
>> from pg_class c inner join pg_index i on c.oid=i.indexrelid where
>> i.indrelid = '"DataRepo_peakdata"'::regclass;
>>
>> run directly before the query in question both on PG10 and on PG13
>> both when the query runs quickly and when it runs slowly. We might
>> see something there that helps indicate what's going on.
>>
>> David
>