Message-ID: <40152FF2.8000007@potentialtech.com>
Date: Mon, 26 Jan 2004 10:19:14 -0500
From: Bill Moran <wmoran@potentialtech.com>
User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.4) Gecko/20031005
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: On the performance of views
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

I have an application that I'm porting from MSSQL to PostgreSQL.  Part of this
application consists of hundreds of stored procedures that I need to convert
to Postgres functions ... or views?

At first I was going to just convert all MSSQL procedures to Postgres functions.
But now that I'm looking at it, a lot of them may be candidates for views.  A
lot of them take on the format of:

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

(this is slightly oversimplified, but as a generalization of hundreds of
functions, it's pretty accurate)

Now, I know this questions is pretty generalized, and I intend to test before
actually commiting to a particular course of action, but I'm very early in the
conversion and I'm curious as to whether people with more experience than I
think that views will provide better performance than functions containing
SQL statements like the above.  The client is _very_ interested in performance
(just like anyone who needs to market things these days ... apparently, if you
admit that something might take a few seconds in your advertising, you're sunk)

Any opinions are welcome.  Also, if this is a relatively unknown thing, I'd
be curious to know that as well, because then it would be worthwhile for me
to record and publish my experience.

-- 
Bill Moran
Potential Technologies
http://www.potentialtech.com