Guy Rouillier wrote:
> I've got back access to my test system. I ran another test run with the
> same input data set. This time I put pg_xlog on a different RAID volume
> (the unused one that I suspect is a software RAID), and I turned
> fsync=off in postgresql.conf. I left the rest of the configuration
> alone (all foreign keys removed), etc. Unfortunately, this only dropped
> elapsed time down to about 28000 seconds (from 30000), still
> significantly more than BigDBMS. Additional info inline below.
Although tuning is extremely important, you also have to look at the application itself. I discovered (the hard way)
thatthere's simply no substitute for a bit of redesign/rewriting of the schema and/or SQL statements.
Many of us who "grew up" on Oracle assume that their SQL is standard stuff, and that Oracle's optimizer is "the way
it'sdone." But in fact most Oracle applications are tweaked and tuned to take advantage of Oracle's strengths and
avoidits weaknesses. If you designed an application from the ground up to use Postgres, then migrated to Oracle, you
wouldprobably be equally frustrated by Oracle's poor performance on your Postgres-tuned application.
I don't know if you have access to the application's SQL, or the time to experiment a bit, but unless your schema is
trivaland your SQL is boneheaded simple, you're not going to get equal performance from Postgres until you do some
analysisof your application under real-world conditions, and optimize the problem areas.
In my case, I found just a few specific SQL constructs that, with a bit of tuning, made massive differences in
performance.
Craig