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
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.

  • 1
    "In SQL Server iirc the execution plans of views are saved to disk so you get a performance increase that way by using them" - Not quite buddy. In SQL Server, an execution plan is stored in the plan cache in Memory (not Disk). But an execution plan is generated even for an adhoc query that isn't stored in a view, so subsequent executions leverage the benefit of not needing to recompile a plan (barring a few other factors). Usually the compile time of an execution plan is negligible anyway, so I wouldn't be super concerned about trying to optimize that time spent, even for 50 times a minute.
    – J.D.
    Jun 13 at 0:22
  • ok fair enough, but I still think a view is superior in this case because it abstracts the complexity away and simplifies other queries
    – jcollum
    Jun 13 at 17:49
  • 1
    Sure that's true. I typically like to use Views to refactor code, for maintainability & reusability purposes, when appropriate. Though you can also run into issues if not architected carefully, when consumed by things that don't necessarily need all the fields in that View and the complexities of the code behind those fields. Unfortunately it's not as simple as code refactoring in an application programming language. Anyway, back to your original question, unfortunately I'm not versed enough on PostgreSQL to reply on if / how query plans are cached there, otherwise I'd provide an answer.
    – J.D.
    Jun 13 at 18:45


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.