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
business_id
,start_date
andend_date
- 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!
Additional information:
- I'm running Postgres 9.6
- I have indexes on the
business_id
,type
,kind
,user_id
andcreated_at
columns in thetransaction
table. These are all single column, btree indexes.
Best Answer
A view cannot help you producing an aggregate based on unknown parameters (
business_id
,start_date
andend_date
). It is nothing else than a given query, stored permanently in the database for later reuse. (Well, the implementation is more complicated, but that does not affect their usage.)Considering your query, the furthest you can get with a view is
Everything that is known beforehand is there, plus the columns you need for producing the desired output.
For obtaining the latter, you have to create a function in any case (optionally, working from the view). Building on the view makes sense when you have several queries that filter the date the same way.
The performance of all these will be the very same. If the indexes makes sense or not depends very much on the actual data. (I am pretty sure you don't need all of them, though.) Without knowing anything about those, it is very hard to guess which improvements are needed. Try what you have, check the
EXPLAIN ANALYZE
output and see if there is something missing.Finally about caching: PostgreSQL is very smart about this. Going into details would fill a chapter or two in a book, but I would not worry about this until I see too many disk reads (which can be seen from
EXPLAIN (ANALYZE, BUFFERS)
).