> if i have a "column" that is a calculation, say a bank balance -> sum > of all the debits and credits...is it more efficient to make a view > that executes the underlying calc query doing the math, or to create a > table that has a column called balance that is updated for each > transaction? > > so in the end "select balance from view" or "select balance from table" > ? > > What are the pros cons ? > How often are you using the "bank balance" value? If you're updating it for every transaction, you would be doing a lot of work computing values that may rarely if ever get used. That's an argument for the "view" route, since the computation only happens when necessary. The opposite argument, is how long does the computation take, and how quickly do you need it? The pre-computed value would obviously be much faster than waiting for it to be computed on the fly. Other things to keep in mind... you might want to make the balance calculation a separate function rather than building it into the table, unless it's used on *every* query. Also, if you would be taking any action with the value returned by the balance calculation, remember to lock any tables necessary to ensure the balance doesn't change between the time you compute it and the time you act on it. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
