4 ms·

what's wrong with views (which should have been used formt he start)? What were the pain points?

by ruslan_talpa 7y ago

bdcravens 7y ago
They are great when used well, but non-materialized views can kill performance with large data sets.
- doh 7y ago
  That's the same as saying "unoptimized selects can kill performance with large data sets". Of course they can. That's what optimization is for. We have quite large amount of data (100TB+ and trillions of rows at this point [0]) and no problem with views. [0] https://www.citusdata.com/customers/pex https://www.citusdata.com/customers/pex
- ruslan_talpa 7y ago
  a view is nothing but a query, so if the view is "killing" the performace for you, running the same query from the client will not change anything, the porformance will get "killed" in the exact same way.
  - bdcravens 7y ago
    Yes, if you are running the same query. Some of the worst use of views I've seen involve massive joins without filters, and then filtering further down, so you end up working with a recordset in the millions of records rather than a few thousand.
    - philwelch 7y ago
      That kind of reminds me of the classic doctors joke though. If it hurts when you do that, don't do that.
    - aidos 7y ago
      So filtering later outside of the db? If you filter on a view the optimiser should be able to sort it out - as others have mentioned, it’s no different from a regular query.
    - ruslan_talpa 7y ago
      I understand the problem you are describing, i would say that is a wrong type of view to create, if one plans to write filters on top of that view then it should be written so the filters can be inlined in the view (which would basically give you the finished query you would be sending from the layer above)
    - sk5t 7y ago
      It's kind of unclear what problem you are trying to describe. Views shouldn't confound the query planner, and creating views with "filters" sounds like probably a mistake--query the view with the predicates you need then.

what's wrong with views (which should have been used formt he start)? What were the pain points?

bdcravens 7y ago

They are great when used well, but non-materialized views can kill performance with large data sets.

doh 7y ago
That's the same as saying "unoptimized selects can kill performance with large data sets". Of course they can. That's what optimization is for. We have quite large amount of data (100TB+ and trillions of rows at this point [0]) and no problem with views. [0] https://www.citusdata.com/customers/pex https://www.citusdata.com/customers/pex
ruslan_talpa 7y ago
a view is nothing but a query, so if the view is "killing" the performace for you, running the same query from the client will not change anything, the porformance will get "killed" in the exact same way.
- bdcravens 7y ago
  Yes, if you are running the same query. Some of the worst use of views I've seen involve massive joins without filters, and then filtering further down, so you end up working with a recordset in the millions of records rather than a few thousand.
  - philwelch 7y ago
    That kind of reminds me of the classic doctors joke though. If it hurts when you do that, don't do that.
  - aidos 7y ago
    So filtering later outside of the db? If you filter on a view the optimiser should be able to sort it out - as others have mentioned, it’s no different from a regular query.
  - ruslan_talpa 7y ago
    I understand the problem you are describing, i would say that is a wrong type of view to create, if one plans to write filters on top of that view then it should be written so the filters can be inlined in the view (which would basically give you the finished query you would be sending from the layer above)
  - sk5t 7y ago
    It's kind of unclear what problem you are trying to describe. Views shouldn't confound the query planner, and creating views with "filters" sounds like probably a mistake--query the view with the predicates you need then.