Horton

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

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Thom Brown <thom@linux.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2017-09-15T21:33:34Z
Lists: pgsql-hackers

Attachments

Hi Thom,

On 2017-09-15 22:05:35 +0100, Thom Brown wrote:
> 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.

I suspect a part of that is that you're testing the patch in isolation,
whereas I tested it as part of multiple speedup patches. There's some
bigger bottlenecks than this one. I've attached my whole stack.

But even that being said, here's the result for these patches in
isolation on my machine:

setup:
psql -p 5440 -f ~/tmp/create_many_cols.sql

pgbench -M prepared -f ~/tmp/pgbench-many-cols.sql -n -T 10 -P 1
master (best of three):
tps = 13347.023301 (excluding connections establishing)
patched (best of three):
tps = 14309.690741 (excluding connections establishing)

Which is a bigger win than what you're observing. I've also attached the
benchmark scripts I used.  Could you detail how your benchmark works a
bit more? Any chance you looped in plpgsql or such?


Just for fun/reference, these are the results with all the patches
applied:
tps = 19069.115553 (excluding connections establishing)
and with just this patch reverted:
tps = 17342.006825 (excluding connections establishing)

Regards,

Andres

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.