Re: Efficiency of Views

Matt Helm <code(dot)name(dot)eric(at)gmail(dot)com> schrieb:

> I have a table (call it T1) made up of a join from two other tables
> and a few calculated columns.
>
> Currently, T1 gets refreshed by manually calling a function that does
> the select on the join and then loops through each row doing an
> update / insert as needed.

Can you describe the tables, the join and the function?

>
> Obviously it would be nice to put this in a view so that I don't
> have to worry about someone forgetting to call the refresh function.

Nice idea ;-)

>
> My question (probably a common one):
>
> When I select a single row from the view using WHERE will
> the view build the calculated columns for every row regardless?

The view is simple a select on the underlaying tables. If you want
select only some rows of the view, you should use indexes.
You can work with EXPLAIN to see how the planner works. A little
example:

test=# select * from master;
id | name
----+------
1 | ich
2 | du
3 | er
(3 rows)

test=# select * from detail;
id | wert | name
----+------+-------
1 | 1 | 1und1
1 | 2 | 1und2
1 | 3 | 1und3
1 | 4 | 1und4
2 | 1 | 1und1
2 | 2 | 1und2
3 | 1 | 3und1
3 | 2 | 3und2
3 | 5 | 3und5
(9 rows)

test=# create view v1 as select a.id, a.name, sum(b.wert) from master a left join detail b on a.id=b.id group by a.id, a.name;
CREATE VIEW

test=# explain select * from v1 where id = 2;
QUERY PLAN
-----------------------------------------------------------------------------------------
HashAggregate (cost=6.95..6.97 rows=1 width=40)
-> Nested Loop Left Join (cost=0.00..6.95 rows=1 width=40)
-> Index Scan using master_pkey on master a (cost=0.00..5.82 rows=1 width=36)
Index Cond: (id = 2)
-> Seq Scan on detail b (cost=0.00..1.11 rows=1 width=8)
Filter: (id = 2)
(6 rows)

The point is: you should define indexes on the columns within you are
searching. I have a index on detail(id), but the table is to small, the
planner don't use this index.

HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°