Re: 8.3devel slower than 8.2 under read-only load

"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
> On Nov 23, 2007 8:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> So I'm satisfied with these results, particularly in view of the
>> fact that what we're measuring is certainly the stupidest, least
>> efficient way to use Postgres.

> Given the emerging world of data-driven content management systems and
> select-mostly web applications, I'd hesitate to say that select-only
> transactions aren't worth optimizing for.

I didn't intend to say that select-only transactions aren't interesting;
rather that there should be some minimal effort on the application side.
The cases we are testing here involve:

1. One query per transaction. Even with the 8.3 improvements to reduce
overhead for select-only transactions, this isn't necessarily a good
idea.

2. *Extremely* trivial queries --- fetching one row from one table on
the basis of its primary key --- which make you wonder why the
programmer is using a SQL database rather than ndb or some such.
Anyone who's used SQL for any length of time knows that it's better to
push more of the application logic onto the database side, but these
queries are typical of apps that think they should do most of the work.

3. No use of prepared statements. Duh, especially in view of #2. There
are reasons to avoid prepared statements in the case that you're issuing
commands that have some intellectual interest for the planner, but these
are not those.

The whole thing is the worst-case scenario for a DBMS that spends any
real effort on analyzing/planning SQL commands.

Even given all that, I think 8.3 would look pretty good if we were
throwing enough concurrent queries at it to make Florian's transaction
concurrency improvements a factor. But with only 10 concurrent
sessions, and pgbench's known limitations for issuing concurrent
commands fast enough to keep things busy, there's no opportunity to
shine in that dimension either.

regards, tom lane