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

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

From: Andres Freund <andres(at)anarazel(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: [PATCH] Custom code int(32|64) => text conversions out of performance reasons
Date: 2010-10-31 21:41:50
Message-ID: 201010312241.50893.andres@anarazel.de (view raw or flat)
Thread:
Lists: pgsql-hackers
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)

Attachment: 0001-Implement-custom-int-248-string-conversion-routines-.patch
Description: text/x-patch (10.4 KB)

Responses

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2010-10-31 21:46:56
Subject: Re: ALTER OBJECT any_name SET SCHEMA name
Previous:From: Greg SmithDate: 2010-10-31 21:31:44
Subject: SR fails to send existing WAL file after off-line copy

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