Re: [HACKERS] PC Week Labs benchmark results

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

So, we scale better. Nice.

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

"NULL" is *not* SQL92 standard (well, at least it wasn't in the draft
standard available on DEC's web site) presumably since including it
generally leads to parsing problems with a one-token-lookahead parser
such as yacc.

Also, since it is the default behavior for column creation, it seems
to be pretty much a useless noise word in this context.

But it will be in the next release for the typical case; I implemented
it a month or so ago but have some other developments I've been
working on and haven't yet committed this one to the source tree. Will
likely be there this weekend.

> 2. copy command 'with null as' option not functional

This was added 1999/12/14 to the development tree. Will be in the next
release.

> 3. try to create an index on a numeric and "no operator class for
> 'numeric' data type" error message results. Numerics not indexable?

Not yet. Should be there for the next release (1-2 months).

> 4. no outer join -- I had to drop one query because of this
> 5. no alter table add constraint
> 6. select count(distinct *) from a view gives a parser error on distinct
> -- distinct keyword not supported here?

These are all high on the ToDo list, but I'm not sure they will be in
the next release.

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

Just guessing, but this is more likely a system resource problem. That
is a lot of buffers!

> Also, is PostgreSQL 7 expected to be SQL-92 compliant? It's pretty close
> now.

What feature do you feel is lacking for Postgres to be SQL92
compliant? As you know, SQL92 defines three levels of compliance, and
although virtually all databases claim compliance it is almost always
to the lowest, most basic level.

Things like outer joins are not required for the basic compliance,
which is how, for example, Oracle gets to claim compliance without
supporting SQL92 outer join syntax.

> I'll be posting complete scripts and C code when the story goes to print
> on Feb. 14.

Great.

Regards.
                  - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California