Re: Single client performance on trivial SELECTs

Greg Smith <greg(at)2ndquadrant(dot)com> writes:
> samples % image name symbol name
> 53548 6.7609 postgres AllocSetAlloc
> 32787 4.1396 postgres MemoryContextAllocZeroAligned
> 26330 3.3244 postgres base_yyparse
> 21723 2.7427 postgres hash_search_with_hash_value
> 20831 2.6301 postgres SearchCatCache
> 19094 2.4108 postgres hash_seq_search
> 18402 2.3234 postgres hash_any
> 15975 2.0170 postgres AllocSetFreeIndex
> 14205 1.7935 postgres _bt_compare
> 13370 1.6881 postgres core_yylex
> 10455 1.3200 postgres MemoryContextAlloc
> 10330 1.3042 postgres LockAcquireExtended
> 10197 1.2875 postgres ScanKeywordLookup
> 9312 1.1757 postgres MemoryContextAllocZero

Yeah, this is pretty typical ...

> I don't know nearly enough about the memory allocator to comment on
> whether it's possible to optimize it better for this case to relieve any
> bottleneck.

I doubt that it's possible to make AllocSetAlloc radically cheaper.
I think the more likely route to improvement there is going to be to
find a way to do fewer pallocs. For instance, if we had more rigorous
rules about which data structures are read-only to which code, we could
probably get rid of a lot of just-in-case tree copying that happens in
the parser and planner.

But at the same time, even if we could drive all palloc costs to zero,
it would only make a 10% difference in this example. And this sort of
fairly flat profile is what I see in most cases these days --- we've
been playing performance whack-a-mole for long enough now that there
isn't much low-hanging fruit left. For better or worse, the system
design we've chosen just isn't amenable to minimal overhead for simple
queries. I think a lot of this ultimately traces to the extensible,
data-type-agnostic design philosophy. The fact that we don't know what
an integer is until we look in pg_type, and don't know what an "="
operator does until we look up its properties, is great from a flexibility
point of view; but this sort of query is where the costs become obvious.

regards, tom lane