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