Re: Perplexing, regular decline in performance

On Tue, Jun 25, 2019 at 8:49 AM Hugh Ranalli <hugh(at)whtc(dot)ca> wrote:
> What we continued to notice was a milder but still definite trend of increased query times, during the course of each week, from the mid to high 200 ms, to the high 300 ms to low 400 ms. Some years ago, someone had noticed that as the number of "raw_page" columns in a particular table grew, performance would decline. They wrote a script that once a week locks the table, deletes the processed large columns (they are not needed after processing), copies the remaining data to a backup table, truncates the original table, then copies it back. When this script runs we see an immediate change in performance, from 380 ms in the hour before the drop, to 250 ms in the hour of the drop. As rows with these populated columns are added during the course of a week, the performance drops, steadily, until the next week's cleaning operation. Each week the performance increase is clear and significant.

Can you show us the definition of the table, including its indexes?
Can you describe the data and distribution of values within the
columns, particularly where they're indexed?

--
Peter Geoghegan