1

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.

Improve this question
1
  • 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.
    – Laurenz Albe
    May 29 '20 at 6:52
2

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

Improve this answer
3
  • 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?
    – user3243135
    May 29 '20 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.
    – a_horse_with_no_name
    May 29 '20 at 14:06
  • 1
    @a_horse_with_no_name: Postgres optimizes whole statements. But my explanation still applies. I added a demo.
    – Erwin Brandstetter
    May 29 '20 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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