From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Shridhar Daithankar <shridhar@frodo.hserus.net>,
	pgsql-performance@postgresql.org
Subject: Re: On the performance of views
Date: Mon, 26 Jan 2004 09:09:41 -0800
User-Agent: KMail/1.5.4
References: <40152FF2.8000007@potentialtech.com>
	<4015326A.8000706@frodo.hserus.net>
In-Reply-To: <4015326A.8000706@frodo.hserus.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200401260909.41091.josh@agliodbs.com>

Bill,

> > SELECT a.cola, b.colb, c.colc
> > FROM a JOIN b JOIN c
> > WHERE a.prikey=$1

If your views are simple, PostgreSQL will be able to "push down" any filter 
criteria into the view itself.   For example,

CREATE view_a AS
SELECT a.cola, b.colb, c.colc
FROM a JOIN b JOIN c;

SELECT * FROM view_a
WHERE a.prikey = 2334432;

will execute just like:

SELECT a.cola, b.colb, c.colc
FROM a JOIN b JOIN c
WHERE a.prikey = 2334432;

However, this does not work for really complex views, which have to be 
materialized or executed as a sub-loop.

The "Procedures faster than views" thing is a SQL Server peculiarity which is 
a result of MS's buggering up views since they bought the code from Sybase.

> To my understanding, views are expanded at runtime and considered while 
> preparing plan for the complete (and possibly bigger) query(Consider a view 
> joined with something else). That is not as easy/possible if at all, when it 
is 
> function. For postgresql query planner, the function is a black box(rightly 
so, 
> I would say).

Well, as of 7.4 SQL functions are inlined.   And simple PL/pgSQL functions 
will be "prepared".   So it's possible that either could execute as fast as a 
view.

Also, if your client is really concerned about no-holds-barred speed, you 
should investigate prepared queries.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco