Re: Perplexing, regular decline in performance

On Tue, Jun 25, 2019 at 8:49 AM Hugh Ranalli <hugh@whtc.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
numberof "raw_page" columns in a particular table grew, performance would decline. They wrote a script that once a week
locksthe table, deletes the processed large columns (they are not needed after processing), copies the remaining data
toa backup table, truncates the original table, then copies it back. When this script runs we see an immediate change
inperformance, from 380 ms in the hour before the drop, to 250 ms in the hour of the drop. As rows with these populated
columnsare 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