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

Re: Are stored procedures pre-compiled?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Neil Conway <nconway(at)klamath(dot)dyndns(dot)org>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Are stored procedures pre-compiled?
Date: 2002-02-22 04:11:57
Message-ID: 13816.1014351117@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> On Thu, 2002-02-21 at 22:15, Tom Lane wrote:
>> I believe that SQL-language functions don't have any such optimization
>> at all :-( ... I think they are re-parsed and re-planned from scratch
>> on each call.

> Would it be possible to enhance SQL-language functions to cache their
> query plan?

Certainly; if plpgsql can do it, so can SQL functions.  You could even
steal (or better, find a way to share) a lot of the code from plpgsql.
But no one's gotten around to it.

A related improvement that's been in the back of my mind for awhile
is to "inline" trivial SQL functions.  If you look in pg_proc you'll
find quite a few SQL functions that are just "SELECT
some-arithmetic-expression".  I would like to get the planner to expand
those into the parse tree of the invoking query, so that the function
call overhead goes away completely.  For example, bit_length(text) is
defined as "select octet_length($1) * 8", so

	SELECT bit_length(f1) FROM text_tbl WHERE ...

could be expanded to

	SELECT octet_length(f1) * 8 FROM text_tbl WHERE ...

which seems to run about three or four times as fast (though of course
some of that differential would go away given caching of SQL-function
plans).

I don't believe this would be excessively difficult, but it hasn't
gotten to the top of the to-do queue...

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2002-02-22 04:13:21
Subject: Re: Summary of new configuration file and data directory locations
Previous:From: Bruce MomjianDate: 2002-02-22 04:07:10
Subject: Re: elog() proposals

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