Re: The Two Towers

> Both databases have current statistics, the data is exactly the same, as shown by the results. Oracle finished in 0.85 second (850 milliseconds) while PostgreSQL took 2127.345 ms, 2.5 times slower than Oracle. The difference that is obvious is the access path: Postgres chose nested loops, while Oracle chose merge join and utilized the primary key for the table. Postgres, apparently cannot do "fast full scan" of an index and doesn't know how to utilize the primary key in cases like this. However, my gripe is that the optimizer should have selected merge join, just like Oracle did. In this case, nested loops are definitely the wrong choice. Rule based optimizers, the kind of the optimizer that takes into consideration only the structure of the table, usually ends up being dominated by the nested loops method. Nested loops method usually dominates the OLTP type applications but can really mess up large reports. I am under the impression that Postgres query planner is geared toward the OLTP type of the database. Maybe a new parameter is needed that would 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_nestloop on demand to see if you get a better result (e.g. SET enable_nestloop TO OFF). Of course, it's a double-edged sword and it's far easier to make queries perform more poorly when toggling these settings.

Jon