Re: Oracle vs PostgreSQL in real life

Jean-Paul ARGUDO wrote:
>
> Okay...
>
> I'm very sceptic today.
>
> I'm making a survey on Oracle 8.0 on NT4 remplacement with a RedHat 7.2/PG 7.2
>
> The customer gave me stuff to migrate, like scripts in Pro*C Oracle that I
> migrated successfully with ECPG. Other stuff with Connect by statments, thanks
> to OpenACS guys, I migrated this Connect by statments too.
>
> But finaly, with all my mind I explained all queries, made all good, I hope
> everything has be done.
>
> The "test" is a big batch that computes stuffs in the database. Here are the
> timings of both Oracle and PG (7.2) :
>
> 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).

Wow! That is huge. Ok, let me ask some questions:

Did you do a "vacuum analyze" on the tables?
If you did not analyze the tables, it may be using table scans instead of
indexes. That would make a huge difference. Also, it may choose poorly between
hash joins and merge joins.

Did you tune "buffers" in postgresql.conf?
If you have too few buffers, you will get no caching effect on the queries.