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