Thread: Speedup idea: avoid using SQL procedures as aliases
I noticed just now that there are a lot of SQL-language procedures in
pg_proc whose only purpose is to provide alternative names for built-in
functions. For example, none of the seven functions named "float8"
is actually a built-in; they are all SQL aliases for built-in functions
like i4tod().
It strikes me that this is pretty inefficient. For example, converting
an int4 column to float seems to take about twice as long if you do it
with float8(int4column) as if you do it with i4tod(int4column), because
the former involves a level of SQL overhead.
I am thinking about fixing this by decoupling the user-level name of
an internal function from its C-language name. The simplest way seems
to be to modify pg_proc.h and Gen_fmgrtab.sh.in so that the C-language
name of an internal function is stored in pg_proc's "prosrc" field
(which is currently unused for internal functions) rather than being
taken from "proname". Then, all of the SQL functions that are simply
aliases for internal functions could be converted to plain internal
function entries that have proname different from prosrc.
Anyone have an objection to this? I suppose we'd need to check that
the regression tests still exercise SQL functions ;-)
regards, tom lane
> I noticed just now that there are a lot of SQL-language procedures in
> pg_proc whose only purpose is to provide alternative names for
> built-in functions.
Yeah. Neat hack, eh? Edmund Mergl found this, and allowed us to use
generic names for functions for the first time. But...
> It strikes me that this is pretty inefficient.
> I am thinking about fixing this by decoupling the user-level name of
> an internal function from its C-language name. The simplest way seems
> to be to modify pg_proc.h and Gen_fmgrtab.sh.in so that the C-language
> name of an internal function is stored in pg_proc's "prosrc" field
> (which is currently unused for internal functions) rather than being
> taken from "proname". Then, all of the SQL functions that are simply
> aliases for internal functions could be converted to plain internal
> function entries that have proname different from prosrc.
> Anyone have an objection to this? I suppose we'd need to check that
> the regression tests still exercise SQL functions ;-)
No objection; I've toyed with the idea of doing this, but didn't have
the guts to touch the layout of system tables. You seem to have no
such qualms ;)
I'd be happy to help with the conversion, though I'd suggest that this
would perhaps be a great topic for v6.6 since it does involve some
fundamental (hopefully isolated) changes. Also, for my participation
(certainly not required) I'd have more time during the next cycle...
- Tom
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> I'd be happy to help with the conversion, though I'd suggest that this
> would perhaps be a great topic for v6.6 since it does involve some
> fundamental (hopefully isolated) changes.
Actually, the changes were *extremely* isolated, being visible nowhere
other than fmgr. Editing pg_proc.h was the only hard part (fortunately
I was able to do the bulk of the work with a sed script).
I see I just got it squeezed in under the wire before Marc declared
beta freeze, however ;-)
regards, tom lane
> Thomas Lockhart <lockhart@alumni.caltech.edu> writes: > > I'd be happy to help with the conversion, though I'd suggest that this > > would perhaps be a great topic for v6.6 since it does involve some > > fundamental (hopefully isolated) changes. > > Actually, the changes were *extremely* isolated, being visible nowhere > other than fmgr. Editing pg_proc.h was the only hard part (fortunately > I was able to do the bulk of the work with a sed script). > > I see I just got it squeezed in under the wire before Marc declared > beta freeze, however ;-) > Ah, sounds like me. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026