I just finished profiling an application that does a lot of SELECTs from
a Postgres database. I expected to find that parsing and conversion of
the data would be a big chunk of its runtime; I was thinking about
converting from plain ASCII SELECTs to binary cursor FETCHes, and wanted
to see what it would buy.
Well, I learned something. Parsing of the data is pretty cheap.
What's chewing up a surprisingly large percentage of the runtime is all
the malloc()s and free()s that libpq does while building and releasing
PGresult objects.
It looks like we could improve the speed of reading data *significantly*
if libpq didn't malloc space separately for each and every field value.
All those small, odd-sized, short-lived malloc blocks are hard on the
average malloc library, or at least on the one I have.
Since PGresult is now an opaque structure, it shouldn't be hard to
change things so that it allocates blocks of, say, a few K at a time,
and then fills those blocks with field values. A little wastage of
space shouldn't be a problem, especially since (I suppose) few apps keep
PGresults around for very long.
I'll probably undertake this improvement sometime soon, but I thought
I'd mention it for the archives, in case I get run over by a truck or
something before I get to it.
regards, tom lane