I'm trying to understand the performance impact of selecting data from a view, where one of the columns in a view is function of other data in the original table.
Does the computation get performed irrespective of whether or not the computed column is in the list of selected columns?
If I had a table and the view declared like so
CREATE TABLE price_data (
ticker text, -- Ticker of the stock
ddate date, -- Date for this price
price float8, -- Closing price on this date
factor float8 -- Factor to convert this price to USD
CREATE VIEW prices AS
price * factor as price_usd
Would that multiplication be performed in a query like the one below?
select ticker, ddate, price, factor from prices
Is there a reference that guarantees this one way or the other? I was reading the documentation on the rule system in Postgres, but I think the answer really lies with the optimiser, since nothing in the rule system documentation indicated that it wouldn't be selected.
I suspect in the above case the computation isn't performed. I changed the view to use division instead of multiplication, and inserted a
price_data. The query above didn't fail, but if the query was modified to select the computed column the modified query failed.
Is there any way to understand what computations are being done when a
select is carried out? I guess I'm looking for something like
EXPLAIN but which also tells me about the computation(s) that are being performed.