View vs. direct Table access
Hi,
we have a database with about 70 tables. Also we have a View over four tables
which specifies three joins.
Here is the View:
CREATE VIEW company_view AS
SELECT u.id, u.portalident, u.nikname, u.email, u.phone, u.mobile, u.fax,
u.url, a.fname, a.sname, a.street, a.number, a.city, a.zip, co.name_de AS
country, c.name AS cname, c.descr AS philosophy, c.logo_big AS logo,
c.logo_small AS logo_small, c.brancheident
FROM portal_user u, address a, company c, country co
WHERE u.id = a.portal_userid
AND u.id = c.portal_userid
AND a.countryident = co.ident;
While testing with about 10.000 rows in the tables joined by the view, we
noticed that the selects, which use the View as their source, are very slow.
So we did the same queries but accessing the tables directly not via the
view, and the performace increased significantly.
Are Views per default slower than direct table access? And if so is there a
factor for calculationg the performace loss.
Thanks in advance
Erwin
----- Original Message -----
From: "Darren Ferguson"
The reason the view is slower is because it has to be created first before
you can select from it.I.e. every time you select from the view it will have to run the view sql
before it can select from it
could u clarify this? shouldn't some kind of dictionary cache be employed?
this reminds me another issue I couldn't find explained in the docs - does
Postgres have some kind of library cache for sharing/reuse of SQL
statements, pl/pgsql functions, etc?
it seems that there is only buffer cache at present
thanx,
Marin
----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "
Import Notes
Reference msg id not found: Pine.LNX.4.10.10203270952280.29945-100000@thread.crystalballinc.com | Resolved by subject fallback
The reason the view is slower is because it has to be created first before
you can select from it.
I.e. every time you select from the view it will have to run the view sql
before it can select from it
Darren Ferguson
On Wed, 27 Mar 2002, Erwin Ambrosch wrote:
Show quoted text
Hi,
we have a database with about 70 tables. Also we have a View over four tables
which specifies three joins.Here is the View:
CREATE VIEW company_view AS
SELECT u.id, u.portalident, u.nikname, u.email, u.phone, u.mobile, u.fax,
u.url, a.fname, a.sname, a.street, a.number, a.city, a.zip, co.name_de AS
country, c.name AS cname, c.descr AS philosophy, c.logo_big AS logo,
c.logo_small AS logo_small, c.brancheident
FROM portal_user u, address a, company c, country co
WHERE u.id = a.portal_userid
AND u.id = c.portal_userid
AND a.countryident = co.ident;While testing with about 10.000 rows in the tables joined by the view, we
noticed that the selects, which use the View as their source, are very slow.
So we did the same queries but accessing the tables directly not via the
view, and the performace increased significantly.Are Views per default slower than direct table access? And if so is there a
factor for calculationg the performace loss.Thanks in advance
Erwin
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
I am not to sure about Postgres. I just quoted the SQL standard on views.
Although judging from the ones i use and there relative slow speed i do
not think Postgres caches them in any way
Please Correct me if i am wrong
Thanks
Darren Ferguson
On Wed, 27 Mar 2002, Marin Dimitrov wrote:
Show quoted text
----- Original Message -----
From: "Darren Ferguson"The reason the view is slower is because it has to be created first before
you can select from it.I.e. every time you select from the view it will have to run the view sql
before it can select from itcould u clarify this? shouldn't some kind of dictionary cache be employed?
this reminds me another issue I couldn't find explained in the docs - does
Postgres have some kind of library cache for sharing/reuse of SQL
statements, pl/pgsql functions, etc?it seems that there is only buffer cache at present
thanx,
Marin
----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Darren Ferguson wrote:
I am not to sure about Postgres. I just quoted the SQL standard on views.
Although judging from the ones i use and there relative slow speed i do
not think Postgres caches them in any wayPlease Correct me if i am wrong
The SQL standard describes the visible behaviour, not the
implementation. PostgreSQL doesn't cache them any different
from regular table scans (in the buffer cache). Nor does
PostgreSQL materialize the entire view when you do a
qualified SELECT on it.
The performance of the view should be exactly the same as the
SELECT statement you defined for it with the additional
qualifications you're using when selecting from the view. I
think you should use EXPLAIN a little to figure out what
exactly happens when you select from the view and then try to
optimize the view definition and the indexes on the base
tables a little.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Erwin Ambrosch <ambre@ebutec.com> writes:
Are Views per default slower than direct table access?
No; they should be pretty much exactly the same (since a view is really
nothing but a macro that gets expanded in-line in the text of a query
that references it).
I speculate that you've got a complex query that is confusing the
planner into making some bad choices, but without more details
--- like a complete example and EXPLAIN output --- it's hard to say.regards, tom lane