title: How would I configure analyze threshold for a table where the data is categorically different every couple months?
body: <p>We host data for an auditing service. Every few months, a new audit comes out with similar questions to previous audits of the same category. Since questions can change verbiage and number, we store each question in each audit separately (we do link them through a &quot;related_questions&quot; table).</p>
  <p>audits</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  <th>passing_score</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>audit_1</td>
  <td>100</td>
  </tr>
  <tr>
  <td>2</td>
  <td>audit_2</td>
  <td>150</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>questions</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>audit_id</th>
  <th>text</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>q1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>q2</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  <td>q1</td>
  </tr>
  <tr>
  <td>4</td>
  <td>2</td>
  <td>q2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>We then have a surveys and responses table. Surveys are the overall response to an audit, while responses store the individual responses to each question.</p>
  <p>surveys</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>audit_id</th>
  <th>overall_score</th>
  <th>pass</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>120</td>
  <td>true</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>95</td>
  <td>false</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  <td>200</td>
  <td>true</td>
  </tr>
  <tr>
  <td>4</td>
  <td>2</td>
  <td>100</td>
  <td>false</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>responses</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>survey_id</th>
  <th>question_id</th>
  <th>score</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>1</td>
  <td>60</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>2</td>
  <td>60</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  <td>1</td>
  <td>60</td>
  </tr>
  <tr>
  <td>4</td>
  <td>2</td>
  <td>2</td>
  <td>35</td>
  </tr>
  <tr>
  <td>5</td>
  <td>3</td>
  <td>3</td>
  <td>100</td>
  </tr>
  <tr>
  <td>6</td>
  <td>3</td>
  <td>4</td>
  <td>100</td>
  </tr>
  <tr>
  <td>7</td>
  <td>4</td>
  <td>3</td>
  <td>50</td>
  </tr>
  <tr>
  <td>8</td>
  <td>4</td>
  <td>4</td>
  <td>50</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The analyze threshold is <code>base threshold + scale factor * number of tuples</code>. The problem with this is that once an audit has finished (after a few months), we'll never receive new surveys or responses for that category of data. The new data that comes in is conceptually all that needs to be analyzed. All data is queried, but the new data has the most traffic.</p>
  <p>If 10% is the ideal scale factor for today and analyze autoruns once every week, a couple years from now analyze may autorun once every 4 months due to the number of tuples. This is problematic when the past 3 months of data is for questions that the analyzer has never seen and so there are no helpful stats for the query planner on this data.</p>
  <p>We could set the scale factor extremely low for this table, but that seems like a cheap solution that could cause issues in the future.</p>