> Both databases have current statistics, the data is exactly the same, as shown by the results. Oracle finished in
0.85second (850 milliseconds) while PostgreSQL took 2127.345 ms, 2.5 times slower than Oracle. The difference that is
obviousis the access path: Postgres chose nested loops, while Oracle chose merge join and utilized the primary key for
thetable. Postgres, apparently cannot do "fast full scan" of an index and doesn't know how to utilize the primary key
incases like this. However, my gripe is that the optimizer should have selected merge join, just like Oracle did. In
thiscase, nested loops are definitely the wrong choice. Rule based optimizers, the kind of the optimizer that takes
intoconsideration only the structure of the table, usually ends up being dominated by the nested loops method. Nested
loopsmethod usually dominates the OLTP type applications but can really mess up large reports. I am under the
impressionthat Postgres query planner is geared toward the OLTP type of the database. Maybe a new parameter is needed
thatwould somehow shift gears to "data warehouse use", on demand? I have to say, the advantage is still on the side of
Sauron.
You may want to check out http://www.postgresql.org/docs/8.4/static/runtime-config-query.html
If you have a good understanding of your query and how it should be run, you can toggle such settings as
enable_nestloopon demand to see if you get a better result (e.g. SET enable_nestloop TO OFF). Of course, it's a
double-edgedsword and it's far easier to make queries perform more poorly when toggling these settings.
Jon