View vs. direct Table access

From: Erwin Ambrosch <ambre(at)ebutec(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: View vs. direct Table access
Date: 2002-03-27 14:02:01
Message-ID: 200203271407.g2RE74g05631@email.ebutec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hillensbeck, Preston 2002-03-27 14:23:38 Difference between text and char(n)?
Previous Message Elematti Eng e Sistemas Ltda 2002-03-27 13:52:29 DBEXPRESS DRIVER FOR POSTGRE