I have two class hierarchies with depths of 4 and 5. They are mapped using table-per-class strategy. Hierarchies have several one-to-many relationships, all in same direction (one hierarchy is always parent, the other always child). The parent side uses lazy fetching (of child collections), and the child side uses eager fetching (of parent). Everything works fine.

One use case shows a list objects from the child side, but requires that parents are also fetched. The query is quite long, over 2 pages in my full-screen xterm, with many inner and outer joins.

All join columns are indexed, and the where clause is empty.

In MySql (4.1 debian, InnoDB tables) this takes unacceptably long to execute. Sizes of tables don't seem to be an issue as the times for 1, 100 and 1000 objects differ by only about 10%. I have used mysql for over 3 years and am quite familiar with it. I have invested significant time in trying to optimize this.

This was the first time i used postgres (version 8.0.4, debian). Default install, no tweaking whatsoever. The same query runs about 5 times as fast as with mysql. I was shocked. I also found out that the execution time depends mostly on object count.

Is it possible that complex joins choke mysql? Has anyone else had a similar experience? Should I keep optimizing mysql, or should I keep postgres?

Best regards,

Keep PostgreSql.
It's a rock solid DBMS with lot more experience on complex queries than MySql.