Re: Any better plan for this query?..

Matthew Wakeling <matthew@flymine.org> writes:
> On Tue, 12 May 2009, Simon Riggs wrote:
>> No, we spawn then authenticate.

> But you still have a single thread doing the accept() and spawn. At some
> point (maybe not now, but in the future) this could become a bottleneck
> given very short-lived connections.

More to the point, each backend process is a pretty heavyweight object:
it is a process, not a thread, and it's not going to be good for much
until it's built up a reasonable amount of stuff in its private caches.
I don't think the small number of cycles executed in the postmaster
process amount to anything at all compared to the other overhead
involved in getting a backend going.

In short: executing a single query per connection is going to suck,
and there is not anything we are going to do about it except to tell
you to use a connection pooler.

MySQL has a different architecture: thread per connection, and AFAIK
whatever caches it has are shared across threads.  So a connection is a
lighter-weight object for them; but there's no free lunch.  They pay for
it in having to tolerate locking/contention overhead on operations that
for us are backend-local.

            regards, tom lane