Re: To use a VIEW or not to use a View.....

Ries van Twisk wrote:
>
> Dear PostgreSQL users,
>
> I have a view and a table,
>
> I understand that when a frontend accesses a VIEW that PostgreSQL cannot use
> a index on that view.
> For example when I do this: SELECT * FROM full_cablelist WHERE
> projectocode=5; Correct?

No.

>
> Now I just want to make sure for myself if the VIEW I created is the right
> way to go, or is it better
> to contruct a SQL in my application that looks like the view and send it to
> postgreSQL so it will
> use all indexes correctly. I use postgreSQL 7.2.1

Views in PostgreSQL aren't materialized. They are implemented as query
rewrite rules that combine your query with the view definition. This is
done before planning and optimizing, so what the query planner is
chewing on (the internal parsetree representation of a query) is the
same as if your application would have sent down the complicated query
over the base tables.

There are a few exceptions where an application could construct a better
WHERE clause, resulting in a different join order or better scan
qualifications. As long as we're not talking about gigabytes here, you
shouldn't worry.

Use tables, views and views over views, it's all fine and your indexes
will be used.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #