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 SELECT ticker, ddate, price, factor, price * factor as price_usd FROM price_data
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.