Skip site navigation (1) Skip section navigation (2)

Re: Do Views offer any performance advantage?

From: Andreas Pflug <Andreas(dot)Pflug(at)web(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Do Views offer any performance advantage?
Date: 2003-04-15 19:55:20
Message-ID: 3E9C63A8.4040801@web.de (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:

>
>There is probably some minuscule cost difference involved --- you save
>parsing and parse analysis of a long query string.  On the other hand,
>you pay to pull the view definition from the catalogs and merge it into
>the given query.  I'd not care to hazard a guess on whether the actual
>net cost is more or less; but in any case these costs will be swamped
>by query planning and execution, if the query is complex.
>
Actually, there are cases when a view can impact performance.
If you are joining a view, it seems to be treated as a subquery, that 
might have a much larger result than you would like.

Imagine
SELECT something
  FROM A JOIN B JOIN C ...
 WHERE A.primaryKeyFoo=1234 ...

 where C is a view, containing JOINs itself, I observed a query plan 
(7.3.2) like
A JOIN B JOIN (D JOIN E)
instead of
A JOIN B JOIN D JOIN E which would be much more efficient for the 
A.primaryKeyFoo restriction.


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-04-15 23:37:06
Subject: Re: Do Views offer any performance advantage?
Previous:From: Greg StarkDate: 2003-04-15 19:04:04
Subject: Using indexes for like foo% type queries when foo isn't constant (not a locale issue)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group