On Tue, Jun 25, 2019 at 8:49 AM Hugh Ranalli <h...@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 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