Apologies for any typos, copied the query from code and renamed things for IP protection.
This is a Type 2 database, updates are inserts with the value of the old record for all fields except for the fields that have changed. Highest ID value for the entity ID is the most current record for that entity.
WITH max_ids as
(
select customer_id, org_id, max(id) as max_id from customers where customer_id = $1 GROUP BY entity_id
)
SELECT customer_id, org_id, customer_name, customer_city, customer_state from customers
WHERE id in (select max_id from max_ids) LIMIT $2 OFFSET $3 order by id desc
Let's say this query will be hit 50 times a minute for a variety of customer_id values. Could we get a performance gain by putting the subquery into a view? E.g.:
CREATE OR REPLACE VIEW customers_current
AS
select max(id) as id, customer_id, org_id
from customers
group by customer_id, org_id
In SQL Server iirc the execution plans of views are saved to disk so you get a performance increase that way by using them. I don't know if this is true in Postgres. I did see an answer on Stackoverflow that said "no, view execution plans are not cached" but there was no source given so it could be just opinion.
We do not want to use a materialized view.
