I have a scenario where I need to run a payroll report. The report calculates the payroll amount, grouped by staff member, for a specific date range.
For example, when running the report for 2016-11-01 to 2016-11-30, I would see the following result:
Staff Id Total ------------------ 1 123.00 2 439.22
I'm using the following query for the above report:
select user_id as staff_id, sum(amount) as total from transaction where business_id = <business_id> and type = 'staff' and kind = 'commission' and created_at between <start_date> and <end_date> group by user_id;
I'm trying to determine the best way to optimize the performance of this query given the following requirements:
- Results will vary based on the
- Data should always be fresh
It appears both views and functions would do the job, but I'm not 100% on which is the best approach given the requirements.
Sidenote: it would be great to cache the data based on the parameters mentioned above, but it seems like there isn't a great solution on the database side. Correct me if I'm wrong!
- I'm running Postgres 9.6
- I have indexes on the
created_atcolumns in the
transactiontable. These are all single column, btree indexes.