follow-up on PC Week Labs benchmark results

Hi all, here are my responses to various questions:

First, there was one more syntax request I forgot to include before:

create view doesn't allow column aliases in the view definition: e.g.
create view myview (a,b,c) as select x,y,z ... doesn't work, but create
view myview select x as a, y as b, z as c ... works.

from Tom Lane:
> Thanks for the report!  I don't suppose you'd be interested in rerunning
your tests on current (pre-beta-7.0) sources?

(and other questions on what version to test)

We get a lot of that anytime we run tests. A simple answer is that PC Week
never benchmarks non-production, non-released code. The next question is
why not wait until version x when we are that much better? We have to put
the stake in the ground sometime, and this story was driven by the news of
InterBase going open source. We cover the news week by week, and all
stories have to have a current news hook of some kind. That being said,
when there is a significant change in the competitive landscape, I'd like
to run the tests again (esp. now that I have gotten all the scripts done).
In particular, I'd like to compare IB 6 with PG 7.x and MySQL later this
year (I didn't benchmark MySQL this time because I ran out of time, but
would very much like to.)

> 65536 buffer limit?

I was using a 4KB page size, so this was 256MB of cache. In this case, I
was using a database that was about 86 MB of data and indices, so I didn't
need this much cache. However, I could easily see a production database
server for a mid-size company equipped with 1 or 2 GB of RAM and I would
assign 80% of that RAM to the db cache. In future tests, I will be testing
with a 4 GB database and so will need to be using as big a cache as
PostgreSQL can support.

> outer joins

As Don Baccus points out, simulating outer joins with a union and a not
exists gets hairy when you have more than two tables involved.
Interestingly, Sybase only supported outer joins as of two months ago
(12.0), though the others have supported them for some time.

(from Ed Loehr)
> I was disappointed this benchmark did not include database recovery
and reliability measurements.  Benchmarks ought to include the most
important characteristics of an RDBMS, and recovery/reliability is
certainly one of them

I quite agree, though, of course, there always a balancing of time to run
the tests vs. value of results gained. The benchmark I chose for PC Week's
use (AS3AP by Turbyfill, Orji and Bitton) is actually much more rounded
than most benchmarks (Wisconsin, TPC-A/B/C, etc.) because it includes a)
load time, b) index time, c) update stats time, d) DSS queries such as
aggregates and counts, e) is both single user and multiuser, and f) uses a
wide variety of data types, not just int and char.

In addition, I have a) extended the query set quite a bit to cover much
more of the SQL92 entry/intermediate level spec, and b) added query log
tables and consistency check queries to do some testing of proper ACID
properties.

Now on the specific issue of recovery, I decided a few years ago not to
measure that metric solely because the TPC-C test does such a good job of
checking for ACIDity. In fact, continual TPC-C testing is a big reason why
today's databases are so reliable. The problem here is that no open source
database has ever been tested by the TPC because none of the development
groups are TPC members. I'd certainly suggest that Red Hat or VA Linux do
this to get some database numbers on the board. It's a key credibility
test because just passing is a very good assurance of really debugged
transaction logging code.