1

We have a table that will hold records on the 108 order.

╔══════════╦═════╦══════╦══════════════════════════╗
║    id    ║  a  ║  b   ║ ...many other columns... ║
╠══════════╬═════╬══════╬══════════════════════════╣
║        1 ║   1 ║  118 ║                      ... ║
║        2 ║   1 ║ 1022 ║                      ... ║
║        3 ║   5 ║  118 ║                      ... ║
║        4 ║   3 ║  118 ║                      ... ║
║      ... ║ ... ║  ... ║                      ... ║
║    10020 ║   5 ║ 1022 ║                      ... ║
║    10021 ║   1 ║  118 ║                      ... ║
║      ... ║ ... ║  ... ║                      ... ║
║  5000000 ║   2 ║   30 ║                      ... ║
║  5000001 ║   1 ║   30 ║                      ... ║
║      ... ║ ... ║  ... ║                      ... ║
║ 28218321 ║   1 ║  118 ║                      ... ║
║ 28218322 ║   4 ║   57 ║                      ... ║
╚══════════╩═════╩══════╩══════════════════════════╝

Column a always holds one of 5 values. Column b always holds one of ~5000 values. a and b are unrelated. So for every value of a there will be at least tens of millions of table rows, and for every value of b there will be at least hundreds of thousands of rows.

A lot of our queries are based on WHERE a=<value> or WHERE b=<value> so we are indexing those columns (two separate, single-column btree indexes). I am trying to get a sense of what the optimal choice of FILLFACTOR would be for this situation.

This table does not receive many UPDATEs, however it receives large bursts of INSERTs (on the order of 106) in short periods of time. At the same time it is under moderate, constant read load.

If my understanding of btree index fillfactor is correct it sounds as though we may benefit for a fairly low fillfactor for a and b indexes, allowing lots of empty space for new row references to be added to the leaf pages as new records come in with the same a or b value as previous ones. Is this correct?

Is this a situation where a low fillfactor would be beneficial and if so what approximate value should we be looking at using?

  • Have you done any testing to determine if the index on just <a> is useful? With only 5 unique values I would be surprised if it's used unless you hint for it. – Jonathan Fite Jun 27 '18 at 20:29
  • No, I haven't, but I will do that when I have some time. – Alex Jul 4 '18 at 13:45
0

The fill factor for a B-btree index is only used during initial creation of the index, and when you insert a new highest key. So any effect of setting this won't last for long on a highly active index which is randomly inserted, as it will quickly settle in to a steady state which is independent of the starting fill factor. If you change the fill factor and then rebuild the index, you might get a boost in performance. But unless you can rebuild the index frequently, it wouldn't be a sustainable boost.

The fill factor for indexes, unlike for tables, is not a very generally useful knob. If it didn't already exist and someone proposed adding it now, they might have a hard time getting it accepted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.