Re: Views- Advantages and Disadvantages

On Wed, 2007-05-09 at 12:02 -0700, Dann Corbit wrote:
> Views can hide important information from the optimizer (especially
> index information).

I believe that you're mistaken, and you can see it rather easily by
explaining a select on a view (or even a view of views). For example:

rkh(at)csb-dev=> \d palias
View "unison.palias"
Column | Type | Modifiers
-------------+--------------------------+-----------
palias_id | integer |
pseq_id | integer |
origin_id | integer |
alias | text |
descr | text |
tax_id | integer |
ref_pseq_id | integer |
added | timestamp with time zone |
View definition:
SELECT pa.palias_id, pv.pseq_id, pa.origin_id, pa.alias,
pa.descr, pa.tax_id, pv.ref_pseq_id, pv.added
FROM paliasorigin pa, pseqalias pv
WHERE pv.palias_id = pa.palias_id AND pv.is_current = true;


rkh(at)csb-dev=> explain select * from palias where tax_id=9606;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------
Hash Join (cost=701397.95..1634572.27 rows=1293890 width=137)
Hash Cond: (pv.palias_id = pa.palias_id)
-> Seq Scan on pseqalias pv (cost=0.00..474670.85
rows=20706650 width=20)
Filter: is_current
-> Hash (cost=647199.80..647199.80 rows=1692012 width=121)
-> Bitmap Heap Scan on paliasorigin pa
(cost=33808.65..647199.80 rows=1692012 width=121)
Recheck Cond: (tax_id = 9606)
-> Bitmap Index Scan on paliasorigin_tax_id_idx
(cost=0.00..33385.65 rows=1692012 width=0)
Index Cond: (tax_id = 9606)
(9 rows)

Long ago I compared a few views with their inlined counterparts and the
upshot is that there is exactly or practically zero difference.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0