[PATCH] Custom code int(32|64) => text conversions out of performance reasons

Hi,

While looking at binary COPY performance I forgot to add BINARY and was a bit 
shocked to see printf that high in the profile...

Setup:

CREATE TABLE convtest AS SELECT a.i ai, b.i bi, a.i*b.i aibi, (a.i*b.i)::text 
aibit FROM generate_series(1,1000) a(i), generate_series(1, 10000) b(i);

Profile with an unmodified pg:

speedtest=# COPY convtest(ai,bi,aibi) TO '/dev/null';
COPY 10000000
Time: 9192.476 ms

Profile:# Events: 9K cycles## Overhead          Command      Shared Object                        Symbol# ........
............... .................  ............................#    18.24%  postgres_oldint  libc-2.12.1.so     [.]
__GI_vfprintf    8.90%  postgres_oldint  libc-2.12.1.so     [.] _itoa_word     8.77%  postgres_oldint  postgres_oldint
 [.] CopyOneRowTo     8.19%  postgres_oldint  libc-2.12.1.so     [.] 
 
_IO_default_xsputn_internal     3.67%  postgres_oldint  postgres_oldint    [.] AllocSetAlloc     3.38%  postgres_oldint
libc-2.12.1.so     [.] __strchrnul     3.24%  postgres_oldint  libc-2.12.1.so     [.] __GI___vsprintf_chk     2.87%
postgres_oldint postgres_oldint    [.] heap_deform_tuple     2.49%  postgres_oldint  libc-2.12.1.so     [.]
_IO_old_init    2.25%  postgres_oldint  libc-2.12.1.so     [.] _IO_new_file_xsputn     2.03%  postgres_oldint
postgres_oldint   [.] appendBinaryStringInfo     1.89%  postgres_oldint  postgres_oldint    [.] heapgettup_pagemode
1.86% postgres_oldint  postgres_oldint    [.] FunctionCall1     1.85%  postgres_oldint  postgres_oldint    [.]
AllocSetCheck    1.79%  postgres_oldint  postgres_oldint    [.] enlargeStringInfo
 



Timing after replacing those sprintf("%li", ...) calls with a quickly coded 
handrolled itoa:

speedtest=# COPY convtest(ai,bi,aibi) TO '/dev/null';
COPY 10000000
Time: 5309.928 ms

Profile:# Events: 5K cycles## Overhead   Command      Shared Object                       Symbol# ........  ........
................. ...........................#    14.96%  postgres  postgres           [.] pg_s32toa    14.75%
postgres postgres           [.] CopyOneRowTo     5.97%  postgres  postgres           [.] AllocSetAlloc     4.73%
postgres postgres           [.] heap_deform_tuple     4.54%  postgres  postgres           [.] AllocSetCheck     4.01%
postgres libc-2.12.1.so     [.] _IO_new_file_xsputn     3.59%  postgres  postgres           [.] heapgettup_pagemode
3.32% postgres  postgres           [.] enlargeStringInfo     3.25%  postgres  postgres           [.]
appendBinaryStringInfo    2.87%  postgres  postgres           [.] CopySendChar     2.65%  postgres  postgres
[.]FunctionCall1     2.44%  postgres  postgres           [.] int4out     2.38%  postgres  [kernel.kallsyms]  [k]
copy_user_generic_string    2.30%  postgres  postgres           [.] AllocSetReset     2.06%  postgres  postgres
 [.] pg_server_to_client     1.89%  postgres  libc-2.12.1.so     [.] __GI_memset     1.87%  postgres  libc-2.12.1.so
[.] memcpy
 



A change from 9192.476ms 5309.928ms seems to be pretty good indication that a 
change in that area is waranted given integer columns are quite ubiquous...

While at it:

* I remove the outdated
-- NOTE: int[24] operators never check for over/underflow!
-- Some of these answers are consequently numerically incorrect.
warnings in the regressions tests.

* I renamed pg_[il]toa to pg_s(16|32|64)toa - I found the names confusing. Not 
sure if its worth it.

* I added some tests for the border cases of 2^31-1 / -2^31

The 'after' profile shows obvious room for furhter improvement, but on a quick 
look I couldn't think of anything. Any Ideas?



Andres


PS: Oh, thats with assertions, but the results are comparable without them 
(8765.796ms vs 4561.673ms)