Re: Oracle vs PostgreSQL in real life : NEWS!!!

> > Oracle on NT 4 : 45 minuts to go , 1200 tps (yes one thousand and two hundred
> > tps)
> >
> > Linux Red Hat 7.2 with PostgreSQL 7.2 : hours to go (statistically, 45 hours),
> > 80 tps (eighty tps).

Well... Where to start?

We work on a team of two. The other one is a C/C++ senior coder. He
mailed me a remark about datatypes on the database. Here is what he sent
me:

Our database has different datatypes, here are a count of distinct
datatypes in all tables:

197 numeric(x)
19 numeric(x,2)
2 varchar(x)
61 char(x)
36 datetime

He asked me about numeric(x) and he questioned my about how PG managed
the NUMERIC types.

I gave him a pointer on "numeric.c" in the PG srcs.

I analyzed this source and found that NUMERIC types are much most
expensive than simple INTEGER.

I really fall on the floor.. :-( I was sure with as good quality PG is,
when NUMERIC(x) columns are declared, It would be translated in INTEGER
(int2, 4 or 8, whatever...).

So, I made a pg_dump of the current database, made some perl
remplacements NUMERIC(x,0) to INTEGER.

I loaded the database and launched treatments: the results are REALLY
IMPRESIVE: here what I have:

((it is a port of Oracle/WinNT stuff to PostgreSQL/Red Hat 7.2)):

Oracle PG72 with NUMERICs PG72 with INTEGERS
--------------------------------------------------------------------------
sample
connect by
query ported 350ms 750ms 569ms
to PG
(thanks to
OpenACS code!)
--------------------------------------------------------------------------
sample "big"
query with
connect bys 3 min 30s 8 min 40s 5 min 1s
and many
sub-queries
--------------------------------------------------------------------------
Big Batch
treatment 1300 queries/s 80 queries/s 250 queries/s
queries

PRO*C to 45 min to go ~4 to 6 DAYS not yet
ECPG to go tested fully

Ratio 1:1 1:21 not yet ..
21 times slower!

--------------------------------------------------------------------------
((but this batch will be yet re-writen in pure C + libpq + SPI,
so we think we'll have better results again))

So as you see, DATA TYPES are REALLY important, as I did write on a
techdocs article ( I should have tought in this earlier )

Then?

I'll inform you of what's going on with this Oracle/winnt 2 PG/linux port :-))

And We thank you _very_ much of all the help you gave us.

Best regards and Wishes,

--
Jean-Paul ARGUDO