If the s_f_promotion_history table will have a explosive growth, then its worth considering partitioning by date and using constraint exclusion to speed up the queries. Else, it makes sense to get started with multiple partial index (like, have a index for each week or something like that. You may want to start with a coarse grain timeline for the index and then fine grain it based on the needs)
