Re: [HACKERS] PC Week Labs benchmark results

Timothy Dyck wrote:
>
> Hi everybody, I'm done my tests of PostgreSQL and Interbase.
>
> I concentrated on two tests, an OLTP Single Read Test, where we read a
> single row out of a 200K row indexed table, and the OLTP Read Mix Test,
> which is a mix of about 30 queries, about half single table selects and
> the other half joins of various complexity (up to four way). For both of
> these tests, InterBase was about 2x to 2.5x as fast as PostgreSQL. In
> multiuser tests (up to 100 users), the situation was reversed, with
> PostgreSQL close to 3 times faster at peak throughput (which was at 50
> concurrent users). The reason why is that InterBase on Linux has a
> process-per-connection architecture without a shared cache. As such, I had
> to really limit cache sizes to allow 100 users to connect, and that really
> hurt InterBase's performance.
>
> I ran both PostgreSQL and InterBase with syncs turned off, and used a
> cache of 65536 4KB pages and 4000K of sort buffer.
>
> Here's a list of things about PostgreSQL I had problems with:
>
> 1. "Null" is not accepted keyword on "create table" ("not null" is ok)

There was some discussion of this in the lists in the past:

http://www.postgresql.org/mhonarc/pgsql-hackers/1998-12/msg00546.html

: : : Now that we have the syntax problem straightened out:
I'm still : confused
: : : about the semantics. Does a "NULL" constraint say
that the field
: : : *must* be null, or only that it *can* be null (in
which case NULL is
: : : just a noise word, since that's the default
condition)? I had assumed
: : : the former, but Bruce seemed to think the latter...
: :
: : Can be null. Noise word. At least that is what I
rememeber Thomas
: : saying, and because it was noise, we removed it. In
fact, it doesn't
: : look like the standard accepts it, but there is no
reason we can't.

: This NULL clause is not part of constraints it is a
default option and
: we already support it,
: there's nothing like:
: CREATE TABLE table1 (field1 type NULL) in SQL92.

: but the following is SQL92 and it works on PostgreSQL:

: prova=> CREATE TABLE table1 (field1 INTEGER DEFAULT NULL);
: CREATE

> 2. copy command 'with null as' option not functional
> 3. try to create an index on a numeric and "no operator class for
> 'numeric' data type" error message results. Numerics not indexable?

That's fixed in current sources...its too bad you aren't
reviewing this a couple of months from now -- but I bet you
hear a lot of that...

> 4. no outer join -- I had to drop one query because of this

That's always been annoying, although it can be simulated
easily with:

SELECT t1.x, t2.y
FROM t1, t2
WHERE t1.x = t2.x
UNION
SELECT t1.x, NULL
FROM t1 WHERE NOT EXISTS ( SELECT t2.x FROM t2 WHERE t1.x =
t2.x );

> 5. no alter table add constraint
> 6. select count(distinct *) from a view gives a parser error on distinct
> -- distinct keyword not supported here?
> 7. one query (dss_select_05) has an avg on a numeric field. I got an
> overflow error (is there a cast to a longer type?). When the avg on
> numeric field is removed, the query consumes memory rapidly and doesn't
> terminate. I dropped this query.
> 8. Can't start postmaster with more than 65536 buffers as I get a "FATAL
> 1: couldn't
> initialize shared buffer pool Hash Tbl". Variable overflow?

If you are referring to the -B option of the postmaster,
each "buffer" is 8K in size. So, for example -B 256 would be
2 megs of buffers. How much RAM was on the test machine? -B
65536 is a 1/2 gig...

>
> Thanks for the tuning suggestions I received from various people.
>
> Also, is PostgreSQL 7 expected to be SQL-92 compliant? It's pretty close
> now.
>
> I'll be posting complete scripts and C code when the story goes to print
> on Feb. 14.
>
> Regards,
> Tim Dyck
> Senior Analyst
> PC Week Labs
>
> ************