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

Re: [PERFORMANCE] Stored Procedures

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Rikard Pavelic <rikard(dot)pavelic(at)zg(dot)htnet(dot)hr>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORMANCE] Stored Procedures
Date: 2006-01-20 19:10:31
Message-ID: 20060120191031.GO20182@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Jan 20, 2006 at 07:50:23PM +0100, Rikard Pavelic wrote:
> >>Hi,
> >> 
> >>Will simple queries such as "SELECT * FROM blah_table WHERE tag='x'; work 
> >>any
> >>faster by putting them into a stored procedure?
> 
> >
> >IMHO no, why do you think so? You can use PREPARE instead, if you have many
> >selects like this.
> 
> 
> I tought that creating stored procedures in database means
> storing it's execution plan (well, actually storing it like a
> compiled object). Well, that's what I've learned couple a years
> ago in colledge ;)

My college professor said it, it must be true! ;P

My understanding is that in plpgsql, 'bare' queries get prepared and act
like prepared statements. IE:

SELECT INTO variable
    field
    FROM table
    WHERE condition = true
;

> What are the advantages of parsing SP functions every time it's called?
> 
> My position is that preparing stored procedures for execution solves
> more problems, that it creates.
> And the most important one to be optimizing access to queries from 
> multiple connections (which is one of the most important reasons 
> for using stored procedures in the first place).

Ok, so post some numbers then. It might be interesting to look at the
cost of preparing a statement, although AFAIK that does not store the
query plan anywhere.

In most databases, query planning seems to be a pretty expensive
operation. My experience is that that isn't the case with PostgreSQL.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-01-20 19:14:29
Subject: Re: [GENERAL] Creation of tsearch2 index is very slow
Previous:From: Jim C. NasbyDate: 2006-01-20 19:04:37
Subject: Re: query stopped working after tables > 50000 records

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