8 ms·

I can't believe that at time of this writing, only a few other comments are mentioning VIEWs! Like that is this dude's exact complaint! smh...

default-kramer 4y ago
Let's say you have 14 derived facts (eg aggregations, computations involving joined tables, etc) about a SalesPerson. Do create 14 single-column views? Or have one view with 14 columns? Either way the composability is terrible. If you go with 14 views, the syntax is awful and not very resistant to refactoring. If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).
- pdntspa 4y ago
  You can SELECT just the columns you want with one view, and IIRC that will exclude other columns from being retrieved unless they are the source of a derived value.
- wswope 4y ago
  > If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization). This is outdated; most modern DBs treat views as they would CTEs. If you don’t reference a defined field downstream, it doesn’t need to get calculated.
  - default-kramer 4y ago
    It's more about the fact that a join can change the shape of the result set. Even if the columns being projected aren't surfaced, the DB still has to process the joins to make sure the result set has the correct shape. For example, a human might know that a join will always be one:one or one:zero-or-one, but the DB has no choice but to make sure. Perhaps using subqueries instead of joins would work, but that gets ugly too. (Or maybe my knowledge is outdated and the optimizers have gotten way better than they were 3-4 years ago.)
    - jdmichal 4y ago
      At least PostgreSQL's query optimizer can and will drop `LEFT JOIN` clauses if the data is not actually being used. It can't do that for `INNER JOIN` because it must verify that a matching row exists.
      - cm2187 4y ago
        For LEFT join it would also need to know the combination of columns you are joining by are unique in the right table, which will be the case in many scenarios (joining on a primary key) but not in the general case.
        trimethylpurine 4y ago
        I'll add that MSSQL at least since 2019 will automatically modify the execution plan to avoid this by the second time the query is executed.
        jdmichal 4y ago
        That's an excellent point. I hadn't thought of it because the joins where I witnessed this were all on primary keys, as $DIETY intended. (That's a joke.)
        trimethylpurine 4y ago
        With most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.
- zasdffaa 4y ago
  As others have said, any decent optimiser will prune out values that aren't SELECT'ed. I don't think it's that complex either - find the unused columns, remove them from subviews, recurse all the way down. Try it on MSSQL/Postgres and you can see it being done.
- fny 4y ago
  The bigger problem is views are tightly coupled to the underlying data.
  - saltcured 4y ago
    Right, there's a big difference between views and the kind of generic abstraction that I think the original poster seeks. You do need something like macros or generated SQL to build a library of algorithms you can apply to data. Imagine wanting something as simple as "find_percentile_value(table, column, percentile)". There is no portable and standard way to write this parametric abstraction and then reuse it on any column source you need to interrogate later.
    - gerdesj 4y ago
      Please give a simple example of what your second para means. Perhaps find_% of all values in the row vs the referenced column, in the referenced table. Now, ... reuse. Surely you write the result to another table and then refer to that.
      - saltcured 4y ago
        The detail of the function isn't that important, but I was thinking of something like the numpy percentile function as an example of a simple functional abstraction. You give it an array representing the population of values and a desired percentile (0..100), and it returns the value corresponding to that desired percentile. Calling it for percentile 50 would return the median value. Other options might select alternative methods for tie-breaking or interpolation between population samples. Note, this was just meant as an example where a view is not helpful. We don't always desire particular calculated data to be named for reuse. Rather, we want a reusable method we can apply to any data. In my earlier post, I tried to address the topic of macros or generated SQL, and so suggested it take a table and column name as input to expand a query idiom for a set of values stored in a table. But, that's not how I'd really want to do it. The rest of this comment will veer into a more elaborate perspective on real functional libraries, rather than mere macros... Today, with dialect-specific mechanisms, I can try to define window functions or ordered-set aggregate functions. It would be possible to solve the percentile problem this way. In fact, many SQL dialects have built-in functions that can help with percentiles, since this is recognized as such a basic statistical concept. But, I only chose percentile as an accessible yet concrete example. These built-in solutions are not the answer to my question. My question is how can user programmers extend the system with their own abstractions. Writing aggregate and window functions requires a big mental shift for the programmer. You usually need to define a state variable and provide a set of input/output/accumulator functions and register all that as an aggregate function. This is because of the way this calling convention is meant to be integrated into query plans in a streaming fashion. It's a bit like forcing a programmer to learn some async or co-routine calling convention without offering a simpler synchronous option as a starting point. It's an obstacle. The application of window or ordered-set aggregate functions also require a lot of verbose syntax to wire up columns or expressions to function inputs and to configure the windows and ordering modes. There isn't really any support to abstract these details away behind the named function, such that the naive user could just call it without knowing how it works internally to solve the whole problem. In other words, having a functional abstraction which can encapsulate data-preconditioning behind an easier interface. Finally, this approach only addresses the case of reducing a set to a scalar or assigning new scalars for every row in a set. To match the generality of the numpy API would require functions that take sets as input and produce arbitrarily different sets as output. It also needs the host language to allow low-syntax manipulation of intermediate data when composing functions. So, for full generality, a data transform function might take one set as input and produce another set as output. We would also want things like: set-based function interface typing and function overloading; functions that can setup their own desired traversals of input data including sorting and filtering; functions that can allocate (potentially) large intermediate data using sets or other common data structure idioms; and complementary call-site syntax to easily compose functions with little syntactic boilerplate but also with low marginal syntax overhead to customize and restructure data passed between functions.
kiernanmcgowan 4y ago
Views, especially Materialized Views if you’re on Postgres, are about as close as you can come to a “one neat trick that solves all your problems” in modern software development imo.
- remus 4y ago
  I'd love to use materialised views more often but my experience is that they're full of annoying limitations, typically around functions that feel like they should be immutable but aren't.
  - kiernanmcgowan 4y ago
    Right, I think the timestamp with timezone type is a great example of one of these short comings. I’ve generally used it as a step in a data pipeline, where some intermittent state or aggregation where being stale/stable for a period of time is ok. Did this recently for a daily trends report on some news data - live querying the data is slow, but baking it off into a MV makes it much faster and keeps the query result from changing too rapidly to new data.
- ttfkam 4y ago
  Materialized views + pg_cron = chef's kiss for simple yet performant report generation.
irrational 4y ago
Oh good, it’s not just me. As soon as I read his post I said to myself, “It sounds like he hasn’t heard of views.” But the first comment didn’t mention views and I was so confused that I started wondering if I misunderstood the post.
viraptor 4y ago
There are situations where you can't use the views. For example I'm writing software which integrates with $vendor app. Not only do I have restricted select access, I have no influence on the schema at all. By definition, I have to send a complete query every time. And there are many similar scenarios.
- spullara 4y ago
  Not SQL's fault. They probably wouldn't let you use macros either.
  - viraptor 4y ago
    It would be nice to have a more composable solution though. Macros etc. don't have to be permanent. On the other hand, CTEs are pretty strictly tied to the structure for the current query.
    - spullara 4y ago
      Views.
      - viraptor 4y ago
        https://news.ycombinator.com/item?id=32803396 https://news.ycombinator.com/item?id=32803396
EMM_386 4y ago
They did mention they've only been using it 3 years. It's been an ANSI standard for 36 years.
wodenokoto 4y ago
I disagree. This is a problem with filtering data. Creating a view every time you want 1 row from each group based on some calculation is beyond cumbersome IMHO.
- tluyben2 4y ago
  But OP says every single time in their post which nicely fits a view as building block for more readable and simpler sql. Their issue seems not a one of filtering problem.
  - wodenokoto 4y ago
    You can't build a view that allows you to do that query with an arbitrary function instead of MAX. It's _every single time_ you need to do this kind of trivial querying you have to write this convoluted mess of SQL.
    - oedo 4y ago
      >with an arbitrary function instead of MAX Many systems support user-defined aggregates[0][1] [0] https://www.postgresql.org/docs/current/sql-createaggregate.html https://www.postgresql.org/docs/current/sql-createaggregate.... [1] https://docs.oracle.com/en/database/oracle/oracle-database/21/addci/using-user-defined-aggregate-functions.html https://docs.oracle.com/en/database/oracle/oracle-database/2...

default-kramer 4y ago

Let's say you have 14 derived facts (eg aggregations, computations involving joined tables, etc) about a SalesPerson. Do create 14 single-column views? Or have one view with 14 columns? Either way the composability is terrible. If you go with 14 views, the syntax is awful and not very resistant to refactoring. If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).

pdntspa 4y ago
You can SELECT just the columns you want with one view, and IIRC that will exclude other columns from being retrieved unless they are the source of a derived value.
wswope 4y ago
> If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization). This is outdated; most modern DBs treat views as they would CTEs. If you don’t reference a defined field downstream, it doesn’t need to get calculated.
- default-kramer 4y ago
  It's more about the fact that a join can change the shape of the result set. Even if the columns being projected aren't surfaced, the DB still has to process the joins to make sure the result set has the correct shape. For example, a human might know that a join will always be one:one or one:zero-or-one, but the DB has no choice but to make sure. Perhaps using subqueries instead of joins would work, but that gets ugly too. (Or maybe my knowledge is outdated and the optimizers have gotten way better than they were 3-4 years ago.)
  - jdmichal 4y ago
    At least PostgreSQL's query optimizer can and will drop `LEFT JOIN` clauses if the data is not actually being used. It can't do that for `INNER JOIN` because it must verify that a matching row exists.
    - cm2187 4y ago
      For LEFT join it would also need to know the combination of columns you are joining by are unique in the right table, which will be the case in many scenarios (joining on a primary key) but not in the general case.
      - trimethylpurine 4y ago
        I'll add that MSSQL at least since 2019 will automatically modify the execution plan to avoid this by the second time the query is executed.
      - jdmichal 4y ago
        That's an excellent point. I hadn't thought of it because the joins where I witnessed this were all on primary keys, as $DIETY intended. (That's a joke.)
      - trimethylpurine 4y ago
        With most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.
zasdffaa 4y ago
As others have said, any decent optimiser will prune out values that aren't SELECT'ed. I don't think it's that complex either - find the unused columns, remove them from subviews, recurse all the way down. Try it on MSSQL/Postgres and you can see it being done.
fny 4y ago
The bigger problem is views are tightly coupled to the underlying data.
- saltcured 4y ago
  Right, there's a big difference between views and the kind of generic abstraction that I think the original poster seeks. You do need something like macros or generated SQL to build a library of algorithms you can apply to data. Imagine wanting something as simple as "find_percentile_value(table, column, percentile)". There is no portable and standard way to write this parametric abstraction and then reuse it on any column source you need to interrogate later.
  - gerdesj 4y ago
    Please give a simple example of what your second para means. Perhaps find_% of all values in the row vs the referenced column, in the referenced table. Now, ... reuse. Surely you write the result to another table and then refer to that.
    - saltcured 4y ago
      The detail of the function isn't that important, but I was thinking of something like the numpy percentile function as an example of a simple functional abstraction. You give it an array representing the population of values and a desired percentile (0..100), and it returns the value corresponding to that desired percentile. Calling it for percentile 50 would return the median value. Other options might select alternative methods for tie-breaking or interpolation between population samples. Note, this was just meant as an example where a view is not helpful. We don't always desire particular calculated data to be named for reuse. Rather, we want a reusable method we can apply to any data. In my earlier post, I tried to address the topic of macros or generated SQL, and so suggested it take a table and column name as input to expand a query idiom for a set of values stored in a table. But, that's not how I'd really want to do it. The rest of this comment will veer into a more elaborate perspective on real functional libraries, rather than mere macros... Today, with dialect-specific mechanisms, I can try to define window functions or ordered-set aggregate functions. It would be possible to solve the percentile problem this way. In fact, many SQL dialects have built-in functions that can help with percentiles, since this is recognized as such a basic statistical concept. But, I only chose percentile as an accessible yet concrete example. These built-in solutions are not the answer to my question. My question is how can user programmers extend the system with their own abstractions. Writing aggregate and window functions requires a big mental shift for the programmer. You usually need to define a state variable and provide a set of input/output/accumulator functions and register all that as an aggregate function. This is because of the way this calling convention is meant to be integrated into query plans in a streaming fashion. It's a bit like forcing a programmer to learn some async or co-routine calling convention without offering a simpler synchronous option as a starting point. It's an obstacle. The application of window or ordered-set aggregate functions also require a lot of verbose syntax to wire up columns or expressions to function inputs and to configure the windows and ordering modes. There isn't really any support to abstract these details away behind the named function, such that the naive user could just call it without knowing how it works internally to solve the whole problem. In other words, having a functional abstraction which can encapsulate data-preconditioning behind an easier interface. Finally, this approach only addresses the case of reducing a set to a scalar or assigning new scalars for every row in a set. To match the generality of the numpy API would require functions that take sets as input and produce arbitrarily different sets as output. It also needs the host language to allow low-syntax manipulation of intermediate data when composing functions. So, for full generality, a data transform function might take one set as input and produce another set as output. We would also want things like: set-based function interface typing and function overloading; functions that can setup their own desired traversals of input data including sorting and filtering; functions that can allocate (potentially) large intermediate data using sets or other common data structure idioms; and complementary call-site syntax to easily compose functions with little syntactic boilerplate but also with low marginal syntax overhead to customize and restructure data passed between functions.

kiernanmcgowan 4y ago

Views, especially Materialized Views if you’re on Postgres, are about as close as you can come to a “one neat trick that solves all your problems” in modern software development imo.

remus 4y ago
I'd love to use materialised views more often but my experience is that they're full of annoying limitations, typically around functions that feel like they should be immutable but aren't.
- kiernanmcgowan 4y ago
  Right, I think the timestamp with timezone type is a great example of one of these short comings. I’ve generally used it as a step in a data pipeline, where some intermittent state or aggregation where being stale/stable for a period of time is ok. Did this recently for a daily trends report on some news data - live querying the data is slow, but baking it off into a MV makes it much faster and keeps the query result from changing too rapidly to new data.
ttfkam 4y ago
Materialized views + pg_cron = chef's kiss for simple yet performant report generation.

irrational 4y ago

Oh good, it’s not just me. As soon as I read his post I said to myself, “It sounds like he hasn’t heard of views.” But the first comment didn’t mention views and I was so confused that I started wondering if I misunderstood the post.

viraptor 4y ago

There are situations where you can't use the views. For example I'm writing software which integrates with $vendor app. Not only do I have restricted select access, I have no influence on the schema at all. By definition, I have to send a complete query every time. And there are many similar scenarios.

spullara 4y ago
Not SQL's fault. They probably wouldn't let you use macros either.
- viraptor 4y ago
  It would be nice to have a more composable solution though. Macros etc. don't have to be permanent. On the other hand, CTEs are pretty strictly tied to the structure for the current query.
  - spullara 4y ago
    Views.
    - viraptor 4y ago
      https://news.ycombinator.com/item?id=32803396 https://news.ycombinator.com/item?id=32803396

EMM_386 4y ago

They did mention they've only been using it 3 years. It's been an ANSI standard for 36 years.

wodenokoto 4y ago

I disagree. This is a problem with filtering data. Creating a view every time you want 1 row from each group based on some calculation is beyond cumbersome IMHO.

tluyben2 4y ago
But OP says every single time in their post which nicely fits a view as building block for more readable and simpler sql. Their issue seems not a one of filtering problem.
- wodenokoto 4y ago
  You can't build a view that allows you to do that query with an arbitrary function instead of MAX. It's _every single time_ you need to do this kind of trivial querying you have to write this convoluted mess of SQL.
  - oedo 4y ago
    >with an arbitrary function instead of MAX Many systems support user-defined aggregates[0][1] [0] https://www.postgresql.org/docs/current/sql-createaggregate.html https://www.postgresql.org/docs/current/sql-createaggregate.... [1] https://docs.oracle.com/en/database/oracle/oracle-database/21/addci/using-user-defined-aggregate-functions.html https://docs.oracle.com/en/database/oracle/oracle-database/2...