This isn’t a good example because you wouldn’t pre calculate vat and store it for product listing, as vat doesn’t apply to all countries and it’s subject to change, and differ between countries. (Japan just changed gst yesterday)
You also wouldn’t want to run a function on something you need to filter against. To give you an example we have the concept of a “deadline” date which is based on the time the record is stored + a period of time which it must be completed by.
Calculating that column in sql before doing a where filter is crazy slow when you’re looking at millions and millions of records. But pre-calculating it and storing it, and then adding an index on top of it, is insanely fast.
This is currently done in code. But if I moved this to a computed column then I can Ensure the result is always up to date if the period changes and avoid code being written to accidentally forget to update this value.
There are use cases for computer columns. As there are for functions. And doing it in code.
This feature in pg12 mainly gives us the ability to index the value which we couldn’t do before.
I'm not sure if I understand you correctly, but logically the WHERE clause happens before the SELECT clause[1], so it only calculates this value for the rows you're interested in. It is also possible to index functions without generated columns.
You also wouldn’t want to run a function on something you need to filter against. To give you an example we have the concept of a “deadline” date which is based on the time the record is stored + a period of time which it must be completed by.
Calculating that column in sql before doing a where filter is crazy slow when you’re looking at millions and millions of records. But pre-calculating it and storing it, and then adding an index on top of it, is insanely fast.
This is currently done in code. But if I moved this to a computed column then I can Ensure the result is always up to date if the period changes and avoid code being written to accidentally forget to update this value.
There are use cases for computer columns. As there are for functions. And doing it in code.
This feature in pg12 mainly gives us the ability to index the value which we couldn’t do before.