Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g5NOI-0006V6-1m for pgsql-hackers@arkaria.postgresql.org; Thu, 27 Sep 2018 03:55:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g5NOF-00053G-91 for pgsql-hackers@arkaria.postgresql.org; Thu, 27 Sep 2018 03:54:59 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g5NOE-00052x-Tq for pgsql-hackers@lists.postgresql.org; Thu, 27 Sep 2018 03:54:59 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g5NO7-0003pz-AN for pgsql-hackers@lists.postgresql.org; Thu, 27 Sep 2018 03:54:57 +0000 Received: from [192.168.127.1] (port=65038 helo=caithnard.riddles.org.uk) by lungold.riddles.org.uk with esmtps (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256) (Exim 4.88 (FreeBSD)) (envelope-from ) id 1g5NMm-000IMS-Rm; Thu, 27 Sep 2018 03:53:28 +0000 Received: from [127.0.0.1] (port=16964 helo=caithnard.riddles.org.uk) by caithnard.riddles.org.uk with esmtp (Exim 4.89 (FreeBSD)) (envelope-from ) id 1g5NMm-000MAj-5z; Thu, 27 Sep 2018 03:53:28 +0000 From: Andrew Gierth To: Andres Freund Cc: Tom Lane , Alexander Kuzmenkov , pgsql-hackers@lists.postgresql.org Subject: Re: Performance improvements for src/port/snprintf.c In-Reply-To: <20180927005705.shbl3n5hq3t65n2j@alap3.anarazel.de> (Andres Freund's message of "Wed, 26 Sep 2018 17:57:05 -0700") Message-ID: <87r2hffwda.fsf@news-spur.riddles.org.uk> References: <11787.1534530779@sss.pgh.pa.us> <2238.1534716465@sss.pgh.pa.us> <15785.1536776055@sss.pgh.pa.us> <20180926220419.lorxpb564gakdjkz@alap3.anarazel.de> <20180926221814.el5nwmkez6zfmzbz@alap3.anarazel.de> <14302.1538005507@sss.pgh.pa.us> <20180926235821.zmpxqv66ob7v2ffi@alap3.anarazel.de> <15882.1538007944@sss.pgh.pa.us> <20180927004022.lfhffbj3roxzf5ca@alap3.anarazel.de> <20180927005705.shbl3n5hq3t65n2j@alap3.anarazel.de> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix) Date: Thu, 27 Sep 2018 04:53:27 +0100 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >>>>> "Andres" == Andres Freund writes: Andres> Hm, stb's results just for floating point isn't bad. The above Andres> numbers were for %f %f. But as the minimal usage would be about Andres> the internal usage of dopr(), here's comparing %.*f: Andres> snprintf time = 1324.87 ms total, 0.000264975 ms per iteration Andres> pg time = 1434.57 ms total, 0.000286915 ms per iteration Andres> stbsp time = 552.14 ms total, 0.000110428 ms per iteration Hmm. We had a case recently on IRC where the performance of float8out turned out to be the major bottleneck: a table of about 2.7 million rows and ~70 float columns showed an overhead of ~66 seconds for doing COPY as opposed to COPY BINARY (the actual problem report was that doing "select * from table" from R was taking a minute+ longer than expected, we got comparative timings for COPY just to narrow down causes). That translates to approx. 0.00035 ms overhead (i.e. time(float8out) - time(float8send)) per conversion (Linux server, hardware unknown). That 66 seconds was the difference between 18s and 1m24s, so it wasn't a small factor but totally dominated the query time. -- Andrew (irc:RhodiumToad)