Re: Efficiency of Views

Matt Helm <code.name.eric@gmail.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°