I have a table with 5 columns of interest:
geo_x integer -- quantized longitude geo_y integer -- quantized latitude context integer -- the type of record hash integer -- a hash of the above 3 columns, modulo divided by n tags integer -- GIN indexed
n as 16 and therefore created 16 indexes.
I also created an index that contained all rows (ignoring
In my tests, I get an almost 13 times speed-up by having 16 indexes vs 1. Great!
However, this is test data (1 million random entries), which is somewhat meaningless. Nonetheless, it proves smaller indexes provide tremendous speed-up. Close to linear in fact.
My question is, is there a way to calculate an optimum number of partial indexes without going through a tedious trial-and-error process?
I have searched, but can't find any best practices regarding the number of partial indexes.
If there is no "formula" to determine the answer, can anyone share their experiences with multiple partial indexes?