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

On Sat, 2007-11-24 at 00:04 -0500, Tom Lane wrote:

> 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.

Well, I've been asked to tune enough applications that fall into this
category that I can say this message isn't getting across anywhere near
as strongly as you might have thought. There is still good reason to use
an RDBMS and Postgres in particular, but still 80-90% (by number) of
access would be the types of queries you mention in (2) above, even if
they represent about 20-30% of total time/resources. In many cases, 100%
of queries are unprepared.

So your efforts are well spent. It has also made me think about further
optimizations and tuning options in this area for 8.4

On the plus side, there are many very savvy people out there too and all
the performance features we put in are being used in serious ways. But
we must cater for both the top end and bottom end of the application
spectrum.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com