Re: Single client performance on trivial SELECTs - Mailing list pgsql-hackers

Greg Smith <greg@2ndquadrant.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