Thread: 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
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: > 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 >
----- 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. "
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: > > ----- 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. " > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
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 way
>
> Please 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