Re: Oracle vs PostgreSQL in real life : NEWS!!! - Mailing list pgsql-hackers

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