From: Jim C. Nasby <jnasby@pervasive.com>
To: Rikard Pavelic <rikard.pavelic@zg.htnet.hr>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORMANCE] Stored Procedures
Date: Fri, 20 Jan 2006 13:10:31 -0600
Message-ID: <20060120191031.GO20182@pervasive.com> (raw)
In-Reply-To: <43D130EF.3010205@zg.htnet.hr>
References: <43D130EF.3010205@zg.htnet.hr>
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@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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-performance@postgresql.org
Cc: jnasby@pervasive.com, rikard.pavelic@zg.htnet.hr
Subject: Re: [PERFORMANCE] Stored Procedures
In-Reply-To: <20060120191031.GO20182@pervasive.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