From: Jan Wieck <JanWieck@Yahoo.com>
To: Ries van Twisk <ries@jongert.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: To use a VIEW or not to use a View.....
Date: Wed, 22 Jan 2003 10:46:54 -0500
Message-ID: <3E2EBCEE.EF487ECC@Yahoo.com> (raw)
References: <002001c2c228$bc3d2780$f100000a@IT001>
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@Yahoo.com #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: JanWieck@Yahoo.com, ries@jongert.nl
Subject: Re: To use a VIEW or not to use a View.....
In-Reply-To: <3E2EBCEE.EF487ECC@Yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox