From: Bill Moran <wmoran@potentialtech.com>
To: Shridhar Daithankar <shridhar@frodo.hserus.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: On the performance of views
Date: Tue, 27 Jan 2004 14:39:21 -0500
Message-ID: <4016BE69.9050909@potentialtech.com> (raw)
In-Reply-To: <4015326A.8000706@frodo.hserus.net>
References: <40152FF2.8000007@potentialtech.com>
<4015326A.8000706@frodo.hserus.net>
Shridhar Daithankar wrote:
> 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.
Oh yeah. This particular difference is causing a lot of headaches, as I have
to track down the type of each field to create a new type for each function.
MSSQL seems to be _very_ loosely typed, in that it will return anything you
want and determine the return type at run time.
Some functions they prototyped in MSSQL even return different types, based
on certian parameters, I'm not sure how I'll do this in Postgres, but I'll
have to figure something out.
>> (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.
Thanks. I think I'm going to track the performance of many of these
functions and write up what I discover.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: wmoran@potentialtech.com, shridhar@frodo.hserus.net, pgsql-performance@postgresql.org
Subject: Re: On the performance of views
In-Reply-To: <4016BE69.9050909@potentialtech.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox