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

I haven't tried tweaking the `scale_factor` settings yet, but I did run the queries you requested. Attached are the results.



Rob

Robert William Leach
Scientific Programmer
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