Thread: replace_text() improvement
I made the patch that improved the performance of replace_text(). The content of the patch is as follows: (1)Create shortcut when subtext was not found. (2)Stop using LEFT and RIGHT macro. In LEFT and RIGHT macro, TEXTPOS is executed by the same content as execution immediately before. The execution frequency of TEXTPOS can be reduced by using text_substring instead of LEFT and RIGHT macro. (3)Add appendStringInfoText, and use it instead of appendStringInfoString. There is an overhead of PG_TEXT_GET_STR when appendStringInfoString is executed by text type. This can be reduced by appendStringInfoText. (4)Reduce execution of TEXTDUP. The effect of the patch that I measured is as follows: - The Data for test was created by 'pgbench -i'. - Test SQL: select replace(aid, '9', 'A') from accounts; - Test results: Linux(CPU: Pentium III, Compiler option: -O2) original: 1.515s patched: 1.250s regards, --- Atsushi Ogawa
Attachment
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Atsushi Ogawa wrote:
>
> I made the patch that improved the performance of replace_text().
> The content of the patch is as follows:
>
> (1)Create shortcut when subtext was not found.
>
> (2)Stop using LEFT and RIGHT macro.
> In LEFT and RIGHT macro, TEXTPOS is executed by the same content as
> execution immediately before. The execution frequency of TEXTPOS can be
> reduced by using text_substring instead of LEFT and RIGHT macro.
>
> (3)Add appendStringInfoText, and use it instead of appendStringInfoString.
> There is an overhead of PG_TEXT_GET_STR when appendStringInfoString is
> executed by text type. This can be reduced by appendStringInfoText.
>
> (4)Reduce execution of TEXTDUP.
>
> The effect of the patch that I measured is as follows:
>
> - The Data for test was created by 'pgbench -i'.
>
> - Test SQL:
> select replace(aid, '9', 'A') from accounts;
>
> - Test results: Linux(CPU: Pentium III, Compiler option: -O2)
> original: 1.515s
> patched: 1.250s
>
> regards,
>
> ---
> Atsushi Ogawa
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. --------------------------------------------------------------------------- Atsushi Ogawa wrote: > > I made the patch that improved the performance of replace_text(). > The content of the patch is as follows: > > (1)Create shortcut when subtext was not found. > > (2)Stop using LEFT and RIGHT macro. > In LEFT and RIGHT macro, TEXTPOS is executed by the same content as > execution immediately before. The execution frequency of TEXTPOS can be > reduced by using text_substring instead of LEFT and RIGHT macro. > > (3)Add appendStringInfoText, and use it instead of appendStringInfoString. > There is an overhead of PG_TEXT_GET_STR when appendStringInfoString is > executed by text type. This can be reduced by appendStringInfoText. > > (4)Reduce execution of TEXTDUP. > > The effect of the patch that I measured is as follows: > > - The Data for test was created by 'pgbench -i'. > > - Test SQL: > select replace(aid, '9', 'A') from accounts; > > - Test results: Linux(CPU: Pentium III, Compiler option: -O2) > original: 1.515s > patched: 1.250s > > regards, > > --- > Atsushi Ogawa [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073