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) that there'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's done." But in fact most Oracle applications are tweaked and tuned to take advantage of Oracle's strengths and avoid its weaknesses. If you designed an application from the ground up to use Postgres, then migrated to Oracle, you would probably 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 trival and your SQL is boneheaded simple, you're not going to get equal performance from Postgres until you do some analysis of 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
