Skip site navigation (1) Skip section navigation (2)

Re: Efficiency of Views

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Efficiency of Views
Date: 2006-03-02 18:22:45
Message-ID: 20060302182244.GA21766@KanotixBox (view raw or flat)
Thread:
Lists: pgsql-novice
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°

In response to

pgsql-novice by date

Next:From: Sean DavisDate: 2006-03-02 18:26:42
Subject: Re: Efficiency of Views
Previous:From: Matt HelmDate: 2006-03-02 17:57:40
Subject: Efficiency of Views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group