On Fri, Jan 20, 2006 at 07:50:23PM +0100, Rikard Pavelic wrote:
> >>Will simple queries such as "SELECT * FROM blah_table WHERE tag='x'; work
> >>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
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
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2006-01-20 19:14:29|
|Subject: Re: [GENERAL] Creation of tsearch2 index is very slow |
|Previous:||From: Jim C. Nasby||Date: 2006-01-20 19:04:37|
|Subject: Re: query stopped working after tables > 50000 records|