Re: are views typically any faster/slower than equivilent joins?

On Sat, 2003-05-31 at 22:55, Brian Tarbox wrote:
> I am working with a highly normalized database. Most of my meaningful
> queries involve joins from a primary table to 4-6 secondary tables.
>
> Would my query performance be significantly faster/slower using a View as
> opposed to a prepared query using join?

There are some corner cases where a view would be slower than a standard
query in 7.3 (bug fix / disabled optimization -- fixed right in 7.4),
but generally you can assume it will be about the same speed.

Some views such as unions will not be as fast as you would like, but
thats a general issue with PostgreSQLs inability to throw away selects
when it won't find results on one side of a union.

CREATE VIEW sales AS SELECT * FROM sales_archive_2002 UNION ALL SELECT *
FROM sales_current;

SELECT * FROM sales WHERE timestamp => CURRENT_TIMESTAMP - INTERVAL '1
day';

The above query would not be so quick.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc