X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 676EAD1CC8B
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 26 Jan 2004 15:30:00 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 98323-09
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 26 Jan 2004 11:29:33 -0400 (AST)
Received: from frodo.hserus.net (frodo.hserus.net [204.74.68.40])
	by svr1.postgresql.org (Postfix) with ESMTP id 6F32AD1CC9C
	for <pgsql-performance@postgresql.org>;
	Mon, 26 Jan 2004 11:29:29 -0400 (AST)
Received: from concord.pspl.co.in ([202.54.11.72]:64736 helo=frodo.hserus.net)
	by frodo.hserus.net with asmtp
	(Cipher TLSv1:DHE-RSA-AES256-SHA:256) (Exim 4.30 #0)
	id 1Al8g9-000FWn-9U by authid <shridhar> with plain
	for <pgsql-performance@postgresql.org>; Mon, 26 Jan 2004 20:59:33 +0530
Message-ID: <4015326A.8000706@frodo.hserus.net>
Date: Mon, 26 Jan 2004 20:59:46 +0530
From: Shridhar Daithankar <shridhar@frodo.hserus.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.6b) Gecko/20031205 Thunderbird/0.4
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: On the performance of views
References: <40152FF2.8000007@potentialtech.com>
In-Reply-To: <40152FF2.8000007@potentialtech.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/269
X-Sequence-Number: 5443

Bill Moran wrote:

> 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

Make sure that you typecase correctly. It makes a differnce of order of 
magnitude when you say 'where intpkey=<somevalue>::int' rather than 'where 
intpkey=<somevalue>'.

It is called typecasting and highly recommened in postgresql for correctly 
choosing indexes.

I remember another post on some list, which said pl/pgsql seems to be very 
strongly typed language compared to MSSQL counterpart. So watch for that as well.

> 
> (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 

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).

So using views opens possibility of changing query plans if required. Most of 
the times that should be faster than using them as functions.

Of course, the standard disclaimer, YMMV. Try yourself.

Correct me if I am wrong.

HTH

  Shridhar