Greg Stark writes: > > > SELECT a, (SELECT name FROM t2 WHERE t2.f2 = t1.f2) > > > FROM t1 > > > GROUP BY f2 > > > > This doesn't solve the problem. It's the GROUP BY that is doing the > > wrong thing. It's grouping, then aggregating. > > But at least in the form above it will consider using an index on f2, and it > will consider using indexes on t1 and t2 to do the join.
There are 20 rows in t2, so an index actually slows down the join. I had to drop the index on t1.f2, because it was trying to use it instead of simply sorting 20 rows. I've got preliminary results for a number of "hard" queries between oracle and postgres (seconds): PG ORA 0 5 q1 1 0 q2 0 5 q3 2 1 q4 219 7 q5 217 5 q6 79 2 q7 31 1 q8 These are averages of 10 runs of each query. I didn't optimize pctfree, etc., but I did run analyze after the oracle import. One of the reason postgres is faster on the q1-4 is that postgres supports OFFSET/LIMIT, and oracle doesn't. q7 and q8 are the queries that I've referred to recently (avg of group by). q5 and q6 are too complex to discuss here, but the fundamental issue is the order in which postgres decides to do things. The choice for me is clear: the developer time trying to figure out how to make the planner do the "obviously right thing" has been too high with postgres. These tests demonstate to me that for even complex queries, oracle wins for our problem. It looks like we'll be migrating to oracle for this project from these preliminary results. It's not just the planner problems. The customer is more familiar with oracle, and the vacuum performance is another problem. Rob ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly