I have a view where each row is associated with a customer, and the columns are various computed values such as life_time_value and purchases_per_week, as well as more complicated statistical values such as probability_of_buying_premium_membership. I have around 20 such columns of varying complexity (both in terms of lines-of-code and also computational complexity), ranging from a single line of SQL to several dozen. Right now they are all in one monster view.

Is there a down-side to splitting them into multiple smaller views and joining them by customer_id?

Ie, break it down into views called customer_life_time_value, customer_purchases_per_week and so on, and then recreate the monster view by joining 20 views? It seems like there shouldn't be a performance hit because of the joining, as it's over a indexed primary key. Many of the columns/views will perform similar calculations (purchases_per_week and purchases_per_quarter would look very similar), but it seems like the DB should be smart enough to share computation if I am selecting from the joined view.

I am using Postgres, but interested for answers in general.

  • 1
    It is hard to answer the question without more detailed information. But what you describes sounds like the anti-pattern that I call "world view": a single view that joind and denormalizes the whole database, and which is used everywhere. May 29, 2020 at 6:52

1 Answer 1


Is there a down-side to splitting them into multiple smaller views and joining them by customer_id?

Yes, definitely. Each view has to scan the whole underlying table on its own, and then you add 20 joins after that. The index does not apply to the derived tables you are joining. The single SELECT can make do with a single scan over the table (or index), it should be substantially cheaper.

Proof: db<>fiddle here

  • Does it matter if each individual view is FROM the customer table as well? I was hoping that if I did something like select * from view1 join view2 using (customer_id) where name = 'Joe', where view1 is something like create view view1 as select some_awesome_calculation(cust.stuff) from customers and similar for the other views, that the database would be smart enough not to scan customers each time per view. Either way, do you have pointers on where to learn such implementation issues? May 29, 2020 at 4:13
  • "Each view has to scan the whole table on its own" - I don't think that's true. Postgres will optimize the whole statement, not each view individually.
    – user1822
    May 29, 2020 at 14:06
  • 1
    @a_horse_with_no_name: Postgres optimizes whole statements. But my explanation still applies. I added a demo. May 29, 2020 at 18:37

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.