Hi,
I have a table with many columns of numbers, and I am calculating various statistics on these numbers using window functions. In particular, I am assigning each row a rank (based on both rank() as well as row_number()), a percentile rank (cume_dist()), a percentile bin (ntile(100)), a running sum and a running average.
I was wondering what indexes it might be helpful to create and maintain on this table so that these window functions work as quickly and efficiently as possible. Would it be helpful to create separate indexes on all the numerical columns in this table? Are there rules of thumb for when too many indexes cause a slowdown because their maintenance uses up more time than I save while running queries that use those indexes? For context, there are about 20 numerical columns in the table and the table seldom contains more than 5000 rows. Right now, my query for calculating these statistics takes about 15 to 20 seconds.
Thank you.