Horton

Re: SendRowDescriptionMessage() is slow for queries with a lot of columns

Thom Brown <thom@linux.com>

View thread View on postgr.es
From: Thom Brown <thom@linux.com>
To: Andres Freund <andres@anarazel.de>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2017-09-15T21:05:35Z
Lists: pgsql-hackers 
On 15 September 2017 at 19:23, Andres Freund <andres@anarazel.de> wrote:
> Hi Thom,
>
> Thanks for taking a whack at this!
>
> On 2017-09-15 12:16:22 +0100, Thom Brown wrote:
>> I've run a fairly basic test with a table with 101 columns, selecting
>> a single row from the table and I get the following results:
>>
>>
>> Columns with 1-character names:
>>
>> master (80 jobs, 80 connections, 60 seconds):
>
> FWIW, I don't think it's useful to test this with a lot of concurrency -
> at that point you're likely saturating the machine with context switches
> etc. unless you have a lot of cores. As this is isn't related to
> concurrency I'd rather just check a single connection.
>
>
>> transaction type: /tmp/test.sql
>> scaling factor: 1
>> query mode: simple
>
> I think you'd need to use prepared statements / -M prepared to see
> benefits - when parsing statements for every execution the bottleneck is
> elsewhere (hello O(#available_columns * #selected_columns) in
> colNameToVar()).

Okay, I've retested it using a prepared statement executed 100,000
times (which selects a single row from a table with 101 columns, each
column is 42-43 characters long, and 2 rows in the table), and I get
the following:

master:

real    1m23.485s
user    1m2.800s
sys    0m1.200s


patched:

real    1m22.530s
user    1m2.860s
sys    0m1.140s


Not sure why I'm not seeing the gain.

Thom

Commits

  1. Replace remaining uses of pq_sendint with pq_sendint{8,16,32}.

  2. Improve performance of SendRowDescriptionMessage.

  3. Use one stringbuffer for all rows printed in printtup.c.

  4. Add configure infrastructure to detect support for C99's restrict.

  5. Add more efficient functions to pqformat API.

  6. Allow to avoid NUL-byte management for stringinfos and use in format.c.

  7. Replace most usages of ntoh[ls] and hton[sl] with pg_bswap.h.

  8. Extend & revamp pg_bswap.h infrastructure.