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

I defined n as 16 and therefore created 16 indexes.

I also created an index that contained all rows (ignoring n).

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?

  • I'd rather check application codes and create partial indexes according to the "where" clause of the queries related – Sahap Asci Nov 28 '17 at 11:59
  • I have. However, the domain divides data into a checkerboard pattern. I indeed that across n partitions. – IamIC Nov 28 '17 at 12:18

Your Answer

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

Browse other questions tagged or ask your own question.